May 1, 2006 at 10:39 am
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
May 1, 2006 at 11:47 am
Your where clause is not correct.
you cannot have different where clauses inside a case statement.
I cannot dicifer what your trying to do but it looks the same.
To me it looks like your trying to do this
declare @test-2 int
set @test-2 = 1
select *
from sysobjects
where case @test-2
when 1
then type = 'p'
else name like 'sys%'
end
Here the where clause does not have an expression
This will work
declare @test-2 int
set @test-2 = 1
select *
from sysobjects
where type = (case @test-2
when 1
then 'p'
when 2
then 'fn'
else 'u'
end)
May 2, 2006 at 7:43 am
When you use a case statement in the where clause I believe you have to compare its returned value to something or else you end up with something like this:
select * from somewhere where 1
I use a little trick like this
select * from somewhere where 1 = case myvalue when 1 then 1 when 2 then 2 when 3 then 3 else null end
May 2, 2006 at 12:32 pm
Thanks Ray M and Jeremy,
It looks like I can only return a value when using a CASE statement within my where clause. What if my where clause is different depending upon the variable is being passed. For instance:
Select * from table where
CASE @variable
WHEN 'Geography' THEN table.geography IN (" + paremeters!variable.value + ")
WHEN 'Region' THEN table.region IN (" + paremeters!variable.value + ")
ELSE 'Individual' THEN table.user = (" + paremeters!variable.value + ") END
I'm also having some trouble combining the CASE statement with the "IN" statement. Is this possible to do? If not, can I use some other method of performing this conditional within my expression?
Thank You!
Jenise
May 3, 2006 at 12:54 pm
Jenise,
It looks like you are combining things like you mentioned. First of all, your ELSE statement in the above example seems correct - you don't want to add the 'IN' part because it may not be in that variable. I think your construct is not quite right...
I think it should look something more like this:
....THEN table.user = parameters!variable.value END
Are you trying to put the delimiters around value that you're assigning? If so, then you'd need to do it a little differently.... THEN table.user = '"' + parameters!variable.value + '"'.
Is there a reason why you need to do the IN statement in there? What exactly are you trying to accomplish with that? If you are trying to do what I think you're doing, you may want to structure it differently:
CASE @variable
....
WHEN 'Region' THEN
If parameters!variable.value in ('Northeast','Southeast','Southwest') then table.region = parameters!variable.value
else
table.region = 'Undefined'
End
....
I THINK this will work in RS, but haven't tested it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply