Query works in SQL Server not in Visual Studio

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Query works in SQL Server not in Visual Studio

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-02-29 : 10:32:59

I have SSRS in Visual Studio. I created a query that works fine in SQL Server Management Studio, but when pasted into Visual Studio I get the error "An expression of non-boolean type specified in a context where a condition is expected, near '('.

Here is the query. Can anyone help on why this isn't working? Thanks.

SELECT CASE WHEN MONTH(dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate) = MONTH(GETDATE()) AND YEAR(dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate) = YEAR(GETDATE())
THEN dbo.MAS_CCS_ARO_InvHistoryDetail.SOExtChargeAmount ELSE 0 END AS CurrentMonth,
CASE WHEN SubString(dbo.MAS_CCS_GL_Account.Account,1,3) = '400' THEN 'ALEDO' ELSE ' ' END AS Location,
dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate, dbo.MAS_CCS_ARN_InvHistoryHeader.InvoiceNumber,
dbo.MAS_CCS_AR1_CustomerMaster.CustomerName, dbo.MAS_CCS_ARO_InvHistoryDetail.DetailSeqNumber,
dbo.MAS_CCS_ARO_InvHistoryDetail.LineType, dbo.MAS_CCS_GL_Account.Account, dbo.MAS_CCS_ARO_InvHistoryDetail.SOExtChargeAmount
FROM dbo.MAS_CCS_AR1_CustomerMaster, dbo.MAS_CCS_ARN_InvHistoryHeader, dbo.MAS_CCS_ARO_InvHistoryDetail,
dbo.MAS_CCS_GL_Account
WHERE dbo.MAS_CCS_AR1_CustomerMaster.CustomerNumber = dbo.MAS_CCS_ARN_InvHistoryHeader.CustomerNumber AND
dbo.MAS_CCS_ARN_InvHistoryHeader.InvoiceNumber = dbo.MAS_CCS_ARO_InvHistoryDetail.InvoiceNumber AND
dbo.MAS_CCS_ARO_InvHistoryDetail.SOGLSalesAcct = dbo.MAS_CCS_GL_Account.AccountKey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-29 : 10:55:23

You have given us absolutely no context at all to help you. How can we answer your question if you don't show us any actual Visual Studio code? Also -- you should not be copying and pasting SQL to your code, try using stored procedures, that's the beauty of them -- you leave your SQL code in your SQL database and there are no "copy and paste" errors to worry about.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-29 : 11:09:53

Try this equivalent code

SELECT		CASE
			WHEN DATEDIFF(MONTH, ih.SOTransDate, GETDATE()) = 0 THEN id.SOExtChargeAmount
			ELSE 0
		END AS CurrentMonth,
		CASE
			WHEN a.Account LIKE '400%' THEN 'ALEDO'
			ELSE ' ' END AS Location,
		ih.SOTransDate,
		ih.InvoiceNumber,
		cm.CustomerName,
		id.DetailSeqNumber,
		id.LineType,
		a.Account,
		id.SOExtChargeAmount
FROM		dbo.MAS_CCS_AR1_CustomerMaster AS cm
INNER JOIN	dbo.MAS_CCS_ARN_InvHistoryHeader AS ih ON ih.CustomerNumber = cm.CustomerNumber
INNER JOIN	dbo.MAS_CCS_ARO_InvHistoryDetail  AS id ON id.InvoiceNumber = ih.InvoiceNumber
INNER JOIN	dbo.MAS_CCS_GL_Account AS a ON a.AccountKey = id.SOGLSalesAcct

E 12°55'05.25"
N 56°04'39.16"

osupratt
Posting Yak Master

238 Posts

Posted - 2008-02-29 : 11:36:40

Peso, thank you very much. It all worked great, but on my account I'm looking to pull out a substring of numbers.

Is there a Mid or SubString type function I can use for example:

accountcode = 400-400-000-1234

substring(accountcode, 5, 3) = '400' then 'Aledo'

???

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-29 : 13:40:14

substring(accountcode, 5, 3) = '400' then 'Aledo'

will work great.

E 12°55'05.25"
N 56°04'39.16"

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-02 : 11:06:12

Sorry, probably another 'dumb' question, but is there any code in SQL or Visual Studio for Year to Date (I need InvoiceAmounts where InvoiceDates are from current year to date and previous year to date). I know in Crystal Reports it's simply 'in YeartoDate', but am not sure of SQL. Any help would be greatly appreciated.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-02 : 12:02:50

no equivalent funtion in SQL. You would add the WHERE clause using the appropriate date column and date functions

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources