Multiple values within Command 1

[dasiga222]

Hi

I am using Crystal reports 10
I was retrieving values from the employee table using a Command

The command I am using is
"select * from employee where dept_no ='{?dep_no }'"

{?dep_no } is the parameter , I am using to retrieve the department number

Earlier, I was submitting only one deptno at a time.
Now I need to enter multiple values of {?dep_no }

How can I do it ?
For example I need a query in the command like

"select * from employee where dept_no in ( '01', 02' ....)

How can I modify my command and How can I modify {?dep_no } to do this

Thanks

 

[trc]

Did you ever find a solution for this?

************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->

 

[lbass]

I don't think you can use multiple value parameters within a command, but after writing the command, you can create a parameter the usual way and add it to report->selection formula->record. The downside is that the parameter will be evaluated locally.

-LB

 

[trc]

Thank you lbass.

************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->

 

[Turkbear]

Hi,
another way is to create the list of values as a string containing the parameter values in the needed format for an IN statement:

('23','25','203','234')

Pass this to the command object
select * from employee where dept_no IN {?dept_no}

should resolve to:
select * from employee where dept_no IN ('23','25','203','234')




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Turkbear,

Can you explain where the list of values as a string should be created? And how you would "pass it to the command"? I'm not following you, but am interested in understanding your approach.

-LB

 

[Turkbear]

Hi,
I created the parameter as a string type.
The command I created just as I posted.
( select * from employee where dept_no IN {?dept_no})

When prompted for a discrete value I typed:

('23','24','203','234')

This was the IN value list and its enclosing parens that the command used..
(If Numerical values, do not use the ' )




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Okay, cool (*), I hadn't thought of that. It appears that to use "in" with a string entered as a discrete value in the parameter selection screen ('ABC','DEF'), that in the command, you do not enclose the parameter in '', but to use a discrete parm with one value entered like ABC (no quotes), you do enclose the parameter in '' in the command.

On another note, have you been able to actually use a number parameter in a command? I haven't been able to get that to work. The command does not give an error until the option is selected.

-LB

 

[Turkbear]

Hi,
Yes..I just tried it and it worked fine..

For a string parameter, the use of ' ' around the each of the string values is actually optional as long as the value has no spaces or commas..

for example, if dept_nbr is a character field..

where dept_nbr in (12,14,25) works
as does
where dept_nbr in ('12','14','25')


at least in Oracle with a native connection..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

It was actually a currency field I was having trouble with (First I keep forgetting the {Orders.Order Amount} is a currency in 11.0). But when I create a currency parm, a simple test using a command with the Xtreme database with a clause like:

where Orders.`Order Amount` <= {?Amt} //where {?Amt} is a currency parameter

I get an error "Count field incorrect". Can you see what I am doing wrong?

-LB

 

[lbass]

Just to clarify, when I was testing adding multiple parameters to a command. When I added them piecemeal, closing and running after each one, I would get an error "Count field incorrect". I seem to be able to get a number, currency, and string parameter to work in one command, as long as I add them all at once. Otherwise I get inconsistent results.

-LB

 

[Turkbear]

Hi,
Sorry, never tried a Currency field ( we do not have the samples installed anymore, so no Xtreme) so I do not know why it would be a problem in the Command ( if the Xtreme database uses such a data type, it should work)..







To Paraphrase:"The Help you get is proportional to the Help you give.."