How to do...well...anything...in DB2 SQL

**Ian Boyd** · Mar 9 '06, 09:15 PM

Re: How to do...well...any thing...in DB2 SQL

7. Casting number to a string

<quote>
CHAR
The CHAR function returns a fixed-length character string representation of:
An integer number, if the first argument is a SMALLINT, INTEGER, or BIGINT

Note: The CAST expression can also be used to return a string expression.
</quote>

So of couse this works:
SELECT CHAR(SomeBigInt ColumnFieldArma dillo) FROM MyTable

i would prefer to use CAST when doing all casts, and the documentation says
i can. But this fails

SELECT CAST(SomeBigInt ColumnFieldArma dillo AS varchar(50)) FROM MyTable

Error: SQL0461N
A value with data type "SYSIBM.BIG INT" cannot be CAST to type
"SYSIBM.VARCHAR ".
SQLSTATE=42846
(State:42846, Native Code: FFFFFE33)

Any ideas?

**Ian Boyd** · Mar 9 '06, 09:45 PM

Re: How to do...well...any thing...in DB2 SQL

> So[color=blue]
> 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?[/color]

Perhaps IBM should ask Oracle or MS if they can license their engines.

**Jeroen van den Broek** · Mar 9 '06, 10:15 PM

Re: How to do...well...any thing...in DB2 SQL

"Ian Boyd" <ian.msnews010@ avatopia.com> schreef in bericht
news:duq3tl01qq e@enews4.newsgu y.com...[color=blue]
> 7. Casting number to a string
>
> <quote>
> CHAR
> The CHAR function returns a fixed-length character string representation
> of:
> An integer number, if the first argument is a SMALLINT, INTEGER, or BIGINT
>
> Note: The CAST expression can also be used to return a string expression.
> </quote>
>
> So of couse this works:
> SELECT CHAR(SomeBigInt ColumnFieldArma dillo) FROM MyTable
>
> i would prefer to use CAST when doing all casts, and the documentation
> says
> i can. But this fails
>
> SELECT CAST(SomeBigInt ColumnFieldArma dillo AS varchar(50)) FROM MyTable
>
> Error: SQL0461N
> A value with data type "SYSIBM.BIG INT" cannot be CAST to type
> "SYSIBM.VARCHAR ".
> SQLSTATE=42846
> (State:42846, Native Code: FFFFFE33)
>
> Any ideas?[/color]

This Cast is not supported.
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".

--
Jeroen

**Jeroen van den Broek** · Mar 9 '06, 10:25 PM

Re: How to do...well...any thing...in DB2 SQL

"Ian Boyd" <ian.msnews010@ avatopia.com> schreef in bericht
news:duq5v901ss d@enews4.newsgu y.com...[color=blue][color=green]
>> 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?[/color]
>
> Perhaps IBM should ask Oracle or MS if they can license their engines.
>[/color]

Ehm, what were you saying about 'walking a fine line' again... ;-)
You really should try to start a thread like this on c.d.o.s., and watch
what happens...

--
Jeroen

**Jeroen van den Broek** · Mar 9 '06, 10:35 PM

Re: How to do...well...any thing...in DB2 SQL

"Jeroen van den Broek" <jeroen@NOSPAM. demon.nl> schreef in bericht
news:12119fi920 89649@corp.supe rnews.com...[color=blue]
>
> "Ian Boyd" <ian.msnews010@ avatopia.com> schreef in bericht
> news:duq3tl01qq e@enews4.newsgu y.com...[color=green]
>> 7. Casting number to a string
>>
>> <quote>
>> CHAR
>> The CHAR function returns a fixed-length character string representation
>> of:
>> An integer number, if the first argument is a SMALLINT, INTEGER, or
>> BIGINT
>>
>> Note: The CAST expression can also be used to return a string expression.
>> </quote>
>>
>> So of couse this works:
>> SELECT CHAR(SomeBigInt ColumnFieldArma dillo) FROM MyTable
>>
>> i would prefer to use CAST when doing all casts, and the documentation
>> says
>> i can. But this fails
>>
>> SELECT CAST(SomeBigInt ColumnFieldArma dillo AS varchar(50)) FROM MyTable
>>
>> Error: SQL0461N
>> A value with data type "SYSIBM.BIG INT" cannot be CAST to type
>> "SYSIBM.VARCHAR ".
>> SQLSTATE=42846
>> (State:42846, Native Code: FFFFFE33)
>>
>> Any ideas?[/color]
>
> This Cast is not supported.
> 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".
>[/color]

