| Author |
Message |
bujji
Newbie
Joined: 17 Dec 2008
Location: India
Online Status: Offline
Posts: 6
|
   Topic: Urgent: Select query in Crystal Report
Posted: 18 Dec 2008 at 12:02am |
Hi,
I have a prompt which takes the user input as 'OPN' ,'TND','*'.
According to his input i should execute one select query if it is 'OPN'
and if he selects 'TND' i should execute one more select query ...
How can i implement this in Crystal Report 11.0 version?
|
 IP Logged |
|
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 18 Dec 2008 at 12:19am |
Hi,
How is the prompt set ,does it take multiple values or single value......
In your record selection formula you can check for
{table.fieldname} like {?prompt_name}
Cheers
Rahul
|
|
IP Logged |
|
bujji
Newbie
Joined: 17 Dec 2008
Location: India
Online Status: Offline
Posts: 6
|
Posted: 18 Dec 2008 at 12:24am |
I have a drop-down so the user has to select only one value either 'TND' or 'OPN' or '*'.
but how can i mention the select query in this selection formula ?
i wrote somethign like this but am getting error :
(if uppercase ({Command.STATUS}) = 'OP'
then
select distinct ld_leg_id,Status from ld_lge_t where CUR_OPTLSTAT_ID in (300)
else
(if uppercase({Command.STATUS} = 'TND' then
select distinct ld_leg_id,Status from ld_lge_t where CUR_OPTLSTAT_ID in (320)
else
Select DISTINCT ld_leg_id, 'OPN' AS status from LD_LEG_T where CUR_OPTLSTAT_ID =300
UNION
Select DISTINCT ld_leg_id, 'TND' AS status from LD_LEG_T where CUR_OPTLSTAT_ID =320
))
|
|
IP Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 18 Dec 2008 at 12:30am |
Hi
I am not clear what you looking for in the report,Have you used command object ?,can't it be done using tables ,or views......
Please let me know what is the expected output in your report ,also if you could post some sample data ......
Cheers
Rahul
|
|
IP Logged |
|
bujji
Newbie
Joined: 17 Dec 2008
Location: India
Online Status: Offline
Posts: 6
|
Posted: 18 Dec 2008 at 12:38am |
Hi,
I have one prompt in the report as 'Status' which has the option of drop-down and it has values 'OPN' and 'TND' and '*'
if the user selects 'OPN' i have to display all the loads which have the value as 300 for the column CUR_OPTLSTAT_ID in the table 'ld_leg_t '.
the select query for this will be "select distinct ld_leg_id,Status from ld_lge_t where CUR_OPTLSTAT_ID in (300)"
similarly for 'TND' i have to display all the loads which have the value as 320 for the column CUR_OPTLSTAT_ID in the table 'ld_leg_t '.the select query for this will be "select distinct ld_leg_id,Status from ld_leg_t where CUR_OPTLSTAT_ID in (320)"
similarly for '*' i have to display all the loads which have the value as 320,300 for the column CUR_OPTLSTAT_ID in the table 'ld_leg_t '.the select query for this will be "select distinct ld_leg_id,Status from ld_leg_t where CUR_OPTLSTAT_ID in (320,300)"
|
|
IP Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 18 Dec 2008 at 12:56am |
Hi
Have you created command object and coded IF else in it ,if yes remove the if else part.
Open your report, go to record selection formula , then use the code below
if {?My Parameter} = 'OPN' then CUR_OPTLSTAT_ID in (300)
ELSE
IF
{?My Parameter} = 'TND' then CUR_OPTLSTAT_ID in (320)
else
{CUR_OPTLSTAT_ID} Like {?My Parameter}
Cheers
Rahul
|
|
IP Logged |
|
bujji
Newbie
Joined: 17 Dec 2008
Location: India
Online Status: Offline
Posts: 6
|
Posted: 18 Dec 2008 at 1:19am |
Hi i tried ur way,
but when am trying to save it it's displaying "A number range is required here" for (300).
|
|
IP Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 18 Dec 2008 at 1:25am |
Hi
Sorry my mistake try this
if {?My Parameter} = 'OPN' then CUR_OPTLSTAT_ID in [300] ELSE IF
{?My Parameter} = 'TND' then CUR_OPTLSTAT_ID in [320]
else
{CUR_OPTLSTAT_ID} Like {?My Parameter}
Does the query work with * parameter value
Cheers
Rahul
Edited by rahulwalawalkar - 18 Dec 2008 at 1:26am
|
|
IP Logged |
|
bujji
Newbie
Joined: 17 Dec 2008
Location: India
Online Status: Offline
Posts: 6
|
Posted: 18 Dec 2008 at 3:12am |
Hi,
I'm not able to fetch any records for any value  |
|
IP Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 18 Dec 2008 at 3:39am |
Hi
email me your report and some sample data in xls sheet
cheers
rahul
|
|
IP Logged |
|
|
|
