Hi Steve,

I am currently working in Crystal Reports SQL Designer to generate a query with the data I need for the reports. The interesting points I have found are you need to understand when to use Transact SQL and Crystal Reports SQL commands. Enclosed is an example of what I mean. Pay attention to the "{?lcplnid}" this allows the user to entered Plan identification number. The "{}" are required for Crystal Reports SQL Designer. The field names have to wrapped in "" a Crystal Reports SQL Designer requirement. If you have a field which will get a new name (ex. APSENR_2.FNAME as First_Name) then no quotes are required around the field name.
Also The field values have to be in ''(ex. APSENR."RELCD" = '1').

Two books I am using are "Understanding the new SQL a complete guide" by Jim Melton and Alan R. Simon (a general understanding of SQL 92 commands) and the book with the best example come from "Advance Transact-SQL for SQL Server 2000" by Itzik Ben-Gan and Tom Moreau, PH.D. . The SQL 7.0 book by Karen Delaney looks like a good book but I have not purchased it yet (lack of money in the book budget).

I hope this helps.

Andy Eggers

Example
SELECT DISTINCT
APSENR."PLNID", APSENR."CARDID", APSENR."LNAME", APSENR."RELCD",
APSENR_2.CARDID as Card_ID, APSENR_2."PERSON", APSENR_2.FNAME as First_Name,
APSENR_2.MNAME as Middle_Name, APSENR_2.LNAME as Last_Name, APSENR_2."DOB", APSENR_2."SEX",
APSENR_2."RELCD", APSENR_2."FLEX1", APSENR_2."FLEX2",
APSENR_2."ELGOVER",APSENR_2."EMPCD",APSPLN.NAME as Plan_Name,APSENR_2."TRMDT",
APSPLN.PLNID as Plan_id, APSPLN."STUDAGE", APSPLN."CHILDAGE", DateDiff (YYYY, APSENR_2.DOB, GETDATE() ) as AGE,
CASE
WHEN APSENR_2."ELGOVER" = '4' AND CAST(DateDiff (YYYY, APSENR_2.DOB, GETDATE() ) as numeric) > CAST( APSPLN."STUDAGE" as numeric)Then 'Over Age Student '
WHEN APSENR_2."RELCD" = '3' AND APSENR_2."ELGOVER" = '3' AND CAST(DateDiff (YYYY, APSENR_2.DOB, GETDATE() ) as numeric) > CAST( APSPLN."CHILDAGE" as numeric)Then 'Over Age Dependent '
WHEN APSENR_2."RELCD" = '3' AND APSENR_2."ELGOVER" ='4' AND CAST(DateDiff (YYYY, APSENR_2.DOB, GETDATE() ) as numeric) < CAST( APSPLN."STUDAGE" as numeric) THEN 'Student'
WHEN APSENR_2."EMPCD" = '05' THEN 'COBRA'
ELSE ' '
END AS "STATUS"
FROM
{oj ("UPSRxData"."dbo"."APSENR" APSENR INNER JOIN "UPSRxData"."dbo"."APSENR" APSENR_2 ON APSENR."CARDID" = APSENR_2."CARDID")
INNER JOIN "UPSRxData"."dbo"."APSPLN" APSPLN ON APSENR_2."PLNID" = APSPLN."PLNID"}
WHERE
APSENR."RELCD" = '1' AND APSENR."PLNID" = {?lcplnid} AND APSENR_2."PLNID" = {?lcplnid} AND APSENR_2."TRMDT" IS NULL
ORDER BY
APSENR."LNAME" ASC, APSENR."PLNID" ASC, APSENR."CARDID" ASC,APSENR_2."PERSON" ASC