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.
| Author |
Topic |
|
tojenise
Starting Member
6 Posts |
 Posted - 2006-05-01 : 12:22:37
|
| Hello,I'm having trouble with building my SQL Query in my Dataset and am wondering if this is even possible to do or if I really just have a syntax error somewhere. The IN statement was working before I added the CASE statement. I would actually like to add one more CASE Else statement to this. I am recieving the error : "Incorrect syntax near the keyword 'IN'. I'm using reporting services 2000.Any help is appreciated. Thank You in advance!="SELECT dbo.BI_S7_ACCOUNT.NAME AS Customer, dbo.BI_S7_PLATFORM.PLATFORM_NAME, dbo.BI_S7_MARKET_SEGMENT.BUSINESS_UNIT, dbo.BI_S7_PRODUCT.NAME AS Device, dbo.BI_S7_PRODUCT.FAMILY, dbo.BI_S7_DEVICE.HEALTH_INDICATOR, dbo.BI_S7_DEVICE.COMMENT, dbo.BI_S7_DEVICE.DEVICE_OUTCOME_DATE, dbo.BI_S7_DEVICE.DEVICE_OUTCOME, dbo.BI_S7_DEVICE.PROD_PRICE, dbo.BI_S7_DEVICE.COMMIT_DATE, BI_CONTACT_EMPLOYEE_USER_1.FIRST_NAME AS FSE_FNAME, BI_CONTACT_EMPLOYEE_USER_1.LAST_NAME AS FSE_LNAME, dbo.BI_CONTACT_EMPLOYEE_USER.FIRST_NAME AS FAE_FNAME, dbo.BI_CONTACT_EMPLOYEE_USER.LAST_NAME AS FAE_LNAME, dbo.BI_S7_BOARD.WW_RANK, dbo.BI_S7_BOARD.GEO_RANK, dbo.BI_S7_BOARD.REG_RANK, dbo.BI_S7_BOARD.TOP_OPPORTUNITY_FLAG, dbo.BI_S7_BOARD.ANNUAL_BD_QTY, dbo.BI_S7_BOARD.BOARD_NAME, dbo.BI_S7_BOARD.ROW_ID AS BID, dbo.BI_S7_BOARD.PROTO_DATE, dbo.BI_S7_BOARD.PROD_DATE, dbo.BI_S7_DEVICE.PROD_PRICE * dbo.BI_S7_BOARD.ANNUAL_BD_QTY AS ANNUAL_DOLLARS, dbo.BI_S7_REGION.REGION, dbo.BI_S7_DEVICE.PROD_PRICE * dbo.BI_S7_BOARD.ANNUAL_BD_QTY AS DEVICE_DOLLARS, dbo.BI_S7_PLATFORM.ROW_ID AS PLATFORM_ID FROM dbo.BI_CONTACT_EMPLOYEE_USER INNER JOIN dbo.BI_S7_BOARD_FAE INNER JOIN dbo.BI_POSITION ON dbo.BI_S7_BOARD_FAE.POSITION_ID = dbo.BI_POSITION.ROW_ID ON dbo.BI_CONTACT_EMPLOYEE_USER.ROW_ID = dbo.BI_POSITION.PRIMARY_EMPLOYEE RIGHT OUTER JOIN dbo.BI_S7_BOARD LEFT OUTER JOIN dbo.BI_POSITION BI_POSITION_1 INNER JOIN dbo.BI_S7_BOARD_FSE ON BI_POSITION_1.ROW_ID = dbo.BI_S7_BOARD_FSE.POSITION_ID INNER JOIN dbo.BI_CONTACT_EMPLOYEE_USER BI_CONTACT_EMPLOYEE_USER_1 ON BI_POSITION_1.PRIMARY_EMPLOYEE = BI_CONTACT_EMPLOYEE_USER_1.ROW_ID ON dbo.BI_S7_BOARD.PRIMARY_FSE = dbo.BI_S7_BOARD_FSE.ROW_ID ON dbo.BI_S7_BOARD_FAE.ROW_ID = dbo.BI_S7_BOARD.PRIMARY_FAE LEFT OUTER JOIN dbo.BI_S7_MARKET_SEGMENT ON dbo.BI_S7_BOARD.END_PRODUCT_ID = dbo.BI_S7_MARKET_SEGMENT.ROW_ID LEFT OUTER JOIN dbo.BI_S7_REGION INNER JOIN dbo.BI_S7_ACCOUNT ON dbo.BI_S7_REGION.ROW_ID = dbo.BI_S7_ACCOUNT.REGION_ID ON dbo.BI_S7_BOARD.ACCT_ID = dbo.BI_S7_ACCOUNT.ROW_ID LEFT OUTER JOIN dbo.BI_S7_PLATFORM ON dbo.BI_S7_BOARD.PLATFORM_ID = dbo.BI_S7_PLATFORM.ROW_ID LEFT OUTER JOIN dbo.BI_S7_DEVICE INNER JOIN dbo.BI_S7_PRODUCT ON dbo.BI_S7_DEVICE.PROD_ID = dbo.BI_S7_PRODUCT.ROW_ID ON dbo.BI_S7_BOARD.ROW_ID = dbo.BI_S7_DEVICE.OPTY_ID WHERE CASE WHEN ((" + Parameters!Filter3.Value + ") = 'Account Geography') THEN ((dbo.BI_S7_REGION.GEOGRAPHY IN (" + Parameters!Filter2.Value + ")) AND (dbo.BI_S7_BOARD.GEO_RANK IS NOT NULL) AND (dbo.BI_S7_BOARD.STATUS = N'Active') OR (dbo.BI_S7_REGION.GEOGRAPHY IN (" + Parameters!Filter2.Value + ")) AND (dbo.BI_S7_BOARD.REG_RANK IS NOT NULL) AND (dbo.BI_S7_BOARD.STATUS = N'Active')) ELSE ((dbo.BI_S7_REGION.GEOGRAPHY IN (" + Parameters!Filter2.Value + ")) AND (dbo.BI_S7_BOARD.GEO_RANK IS NOT NULL) AND (dbo.BI_S7_BOARD.STATUS = N'Active') OR (dbo.BI_S7_REGION.GEOGRAPHY IN (" + Parameters!Filter2.Value + ")) AND (dbo.BI_S7_BOARD.REG_RANK IS NOT NULL) AND (dbo.BI_S7_BOARD.STATUS = N'Active')) END ORDER BY CASE WHEN dbo.BI_S7_BOARD.REG_RANK IS NOT NULL THEN dbo.BI_S7_BOARD.REG_RANK ELSE 999 END"Thank You,Jenise |
|
|
|
|
|
|
|
CASE statement in RS 2000 expression