RE: Can SQL recognize a nested data structure?

Thanks Rob. Very interesting.  

My question about CL is we have a combination of old(rpg36/rpg) and new
(rpg4) modules and keep calling them from each other.  So the cl
override works for the job and we are fine.  But it does not work
usually for sql and we need to do a prepare like you did. Which is
usually more work. 

I think, 4a doesn't work (I tried) 4b does. And I am in v5r1 but we have
to compile down to accommodate our clients. Perhaps it will work in
v5r2.

Thanks again,
Sudha

Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax


-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Thursday, August 19, 2004 10:55 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Can SQL recognize a nested data structure?

1)  As far as what qualified does - RTFM.
But basically it simply does the following.  If your file, IIM, has a 
field, IPROD, you will now refer to it as the datastructure name dot
field 
name, or, iim.iprod.  This allows you to do things like:
     D new           e Ds                  extname(iim) qualified
     D old           e Ds                  extname(iim) qualified
      /free
       new.iprod = old.iprod;

2)  Will an override of the database work on the extname?
Are you asking if you write your own CL program to compile the RPGLE 
program, and you do an override of the IIM prior to the compile, will
the 
field names in the NEW datastructure and the field names in the OLD 
datastructure, now have the field names in the overridden file instead?
I 
would assume so.  But there's a common joke in America about assume
being 
a compound word.  I can't go into any more detail because I'll get
flooded 
from email programs with 'objectionable' content messages.

3)  A common way to do overrides of databases without using CL, in 
tradition I/O is to use something like
     Fmyfile    if   e             disk    extfile(MyVar) usropn    
      /free 
       MyVar='QTEMP/TESTFILE'; 
       Open(e) Myfile; 

4)  There are two ways to specify a file name in your SQL like you are 
trying to do.  (Hopefully they'll both work.)
4a)  Method 1
       MyString='Select * from ?' 
      /end-free 
     C/EXEC SQL 
     C+ Prepare S1 from :MyString 
     C/END-EXEC 
     C/EXEC SQL 
     C+ Declare C1 cursor for S1
     C/END-EXEC 
      // MyFile will be placed in the question mark position in S1  
     C/EXEC SQL 
     C+ Open C1 Using :MyFile 
     C/END-EXEC 
4b)  Method 2
       MyString='Select * from ' + :MyFile 
      /end-free 
     C/EXEC SQL 
     C+ Prepare S1 from :MyString 
     C/END-EXEC 
     C/EXEC SQL 
     C+ Declare C1 cursor for S1
     C/END-EXEC 
      // MyFile will be placed in the question mark position in S1  
     C/EXEC SQL 
     C+ Open C1
     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





<SRamanujan@xxxxxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
08/19/2004 10:04 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
<rpg400-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: Can SQL recognize a nested data structure?






A couple of questions, a little off topic. But, what does the
'qualified' do in the d-spec?  will an override of a database work on
the extname. i.e. can extname(iim) in the example, take up the
overridden database name in the cl?  And will that affect the being file
sql-ed also?  I haven't been able to pass the file name as parameter to
sql 'select before i.e. select * from :filename'. Can this be done now?

Thanks.

Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax

-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Wednesday, August 18, 2004 10:01 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Can SQL recognize a nested data structure?

This will work in V5R3  (tested it here):


     D iimds         e Ds                  extname(iim) dim(10)
     D                                     qualified
     D  rrn                          10s 0



Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.