on 01-07-2014 10:02 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
User | Count |
---|---|
94 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.