I actually generally abide by the listing of fields. This scenario is occurring in a file encapsulated service program. Before on every write it would set the last change date to %timestamp. I still have to manually set the Last Changed by User field to the current user. When it inserts/updates, it's not keeping track of each field that was set/changed. The set procedures are loading the value into a file data structure, hence the insert/update using the file data structure.
The 'fix' I had to make to use the auto-updating timestamp field was to list out each field individually anyway. The view would have worked, but I want to be able to present the last changed date to the user even though they can't set it.
I appreciate all the feedback. Our shop is gradually embracing the features SQL provides.
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Friday, March 15, 2013 12:42 PM
To: Midrange Systems Technical Discussion
Subject: Re: AW: SQL Table with ROW CHANGE TIMESTAMP
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
Kendallville, IN 46755
From: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
Date: 03/15/2013 01:37 PM
Subject: AW: SQL Table with ROW CHANGE TIMESTAMP
Sent by: midrange-l-bounces@xxxxxxxxxxxx
That specification "Indicates the column is not visible in SQL
unless it is referred to explicitly by name. For example, SELECT * does
include any hidden columns in the result." Perhaps the DS defined with
external TABLE honors [or should honor] that?
But if he defines an external data structure all columns (implicitly
or not are included) and when reading a record with SELECT * you'll get
Again another reason why columns should be explicitly listed.
Mit freundlichen Grüßen / Best regards
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von CRPence
Gesendet: Friday, 15.3 2013 18:06
Betreff: Re: SQL Table with ROW CHANGE TIMESTAMP
On 15 Mar 2013 07:54, Anderson, Kurt wrote:
Just got back from the WMCPA conference and I'm using today to apply
some of the learning that occurred earlier this week.
One thing I learned about was being able to have my SQL table
automatically set the last change timestamp field w/o me doing it.
ChgDate TIMESTAMP FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT
I added some records via DBU and saw that this worked great.
Unfortunately this seems to be de-simplifying the embedded SQL in my
I had Insert into BillAgentP Values(:ds_Insert); Where ds_Insert is a
Qualified DS defined like the file. But now that doesn't work b/c it
doesn't like that I'm updating ChgDate. Is my only recourse to list
out all the fields except for ChgDate?
I'm at IBM i 7.1
And if that column is defined using IMPLICITLY HIDDEN specification on
the ROW CHANGE TIMESTAMP? That specification "Indicates the column is not
visible in SQL statements unless it is referred to explicitly by name. For
example, SELECT * does not include any hidden columns in the result."
Perhaps the DS defined with the external TABLE honors [or should honor]
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l