And this is what the Message Reference has to say on this particular error
message:

---------------------------------------
SQL0461N A value with data type "<source-data-type>" cannot be CAST to type
"<target-data-type>".

Explanation: The statement contains a CAST with the first operand having a
data type of "<source-data-type>" to be cast to the data type
"<target-data-type>". This cast is not supported.

User Response: Change the data type of either the source or target so that
the cast is supported. For predefined data types these are documented in the
SQL Reference. For a cast involving a user-defined distinct type, the cast
can be between the base data type and the user-defined distinct type or from
a data type that is promotable to the base data type to the user-defined
distinct type.

sqlcode: -461
---------------------------------------

--
Jeroen
sqlstate: 42846

**Ian Boyd** · Mar 9 '06, 10:35 PM

Re: How to do...well...any thing...in DB2 SQL

In SQL Server, just so you know what i'm basing it off of:

CREATE TABLE #t2 (Weight real) --btw prefix # means temporary. i could also
use a table variable

insert into #t2 (Weight) VALUES (1)
insert into #t2 (Weight) VALUES (1.1)
insert into #t2 (Weight) VALUES (1.01)
insert into #t2 (Weight) VALUES (1.001)
insert into #t2 (Weight) VALUES (1.0001)
insert into #t2 (Weight) VALUES (1.00001)
insert into #t2 (Weight) VALUES (1.000001)
insert into #t2 (Weight) VALUES (1.0000001)
insert into #t2 (Weight) VALUES (1.00000001)
insert into #t2 (Weight) VALUES (1.000000001)
insert into #t2 (Weight) VALUES (1.0000000001)
insert into #t2 (Weight) VALUES (1.00000000001)
insert into #t2 (Weight) VALUES (1.000000000001 )
insert into #t2 (Weight) VALUES (1.000000000000 1)
insert into #t2 (Weight) VALUES (1.000000000000 01)

SELECT
Weight,
CAST(Weight AS varchar(255)) AS [Using CAST],
CONVERT(varchar (255), Weight) AS [Using CONVERT with default formatting],
CONVERT(varchar (255), Weight, 1) AS [Using CONVERT with 8 digits
(scientific notation)],
CONVERT(varchar (255), Weight, 2) AS [Using CONVERT with 16 digits
(scientific notation)]
FROM #t2
ORDER BY Weight DESC

1.1 1.1 1.1 1.1000000e+000 1.1000000238418 58e+000
1.01 1.01 1.01 1.0100000e+000 1.0099999904632 57e+000
1.001 1.001 1.001 1.0010000e+000 1.0010000467300 42e+000
1.0001 1.0001 1.0001 1.0001000e+000 1.0001000165939 33e+000
1.00001 1.00001 1.00001 1.0000100e+000 1.0000100135803 22e+000
1.000001 1 1 1.0000010e+000 1.0000009536743 16e+000
1.0000001 1 1 1.0000001e+000 1.0000001192092 90e+000
1.0 1 1 1.0000000e+000 1.0000000000000 00e+000
1.0 1 1 1.0000000e+000 1.0000000000000 00e+000
1.0 1 1 1.0000000e+000 1.0000000000000 00e+000
1.0 1 1 1.0000000e+000 1.0000000000000 00e+000
1.0 1 1 1.0000000e+000 1.0000000000000 00e+000
1.0 1 1 1.0000000e+000 1.0000000000000 00e+000
1.0 1 1 1.0000000e+000 1.0000000000000 00e+000
1.0 1 1 1.0000000e+000 1.0000000000000 00e+000

