× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. August 2004

RE: SQL overrides, was Can SQL recognize a nested data structure?

Interesting.  I've done overrides before for SQL (so I can access specific 
members) and that worked.  Perhaps it's the SCOPE of your override that's 
blowing you grief?

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 11:08 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?






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.


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

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.