The If ... Else Statement In SQL Query.. Please Help To Correct Query.. Thank You
Jan 3, 2008
1 SELECT distinct A.EAIndex,
2 A.ChainCode AS ChainCode,
3 A.JobDate,
4 C.BillerName,
5 B.BusinessCode,
6 D.ChainName,
7 A.BillBegDate,
8 A.BillEndDate,
9 A.PostDate,
10 A.BillCount,
11 A.ChAmount,
12 A.PostAmount,
13 A.ProChargeFee,
14 A.BalanceStatus AS BalanceStatus,
15 B.BillerInfoCode AS BillerInfoCode ,
16 CONVERT(varchar(10), A.BalanceDate, 112)BalanceDate,
17 A.AdjustDate,
18 C.BillerCode ,
19 (a.ChAmount- a.ProChargeFee) AS Fee,
20 E.EABillerCode, E.RelAdjustDate
21 FROM ZT_EAccount A
22 Inner Join ZT_BillerInfo B On Right('00000'+Rtrim(Ltrim(A.BillerCode)),5) = Right('00000'+Rtrim(Ltrim(B.BillerInfoCode)),5)
23 Inner Join ZT_Biller C ON B.ParentCode = C.BillerCode
24 Inner Join ZT_Chain D On A.ChainCode = D.ChainCode
25 Inner Join ZT_EAccountAdjust E ON a.EAIndex=E.EAIndex and a.BillerCode=E.BusCode
26 Where A.JobDate BETWEEN '20071001' AND '20071005' AND C.CompanyCode='533'
In line 13
I want to add a Query to make sure if ZT_BillerInfo.Rmflag = 1, if it's =1 then ProChargeFee =0
if ZT_BillerInfo.RmFlag =0 , then ProChargeFee = a.ProChargeFee.
I add this line
�Case B.RmFlag = '1' then 0 else A.ProChargeFee 】
instead of Line 13 (A.ProChargeFee)
but I execute Sql I got error message on the line I have jsut modified..
can you please help me to know why and how to correct it?
DECLARE @fullname nvarchar(50) SET @fullname = (SELECT (OriginalName + ContentType) AS Name FROM Files_Dyn) INSERT INTO Files_Dyn(FullName) VALUES (@fullname) where username = 'user_admin' ...what is the wrong with this query..it is giving 'Incorrect syntax near the keyword 'where'. Please correct me! thanks in advance!
I’ve got a table called tblApplicant_Details with the following fields - Applicant_ID, Application_ID, Net_Income, Loans.
In this table I’ve got a list of people and their income details (Net_Income) and expenses (loans). In some cases there will be 2 applicants with the same application_ID.
What I need to do is select one applicant (Applicant_ID) per application (Application_ID). In the case of 2 applicants for an application, I need to select the person with the highest income (net_income - loans), if both of the applicants have the same income I want the one with the lowest Applicant_ID and if only one person applies then that person.
Below is the code I’ve been using. I’ve noticed that its not always selecting an applicant for each application. I know it’s also very long for what I am trying to do but I was hoping someone would be able to tell me how I can fix it and tidy it up a bit.
SELECT Application_ID, MIN(Applicant_ID) AS Applicant_ID FROM (SELECT DERIVEDTBL.Application_ID, dbo.tblAPPLICANT_DETAILS.Applicant_ID FROM (SELECT MAX(APPLICANT.Net_Income - APPLICANT.Loans) AS Income, APPLICATION.Application_ID FROM dbo.tblAPPLICANT_DETAILS APPLICANT INNER JOIN dbo.tblAPPLICATION_DETAILS APPLICATION ON APPLICANT.Application_ID = APPLICATION.Application_ID GROUP BY APPLICATION.Application_ID) DERIVEDTBL INNER JOIN dbo.tblAPPLICANT_DETAILS ON DERIVEDTBL.Application_ID = dbo.tblAPPLICANT_DETAILS.Application_ID AND DERIVEDTBL.Income = dbo.tblAPPLICANT_DETAILS.Net_Income - Loans) DERIVEDTBL GROUP BY Application_ID
select min(dbo.FS_ItemInventory.Bin) from (SELECT MIN(dbo.FS_LotTrace.LotNumber),dbo.FS_ItemInventory.Bin FROM dbo.FS_LotTrace RIGHT OUTER JOIN dbo.FS_MOHeader AS h INNER JOIN dbo.FS_MOLine AS l ON l.MOHeaderKey = h.MOHeaderKey INNER JOIN dbo.FS_Item AS i ON i.ItemKey = l.ItemKey INNER JOIN dbo.FS_MOLineData ON l.MOLineKey = dbo.FS_MOLineData.MOLineKey LEFT OUTER JOIN dbo.FS_DemandSupply AS ds ON l.MOLineKey = ds.TopLevelDemandSupplyKey LEFT OUTER JOIN dbo.FS_ItemInventory RIGHT OUTER JOIN dbo.FS_Item AS di ON dbo.FS_ItemInventory.ItemKey = di.ItemKey on ds.DemandItemKey = di.ItemKey on dbo.FS_LotTrace.LotTraceKey = dbo.FS_ItemInventory.LotTraceKey where (dbo.FS_ItemInventory.InventoryCategory!='H' or dbo.FS_ItemInventory.InventoryCategory is null) group by di.ItemNumber,dbo.FS_ItemInventory.Bin)
select min(dbo.FS_ItemInventory.Bin) from (SELECT MIN(dbo.FS_LotTrace.LotNumber),dbo.FS_ItemInventory.Bin FROM dbo.FS_LotTrace RIGHT OUTER JOIN dbo.FS_MOHeader AS h INNER JOIN dbo.FS_MOLine AS l ON l.MOHeaderKey = h.MOHeaderKey INNER JOIN dbo.FS_Item AS i ON i.ItemKey = l.ItemKey INNER JOIN dbo.FS_MOLineData ON l.MOLineKey = dbo.FS_MOLineData.MOLineKey LEFT OUTER JOIN dbo.FS_DemandSupply AS ds ON l.MOLineKey = ds.TopLevelDemandSupplyKey LEFT OUTER JOIN dbo.FS_ItemInventory RIGHT OUTER JOIN dbo.FS_Item AS di ON dbo.FS_ItemInventory.ItemKey = di.ItemKey on ds.DemandItemKey = di.ItemKey on dbo.FS_LotTrace.LotTraceKey = dbo.FS_ItemInventory.LotTraceKey where (dbo.FS_ItemInventory.InventoryCategory!='H' or dbo.FS_ItemInventory.InventoryCategory is null) group by di.ItemNumber,dbo.FS_ItemInventory.Bin)
I am new at SQL and am using SQL server express edition and im a bit stuck! I am using ASP.NET and C# in my website which is using sql database back end.
String SQLroom = "SELECT DISTINCT RoomName FROM Room INNER JOIN RoomCalendar ON Room.RoomID = RoomCalendar.RoomID WHERE Capacity = '" + reqCapacity + "' " + " AND NOT ('" + newRoomEnd + "' <= roomStartDateTime OR '" + newRoomStart + "' >= roomEndDateTime) AND (OHP = '" + ohpYesNo + "' AND AV = '" + avYesNo + "') ";
This is my SQL string... what it is trying to do is:
find the room where the capacity is the reqcapacity entered by user and the startdatetime and enddatetime entered by the user are not present in the table for that room capacity and then look at whether the user requires OHP or AV facilities, which are stored in the database as either yes or no values. The problem i am having is with the condition in the sql query... because the user may require an OHP and not AV, but then the room returned "`could" have AV facilities, as it wouldnt make a difference to them if it was there or not, basically, the yes condition has to be satisfied.
Not sure whether i should be using AND, or OR? or a combination.
Hi, I am using SqlCacheDependency with the query notifications of SQL server 2005. It is working nicely with the dbo.owner account but not with my web user account, so I am thinking something is wrong with the permissions. After searching around, it seems that the main permissions to enable are these: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO theAccount GRANT RECEIVE ON QueryNotificationErrorsQueue TO theAccount GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to theAccount GRANT SELECT TO theAccount.
I enabled these and it still doesn't work. When I change my table, my cache is not getting invalidations. Does anyone know what could be going on? Or perhaps how can I trace the problem and get some clues? thanks so much, -tajmahal
Hi, I'm new to SQL and was wondering if there was an easier way to filter data.
I have two tables -
The first table called Names of Companies has a column named: NAMES
NAMES XYZ Company ABC Limited Liability Company ZZZ Corporation KKK Inc. ABC Inc.
The second table called Keywords has a column named: WORDS
WORDS Company Limited
I want to search for all NAMES that contain the WORDS in some form
The results should be:
NAMES XYZ Company ABC Limited Liability Company
Technically, I can get the results I want by manually typing into the SQL statement all the words that appear in the WORDS column.
SELECT * FROM [Names of Companies] WHERE [Names of Companies].Names Like "*Company*" Or ([Names of Companies].Names) Like "*limited*"));
But is there a way that I reference the table Keyword instead of typing into the query statement all the words that appear in the column WORD? I have a lot of words to search for.
I have this statement, sometimes the Dominator can be zero so i tried this if statement but the error i'm getting is:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.(42000,147)
This is the query i'm using: SELECT DBO.THIT_RATIO_DETL.F_DIVISION_NO, Sum(Case When dbo.THIT_RATIO_DETL.STATUS_CD ="B" OR dbo.THIT_RATIO_DETL.STATUS_CD="K" THEN 1 ELSE 0 END) AS BOUND, Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END) as QUOTE,
Sum(Case When dbo.THIT_RATIO_DETL.STATUS_CD ="B" OR dbo.THIT_RATIO_DETL.STATUS_CD="K" THEN 1 ELSE 0 END)*1.0 / If(Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END)= 0 Then 1 else Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END)) as HIT_RATIO FROM dbo.THIT_RATIO_DETL Group by dbo.thit_ratio_detl.F_DIVISION_NO
Not sure if in SQL server i can correct the problem like that, any help is greatly appriciated...
I have a page that is displaying different movie covers. They each have their respective genres stored in several keys (genre0, genre1, genre2). Now I can search within the movies by changing the url variable movies.aspx?genre=action but if I simply go to movies.aspx, I'm wanting all genres to appear. But for me, the page is blank if I don't specify the genre. So I've had to start filling in the genre All for all movies so I can go to movies.aspx?genre=all but I know this can't be necessary. Is it my SQL statement for the datasource causing the problem? Code is in VB. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Movies] WHERE [genre0] = @strGenre OR [genre1] = @strGenre OR [genre2] = @strGenre"> <SelectParameters> <asp:QueryStringParameter Name="strGenre" QueryStringField="genre" /> </SelectParameters> </asp:SqlDataSource>
I want to Add a if .. else statement to make sure if the Data is exist in Table if (exist ) { do a update query} else{ do a Insert Query} I Know how to update and how to insert but how to check if the data is exist in Table and how to add a if ..else to query? thank you very much
I have searched the net for the following but am not too sure how it's done. I am trying to create an if statement within a sql query where based on the results it will stop or continue something like:
count(*) from test_table where test_column = 0 go if result = 0 then ALTER TABLE test_table DROP COLUMN test_column go select into test_table_2 from test_table go
SQL Ver: 2008 (not r2) Problem: The following code returns correct results when moving variable declarations and update statement outside a stored procedure, but fails to return a value other than zero for the "COMPANY TOTAL" records. The "DEPT TOTAL" result works fine both in and outside the sp.This may have to do with handling NULL values since I was getting warning message earlier involving a value being eliminated by an aggregate function involving a NULL. I only got this message when running inside the sp, not when running standalone. I wrapped the values inside the SUM functions with an ISNULL, and now return a zero rather than NULL for the "COMPANY TOTAL" records when running inside SP.All variable values are correct when running.
SQL CODE: DECLARE     @WIPMonthCurrent date = (SELECT TOP 1 WIPMonth FROM budxcWIPMonths WHERE ActiveWIPPeriod = 'Y')   select @WIPMonthCurrent as WIPMonthCurrent   [code]....
I have an ASP form that takes the information that is entered on the form and inserts it into a Microsoft Access database. It works great. In addition to the fields from the form, I also want to add the current date into the InitDate field. How would I modify the SQL query below to insert the current date into the COS database? conn.execute SqlQry Sql = "INSERT INTO COS ([Name of School], [Director of COS], [Address], [City], [State], [zip], [PhoneNumber], " Sql = Sql & "[general_notes], [type], [DEPT], ) " Sql = Sql & "VALUES ('" & m_CompanySchoolName & "', '" & m_FullName & "', '" & m_StreetAddress & "', '" & m_City Sql = Sql & "', '" & m_State & "', '" & m_Zip & "', '" & m_TelephoneNumber & "', '" & m_Message & "', 'COSMETOLOGY', '" Sql = Sql & m_Department & "', '" & m_EmailAddress & "')" response.write Sql response.end conn.execute Sql
I am trying to troubleshoot some high activity on one of my SQL Servers, and when I run sp_who2 'active' I get the current activity, and spid number. I check under Process Info for the spid, and wHEN i double click the spid, I get part of the sql statement run by that trouble spid. Is there a way to get the whole sql statement in Query Analyzer without running SQL Profiler. Thanks in advance for any help.
I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this.
I could use the "decode function" but
I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc..
I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this.
I could use the "decode function" but
I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc..
I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
I have a table called notes, with thousands of rows of "notes" entered by customer services agents. Each row has an account number, date and username columns.
An account can have many notes on thetable.
How can I select the last two notes on the table left for each account? Trying to use select top 2, but of course it's only giving me the top 2 notes for ALL accounts.
I have a C# server application which clients can send arbitrary SQL statements to. These can be absolutely anything - creating tables/views, selecting from tables/views, inserts, updates, deletes, you name it.
There are two return parameters from the server method which executes the SQL - a results set containing the data, and a count of the rows that were updated - (either one or the other should be populated depending on the type of command sent to it). To deal with this, what I planned on doing was (pseudocode follows..):
Unfortunately this doesn't really work, as OpenReaderCursor is able to execute Non Queries (eg. UPDATE/INSERT/DELETEs etc) but doesn't give me a row count, and trying the other way round, ExecuteNonQueryCommand is happy to execute SELECT statements, but I can't then of course return a results set as I don't have access to it.
My question then, if you will excuse the waffle above, is "Is there a simple way of determining if a string containing an SQL statement is a query?" - or will I have to come up with some way of dealing with this in my application code?
Please don't slate the design (ideally I would have two methods on the server, one for queries which returns results and the other for nonqueries which returns an updatecount) but there's nothing I can do, this is how it must be done (the interface was defined long long ago)
Hi all - having a prob trying to figure this one out! It's an accounts aplication and I need to produce a monthly statement for clients which will be mailed automatically at the end of the month...
I have 3 tables (simplified below):
Invoice invoiceid int invoicedate datetime clientid int subtotal decimal(19,2) paid int
Credit creditid int creditdate datetime clientid int subtotal decimal(19,2) paid int
Payments paymentid int paymentdate datetime amount decimal(19,2) allocated int
The report I need to generate has the client's details at the top and then a list of unpaid invoices, credits and unallocated payments - all sorted by date - with the following columns (sample data supplied)
Why couldn't they make SQL syntax error mistakes a little less vague.
Anyway, I was wondering, is it possible to use a set in your case statement?
CASE ( select distinct tbhtg.TrainingBlockHistoryTypeGroupingCd from tblTrainingBlockHistory tbh inner join tblTrainingBlockHistoryType tbht on tbh.TrainingBlockHistoryTypeCd = tbht.TrainingBlockHistoryTypeCd inner join tblTrainingBlockHistoryTypeGrouping tbhtg on tbht.TrainingBlockHistoryTypeGroupingCd = tbhtg.TrainingBlockHistoryTypeGroupingCd where (select dbo.fnTrainingBlockStatus( 1234, getdate())) = tbht.TrainingBlockHistoryTypeCd ) WHEN 'S' then (COUNT(DISTINCT TRD.TrainingBlockHistoryId) = COUNT(DISTINCT SWT.TrainingBlockHistoryId)) end
This is giving me an error on the WHEN statement. The error is "Incorrect syntax near '='" Have no idea how to fix this. But the select statement seems to work, and as far as I can tell, that is how you write a CASE statement.
Also, this CASE statement is inside the HAVING clause - is that going to be a problem?
Hi Group!I am having a problem of using SUM under UPDATE statement. I understandthat SQL does not allow me to use SUM in UPDATE, but unfortunately, Ican not find a way to get around it. Can someone please give me someidea? Thanks a million!--Here is the criteria:Under the same PORTFOLIO_ID, if the ACCOUNT_OPENDATE is in the samemonth as the PORTFOLIO_OPENDATE, then sum the account_openamt for thesame PRODUCT, and update the CHECKING_OPENAMT, SAVINGS_OPENAMT,CD_OPENAMT, etc in table PORTFOLIO. For other accounts opened NOT inthe same month as the PORTFOLIO_OPENDATE, just ignore them.--Here are the tablescreate table portfolio(portfolio_id int,portfolio_opendate smalldatetime,checking_openamt money,savings_openamt money,cd_openamt money)insert into portfolio values(1,'2/15/2005',0,0,0)create table account(portfolio_id int,product varchar(20),account_opendate smalldatetime,account_openamt money)insert into account values(1,'checking','2/15/2005',2000)insert into account values(1,'checking','2/20/2005',3000)insert into account values(1,'savings','2/20/2005',3000)insert into account values(1,'cd','5/15/2005',5000)--Ideal Output--id portfolio_opendatechecking_openamtsavings_openamtcd_openamt1 2/15/2005500030000--Here is my query:update portfolioset checking_openamt=sum(b.account_openamt) --problem appears!from portfolio as ajoin account as bon a.id=b.idand year(a.portfolio_opendate)=year(b.account_opendate )and month(a.portfolio_opendate)=month(b.account_openda te)and product ='checking'--and product='savings'--and product='cd'Thanks again!!