May not be perfect, but 99.9% of the time it is all that i needed.
Espeically for money amounts, weights, emperical values.

The best i can come up with for DB2 so far is:

select CAST(CHAR(DECIM AL(HoursSpent_T estField, 31, 7)) AS varchar(50))
from daily_logs

1
-------------------------------------
000000000000000 000000003.14159 30
000000000000000 000000001.00000 00
000000000000000 000000002.00000 00
000000000000000 000000003.00000 00
000000000000000 000000003.00000 00

Tomorrow i will figure out how to do LTRIM and RTRIM away "0"

**Jeroen van den Broek** · Mar 9 '06, 10:45 PM

Re: How to do...well...any thing...in DB2 SQL

"Jeroen van den Broek" <jeroen@NOSPAM. demon.nl> schreef in bericht
news:12119fi920 89649@corp.supe rnews.com...[color=blue]
>
> "Ian Boyd" <ian.msnews010@ avatopia.com> schreef in bericht
> news:duq3tl01qq e@enews4.newsgu y.com...[color=green]
>> 7. Casting number to a string
>>
>> <quote>
>> CHAR
>> The CHAR function returns a fixed-length character string representation
>> of:
>> An integer number, if the first argument is a SMALLINT, INTEGER, or
>> BIGINT
>>
>> Note: The CAST expression can also be used to return a string expression.
>> </quote>
>>
>> So of couse this works:
>> SELECT CHAR(SomeBigInt ColumnFieldArma dillo) FROM MyTable
>>
>> i would prefer to use CAST when doing all casts, and the documentation
>> says
>> i can. But this fails
>>
>> SELECT CAST(SomeBigInt ColumnFieldArma dillo AS varchar(50)) FROM MyTable
>>
>> Error: SQL0461N
>> A value with data type "SYSIBM.BIG INT" cannot be CAST to type
>> "SYSIBM.VARCHAR ".
>> SQLSTATE=42846
>> (State:42846, Native Code: FFFFFE33)
>>
>> Any ideas?[/color]
>
> This Cast is not supported.
> 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".
>[/color]

As you can see in that table, a Cast between BIGINT and CHAR is supported,
as is a Cast between CHAR and VARCHAR, so you might try:

SELECT CAST(CAST(SomeB igIntColumnFiel dArmadillo AS char(50)) AS varchar(50))
FROM MyTable

--
Jeroen

**Will Honea** · Mar 10 '06, 05:55 AM

Re: How to do...well...any thing...in DB2 SQL

On Thu, 9 Mar 2006 19:56:49 UTC "Dave Hughes" <dave@waveform. plus.com>
wrote:
[color=blue]
> 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)[/color]

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?

--
Will Honea

**Dave Hughes** · Mar 10 '06, 01:15 PM

Re: How to do...well...any thing...in DB2 SQL

Will Honea wrote:
[color=blue]
> On Thu, 9 Mar 2006 19:56:49 UTC "Dave Hughes" <dave@waveform. plus.com>
> wrote:
>[color=green]
> > 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)[/color]
>
> 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?[/color]

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.

--

**Ian Boyd** · Mar 10 '06, 02:35 PM

Re: How to do...well...any thing...in DB2 SQL

> Have a look at the SQL Reference Vol1. Chapter 2 Language elements.[color=blue]
> 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".[/color]

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.

**Ian Boyd** · Mar 10 '06, 02:55 PM

Re: How to do...well...any thing...in DB2 SQL

>>> So[color=blue][color=green][color=darkred]
>>> 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?[/color][/color][/color]
[color=blue][color=green]
>> Perhaps IBM should ask Oracle or MS if they can license their engines.[/color][/color]
[color=blue]
> Ehm, what were you saying about 'walking a fine line' again... ;-)[/color]

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.

[color=blue]
> "Who's gonna pay for it and which feature should be moved back instead?"[/color]

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.
[color=blue]
> You really should try to start a thread like this on c.d.o.s., and watch
> what happens...[/color]

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.

