datasource error [RESOLVED]

[DanM]

I have another of these strange datasource situations where the SQL works from the iseries but is not producing any data from within my app..... i see this warning in "ERRORS" but i don't know what it means.... any ideas?



VV00060 - Exception encountered during SQL execution,likely resulting in truncated data or an empty data set (SQLSTT=01565 - SQL0420 - Character in CAST argument not valid.). Exception occurred on record number 5 in the record set.
Help Text
SQL statement is: SELECT f1.imsid AS f1_imsid, f1.imsrcsys AS f1_imsrcsys, f1.imseries AS f1_imseries, f1.immanager AS f1_immanager, f1.immanagern AS f1_immanagern, f1.imitem AS f1_imitem, f1.imnaml AS f1_imnaml, f1.imnams AS f1_imnams, f1.imrptc AS f1_imrptc, f1.imprtyp AS f1_imprtyp, f1.imitype AS f1_imitype, f1.imistyp AS f1_imistyp, f1.imfshpdt AS f1_imfshpdt, f1.immthsc AS f1_immthsc, f1.immthss AS f1_immthss, f1.immthscx AS f1_immthscx, f1.immthssx AS f1_immthssx, f1.imallsh AS f1_imallsh, f1.imallqs AS f1_imallqs, f1.imrwgt AS f1_imrwgt, f1.imolcomm AS f1_imolcomm, f1.imshpint AS f1_imshpint, f1.imnxtcycdt AS f1_imnxtcycdt, f1.imlstrundt AS f1_imlstrundt, f1.imholidypb AS f1_imholidypb, f1.imeffon AS f1_imeffon, f1.imuninspp AS f1_imuninspp, f1.imarchive AS f1_imarchive, f1.imtested AS f1_imtested, f1.imvendor AS f1_imvendor, f1.imdftfulf AS f1_imdftfulf, f1.imdftctrc AS f1_imdftctrc, f1.imdftprcc AS f1_imdftprcc, f1.imdftprce AS f1_imdftprce, f1.imdftshnd AS f1_imdftshnd, f1.imdfttot$ AS f1_imdfttot$, f1.iminstall AS f1_iminstall, f1.imavaildt AS f1_imavaildt, f1.imavailms1 AS f1_imavailms1, f1.imavailms2 AS f1_imavailms2, f1.imcrtby AS f1_imcrtby, f1.imcrtdt AS f1_imcrtdt, f1.imcrttm AS f1_imcrttm, f1.imchgby AS f1_imchgby, f1.imchgdt AS f1_imchgdt, f1.imchgtm AS f1_imchgtm, f1.imitem concat ' - ' concat f1.imnaml AS itemdescription, f1.imdow AS f1_imdow, f1.imvendtxt AS f1_imvendtxt, case WHEN f1.imcrtdt <> '0001-01-01' THEN 'Created on ' concat month(f1.imcrtdt) concat '-' concat day(f1.imcrtdt) concat '-' concat year(f1.imcrtdt) concat ' by ' concat f1.imcrtby ELSE ' ' END AS crtby, case WHEN f1.imchgdt <> '0001-01-01' THEN 'Changed on ' concat month(f1.imchgdt) concat '-' concat day(f1.imchgdt) concat '-' concat year(f1.imchgdt) concat ' by ' concat f1.imchgby ELSE ' ' END AS chgby, case WHEN f1.imavaildt <> '0001-01-01' THEN month(f1.imavaildt) concat '-' concat day(f1.imavaildt) concat '-' concat year(f1.imavaildt) ELSE ' ' END AS availd, case WHEN f1.imlstrundt <> '0001-01-01' THEN month(f1.imlstrundt) concat '-' concat day(f1.imlstrundt) concat '-' concat year(f1.imlstrundt) ELSE ' ' END AS lastrund, case WHEN f1.imfshpdt <> '0001-01-01' THEN month(f1.imfshpdt) concat '-' concat day(f1.imfshpdt) concat '-' concat year(f1.imfshpdt) ELSE ' ' END AS firstrund, case WHEN f1.imnxtcycdt <> '0001-01-01' THEN month(f1.imnxtcycdt) concat '-' concat day(f1.imnxtcycdt) concat '-' concat year(f1.imnxtcycdt) ELSE ' ' END AS nextrund, '$' concat f1.imdftprce AS price, '$' concat f1.imdftshnd AS sands, '$' concat f1.imdfttot$ AS total, f1.imqavail AS f1_imqavail, f1.imqbklog AS f1_imqbklog, f1.imqresrv AS f1_imqresrv, f1.imqonord AS f1_imqonord, f1.imurl AS f1_imurl, f1.imclsord AS f1_imclsord, case WHEN f3.olsortitem is not NULL THEN f3.olsortitem ELSE '' END AS pparent, case WHEN f3.olsortitem is not NULL THEN f3.olavailble ELSE '' END AS pavail, case WHEN f3.olsortitem is not NULL THEN f3.olallbklog ELSE '' END AS pbklog, case WHEN f3.olsortitem is not NULL THEN f3.olreserves ELSE '' END AS preserves, case WHEN f3.olsortitem is not NULL THEN f3.olonorder ELSE '' END AS ponorder, rrn(f1) AS rrn1, rrn(f2) AS rrn2, rrn(f3) AS rrn3 FROM vpd001wk3a AS f1 LEFT OUTER JOIN vpd001w9 AS f2 ON f1.imsid = f2.olsid and f1.imitem = trim(f2.olitem) LEFT OUTER JOIN vpd001w9 AS f3 ON f2.olsid = f3.olsid and trim(f2.olsortitem) = trim(f3.olsortitem) and trim(f3.olsortitem) = f3.olitem fetch first 20 rows only optimize for 20 rows

[robert.swanson]

My first guess would be one of the six CASE statements that use date functions -- MONTH(), DAY(), YEAR() -- might be having trouble with an invalid date it's trying to cast.

To test that, try removing the six segments that are creating your crtby, chgby, availd, lastrund, firstrund and nextrund columns, verify the statement now works, then (assuming it does work) add back the underlying date columns f1.imcrtdt, f1.imchgdt, f1.imavaildt, 1.imlstrundt, f1.imfshpdt and f1.imnxtcycdt and see if the 5th row in the result set shows any funky values.

[DanM]

seems an item # in my database -- one of the outer joins --- had a ? in it...when i got rid of it, all was good....

does that make sense??? the item # is just a straight character field....

[robert.swanson]

That "?" you see is most likely an non-renderable hex character inside the field. Some tools like STRSQL can handle that (i.e., by replacing the non-printable characters with question marks), but the SQL API that Valence uses will abort when it hits an exception like that.

[DanM]

riddle solved..... thanx.....