|
Thanks Eric. I have the problem solved with the null indicators now, but I will experiment with the Coalesce. Can I use the Coalesce if I have a Select * SCMS, UPRM -----Original Message----- From: DeLong, Eric [mailto:EDeLong@xxxxxxxxxxxxxxx] Sent: Tuesday, January 20, 2004 1:51 PM To: 'Midrange Systems Technical Discussion' Subject: RE: Embedded Sql problem Mike, Every field you selected from UPRM (the right side of the "left outer join") has the potential to return a null value. The most direct way to avoid having to deal with a null indicator would be to use Coalesce(field,dft_value) on the fields from the UPRM file. Eric DeLong Sally Beauty Company MIS-Project Manager (BSG) 940-898-7863 or ext. 1863 -----Original Message----- From: Smith, Mike [mailto:Mike_Smith@xxxxxxxxxxxxxxxx] Sent: Tuesday, January 20, 2004 11:34 AM To: Midrange Systems Technical Discussion Subject: RE: Embedded Sql problem Rob, I tried that. However, I'm now getting the unusual message SQL0303 that is telling me the Host variable PRMDS.UPSAD not compatible. SQLCOD = -000000303. My fetch statement now looks like FETCH Next From CtlCurs INTO :SMSO#,:SMSCD,:SMCRW,:UPRTE, :UPSAD,:SCEST, :NullIND I've switched locations in the list for the NullInd, because I wasn't sure exactly where it needs to go. No difference. I've tried making using host variable names. No difference. I've written hundreds of programs like these(however not usually with a specific Outer join.) and never had a problem like this. I have a feeling it has to do with the fact, that the record that isn't showing up doesn't have a record with the UPRM file so that UPSAD and UPRTE are blank. However, I kind of thought that was what the indicator variable was handling. -----Original Message----- From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] Sent: Tuesday, January 20, 2004 11:26 AM To: Midrange Systems Technical Discussion Subject: RE: Embedded Sql problem Mike, in your earlier desired result you listed two columns. Is that all you really want? If so, then drop the SELECT * and instead just select the two desired columns. then it would be, (assuming that only the first column supports null values): C/EXEC SQL C+ Fetch NEXT FROM CTLCURS Into :ColA :indA ,:ColB C/END-EXEC Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com "Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx> Sent by: midrange-l-bounces@xxxxxxxxxxxx 01/20/2004 11:07 AM Please respond to Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> To "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx> cc Fax to Subject RE: Embedded Sql problem Well, I've found that I'm getting an -SQL0305 error which indicates that I need a Indicator Variable. Some snippets of the code * DATA STRUCTURES D SODS E DS EXTNAME(SCMS) INZ D PrmDS E DS EXTNAME(UPRM) INZ D CodDS E DS EXTNAME(SOCD) INZ C Eval SelectOne = %TRIM(SQLARY(1)) + ' ' + C %TRIM(SQLARY(2)) + ' ' + C %TRIM(SQLARY(3)) + ' ' + C %TRIM(SQLARY(4)) + ' ' + C %TRIM(SQLARY(5)) + ' ' + C %TRIM(SQLARY(6)) + ' ' + C %TRIM(SQLARY(7)) + ' ' + C %TRIM(SQLARY(8)) + ' ' + C %TRIM(SQLARY(9)) + ' ' + C Where + ' ' + C 'Order By ' + %trim(OrderBy) C/EXEC SQL C+ PREPARE SEL FROM :SelectOne C/END-EXEC C/EXEC SQL C+ Declare CTLCURS SCROLL Cursor For SEL C/END-EXEC *OPEN THE CURSOR C/EXEC SQL C+ Open CTLCURS using :sodat C/END-EXEC I fetch using the following C/EXEC SQL C+ Fetch NEXT FROM CTLCURS Into :SoDS ,:prmDS, :CodDS C/END-EXEC I can't figure out how to incorporate a indicator variable the way I have it set up _______________________________________________ This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. _______________________________________________ This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. _______________________________________________ This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. _______________________________________________ This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
