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?
thank you very much
View 4 Replies
ADVERTISEMENT
Feb 26, 2008
Hi all,
I have written a query which is not producing the correct ouput.
Please help me to solve the query issue.
my query goes like this:-
SELECT ResourceInformation.PreferredFirstName, ResourceInformation.PreferredLastName, ResourceInformation.CellPhone,
ResourceInformation.HomePhone, ResourceInformation.HomeAddressLine1, ResourceInformation.HomeAddressLine2,
ResourceInformation.HomeAddressState,ResourceInformation.HomeAddressCity,ResourceInformation.HomeAddressZIP,
ResourceInformation.HomeAddressCountry, EmploymentInformation.ArrangementType, EmploymentInformation.PracticeGroup, Projects.Recruiter,
Projects.AccountManager, Projects.TEPOApproved, Projects.BillRate, Projects.Expenses, Projects.ClientProjMgr,
Projects.ClientAddressLine1,Projects.ClientAddressLine2,Projects.ClientAddressCity,Projects.ClientAddressZIP,Projects.ClientAddressState,
Projects.ClientAddressCountry,Projects.LabourCategoryNotes, Projects.Client, Projects.EndClient, Projects.ProjectStartDate,
Projects.DrugScreenRequired, Projects.SkillSet, Projects.AribaProposal, Projects.Sourcer, Projects.ProjectEndDate, Subtier.SubtierCompany,
Subtier.SubtierContactName, Subtier.SubtierAddressLine1,Subtier.SubtierAddressLine2,Subtier.SubtierAddressCity,Subtier.SubtierAddressState,
Subtier.SubtierAddressZIP,Subtier.SubtierAddressCountry, Subtier.SubtierEmail, Subtier.SubtierPhone, Subtier.TaxID,
Subtier.SubtierVendorPassthrough, Subtier.ClientPassthrough, Compensation.PaymentTerms
FROM ResourceInformation INNER JOIN
EmploymentInformation ON ResourceInformation.ResourceID = EmploymentInformation.ResourceID CROSS JOIN
Projects CROSS JOIN
Subtier CROSS JOIN
Compensation
WHERE (Projects.EmploymentID = 23) AND (ResourceInformation.ResourceID = 23)
View 1 Replies
View Related
Sep 29, 2007
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!
View 10 Replies
View Related
Mar 2, 2006
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
Thanks
View 3 Replies
View Related
Jun 2, 2008
hi!
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)
when i tried this query,
i get a error like
Incorrect syntax near ')'
pls provide the correct query for this.
reg,
s.srini
View 3 Replies
View Related
Jun 2, 2008
hi!
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)
when i tried this query,
i get a error like
Incorrect syntax near ')'
pls provide the correct query for this.
reg,
s.srini
View 1 Replies
View Related
Feb 6, 2006
Hello.
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.
Any ideas gratefully appreciated....
Sandy
View 2 Replies
View Related
Jan 3, 2008
I'm using AVG in a simple query but the results are being returned as INTEGERs and not the correct DOUBLE form.
AVG is being used on integer fields in a .mdf database eg: "SELECT AVG(intField) AS Results FROM myDB"
Thanks,
Geoff
View 1 Replies
View Related
Nov 10, 2003
I have the following .bat file that I call to register users to my MSDE instance...
echo osql -E -S %COMPUTERNAME%OfficiumInstance -Q "sp_grantlogin '" + %COMPUTERNAME% + "ASPNET'"
echo osql -E -S %COMPUTERNAME%OfficiumInstance -d Officium -Q "sp_grantdbaccess '" + %COMPUTERNAME% + "ASPNET'"
echo osql -E -S %COMPUTERNAME%OfficiumInstance -d Officium -Q "sp_addrolemember 'db_owner', '" + %COMPUTERNAME% + "ASPNET'"
My question is how do I correctly append %COMPUTERNAME% as a string to
I have tried using + signs...
"sp_grantlogin '" + %COMPUTERNAME% + "ASPNET'"
But that doesn't work.
I have looked for .bat file tutorials on the web but can't find the correct way to do this.
Greg
View 1 Replies
View Related
Jul 5, 2007
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
View 3 Replies
View Related
May 22, 2015
DECLARE @DBEngineLogin VARCHAR(100)
DECLARE @AgentLogin VARCHAR(100)
EXECUTE master.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEMCurrentControlSetServicesMSSQLServer',
@value_name = N'ObjectName',
@value = @DBEngineLogin OUTPUT
[Code] ....
Query to get the exact servername ( do not want to use @@servername), either need to use registry to read or using DMV...
View 3 Replies
View Related
Feb 27, 2007
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.
Can anyone recommend a better way to do this?
Thanks for all your help!
View 8 Replies
View Related
May 2, 2007
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...
View 8 Replies
View Related
Nov 16, 2007
CASE When FundName Is Null Then ShortName
When pf.FundId = 0 Then FundName = Loans
ELSE FundName END as FundNames,
Regards
Karen
View 8 Replies
View Related
Apr 6, 2007
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>
<asp:DataList ID="DataList1" runat="server" DataKeyField="movieID" DataSourceID="SqlDataSource1" RepeatColumns="5" RepeatDirection="Horizontal" CellPadding="5" BorderStyle="None" BorderWidth="0px">
<ItemTemplate>
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "movie.aspx?id=" & Eval("movieID") %>'>
<asp:Image ID="Image1" runat="server" ImageUrl='<%# Eval("imageURL", "{0}") %>' BorderStyle="None" /><br />
</asp:HyperLink>
</ItemTemplate>
</asp:DataList>
View 2 Replies
View Related
Nov 23, 2004
I am using @strsql to construct the where condition for a select query.
Can you please correct my syntax.
DECLARE @strsql nvarchar(2000)
IF @ProgNO <> '' then
strsql = WHERE ProgNO = @ProgNO
end if
If @ProjNO <> '' then
if strsql <> '' then
strsql = strsql & " and ProjNO =@ProjNO
ELSE
strsql = wHERE ProjNO =@ProjNO
END IF
END IF
Thank you very much.
View 9 Replies
View Related
Oct 15, 2007
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
View 9 Replies
View Related
Jan 26, 2006
In access you can use an imediate if statement in the query. can you do the same thing in SQL server?
View 3 Replies
View Related
Aug 27, 2004
Hi all;
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
and so on and so forth
any will help will be apprecated
thanks
View 3 Replies
View Related
May 1, 2015
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]....
View 10 Replies
View Related
Oct 10, 2007
Hello! I have this stored procedure:ALTER PROCEDURE [dbo].[spAddToFavorites]
@Userid uniqueidentifier OUTPUT,
@KeyID uniqueidentifier
ASINSERT INTO Associations (Userid,BusinessID) values (@Userid, (SELECT Userid from UserProfile WHERE KeyID= @KeyID))
RETURN
But it says that I can't do it like that. I need to select that userid based on the keyid I am gonna pass in. Any help on this appreciated.
View 4 Replies
View Related
Jan 28, 2008
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
View 14 Replies
View Related
Jun 29, 2005
I have a column in the database that stored moduleId that are seperated by '|' (pipes). For Example: '527|343|454'
I need to add a where clause to a query that pulls the data based on a
ModuleId. For Example: select * from table where 527 in [column above]
Does anyone know how I can do this in a query? Normally I could
use an IN statement, ex: select * from table where 527 in (527,343,454)
How can I get the column in that format?
Thanks for the help in advance,
KM
View 2 Replies
View Related
Jul 16, 2004
Hi,
The below query has some problem :
update (table1 inner join table2 on table1.sno = table2.sno) inner join table3 on table2.sno1 = table3.sno set table1.sno1 = 2
SQL Server 2000, throws a syntax error :
'Incorrect syntax near keyword '(' ' on executing the above statement.
Please advice 'what is the error in the above statement ? '
Thanks,
Sam
View 2 Replies
View Related
Jun 1, 2004
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.
View 3 Replies
View Related
Nov 18, 2014
How to write query with PRINT Statement for below query.
WITH CTE1 AS (SELECT * FROM table1),
CTE2 AS (SELECT * FROM table2),
CTE3 AS (SELECT * FROM table3)
In between to use PRINT statement seeing where my current query is.
So once we process "SEELCT * FROM table1" and gets completed I want to print as "Table table1 completed".
View 1 Replies
View Related
Jun 7, 2008
Hello,
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?
View 1 Replies
View Related
Jun 7, 2008
Hello,
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?
View 1 Replies
View Related
Oct 4, 2006
Hi,
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.
Please help.
Thanks very much.
View 2 Replies
View Related
Jul 27, 2007
Hi,
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..):
try
{
reader = OpenReaderCursor(statement)
}
catch
{
updatedCount = ExecuteNonQueryCommand(statement)
}
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)
Thanks for your time, all ideas appreciated
Jim
View 1 Replies
View Related
Feb 27, 2008
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)
TYPE|DATE|OUR REF|CREDIT|DEBIT
Invoice | 01-mar-2008 | Inv01 | | £100.00
Credit | 02-mar-2008 | Cre23 | £50 |
Payment | 03-mar-2008 | P04 | £40 |
Would be really grateful for any help!
Thanks in advance,
Stephen.
View 3 Replies
View Related
Mar 4, 2008
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?
View 3 Replies
View Related
Feb 15, 2006
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!!
View 3 Replies
View Related