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

 

[lbass]

I'm not very familiar with this, but since I believe this may be your second post without a response so far, I'll take a shot at it.

I have never been able to get cast() to work with my datasources, but I have used:

{fn convert(`table`.`number`,sql_varchar)}

...successfully, so I wonder whether adding the preface "sql_" would make a difference in cast()--or maybe you could use the convert function above, which does the same thing, I believe.

-LB

 

[jhomrighaus]

I think that might have fixed it Thanks SOOO Much