Converting a CL w/ Query/400 and CPYF to an SQL

Question

We have this SQL it passed Crystal Reports but it is not returning any 'PAL' rows. The orig Query/400 is pretty simple:

It's a matched Join between ICLOCMLM and ICBLDTIR; joining on these: (Join 1 or Join 2 same issue)

T01.LMLOC1        EQ       T02.IRLOC1   
T01.LMLOC2        EQ       T02.IRLOC2   
T01.LMLOC3        EQ       T02.IRLOC3

selects records: T01.LMLTPC LIST 'PAL' 'RAK' sorts:

rty A/D  Field          
 10  A   T01.LMLOC1     
 20  A   T01.LMLOC2     
 30  A   T01.LMLOC3

and creates an output file: BLDPALQ option 1 to replace.

then this file gets added after the BAL file is added, giving us the records we need, but for some reason in this SQL the PAL records are not being added. only the RAK. IOW, the Query/400 and that CPYF are doing what we need, but not this SQL.

CPYF       FROMFILE(ASTDTA/ICBALMIE) +                 
           TOFILE(ASTCCDTA/ACBALMPK) +               
           MBROPT(*REPLACE) FMTOPT(*MAP *DROP)       
MONMSG CPF0000                                         
                               */                       
CPYF       FROMFILE(TEMPLIB/BLDPALQ) +                   
           TOFILE(ASTCCDTA/ACBALMPK) +               
           MBROPT(*ADD) FMTOPT(*MAP *DROP)           
           MONMSG CPF0000

SELECT
        LMLTPC,
        COALESCE(IRLOC1,'') as IRLOC1,
        COALESCE(IRLOC2,'') as IRLOC2,
        COALESCE(IRLOC3,'') as IRLOC3,
        IRPRT#,
        IRQOH#,
        IRWHS#,
        '' as IEPRT#,
        '.00' as IEQOH#,
        '' as IELOC1,
        '' as IELOC2,
        '' as IELOC3,
        '' as IEWHS#
 FROM ASTDTA.ICLOCMLM mlm
 left join ASTDTA.ICBLDTIR tir
        on mlm.LMLOC1 = tir.IRLOC1
        and mlm.LMLOC2 = tir.IRLOC2
        and mlm.LMLOC3 = tir.IRLOC3
where LMLTPC in ('PAL', 'RAK')

UNION ALL

SELECT  
    ' ' as LMLTPC, 
    ' ' as IRLOC1, 
    ' ' as IRLOC2, 
    ' ' as IRLOC3,
    '' as IRPRT#, 
    '.00' as IRQOH#, 
    '' as IRWHS#, 
    IEPRT#, 
    IEQOH#, 
    IELOC1,
    IELOC2, 
    IELOC3,
    IEWHS# 
FROM ASTDTA.ICBALMIE

Strange when I run this query on the 400 I DO get PAL Records so it must be something in the joining.

SELECT ALL
   T01.LMCOM#,
   T01.LMWHS#,
   T01.LMLOC1,
   T01.LMLOC2,
   T01.LMLOC3,
   T01.LMLTPC,
   T01.LMLCT1,
   T01.LMLCT2,
   T01.LMRIDC,
   T01.LMQTM#,
   T01.LMQMX#,
   T01.LMWGHT,
   T01.LMACTF                  
FROM ASTDTA/ICLOC1 T01
WHERE t01.LMLTPC = 'PAL'

I have full join but it still doesn't work:

SELECT
   T01.LMLTPC,
   T02.IRCOM#,
   T02.IRWHS#,
   T02.IRPRT#,
   T02.IRUM,
   T02.IRLOC1,
   T02.IRLOC2,
   T02.IRLOC3,
   T02.IRLOT#,
   T02.IRFL50,
   T02.IREXPD,
   T02.IRQOH#,
   T02.IRQTM#,
   T02.IRQMX#,
   T02.IRLTPC,
   T02.IRQCM#
FROM ASTDTA.ICLOC1 T01,
     ASTDTA.ICBLD1 T02
WHERE T01.LMLOC1 = T02.IRLOC1
   AND T01.LMLOC2 = T02.IRLOC2
   AND T01.LMLOC3 = T02.IRLOC3
ORDER BY T01.LMLOC1 ASC, T01.LMLOC2 ASC, T01.LMLOC3 ASC

Buck Calabro Buck Calabro · Accepted Answer · 2013-08-07T01:22:34

You might be getting the rows you want, but masking them with the COALESCE. Try selecting the columns from the left side and see if those are the rows you expect:

SELECT
        LMLTPC,
        LMLOC1,
        LMLOC2,
        LMLOC3,
        IRPRT#...

Converting a CL w/ Query/400 and CPYF to an SQL

2 Answers