multiple parameter in crystal reports(CAse in SQL)

[Former Member]

Hi All,

I am using add command in crystal report

and

Query is

select a.text_value, s.user_sampleid,ns.study  from naiv_sample_attributes a inner join nai_samples s

on s.sample_id=a.sample_id

inner join nai_results r on s.sample_id=r.sample_id

inner join nai_submissions ns on

ns.submission_id=s.submission_id     

where a.name='Date Received'

and

  to_date(a.text_value,'DD-MON-yyyy HH24:mi:ss') between

  to_date('01-APR-2013 08:53:22','DD-MON-yyyy HH24:mi:ss')

and to_date('30-APR-2013 08:53:25','DD-MON-yyyy HH24:mi:ss')

and r.Status in {&Status}

now Status parameter contains 3 values 'COMPLETE' , 'SUSPECT' and COMPLETE/ SUSPECT so if i select COMPLETE/ SUSPECT it should use COMPLETE and if I select SUSPECT it should take SUSPECT ,i need it to write in SQL only (i know we can do this through formula).How to write this by using case? if so please explain me

thanks

[JWiseman · Accepted solution]

hi Venkatesh,

you shouldn't require a case statement for this as you're using a prompt / parameter to define the status in the where clause.

you should have created a parameter inside the command as opposed to using an on-report parameter. once you have that command parameter you change the where clause last line to

and r.Status = '{?Status}' or '{?Status}' = 'COMPLETE/SUSPECT'

i'm assuming that if the end user selects complete/suspect that you want to show both complete & suspect...if that's not correct, please let me know what should be returned.

cheers,

jamie

[Former Member · Accepted solution]

Hi,

there is no COMPLETE/SUSPECT in DB ...

if the user selcts  COMPLETE/SUSPECT he should have only COMPLETE....

If I write r.Status = '{?Status}' and enter '{?Status}= COMPLETE/SUSPECT it wouldn't return me anything. as it is not in my db

[JWiseman · Accepted solution]

okay...try something like this...note that this is sap hana sql so your syntax may vary...please consult a forum or online help for your database if this doesn't work...

and r.Status =

  CASE '{?status}'

  WHEN 'COMPLETE/SUSPECT'  THEN 'COMPLETE'

  ELSE  '{?status}'

  END

[Former Member · Accepted solution]

Hi,

i am writing the query as

r.status =

  CASE

WHEN '{?status}'= 'COMPLETE/SUSPECT'  THEN 'COMPLETE'

  ELSE  '{?status}'

  END

so if i write 'COMPLETE/SUSPECT' then 'complete' will be returned and my doubt is ; is tis the same as r.status ='COMPLETE'.

Thnkx for replying

[JWiseman · Accepted solution]

hi Venkatesh,

what is your database? (oracle, sql server?) perhaps there's someone out there with that db type that can verify the case syntax.

however, looking at it though it does look like the following logic (record selection style) should be occuring...

IF  {?status} = 'COMPLETE/SUSPECT'

     THEN r.status = 'COMPLETE'

     ELSE  r.status = {?status}

are you getting an error in query or are you not getting the results you need? and if so what is the full command object / query that you're using?

-jamie

[Former Member · Accepted solution]

Hi,

I am using Oracle 10g CR 2008,

Record selection formula  is correct.

I am not getting any error but not getting correct results.it giving me everything .

[JWiseman · Accepted solution]

thanks for the info. what is the complete query / command that you are using for the report after you've finished the where clause?

the above case statement should be in the where clause. once you've written the command with any filters in the where clause, try to avoid using a Record Selection formula on the report for performance reasons. the syntax that i posted above was just as a reference.

[JWiseman · Accepted solution]

Venkatesh,

you can always move away from a case statement if you want to...i.e. change the last part of the where clause to

AND

(r.Status = '{?status}'  OR  '{?status}' =  'COMPLETE/SUSPECT'  AND r.Status = 'COMPLETE')