passing Parameter to QUERY?

[seba]

Hi

Im trying to pass a user entered parameter to the WHERE clause of my SQL QUERY statement, can someone tell me how to do this?

For example, instead of the date entered below , I wish for "'20071010'" to be a user prompted value. How would I do this?

SELECT
SALES_HISTORY_DETAIL."SHD_DESCRIPTION",
FROM
SALES_HISTORY_DETAIL
WHERE
SALES_HISTORY_DETAIL.INVOICE_DATE = "20071010"


Thanks!

[Besoup]

umm you're going to have to tell use what you're using for your reporting and what you would like to pass your variable from...

[seba]

hi

Im using CR 8.5

Im trying to pass user prompted parameters to the SQL query somehow but Im not having any luck

What Im doing so far is:

1)Creating a Parameter Field called

 userdate

2)Using the Select Expert to specify which column is to equal the user entered paramater

{SALES_HISTORY_DETAIL.INVOICE_DATE} = {?userdate}

so far this isnt working for me.

THANKS FOR ANY HELP!

[brucevde]

so far this isnt working for me.

Can you be more specific?

Is the parameter defined as Date or DateTime or ?
Is the Invoice_Date field defined as Datetime or a string?

Verify the SQL statement that CR is using, Database menu -> Show SQL Query.

[seba]

Hi Bruce.

the parameter "userdate" is a string as is the INVOICE_DATE field in the DB so they both match....

Bruce I must ask because I dont know. Once I do a Select Expert entry, will this show up in the SHOW SQL QUERY window? so for example if my SELECT EXPERT entry is

{SALES_HISTORY_DETAIL.INVOICE_DATE} = {?userdate}


will there be anything to this effect in the SQL QUERY?

Because when I do anything to the SELECT EXPERT nothing shows up in the SQL QUERY WINDOW (except what I had initially)

Or does CR first execute the SQL QUERY and THEN it executes the SELECT EXPERT entry?

Thank You

[brucevde]

It depends on whether or not the option "Use Index or Server for speed". If that option is on, then Crystal will generate a Where clause (if it can) from your selection formula.

I always have the option "Perform Grouping on Server" on and the option "Always Sort Locally" off as well.

[seba]

BRUCE, can I put in the SQL QUERY window a where clause that accepts a parameter my self?

[brucevde]

No. The SQL Query window shows you the generated sql statement that is going to be sent to the database server. You can modify the Where clause with hardcoded values but not parameterized values.

[seba]

this is so weird, when I hard code the WHERE CLAUSE my self by putting the date in the SQL QUERY window

WHERE
SALES_HISTORY_DETAIL.INVOICE_DATE = '20071010'

everything works fine, the minute I take that WHERE CLAUSE out and try to do it with the user parameter it crashes the CR app....

[seba]

BRUCE,

If you don't mind, can you tell me if I'm doing the right steps in creating a user parameter?

1) I take OUT my hard coded WHERE statement from the SQL QUERY window

SALES_HISTORY_DETAIL.INVOICE_DATE = '20071010'

2) I create a user parameter called "userdata" which is just a string
3) I go to the SELECT EXPERT and select the INVOICE_DATE column and use the formula expert to create the following formula:

{SALES_HISTORY_DETAIL.INVOICE_DATE} = {?userdate}


I try to run this report, i enter the user prompted parameter 20071010 and it just hangs......