How to do...well...anything...in DB2 SQL
in [DB2]
|
Prev: SQL0901N Error.
Next: Convert DECIMAL to DATE
From: Dave Hughes on 10 Mar 2006 08:00 Will Honea wrote: > On Thu, 9 Mar 2006 19:56:49 UTC "Dave Hughes" <dave(a)waveform.plus.com> > wrote: > > > I'm pretty sure I've provided the syntax for this in a couple of > > other comments, but maybe I didn't indicate it explicitly: > > > > SELECT COLA, COLB, COLC > > FROM ( > > VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9) > > ) AS TEMP(COLA, COLB, COLC) > > What is the semantic difference between your syntax and the form > > WITH TEMP(COLA,COLB,COLC) AS > ( SELECT .... ) > > They appear equivilant - am I missing something? The WITH ... construct is a "common table expression" which was syntax introduced in the SQL-99 standard. Functionally, both your version and my version above are identical. That said, common table expressions are considerably more powerful than sub-selects and (in my personal opinion) a lot clearer especially if a query includes many sub-selects. One of the things you can do with common table expressions (that you can't do with ordinary sub-selects) is "recursive" queries. I haven't seen them used much in practice, and you've got to be a bit careful with (it's possible to make an infinitely recursive query), but there's some interesting examples in the DB2 Info Center under Reference / SQL / Queries / Select-statement (see the "Recursion example: bill of materials" section). One last thing, using a common table expression instead of a sub-select in my example, one wouldn't even need a SELECT expression around the VALUES expression: WITH TEMP(COLA, COLB, COLC) AS ( VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9) ) SELECT ... Anyway, thanks for bringing that up - I should have mentioned common table expressions in my reply, but forgot :) Dave. --
From: Ian Boyd on 10 Mar 2006 09:01 > Have a look at the SQL Reference Vol1. Chapter 2 Language elements. > There is a paragraph called "casting between data types" (in the version > I'm reading now it starts on page 96). > It contains Table 8: "Supported Casts between Built-in Data Types". i have that table printed out next to me :) i was hoping the sentence under CHAR saying that CAST *can* be used to convert numbers to strings trumps the table.
From: Ian Boyd on 10 Mar 2006 08:59 >>> So >>> it boils down to question we always have with new functionality: Who's >>> gonna pay for it and which feature should be moved back instead? >> Perhaps IBM should ask Oracle or MS if they can license their engines. > Ehm, what were you saying about 'walking a fine line' again... ;-) Yeah, that was in jest. But my point was that database vendors should be competing for customers. And it's a real problem if you don't do what they can do. > "Who's gonna pay for it and which feature should be moved back instead?" i assume that IBM has bottomless pits of money to draw from when updating DB2. My first exposure to DB2 was not a very pleasent one - version i don't know what running on an AS/400. It's a gawdawefull mess, that really soured my opinion on DB2. Seeing the improvements in the version we got shipped (8.2), i assumed that DB2 just made leaps and bounds since then, and that all these features that other vendors have would already be in "Big Blues" version. > You really should try to start a thread like this on c.d.o.s., and watch > what happens... If we ever have a customer that insists on Oracle, then i guess i'll be learning Oracle-SQL. i hope they aren't case-sensitive, i hope they allow mixed-case identifiers, i hope they allow identifiers to be longer than 18 characters.
From: Ian Boyd on 10 Mar 2006 09:53 8. Triggers for update My first task with DB2 was to create a table and 3 triggers. i've created the table - mostly the way i wanted, and i've created two of the three required triggers: AFTER INSERT AFTER DELETE Now i just have to finish my last one, the hardest one, the one with the most problems coming together. i'm also try to take the advice of people and use the FOR EACH ROW rather than the simpler more obvious FOR EACH STATEMENT. So, now for my update trigger, and the error that DB2 is giving me. NOTE: i am intentionally not snipping apparently repetative code, because the error talks about "too much." Also note, only formatted the first few entries and the last entry, as OE does not honor tab characters when pasting. <query> CREATE TRIGGER SUPERDUDE.LU_DAILYLOGS AFTER UPDATE ON SUPERDUDE.DAILY_LOGS REFERENCING OLD AS d NEW AS i FOR EACH ROW BEGIN ATOMIC DECLARE SavedUserID INTEGER; SET SavedUserID = ( SELECT User_ID FROM Connection_Users WHERE Application_ID = Application_ID()); --Daily_Log_Number varchar(20) IF (d.Daily_Log_Number IS NULL AND i.Daily_Log_Number IS NOT NULL) OR (d.Daily_Log_Number IS NOT NULL AND i.Daily_Log_Number IS NULL) OR (d.Daily_Log_Number <> i.Daily_Log_Number) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, d.Daily_Log_Number, i.Daily_Log_Number); END IF; --Created_By_User_ID bigint --Syntax for CAST(abigint AS varchar(50)) is -- CAST(CAST(abigint AS char(50)) AS varchar(50)) IF (d.Created_By_User_ID IS NULL AND i.Created_By_User_ID IS NOT NULL) OR (d.Created_By_User_ID IS NOT NULL AND i.Created_By_User_ID IS NULL) OR (d.Created_By_User_ID <> i.Created_By_User_ID) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, CAST(CAST(d.Created_By_User_ID AS char(50)) AS varchar(50)), CAST(CAST(i.Created_By_User_ID AS char(50)) AS varchar(50)) ); END IF; --Property_ID bigint --Syntax for CAST(abigint AS varchar(50)) is -- CAST(CAST(abigint AS char(50)) AS varchar(50)) IF (d.Property_ID IS NULL AND i.Property_ID IS NOT NULL) OR (d.Property_ID IS NOT NULL AND i.Property_ID IS NULL) OR (d.Property_ID <> i.Property_ID) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, CAST(CAST(d.Property_ID AS char(50)) AS varchar(50)), CAST(CAST(i.Property_ID AS char(50)) AS varchar(50)) ); END IF; --Shift_ID bigint --Syntax for CAST(abigint AS) varchar(50)) is -- CAST(CAST(abigint AS char(50)) AS varchar(50)) IF (d.Shift_ID IS NULL AND i.Shift_ID IS NOT NULL) OR (d.Shift_ID IS NOT NULL AND i.Shift_ID IS NULL) OR (d.Shift_ID <> i.Shift_ID) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, CAST(CAST(d.Shift_ID AS char(50)) AS varchar(50)), CAST(CAST(i.Shift_ID AS char(50)) AS varchar(50)) ); END IF; --Bay_Number varchar(25) IF (d.Bay_Number IS NULL AND i.Bay_Number IS NOT NULL) OR (d.Bay_Number IS NOT NULL AND i.Bay_Number IS NULL) OR (d.Bay_Number <> i.Bay_Number) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, d.Bay_Number, i.Bay_Number ); END IF; --Supervisor_User_ID bigint --Syntax for CAST(abigint AS varchar(50)) is -- CAST(CAST(abigint AS char(50)) AS varchar(50)) IF (d.Supervisor_User_ID IS NULL AND i.Supervisor_User_ID IS NOT NULL) OR (d.Supervisor_User_ID IS NOT NULL AND i.Supervisor_User_ID IS NULL) OR (d.Supervisor_User_ID <> i.Supervisor_User_ID) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, CAST(CAST(d.Supervisor_User_ID AS char(50)) AS varchar(50)), CAST(CAST(i.Supervisor_User_ID AS char(50)) AS varchar(50)) ); END IF; --Bay_Number varchar(25) IF (d.Bay_Number IS NULL AND i.Bay_Number IS NOT NULL) OR (d.Bay_Number IS NOT NULL AND i.Bay_Number IS NULL) OR (d.Bay_Number <> i.Bay_Number) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, d.Bay_Number, i.Bay_Number); END IF; --Location_ID bigint --Syntax for CAST(abigint AS varchar(50)) is -- CAST(CAST(abigint AS char(50)) AS varchar(50)) IF (d.Location_ID IS NULL AND i.Location_ID IS NOT NULL) OR (d.Location_ID IS NOT NULL AND i.Location_ID IS NULL) OR (d.Location_ID <> i.Location_ID) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, CAST(CAST(d.Location_ID AS char(50)) AS varchar(50)), CAST(CAST(i.Location_ID AS char(50)) AS varchar(50)) ); END IF; --Occurrence_ID bigint --Syntax for CAST(abigint AS varchar(50)) is -- CAST(CAST(abigint AS char(50)) AS varchar(50)) IF (d.Occurrence_ID IS NULL AND i.Occurrence_ID IS NOT NULL) OR (d.Occurrence_ID IS NOT NULL AND i.Occurrence_ID IS NULL) OR (d.Occurrence_ID <> i.Occurrence_ID) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, CAST(CAST(d.Occurrence_ID AS char(50)) AS varchar(50)), CAST(CAST(i.Occurrence_ID AS char(50)) AS varchar(50)) ); END IF; --Checklist_ID bigint --Syntax for CAST(abigint AS varchar(50)) is -- CAST(CAST(abigint AS char(50)) AS varchar(50)) IF (d.Checklist_ID IS NULL AND i.Checklist_ID IS NOT NULL) OR (d.Checklist_ID IS NOT NULL AND i.Checklist_ID IS NULL) OR (d.Checklist_ID <> i.Checklist_ID) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, CAST(CAST(d.Checklist_ID AS char(50)) AS varchar(50)), CAST(CAST(i.Checklist_ID AS char(50)) AS varchar(50)) ); END IF; --Daily_Log_Type_ID bigint --Syntax for CAST(abigint AS varchar(50)) is -- CAST(CAST(abigint AS char(50) AS varchar(50)) IF (d.Daily_Log_Type_ID IS NULL AND i.Daily_Log_Type_ID IS NOT NULL) OR (d.Daily_Log_Type_ID IS NOT NULL AND i.Daily_Log_Type_ID IS NULL) OR (d.Daily_Log_Type_ID <> i.Daily_Log_Type_ID) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, CAST(CAST(d.Daily_Log_Type_ID AS char(50)) AS varchar(50)), CAST(CAST(i.Daily_Log_Type_ID AS char(50)) AS varchar(50)) ); END IF; --Daily_Log_SubType_ID bigint --Syntax for CAST(abigint AS varchar(50)) is -- CAST(CAST(abigint AS char(50)) AS varchar(50)) IF (d.Daily_Log_SubType_ID IS NULL AND i.Daily_Log_SubType_ID IS NOT NULL) OR (d.Daily_Log_SubType_ID IS NOT NULL AND i.Daily_Log_SubType_ID IS NULL) OR (d.Daily_Log_SubType_ID <> i.Daily_Log_SubType_ID) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, CAST(CAST(d.Daily_Log_SubType_ID AS char(50)) AS varchar(50)), CAST(CAST(i.Daily_Log_SubType_ID AS char(50)) AS varchar(50)) ); END IF; --Start_Date timestamp IF (d.Start_Date IS NULL AND i.Start_Date IS NOT NULL) OR (d.Start_Date IS NOT NULL AND i.Start_Date IS NULL) OR (d.Start_Date <> i.Start_Date) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, CAST(d.Start_Date AS varchar(50)), CAST(i.Start_Date AS varchar(50)) ); END IF; --End_Date timestamp IF (d.Start_Date IS NULL AND i.Start_Date IS NOT NULL) OR (d.Start_Date IS NOT NULL AND i.Start_Date IS NULL) OR (d.Start_Date <> i.Start_Date) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, CAST(d.End_Date AS varchar(50)), CAST(i.End_Date AS varchar(50)) ); END IF; --Description varchar(2048) IF (d.Description IS NULL AND i.Description IS NOT NULL) OR (d.Description IS NOT NULL AND i.Description IS NULL) OR (d.Description <> i.Description) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( i.Daily_Log_ID, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, i.Daily_Log_ID, i.Daily_Log_Number, d.Description, i.Description); END IF; END </query> Now the error message i get is: <error> Error: SQL1424N Too many references to transition variables and transition table columns or the row length for these references is too long. Reason code="2". LINE NUMBER=524. SQLSTATE=54040 (State:54040, Native Code: FFFFFA70) </error> The web-sites explains this error: The trigger includes a REFERENCING clause that identifies one or more transition tables and transition variables. The triggered action of the trigger contains references to transition table columns or transition variables with one of the following conditions identified by the reason code: 1 references total more than the limit of the number of columns in a table 2 sum of the lengths of the references exceeds the maximum length of a row in a table. User Response: Reduce the number of references to transition variables and transition table columns in the trigger action of the trigger so that the length is reduced or the total number of such references is less than the maximum number of columns in a table. Now, i am taking this to mean that because for every field i do: IF (d.Daily_Log_Number IS NULL AND i.Daily_Log_Number IS NOT NULL) OR (d.Daily_Log_Number IS NOT NULL AND i.Daily_Log_Number IS NULL) OR (d.Daily_Log_Number <> i.Daily_Log_Number) THEN ... i.Daily_Log_ID, ... i.Daily_Log_ID, i.Daily_Log_Number, d.Daily_Log_Number, i.Daily_Log_Number); END IF; For every field in the table, my trigger references the "Old row" transition variable 4 times, and the "New row" transition variable 6 times. Is it really that fact that i am talking to a variable too many times? What should i be doing instead? Should i declare a variable for every row's old value and every row's new value, then copy the values from the old and new transition variables into my local variables, then do everything from those? Is it really a limitation on the number of times i can use a transition variable? Is something like the following going to "hit my limit": IF (i.Daily_Log_Number <> 1) and (i.Daily_Log_Number <> 2) and (i.Daily_Log_Number <> 3) and (i.Daily_Log_Number <> 4) and (i.Daily_Log_Number <> 5) and ... (i.Daily_Log_Number <> 997423) THEN Is just referencing a variable bad? Do i have to write my trigger like: CREATE TRIGGER SUPERDUDE.LU_DAILYLOGS AFTER UPDATE ON SUPERDUDE.DAILY_LOGS REFERENCING OLD AS d NEW AS i FOR EACH ROW BEGIN ATOMIC --Get the row key value DECLARE rowid BIGINT; SET rowid = i.Daily_Log_ID; --Daily_Log_Number varchar(20) DECLARE d_Daily_Log_Number varchar(20); DECLARE i_Daily_Log_Number varchar(20); SET d_Daily_Log_Number = d.Daily_Log_Number; SET i_Daily_Log_Number = i.Daily_Log_Number; IF (d_Daily_Log_Number IS NULL AND i_Daily_Log_Number IS NOT NULL) OR (d_Daily_Log_Number IS NOT NULL AND i_Daily_Log_Number IS NULL) OR (d_Daily_Log_Number <> i_Daily_Log_Number) THEN INSERT INTO Audit_Log( RowID, ChangeType, UserID, TableName, FieldName, Username, Hostname, Appname, TagID, Tag, OldValue, NewValue) VALUES ( rowid, 'UPDATED', SavedUserID, 'Daily_Logs', '', SESSION_USER, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, rowid, i.Daily_Log_Number, d_Daily_Log_Number, i_Daily_Log_Number); END IF; i can do this, it will make things much more difficult, but i can do it. But is DB2 telling me this is the trick i have to do to get around it's n-references to a transisition variable limitation? Or am i missing something more basic, that will make it just work?
From: Brian Tkatch on 10 Mar 2006 10:58
>If we ever have a customer that insists on Oracle, then i guess i'll be learning Oracle-SQL It's called PL/SQL. >i hope they aren't case-sensitive For object names, databases are case-sensitive. However, if not enclosed in double-quotes, it is implicitly uppercased before being evaluated. Therefore, it appears as case-insensitive. SQL Server breaks this, however, and is rather annoying. >i hope they allow mixed-case identifiers Yes. >i hope they allow identifiers to be longer than 18 characters. IIRC, it's 30. Been a while though. B. |