Sub select issue

Question

Hi, I'm trying to do a subselect in SQL on an AS400 and getting a "Data conversion or data mapping error" - I'm pretty sure its to do with the way SQL is handling dates in the subselect (specifically it's changing the format by adding commas into a decimal field and it's getting confused when it does the next select) - could someone confirm this for me?? maybe suggest how I need to get round this problem??

Basically, I have something like below, with dates as decimal and in this format: CCYYMMDD (ie if you just do a select on the dates they come out as CC,YYM,MDD). The date is coming from table3

SELECT *
FROM TABLE1 A
     CROSS JOIN TABLE2 B
     LEFT OUTER JOIN (SELECT *
                      FROM TABLE3 C 
                      LEFT OUTER JOIN TABLE4 D ON (blah)
                      INNER JOIN TABLE5 E  ON (blah)
                      WHERE DATE >= 20080101
                      AND   DATE <= 20090101
                     ) AS C ON (blah AND blah)

Answer 1

I have little and dated AS/400 experience, but your problem is classic divide and conquer.

Isolate the sub-query - does it run ok by itself? Then start with table 1 and make sure the cross-query works Then add in the sub-query.

I don't know if AS/400 supports it, but SQL Server's common table expressions are very helpful - basically locally-scoped views. I only mention it because you could create a view that was your sub-query for better understanding.

All in all, I suspect your problem is within the 'blah and blah' :)

Answer 2

To answer this question properly it would help to know what flavor of "SQL" the AS400 is working with. The AS400 by its self is just a server. The AS400 can work with many database flavors such as DB2, MS SQL Server, Oracle, etc...

To take a quick stab at this without knowing which SQL flavor I would say you need to put '' around your date values so they don't get treated as numeric values.

WHERE DATE >= '20080101' AND DATE <= '20090101'

Answer 3

If you are working with the native AS400 db its flavor is: DB2 for iSeries (not to be confused with DB2 for Linux and other platforms)

If so, and the DATE fields in table 3 are decimal numeric in CCYYMMDD format as you say, your comparison is just fine. The commas are a format applied to decimals for the display and are not stored with the values.

I agree with n8wrl, try a simple "select from Table3 Where DATE >= 20080101" and see if that runs, and work your way out from there.

blah, blahs are very touchy :)