RE: Embedded Sql problem

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

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.