comp.sys.ibm.as400.misc - IBM AS/400 miscellaneous topics.
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 pre-compilers. Sam "Andrew Goodspeed" < XXXX@XXXXX.COM > wrote in message news: XXXX@XXXXX.COM ... > I found a relevant posting from Kent Milligan dated 7JUN2001 on this > 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 qualified. > 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 > MIDINT." > > As documented, this seems an odd way of supporting qualified subfields, > that is, by stripping off the qualification. What is even odder (to me), > is that the documentation is entirely correct. Given the code snippet: > > 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 it > 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.
Hello. v8.2. Is it possible? Example: --------------- create function t(v varchar(1)) modifies sql data returns table(c varchar(1)) begin atomic return select v from sysibm.sysdummy1; end@ declare global temporary table session.test(c varchar(1)) on commit preserve rows@ --------------- Now I have tried: --------------- 1. insert into session.test select c from table(t('1')) as f; --- SQL20267, Reason Code=2 2. insert into session.test with a(c) as (select c from table(t('1')) as f) select c from a; --- SQL20165 3. begin atomic for g as with a(c) as ( select c from table(t('1')) as f ) select c from a do insert into session.test values (g.c); end for; end@ --- SQL0901 (known bug) --------------- Sincerely, Mark B.
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. Thanks.
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 into...... Now I'm lost. Syntax for an UPDATE command would be just as helpful. Thanks.