AS/400 >> Using Qualified Data-Strucutue & Embedded SQL "INSERT"

by pauljsgroi » Wed, 25 Aug 2004 21:46:33 GMT

consider the following DS:

d dsbcf e ds extname(sobcf)
d dsbothbcf ds qualified
d dscurrbcf likeds(dsbcf)
d dspriorbcf likeds(dsbcf)

--- we're trying to perform the following INSERT:

c/exec sql
c+ insert into sobcf
c+ values (:dsbothbcf.dscurrbcf)

---Note, we'd like to be able to "qualify" the dscurrbcf portion of dsbothbcf
-- doesn't this look like it should work -- but -- it doesn't...

any ideas????

it's weird -- the error we get is "dscurrbcf not declared or not usable"

any help would be appreciated, thanx!

paul j. sgroi

AS/400 >> Using Qualified Data-Strucutue & Embedded SQL "INSERT"

by SamL » Thu, 26 Aug 2004 09:12:00 GMT

I'm fairly sure the SQL Precompiler won't support anything except a plain
vanilla DS until V5R3.



AS/400 >> Using Qualified Data-Strucutue & Embedded SQL "INSERT"

by Birgitta.Hauser » Thu, 26 Aug 2004 11:10:01 GMT

Hi Paul,

qualified datastructures cannot be handled by the SQL precompiler
before release V5R3M0.

Normally the SQL precompiler developement is one or two releases
behind the RPG compiler development.


Similar Threads

1. RPG ILE qualified subfield names in embedded SQL statements

Fascinating.  I tried qualified subfields recently on V5R1 only
to discover it didn't work.  I just gave up and didn't look at
the generated source.  I ended up using a PREFIX keyword on the
externally described data structures.

Possibly stupid question:  You're sure you compiled to V5R2?
(Our development box is at V5R2, but all the CRT... commands have
been changed to specify a target release of V5R1.)

For as much as IBM has pushed SQL on the iSeries, their haven't
helped the cause with the level of sophistication of their


"Andrew Goodspeed" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
> I found a relevant posting from Kent Milligan dated 7JUN2001 on
> topic, which seemed to be asking whether this was an
enhancement that
> should be pursued.  Based on the documentation (for V5R2) I
presume that
> it was.  To wit:
> "When writing an SQL statement, referrals to subfields can be
> Use the name of the data structure, followed by a period and
the name of
> the subfield. For example, PEMPL.MIDINT is the same as
specifying only
> As documented, this seems an odd way of supporting qualified
> that is, by stripping off the qualification.  What is even
odder (to me),
> is that the documentation is entirely correct.  Given the code
>      C/EXEC SQL
>      C+ FETCH AchRequestsC INTO :AchReqTmp.ExtVerId,
>      C+                         :AchReqTmp.ExtAcctHldTyp,
>      C+                         :AchReqTmp.ExtTranDesc,
>      C+                         :AchReqTmp.ExtTranDate,
>      C+                         :AchReqTmp.ExtExecDate,
>      C+                         :AchReqTmp.ExtTranType,
>      C+                         :AchReqTmp.ExtAcctType,
>      C+                         :AchReqTmp.ExtPrenote,
>      C+                         :AchReqTmp.ExtTrgBnkRT,
>      C+                         :AchReqTmp.ExtTrgBnkRTCk,
>      C+                         :AchReqTmp.ExtAccount,
>      C+                         :AchReqTmp.ExtAmount,
>      C+                         :AchReqTmp.ExtRcpId,
>      C+                         :AchReqTmp.ExtRcpName
>      C/END-EXEC
> The precompiler generates:
>  454 C                   Z-ADD     -4            SQLER6
>  455 C                   CALL      'QSQROUTE'
>  456 C                   PARM                    SQLCA
>  457 C                   PARM                    SQL_00006
>  458 C     SQL_00009     IFEQ      '1'
>  459 C                   EVAL      EXTVERID = SQL_00011
>  460 C                   EVAL      EXTACCTHLDTYP = SQL_00012
>  461 C                   EVAL      EXTTRANDESC = SQL_00013
>  462 C                   EVAL      EXTTRANDATE = SQL_00014
>  463 C                   EVAL      EXTEXECDATE = SQL_00015
>  464 C                   EVAL      EXTTRANTYPE = SQL_00016
>  465 C                   EVAL      EXTACCTTYPE = SQL_00017
>  466 C                   EVAL      EXTPRENOTE = SQL_00018
>  467 C                   EVAL      EXTTRGBNKRT = SQL_00019
>  468 C                   EVAL      EXTTRGBNKRTCK = SQL_00020
>  469 C                   EVAL      EXTACCOUNT = SQL_00021
>  470 C                   EVAL      EXTAMOUNT = SQL_00022
>  471 C                   EVAL      EXTRCPID = SQL_00023
>  472 C                   EVAL      EXTRCPNAME = SQL_00024
>  473 C                   END
> Now, it does require that the qualification is valid, but once
> verifies that, it renders the qualification moot by simply
removing it.
> Needles to say, the compile fails on this generated code.
> Is this a feature or a bug, and is there any incantation that
can get it
> to work in a sensible fashion?
> Much obliged.

2. Embedded SQL calling another Embedded SQL program - AS/400

3. Insert select from SQL table function that modifies sql data



Is it possible?
create function t(v varchar(1))
modifies sql data
returns table(c varchar(1))
begin atomic
return select v from sysibm.sysdummy1;
declare global temporary table session.test(c varchar(1)) on commit
preserve rows@
Now I have tried:
insert into session.test select c from table(t('1')) as f;
--- SQL20267, Reason Code=2
insert into session.test
with a(c) as (select c from table(t('1')) as f)
select c from a;
--- SQL20165
begin atomic
for g as
  with a(c) as (
  select c from table(t('1')) as f
  select c from a
  insert into session.test values (g.c);
end for;
--- SQL0901 (known bug)

Mark B.

4. Using vars for table names in COBOL embedded SQL - AS/400

5. Using SQL to Manipulate Timestamps using Quarterly data

I am looking for SQL syntax that will enable me to subtract quarters
from timestamps.  Any suggestions?

Pseudo-code example -

YEAR( timestamp_column - 9 Quarters)

I know that I can easily subtract things like DAYS.  However,  I can't
seem to be able to manipulate using Quarters.


6. Using Transactions to Insert Data to Db2 Database

7. How to simply insert or update to a BLOB column using data from file

Can someone provide me the simple syntax necessary to insert or update
to a row containing a single BLOB column, where the BLOB data will be
obtained from a file?  This is on a linux installation.  The table has
2 INTs and 1 BLOB column.

So, I've scoured various docs and such, and the closest I've come up
with is some sort of animal that looks like this:

db2 load from /tmp/myblobfile of asc method L (1 780) insert

Now I'm lost.

Syntax for an UPDATE command would be just as helpful.


8. Inserting Data using a Stored Procedure