Advanced Union Field

[jhomrighaus]

Hello,
I am working to extract data from a Laboratory Information Management System. The LIMS is an AdvantageDB, and I am interfacing with an ODBC conduit.

I am trying to develop a summary report of QA data and this involves cross connecting about a dozen tables with 6 relationship "lanes" through a set of un related injection log tables(bear with me I know this is hard to understand), 4 of which are alias to eliminate looping problems.

I have ultimately to produce a single field called PrepMethod that is the combination of 4 fields that are aliases and 2 other fields from 2 unrelated tables. All the field types are string and I can create a report that populates all the fields individually. I need to combine them all to 1 field that i can conduct grouping on to generate statistics on the groups.

I know I could do this with a Union Query but i cannot seem to input any kind of a Union command into CR9.0. I have tried to create calculated concatenate, Join and Array fields but none will result in a string that I can use.

Can anyone help me or point me in the right direction to solve this problem.

Thanks,


Here is the SQL Query from Crystal(I cant seem to edit it directly only view it)

SELECT DISTINCT "Oftasks"."AnalyzedDate", "Oftasks"."CUCODE", "Oftasks"."Proc", "OF_QCRESULTS"."INJID", "OF_QCRESULTS"."TaskID", "OfCustod"."CLCODE", "EX_EXTRACTTYPE"."ExtractionName", "VP_Result"."PrepType", "DG_RESULT"."Method", "EX_EXTRACTTYPE_1"."ExtractionName", "EX_EXTRACTTYPE_2"."ExtractionName", "EX_EXTRACTTYPE_3"."ExtractionName", "OF_QCRESULTS"."Result", "OF_QCRESULTS"."Units", "OF_QCRESULTS"."QCType", "OF_QCRESULTS"."Analyte", "Ofproced"."CertMethodName"
FROM {oj (((((((((((((((((("OF_QCRESULTS" "OF_QCRESULTS" INNER JOIN "Oftasks" "Oftasks" ON "OF_QCRESULTS"."TaskID"="Oftasks"."TaskID") LEFT OUTER JOIN "G1_Injection" "G1_Injection" ON ("OF_QCRESULTS"."INJID"="G1_Injection"."InjID") AND ("Oftasks"."CUCODE"="G1_Injection"."SampleName")) LEFT OUTER JOIN "M1_Injection" "M1_Injection" ON ("OF_QCRESULTS"."INJID"="M1_Injection"."INJID") AND ("Oftasks"."CUCODE"="M1_Injection"."SampleName")) LEFT OUTER JOIN "M2_Injection" "M2_Injection" ON ("OF_QCRESULTS"."INJID"="M2_Injection"."INJID") AND ("Oftasks"."CUCODE"="M2_Injection"."SampleName")) LEFT OUTER JOIN "G2_Injection" "G2_Injection" ON ("OF_QCRESULTS"."INJID"="G2_Injection"."INJID") AND ("Oftasks"."CUCODE"="G2_Injection"."SampleName")) LEFT OUTER JOIN "ME_Injection" "ME_Injection" ON ("OF_QCRESULTS"."INJID"="ME_Injection"."INJID") AND ("Oftasks"."CUCODE"="ME_Injection"."SampleName")) LEFT OUTER JOIN "C1_INJECTION" "C1_INJECTION" ON ("OF_QCRESULTS"."INJID"="C1_INJECTION"."InjID") AND ("Oftasks"."CUCODE"="C1_INJECTION"."SampleName")) LEFT OUTER JOIN "Ex_res" "Ex_res_3" ON ("C1_INJECTION"."PrepID"="Ex_res_3"."PrepID") AND ("C1_INJECTION"."SampleName"="Ex_res_3"."SampleName")) LEFT OUTER JOIN "EX_EXTRACTTYPE" "EX_EXTRACTTYPE_3" ON "Ex_res_3"."ExtractType"="EX_EXTRACTTYPE_3"."ExtractType") LEFT OUTER JOIN "Ex_res" "Ex_res_2" ON ("M2_Injection"."PrepID"="Ex_res_2"."PrepID") AND ("M2_Injection"."SampleName"="Ex_res_2"."SampleName")) LEFT OUTER JOIN "EX_EXTRACTTYPE" "EX_EXTRACTTYPE_2" ON "Ex_res_2"."ExtractType"="EX_EXTRACTTYPE_2"."ExtractType") LEFT OUTER JOIN "Ex_res" "Ex_res_1" ON ("G2_Injection"."PrepID"="Ex_res_1"."PrepID") AND ("G2_Injection"."SampleName"="Ex_res_1"."SampleName")) LEFT OUTER JOIN "EX_EXTRACTTYPE" "EX_EXTRACTTYPE_1" ON "Ex_res_1"."ExtractType"="EX_EXTRACTTYPE_1"."ExtractType") LEFT OUTER JOIN "Ex_res" "Ex_res" ON ("G1_Injection"."PrepID"="Ex_res"."PrepID") AND ("G1_Injection"."SampleName"="Ex_res"."SampleName")) LEFT OUTER JOIN "EX_EXTRACTTYPE" "EX_EXTRACTTYPE" ON "Ex_res"."ExtractType"="EX_EXTRACTTYPE"."ExtractType") LEFT OUTER JOIN "VP_Result" "VP_Result" ON ("M1_Injection"."PrepID"="VP_Result"."PrepID") AND ("M1_Injection"."SampleName"="VP_Result"."SampleName")) LEFT OUTER JOIN "DG_RESULT" "DG_RESULT" ON ("ME_Injection"."PrepID"="DG_RESULT"."PrepID") AND ("ME_Injection"."SampleName"="DG_RESULT"."SampleName")) LEFT OUTER JOIN "Ofproced" "Ofproced" ON "Oftasks"."PRCODE"="Ofproced"."PRCODE") LEFT OUTER JOIN "OfCustod" "OfCustod" ON "Oftasks"."CUCODE"="OfCustod"."CUCODE"}
WHERE ("Oftasks"."AnalyzedDate">={d '2006-01-01'} AND "Oftasks"."AnalyzedDate"<={d '2007-01-01'}) AND NOT ("OfCustod"."CLCODE"='ELAP' OR "OfCustod"."CLCODE"='NEA')
ORDER BY "Ofproced"."CertMethodName", "Oftasks"."CUCODE"


The fields i am interested in combining are;

"EX_EXTRACTTYPE"."ExtractionName"
"VP_Result"."PrepType"
"DG_RESULT"."Method"
"EX_EXTRACTTYPE_1"."ExtractionName" "EX_EXTRACTTYPE_2"."ExtractionName" "EX_EXTRACTTYPE_3"."ExtractionName"

Thx

 

[synapsevampire]

Under your datasource you'll see Add Command, youcan paste SQL there, so if your database supports a UNION query, you can paste the entire SQL for the report there.

-k