Using Parameters in Select statement 2

[WarrenTheWindmill]

I have a parameter which prompts the user to input a date. I then want to use that date in the main SQL statement.

I am selecting records to populate a "customers" type main report which has a "transactions" sub report. I only want the Customer name to appear in the main report if he has transactions for the date in the parameter.

My SQL statement includes the following:

WHERE
EXISTS
(Select client_id from ledger where ledger.client_id = clients.client_id and ledger.trans_date =

I then want to insert the results of the parameter. I have tried using the parameter name but can't get anything to work.

Or am I going about this the wrong way? Any suggestions would be much appreciated.

Thanks

 

[kenhamady]

Use the parameter in the Selection formula, and let CR generate the SQL statement.

{date} = {?Param} Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[WarrenTheWindmill]

Hi Ken

Thanks for the response, but putting the ledger date into the select expert doesn't work.

The whole query I am using is:

SELECT 
    "CLIENTS"."CLIENT_ID",
    "CLIENTS"."CLIENT_REF", 
    "CLIENTS"."FORENAMES", 
    "CLIENTS"."SURNAME", 
    "ADDRESSES"."POST_CODE", 
    "ADDRESSES"."ADDR_LINE1", 
    "ADDRESSES"."ADDR_LINE2", 
    "ADDRESSES"."ADDR_LINE3", 
    "ADDRESSES"."ADDR_LINE4", 
    "ADDRESSES"."ADDR_LINE5", 
    "ADDRESSES"."END_DATE" 
FROM
    ADDRESSES,CLIENTS               
WHERE
    EXISTS (Select client_id from trading_ledger where    trading_ledger.client_id = clients.client_id)       
    AND ADDRESSES.SOURCE_TABLE_RC_ID = 205001        
    AND ADDRESSES.SOURCE_ID = CLIENTS.CLIENT_ID
    AND Decode  clients.corresp_addr_type_rc_id,130001,100001, 130002,100002) = Addresses.Addr_Type_Rc_Id
    AND (ADDRESSES.END_DATE) is null                                                                    
ORDER BY
    "CLIENTS"."CLIENT_REF" ASC

The ledger date needs to be inserted into the subquery, so that only clients with records on the selected date will be returned.
If I use the select expert CR simply adds ledger date to the end of the main select.

 

[IdoMillet]

It looks like you could avoid the subquery by doing a simple join to the trading_ledger.

You may have used a subquery to avoid joining each
row from the outer query to multiple rows in the subquery,
but you can achieve the same effect via other means
(VIEW, GROUP BY, MAX, ...).

hth,
- Ido

 

[MalcolmW]

If you want to use the subquery in your SQL statement, then you can just move your SQL statement into a stored proc, and call that, passing a parameter.
See

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf.asp

for details Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net