Using the IN statement as parameter in SQL (Add command) 1

[antmoe3]

I am using the IN statement as parameter in SQL using the (Add command). How do I pass these values as parameters?

where A.SELL_STORE = {? Division}
and A.FILL_STORE in (001,002,004,035)

 

[synapsevampire]

If you mean to say that you wish to pass multiple values within a SQL statement, consider for a moment how you might do this using conventional SQL.

Right, very few databases will allow for passing an array so it can't be done without parsing out a string in an SP.

Consider creating a View on the database and expose the field that you need to use for the multi value parameter in the select line.

Your example doesn't have an in statement as a parameter, it has an in statement being used against hard coded values, hence my confusion about your intent.

-k

 

[antmoe3]

When writing and SQL statement I can use the IN command

select dept from computer where dept in ('2',4','6')

Using the Add Command in Crystal, How would I do this if I need to have the same thing using a parameter? Would it be?

select dept from computer where dept in ({?dept})

or

select dept from computer where dept in [{?dept}]

 

[lbass]

It would be:

where dept in {?dept}

...assuming you created the parameter within the command. But SV's point was that you can't easily use multiple value parameters in a command. In your example, when the parameter screen pops up, you would have to enter the values as an array like this:

('2',4','6')

-LB

 

[BobSuruncle]

You can only pass multivalue parameters to a Command file if you use a "Container Report/Subreport" setup.

Create a container report that has your multivalue parameter.
Create a formula, using the "Join" function to concatenate all of the elements together.
eg
@Depts

"'" & Join( {?Dept},"','" ) & "'"

In your subreport, create your Command file with a string parameter.
eg

SELECT *
FROM YourTable
WHERE dept in ( {?SubDept} )

Link Your main report to the subreport uning the @Depts formula from the main report to the parameter in the subreport.

Not pretty, but it works.


Bob Suruncle

 

[lbass]

Bob, I was interested in your method and got it to work, but I'm wondering whether you think this approach has any advantages over creating a command and linking it to a table in a main report with the parameter developed in the main report. In the latter case, I think the link gets processed locally, which is not a plus, but with a subreport you are essentially processing two reports, also.

-LB

 

[BobSuruncle]

LB, you're right about the subreport, but if the container doesn't use a data connection then there's not too much overhead. (placing the subreport in the report footer should still allow the subreport to be fired. If not, then have the container return 1 record from some dummy table)
I was just at a client site where i tried both methods.
The Container/Subreport was far superior in terms of performance as compared to linking a command to the report's tables.
You do lose a couple of things with my method, however.
Drill-down is different; you must first drill to the subreport and then further drill into records in the subreport.
The main report gets no group tree; it will only show correctly when you've drilled to the subreport.
It was, for me, the perfect solution at this client as they had very definite requirements for 3 parameters(One, Many or ALL) and we were forced to use command files(dba wasn't about to create a stored procedure and we had a custom function from Oracle that took a Year as an argument-SQL Expressions don't take parms)
Thanks for the input.


Bob Suruncle

 

[lbass]

Thanks for the explanation (*).

-LB

 

[synapsevampire]

Bob: A Stored Procedure wouldn't work either unless the users created a comma delimited string or some such to pass via a parameter, hence a View makes more sense with multiple value parameters.

I've had numerous clients state that they don't want to create any database objects, but after I sit down with the IT management they ALWAYS decide to do so, and more often they tell the dba to hush and get out of the way.

Annoying lot, many dbas... Many don't seem to care about cost saving efficiencies, they just want to prevent any additional work from coming their way.

Likely the only difference bewteen your Command and a View is that they need to add CREATE VIEW <name> AS to the front of the query, and they need to add this script to whatever DDL or whatever they have for restoring the database.

-k

 

[antmoe3]

Ok. There is not sub reports. I am pasting sql created by another developer in the Add Command. Then I need to replace the multiple values from the IN statement that Crystal will understand. This is what I need.
Using A.FILL_STORE IN {?Store} will not work. Can anyone help.

select A.FILL_QTY
from STORE A
where A.SELL_STORE = {? Division}
and A.FILL_STORE IN (001,002,004,035)

 

[lbass]

The array of possibilities has already been presented to you. If you need to have the parameter within the command, you must create it within the command, and then follow my suggestion from March 8, 7:45.

Or you could create a multiple value parameter in a main report and then use it in the record selection formula to limit the command field A.Fill_Store, but this will slow your report.

-LB