Posted: Mon Feb 05, 2007 4:36 pm Post subject: Insert with Subselect and Nested Table Expression (DB2 Z/os)
I always turn to Kolusu when all other sources fail!
I am trying to insert into an audit table of our own design. The idea is to name some identifying data about the row, then either a count value or a max or min value to identify some things about a given set of inserts, which are normally identifiable by a RUN_ID generated by a separate process, and included in every row of the source table (not the audit table); however, in this case, the source table does not contain a RUN_ID; my thought was to create a fake row (with zero for a RUN_ID), then, during my insert statement, select the MIN value and subtract 1 from it (to avoid collisions with otherwise always positive values). I'm sure I'm just making a stupid syntax error, but I can't see how to fix it! The statement that's failing (-104, unexpected token):
Code:
INSERT INTO AUDIT.AUDIT_CTL_RUN
(
AUD_RUN_ID
,AUD_SRC
,AUD_TABLE_NM
,AUD_BUS_UNIT_NM
,AUD_CNTRY_CD
,AUD_COLUMN_NM
,AUD_COUNT_TYPE
,AUD_COUNT_NBR
)
select
(SELECT (MIN(AUD_RUN_ID)) - 1
FROM AUDIT.AUDIT_CTL_RUN WHERE
AUD_TABLE_NM = 'XYZ_CUST_ADMIN' AND AUD_SRC = 'M' )
, 'M'
, 'XYZ_CUST_ADMIN'
, 'ALL'
, 'ALL'
, '__TABLE'
, 'UC'
, COUNT(*)
FROM SOURCE.XYZ_CUST_ADMIN
;
This is DB2 for Z/OS V7. Any ideas? Thanks... _________________ Alan Gredell
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Mon Feb 05, 2007 10:26 pm Post subject:
agredell,
I am assuming that your table consists of only the columns you mentioned in the INSERT statement. Try this Insert which will give you the desired results.
Code:
INSERT INTO AUDIT.AUDIT_CTL_RUN
(SELECT MIN(AUD_RUN_ID) - 1
,CHAR('M')
,CHAR('XYZ_CUST_ADMIN')
,CHAR('ALL')
,CHAR('ALL')
,CHAR('__TABLE')
,CHAR('UC')
,COUNT(*)
FROM SOURCE.XYZ_CUST_ADMIN
WHERE AUD_TABLE_NM = 'XYZ_CUST_ADMIN'
AND AUD_SRC = 'M')
;
If you have additional columns then just specify them in the inner SELECT statement with CHAR/INT functions depending on the column properties.
Thanks for the responses, programmer and Kolusu; programmer, the syntax still must be off, though I cannot see why; I get a -104 on the token "MIN" for your example. You are on the right track!!
Kolusu, unfortunately those are not the only columns; there are two more which are intended to take default values for current timestamp and current sqlid. So I think I DO need the "Values" form (I had tried with it, really, just forgot to put it back in for my example ) . I might have been missing the open paren on the VALUES clause... _________________ Alan Gredell
I cannot see any error with the syntax. The best I could advice would be to check the column definition for AUD_RUN_ID but again if that was an issue, you would have got the SQLCODE as -171 _________________ Regards,
Programmer
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Tue Feb 06, 2007 1:32 pm Post subject:
agredell,
You canNOT use VALUES and a SELECT statement at the same time. When you specify columns you need to specify VALUES and when you specify that you cannot use scalar functions(min, max , count...) .A value cannot be inserted into a column that is derived from
A constant, expression, or scalar function
The same base table column as some other column of the view
That is the reason for your -104 error.
So if you other columns which takes in the default values you can speficy them in the select statement as follows
Code:
INSERT INTO AUDIT.AUDIT_CTL_RUN
(SELECT MIN(AUD_RUN_ID) - 1
,CHAR('M')
,CHAR('XYZ_CUST_ADMIN')
,CHAR('ALL')
,CHAR('ALL')
,CHAR('__TABLE')
,CHAR('UC')
,COUNT(*)
,CURRENT TIMESTAMP
,CURRENT SQLID
FROM SOURCE.XYZ_CUST_ADMIN
WHERE AUD_TABLE_NM = 'XYZ_CUST_ADMIN'
AND AUD_SRC = 'M')
;
Current timestamp and Current SQLID are 26 and 8 bytes respectively.
Kolusu, you are so very close; the only problem with your example is that the "COUNT(*)" function is a count from a different table, and THAT is exactly why I'm trying to use the nested table expression; my attempt at using your code was modified to the following, which still gets a -104 on the word COUNT (does this reflect your previous post that says I can't derive the column from a scalar function?):
Code:
INSERT INTO AUDIT.AUD_CTL_RUN
(SELECT MIN(AUD_RUN_ID) - 1
,CHAR('M')
,CHAR(XYZ_CUST_ADMIN')
,CHAR('ALL')
,CHAR('ALL')
,CHAR('__TABLE')
,CHAR('UC')
,(SELECT COUNT(*) FROM SOURCE.XYZ_CUST_ADMIN) AS COUNT
,CURRENT TIMESTAMP
,CURRENT SQLID
FROM AUDIT.AUD_CTL_RUN
WHERE AUD_TABLE_NM = 'XYZ_CUST_ADMIN'
AND AUD_SRC = 'M')
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Tue Feb 06, 2007 4:57 pm Post subject:
agredell,
ok here is a way to get the desired results. Since you want to pull column function values from 2 different tables, we need to 2 sql statements with union and use the sum function to get the actual values.
Notice the values int(0) for the AUD_RUN_ID & Count(*) values. We create 2 rows for 2 tables and then sum on the min value and count value , so that we have a single row to be inserted.
Code:
INSERT INTO AUDIT.AUD_CTL_RUN
(SELECT SUM(A.SEQ01)
,A.COL02
,A.COL03
,A.COL04
,A.COL05
,A.COL06
,A.COL07
,SUM(A.CNT08)
,A.COL09
,A.COL10
FROM (SELECT MIN(AUD_RUN_ID) - 1 AS SEQ01
,CHAR('M') AS COL02
,CHAR('XYZ_CUST_ADMIN') AS COL03
,CHAR('ALL') AS COL04
,CHAR('ALL') AS COL05
,CHAR('__TABLE') AS COL06
,CHAR('UC') AS COL07
,INT(0) AS CNT08
,CURRENT TIMESTAMP AS COL09
,CURRENT SQLID AS COL10
FROM AUDIT.AUD_CTL_RUN
WHERE AUD_TABLE_NM = 'XYZ_CUST_ADMIN'
AND AUD_SRC = 'M'
UNION
SELECT INT(0) AS SEQ01
,CHAR('M') AS COL02
,CHAR('XYZ_CUST_ADMIN') AS COL03
,CHAR('ALL') AS COL04
,CHAR('ALL') AS COL05
,CHAR('__TABLE') AS COL06
,CHAR('UC') AS COL07
,COUNT(*) AS CNT08
,CURRENT TIMESTAMP AS COL09
,CURRENT SQLID AS COL10
FROM SOURCE.XYZ_CUST_ADMIN) A
GROUP BY A.COL02
,A.COL03
,A.COL04
,A.COL05
,A.COL06
,A.COL07
,A.COL09
,A.COL10);
Hope this helps...
Cheers
Kolusu
PS: I did not check the sql for optimization. It is just written on the fly. _________________ Kolusu
www.linkedin.com/in/kolusu
Once again, Kolusu, you have come to the rescue!! With some additional modifications and a long time until I figured out the cause for a -408 value problem (the last two columns, representing the SQLID and the current timestamp, were in the wrong order!), I was able to make the query work.
I greatly appreciate your efforts!!! _________________ Alan Gredell
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum