|
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.