MVSFORUMS.com

[agredell]

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):

[programmer1]

Is this what you are trying to do?

[dbzTHEdinosauer]

good eyes, programmer.
_________________
Dick Brenholtz
American living in Varel, Germany

[kolusu]

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.

[agredell]

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

[programmer1]

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

[kolusu]

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

[programmer1]

[agredell]

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?):

[kolusu]

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.

[agredell]

Very ingenious, and makes sense! I will report back as soon as I can try it. Thanks much again, Kolusu!!!
_________________
Alan Gredell

[agredell]

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

[kolusu]

agredell,

Glad I could help.
_________________
Kolusu
www.linkedin.com/in/kolusu