(CR8.5) Parameters and SQL Queries

[Gameboy9]

I'm trying to use parameters within an SQL Query... but I'm not being very successful. The SQL query below is very complex, which is why I'm trying to do this. Should I even try a different method, or is there a way to get parameters in the SQL?

Thanks for any help you have in advance.

(I apologize if this is in the wrong forum... I can see this being in forum 1 too...)

SELECT hrpersnl.`p_empno`, hrpersnl.`p_fname`, hrpersnl.`p_lasthire`, hrpersnl.`p_level2`, hrpersnl.`p_level3`, hrpersnl.`p_lname`, hrpersnl.`p_misc1`, hrpersnl.`p_misc2`, tkmul001.`u_trndat`, tkmul001.`u_trnexp`, tkmul001.`u_trntyp`

FROM hrpersnl LEFT JOIN tkmul001 ON hrpersnl.p_empno = tkmul001.u_empno AND tkmul001.u_trntyp = "JC20"

WHERE hrpersnl.p_empno NOT IN (SELECT DISTINCT tkmul001.u_empno FROM tkmul001 WHERE tkmul001.u_trntyp = 'JC20' AND tkmul001.u_trnexp >= {^2004-09-03}) AND (hrpersnl.p_misc2 = 'EI' OR hrpersnl.p_misc2 = 'E' OR hrpersnl.p_misc2 = 'AEI' OR hrpersnl.p_misc2 = 'AE') AND hrpersnl.p_active = 'A' AND (hrpersnl.p_jobcode = '130' OR hrpersnl.p_jobcode = '210' OR hrpersnl.p_jobcode = '212' OR hrpersnl.p_jobcode = '215' OR hrpersnl.p_jobcode = '218' OR hrpersnl.p_jobcode = '216')

ORDER BY hrpersnl.p_level2 ASC, hrpersnl.p_lname ASC, hrpersnl.p_fname ASC, tkmul001.u_trntyp ASC

*** WHERE I WANT PARAMETERS: tkmul001.u_trnexp - but I can't do it using ordinary measures.

 

[bobpv]

Why not just pass the data from SQL to Crystal and then create a parameter inside Crystal to filter the data? Are you returning a HUGE amount of information?

Parameters will work fine with Crystal, I use them all the time when creating Stored Procedures and basing my report on them.

Example

Create Procedure XXXX
@parameter name numeric = 0 (or CHAR(X) or what you wish your parameter you return)
as
Select statement
Where
fieldname = @parameter name

Then Crystal will see the parameter when opening the report, and promt you for an entry. This is a simplified explanation, I do hope it helps?

Bob
Robert Pevey and Associates

http://www.peveyweb.com