**Ian Boyd** · Mar 10 '06, 03:26 PM

Re: How to do...well...any thing...in DB2 SQL

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_DA ILYLOGS
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_User s
WHERE Application_ID = Application_ID( ));

--Daily_Log_Numbe r varchar(20)
IF (d.Daily_Log_Nu mber IS NULL AND i.Daily_Log_Num ber IS NOT NULL) OR
(d.Daily_Log_Nu mber IS NOT NULL AND i.Daily_Log_Num ber IS NULL)
OR
(d.Daily_Log_Nu mber <> i.Daily_Log_Num ber) 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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
d.Daily_Log_Num ber,
i.Daily_Log_Num ber);
END IF;

--Created_By_User _ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigi nt AS char(50)) AS varchar(50))
IF (d.Created_By_U ser_ID IS NULL AND i.Created_By_Us er_ID IS NOT NULL)
OR
(d.Created_By_U ser_ID IS NOT NULL AND i.Created_By_Us er_ID IS
NULL) OR
(d.Created_By_U ser_ID <> i.Created_By_Us er_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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
CAST(CAST(d.Cre ated_By_User_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Cre ated_By_User_ID AS char(50)) AS varchar(50))
);
END IF;

--Property_ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigi nt 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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
CAST(CAST(d.Pro perty_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Pro perty_ID AS char(50)) AS varchar(50))
);
END IF;

--Shift_ID bigint
--Syntax for CAST(abigint AS) varchar(50)) is
-- CAST(CAST(abigi nt 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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
CAST(CAST(d.Shi ft_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Shi ft_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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
d.Bay_Number,
i.Bay_Number
);
END IF;

--Supervisor_User _ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigi nt AS char(50)) AS varchar(50))
IF (d.Supervisor_U ser_ID IS NULL AND i.Supervisor_Us er_ID IS NOT NULL) OR
(d.Supervisor_U ser_ID IS NOT NULL AND i.Supervisor_Us er_ID IS NULL) OR
(d.Supervisor_U ser_ID <> i.Supervisor_Us er_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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
CAST(CAST(d.Sup ervisor_User_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Sup ervisor_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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
d.Bay_Number,
i.Bay_Number);
END IF;

--Location_ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigi nt 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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
CAST(CAST(d.Loc ation_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Loc ation_ID AS char(50)) AS varchar(50))
);
END IF;

--Occurrence_ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigi nt AS char(50)) AS varchar(50))
IF (d.Occurrence_I D IS NULL AND i.Occurrence_ID IS NOT NULL) OR
(d.Occurrence_I D IS NOT NULL AND i.Occurrence_ID IS NULL) OR
(d.Occurrence_I D <> 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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
CAST(CAST(d.Occ urrence_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Occ urrence_ID AS char(50)) AS varchar(50))
);
END IF;

--Checklist_ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigi nt 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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
CAST(CAST(d.Che cklist_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Che cklist_ID AS char(50)) AS varchar(50))
);
END IF;

--Daily_Log_Type_ ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigi nt AS char(50) AS varchar(50))
IF (d.Daily_Log_Ty pe_ID IS NULL AND i.Daily_Log_Typ e_ID IS NOT NULL) OR
(d.Daily_Log_Ty pe_ID IS NOT NULL AND i.Daily_Log_Typ e_ID IS NULL) OR
(d.Daily_Log_Ty pe_ID <> i.Daily_Log_Typ e_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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
CAST(CAST(d.Dai ly_Log_Type_ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Dai ly_Log_Type_ID AS char(50)) AS varchar(50))
);
END IF;

