RE: SQL COALESCE a data structure

Easy answer is you can't.

Couple of thoughts:

1) Do you really need to use all the fields from all the files?  By
coding your SQL statement this way and using external data structures,
you're losing one of the benefits of SQL; program independence from the
structure of the data.  Your program would need to be recompiled
whenever the file changes.  Generally speaking, using * to get all the
fields is a bad idea.

2) While you can't COALESCE the structure, you can I believe pass along
an null indicator structure that would provide a way SQL to communicate
the fact that a field was NULL.

D dmitmmstDS    E DS                  ExtName(DMITMMST)
D dmvdrmstDS    E DS                  ExtName(DMVDRMST)
D                                     Prefix(v)
D dmbyrmstDS    E DS                  ExtName(DMBYRMST)
D                                     Prefix(b)
D dmItmMstNullDS...
D                 s              4i 0 dim(NBR_FIELDS_IN_DMITMMSTDS)
D dmVdrMstNullDS...
D                 s              4i 0 dim(NBR_FIELDS_IN_DMITMVDRDS)
D dmByrMstNullDS...
D                 s              4i 0 dim(NBR_FIELDS_IN_DMITMBYRDS)
Exec SQL  Declare MyCursor cursor
            for Select i.*, v.*, b.*
                  From DMITMMST I
                      Left Outer Join
                    DMVDRMST V On i.VNDNR = v.VNDNR
                      Left Outer Join
                    DMBYRMST B On v.BUYER = b.BYCODE
                  Where i.ACREC = 'A'
                  Order By v.BUYER;

(then later, when fetching)

Exec SQL  Fetch Next
            from MyCursor
            into :dmitmmstDS :dmItmMstNullDS,
                 :dmvdrmstDS :dmVdrMstNullDS,
                 :dmbyrmstDS :dmByrMstNullDS;

If dmItmMstNullDS(1) < 0;
  //first field in dmItmMstrDS is null, 
Endif;


You can see that the problem here is that you have to check for null
based upon the order of the fields in the data structure.

Again, by using the * to get all the fields and by using the file as an
externally defined data structure you are asking for trouble when the
file changes in the future.

Even if you need all the fields, you'd be better off specifying them
explicitly along with using an explicitly defined DS to hold the
results.  


HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx 
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Friday, September 08, 2006 10:28 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: SQL COALESCE a data structure

If I'm doing a left outer join and there is no match in one 
or more files, I
can default individual fields with COALESCE like this:

Exec SQL  Declare MyCursor cursor
            for Select s.ITNBR,
                       s.QTYSH,
                       s.UNITS,
                       s.INVDT,
                       Coalesce(c.DLYCL, ' '),
                       Coalesce(i.PACKX, 0)
                  From SLSHST S
                      Left Outer Join
                    EOMCUS C On s.CUSNR = c.CUSNR
                      Left Outer Join
                    EOMITM I On s.ITNBR = i.ITNBR
                  Order By c.DLYCL, s.ITNBR;

(then later, when fetching)

Exec SQL  Fetch Next
            from MyCursor
            Into :wITNBR,
                 :wQTYSH,
                 :wUNITS,
                 :wINVDT,
                 :wDLYCL,
                 :wPACKX;


But how would I COALESCE an entire DS in a statement like this?

D dmitmmstDS    E DS                  ExtName(DMITMMST)
D dmvdrmstDS    E DS                  ExtName(DMVDRMST)
D                                     Prefix(v)
D dmbyrmstDS    E DS                  ExtName(DMBYRMST)
D                                     Prefix(b)

Exec SQL  Declare MyCursor cursor
            for Select i.*, v.*, b.*
                  From DMITMMST I
                      Left Outer Join
                    DMVDRMST V On i.VNDNR = v.VNDNR
                      Left Outer Join
                    DMBYRMST B On v.BUYER = b.BYCODE
                  Where i.ACREC = 'A'
                  Order By v.BUYER;

(then later, when fetching)

Exec SQL  Fetch Next
            from MyCursor
            into :dmitmmstDS,
                 :dmvdrmstDS,
                 :dmbyrmstDS;

Is there some global SQL setting where I can tell it to, for 
example, set
all character fields to blank, numeric to 0, and 
date/time/timestamps to an
appropriate value?

Thanks in advance.  The more I learn about SQL, the more flexible and
powerful it seems.  And I know I'm only scratching the surface.

-- 
Jeff Crosby
Dilgard Frozen Foods, Inc.
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531

The opinions expressed are my own and not necessarily the 
opinion of my
company.  Unless I say so.


-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) 
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.