|
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 mailing list archive is Copyright 1997-2025 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.