--Daily_Log_SubTy pe_ID bigint
--Syntax for CAST(abigint AS varchar(50)) is
-- CAST(CAST(abigi nt AS char(50)) AS varchar(50))
IF (d.Daily_Log_Su bType_ID IS NULL AND i.Daily_Log_Sub Type_ID IS NOT NULL)
OR
(d.Daily_Log_Su bType_ID IS NOT NULL AND i.Daily_Log_Sub Type_ID IS NULL) OR
(d.Daily_Log_Su bType_ID <> i.Daily_Log_Sub 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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
CAST(CAST(d.Dai ly_Log_SubType_ ID AS char(50)) AS varchar(50)),
CAST(CAST(i.Dai ly_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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
CAST(d.Start_Da te AS varchar(50)),
CAST(i.Start_Da te 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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
i.Daily_Log_ID,
i.Daily_Log_Num ber,
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_Nu mber IS NULL AND i.Daily_Log_Num ber IS NOT NULL) OR
(d.Daily_Log_Nu mber IS NOT NULL AND i.Daily_Log_Num ber IS NULL)
OR
(d.Daily_Log_Nu mber <> i.Daily_Log_Num ber) THEN
...
i.Daily_Log_ID,
...
i.Daily_Log_ID,
i.Daily_Log_Num ber,
d.Daily_Log_Num ber,
i.Daily_Log_Num ber);
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_Nu mber <> 1) and
(i.Daily_Log_Nu mber <> 2) and
(i.Daily_Log_Nu mber <> 3) and
(i.Daily_Log_Nu mber <> 4) and
(i.Daily_Log_Nu mber <> 5) and
...
(i.Daily_Log_Nu mber <> 997423) THEN

Is just referencing a variable bad?

Do i have to write my trigger like:

CREATE TRIGGER SUPERDUDE.LU_DA ILYLOGS
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_Numbe r varchar(20)
DECLARE d_Daily_Log_Num ber varchar(20);
DECLARE i_Daily_Log_Num ber varchar(20);
SET d_Daily_Log_Num ber = d.Daily_Log_Num ber;
SET i_Daily_Log_Num ber = i.Daily_Log_Num ber;

IF (d_Daily_Log_Nu mber IS NULL AND i_Daily_Log_Num ber IS NOT NULL) OR
(d_Daily_Log_Nu mber IS NOT NULL AND i_Daily_Log_Num ber IS NULL)
OR
(d_Daily_Log_Nu mber <> i_Daily_Log_Num ber) 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_WRKSTNNA ME,
CURRENT CLIENT_APPLNAME ,
rowid,
i.Daily_Log_Num ber,
d_Daily_Log_Num ber,
i_Daily_Log_Num ber);
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?

**Brian Tkatch** · Mar 10 '06, 04:05 PM

Re: How to do...well...any thing...in DB2 SQL

>If we ever have a customer that insists on Oracle, then i guess i'll be learning Oracle-SQL

It's called PL/SQL.
[color=blue]
>i hope they aren't case-sensitive[/color]

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.
[color=blue]
>i hope they allow mixed-case identifiers[/color]

Yes.
[color=blue]
>i hope they allow identifiers to be longer than 18 characters.[/color]

IIRC, it's 30.

Been a while though.

B.

**Brian Tkatch** · Mar 10 '06, 04:15 PM

Re: How to do...well...any thing...in DB2 SQL

>Right now it's WoW.

Which server?

I finally just got enough gold to get my epic mount. I just need a
guildy to buy it for me, so i can save the extra hundred gold.

B.

**Serge Rielau** · Mar 10 '06, 04:15 PM

Re: How to do...well...any thing...in DB2 SQL

Ian Boyd wrote:[color=blue]
> 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.[/color]
The error is related to the semantic temporary table that needs to hold
the row.
I presume you have created the database using a default 4K page size and
not added any other table spaces.
Add an 8K SYSTEM TEMPORARY tablespace (and an 8k Buffer pool to serve )
using the CREATE BUFFERPOOL and CREATE TABLESPACE SQL statements.
This should solve the problem.
If you don't want to manage different tablespace (as I suspect :-)
recreate your database with a bigger default page size (assuming you're
on FP9 or higher) from the get go.
You can go up to 32K if you wish.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

How to do...well...anything...in DB2 SQL

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment