CAST Function issue in CR9

[jhomrighaus]

Hi,

I am attempting to use a cast function as a part of a SQL query in CR9 this is the SQL code I am using;

SELECT "OfCustod"."CLCODE", "OfCustod"."PJCODE", "OfCustod"."CUCODE",
"OfCustod"."SDG", "OfCustod"."ReceivedDate", "OfCustod"."ReceivedTime",
"OfCustod"."Receiver", "OfCustod"."Sampler", "Ofprojec"."Project",
"Ofprojec"."ClientNo", "Ofresults"."Method", "Ofresults"."Flag",
"Ofsample"."Sample", "Ofsample"."SampleNo", "Ofsample"."ClientSample",
"Ofsample"."SampleName", "Ofsample"."LWSAMPNO",
"Ofsample"."CollectionDate", "Ofsample"."CollectionTime",
"Ofsample"."Matrix", "Ofsample"."ClientMatrix", "Oftasks"."PRCODE",
"Oftasks"."Proc", "Oftasks"."SampleType", "Oftasks"."AnalyzedDate",
"Oftasks"."AnalyzedTime", "Oftasks"."Analyst", "LW_QUALIFIER"."Qualifier",
"LW_QUALIFIER"."Note", "Ofresults"."INJID", "Ofresults"."DF",
"Ofresults"."Result", "Ofresults"."Q1", CAST("Ofresults"."Q1" AS
VARCHAR(30)) AS testvalue
FROM {oj (((("OfCustod" "OfCustod" LEFT OUTER JOIN "Ofprojec"
"Ofprojec" ON ("OfCustod"."PJCODE"="Ofprojec"."PJCODE") AND
("OfCustod"."CLCODE"="Ofprojec"."CLCode")) LEFT OUTER JOIN "Oftasks"
"Oftasks" ON "OfCustod"."CUCODE"="Oftasks"."CUCODE") LEFT OUTER JOIN
"Ofsample" "Ofsample" ON ("Oftasks"."CUCODE"="Ofsample"."CUCODE") AND
("Oftasks"."SMPID"="Ofsample"."SMPID")) LEFT OUTER JOIN "Ofresults"
"Ofresults" ON "Oftasks"."TaskID"="Ofresults"."TaskID") LEFT OUTER JOIN
"LW_QUALIFIER" "LW_QUALIFIER" ON "Ofresults"."Q1"="LW_QUALIFIER"."QID"}
WHERE "OfCustod"."CUCODE"='07100151' AND "Ofresults"."Q1"<>0


With this code i get this error;

Failed to open a rowset
Details: HY000:[extended Systems][Advantage SQL][ASA] Error 7200: AQE
Error: State = S0000; Native Error = 2159; [Exteneded Systems][Advantage
SQL Engine]INvalid argument to scalar function: CAST


I get this error no matter what data type i use(Char, VARCHAR, TXT, STR etc)

I have tried several variations of adding spaces, using , instead of AS, etc and either get the same error or a Lexical syntax error about not able to find the from statement.

It seems as if crystal is expecting some other argument or something in order to parse the CAST statement.

My long term task here is to combine 4 queries similar to the one above(pulling 4 different fields in one table and retrieving their explanations) and a related query that is pulling taging the FLAG field above which is in string format, and UNION all the data into a field called testdata so I need them all to have the same data type.

Any help to be had?

Thanks
Jason

 

[jdemmi]

why not try creating a view and then using the view in the CR?

create view viewname
as select bla

http://msdn.microsoft.com/en-us/library/ms187956.aspx

(assuming MSSQL)

http://www.psoug.org/reference/views.html

(assuming oracle)

-- Jason
"It's Just Ones and Zeros

 

[beltmanjr]

Does it work without the CAST part?

I just tried a CAST in a command statement without issues.

I was actually wondering what the '{oj' thing does