Hi All, When I try to write a CASE statement in SSRS 2008 R2 Query Designer then it is giving me error:- Error in list of function arguments: '(' not recognized. Error in list of function arguments: ')' not recognized. Error in list of function arguments: 'WHERE' not recognized. Unable to parse query text. Below is my CASE statement, the same syntax is running perfectly in Toad Oracle:- case when (g.CODE='GG') then ya.AMOUNT else 0 end as Amount ("g" and "ya" are alias name of the table) If I remove the above line from the query then it is running well in SSRS designer, its only this CASE statement which is giving me error. Any idea.? Thanks,

Try removing the braces case when g.CODE='GG' then ya.AMOUNT else 0 end as Amount

Hi I already tried that removing brackets but then it complains about "g" and "ya" saying its not recognized, same as paranthesis. Actually, this one is relatively simple CASE statement that I have. But, I have few more CASE statemens also to write that involves little more conditions within it so its not good idea if I skip brackets. Since I have ANDs/ORs within CASE statements, it will require brackets. Any idea .?

Please post the complete query.

Here is the query (CASE statements marked in <bold> which if I remove then query runs perfectly) :- SELECT BA.REGION_CODE, BA.REGION_NAME, T.YEAR_ID, BA.BUSINESS_AREA_NAME || ' - ' || BA.BUSINESS_AREA_CODE AS BUSINESS_AREA, TO_CHAR(CP.COUNTRY_PROGRAMME_START_DATE, 'yyyy') AS STARTYEAR, TO_CHAR(CP.COUNTRY_PROGRAMME_END_DATE, 'yyyy') AS ENDYEAR, YA.ALLOCATION_AMT, YA.COMMITMENT_AMT, YA.EXPENDITURE_AMT, G.FUND_TYPE_CODE, G.FUND_ISSUE_YEAR, G.GRANT_REF, G.FUNDING_SUB_CATEGORY_CODE, G.DONOR_CODE, case when (g.FUND_TYPE_CODE='GG') then ya.ALLOCATION_AMT else 0 end as RR_PSD, case when ((g.FUND_ISSUE_YEAR < '2008' and (g.FUND_TYPE_CODE='GP' and substr(g.GRANT_REF,5,2)='80') or (g.GRANT_REF between 'GP068145' and 'GP068171') or (g.FUND_TYPE_CODE='GS')) OR (g.FUND_ISSUE_YEAR >= '2008' and g.FUND_TYPE_CODE='GS')) then ya.ALLOCATION_AMT else 0 end as RR_Set_Aside FROM VISIONRPT.YEARLY_AGGREGATE YA, VISIONRPT.D_COUNTRY_PROGRAMME CP, VISIONRPT.D_BUSINESS_AREA BA, VISIONRPT.D_GRANT_MASTER G, VISIONRPT.D_TIME T WHERE YA.COUNTRY_PROGRAMME_SKEY = CP.COUNTRY_PROGRAMME_SKEY AND YA.BUSINESS_AREA_SKEY = BA.BUSINESS_AREA_SKEY AND YA.GRANT_MASTER_SKEY = G.GRANT_MASTER_SKEY AND YA.TIME_SKEY = T.TIME_SKEY AND (CP.CURRENT_FLAG = 'Y') AND (BA.REGION_CODE IN (:p_region)) AND (T.YEAR_ID = :p_year)

Kindly reply I have posted the query.

Not Sure about the error. But can you try the below Case statement case when (g.FUND_TYPE_CODE='GG') then ya.ALLOCATION_AMT else 0 end as RR_PSD, case when ( ( (g.FUND_ISSUE_YEAR < '2008') and (g.FUND_TYPE_CODE='GP' and substr(g.GRANT_REF,5,2)='80') or (g.GRANT_REF between 'GP068145' and 'GP068171') or (g.FUND_TYPE_CODE='GS') ) OR (g.FUND_ISSUE_YEAR >= '2008' and g.FUND_TYPE_CODE='GS') ) then ya.ALLOCATION_AMT else 0 end as RR_Set_Aside If still error's out , i suggest you to add line by line code to the CASE statement and check the point WHERE it errors out.

Hi I think I cannot change the code for CASE structure..i.e. it will give different result in that case (the brackets, ANDs, ORs are important here) Also, first I added only very first CASE statement only which is simpler one and over there only it started giving that errors.

I have googled a bit on the error and it seems the designer is unable to evaluate the query as it finds a syntax error in it. Can you try wrinting the query as below with exact line breaks... ? It is weird try though. But trying to figure out why it says a syntax error ? case when g.FUND_TYPE_CODE='GG' then ya.ALLOCATION_AMT else 0 end as RR_PSD

Hi, I think I got resolution for this. 1) Instead of running query in SSRS query designer , run it in "Edit As Text" mode . Over there it run correctly. 2) This reason is because of one of my parameter (:p_region) i.e. if i remove this parameter then it runs perfect. but in Oracle toad it is running correctly even with this parameter. So, in a nutshell, there is nothing wrong with the CASE syntaxes :) Such complex CASE structures should be run in always Edit As Text mode, that's my observation. Thanks. Marking this as answer.