Re: Embedded Sql problem

You need a cursor.

d scms    ds                            extname(scms) qualified
d uprm    ds                            extname(uprm) qualfied

 // put every field that you will select on your SELECT * in this data 
structure.
d mysql  ds                             qualified
d  myflda                               like(scms.myfielda)
d  myfldc                               like(uprm.myfieldb)

C/EXEC SQL 
c+ declare C1 cursor for
C+ (that same select statement here)
C/END-EXEC

C/EXEC SQL 
C+ Open C1
C/END-EXEC
 
 /free
  dou sqlcod<>0;
 /end-free 
C/EXEC SQL 
C+ fetch C1 into mysql
C/END-EXEC
 /free
    select;
    when sqlcod<>0;
      leave;
    EndSl;
  // now do your processing based on the fields in mysql
  EndDo;
 /end-free
C/EXEC SQL 
C+ Close C1
C/END-EXEC

 
Rob Berendt
-- 
"All creatures will make merry... under pain of death."
-Ming the Merciless (Flash Gordon)




"Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces+rob=dekko.com@xxxxxxxxxxxx
01/20/2004 08:27 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
<midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
Embedded Sql problem






I have a SQL statement that i developed using interactive SQL.  I get
the results that i'm looking for.  I then took this SQL and embedded it
into an RPG application. 
I no longer get the results that I am expecting.  I put the program into
debug and copied by select statement directly from the rpg and pasted it
back to interactive sql(just to be sure that i didn't miss type. )
Again i get the results i'm expecting.  I've tried changing the (allow
copy of data) parm on the compile from yes, to no and back to *optimize.
Same results. 
 
here are the results i'm looking for. 
 974706   D4 
 908231   I8 
 908232   I8 
 913235   I8 
 913236   I8 
 
I don't get the record with the D4 when embedded. 
 
Here is the SQL statement. 
The D4 record from above exists in SCMS but not in UPRM. 
 
select * from SCMS Left outer join UPRM on SMPRM = UPPRM join SOCD 
on SMSCD = SCSCD where SMCLO = ' ' and SMSDT = 20031111 
and SMCRW in ('RG', '00170','00540','00560','00570', '00580','00590'
,'00710', '00840','00890', '01050','01140','01190','01310','01330', 
'01488', '01600',' ') 
 
Any idea why this will work interactively but not embedded?
 
Thanks 
 
Mike 
_______________________________________________
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.