× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.