RE: Ref constraint: Numeric to Character

Oh, I see. You're suggesting a DIY derived column. That should work.

Thanks!



-----Original Message-----
From: Rob Berendt [mailto:rob@xxxxxxxxx]
Sent: Monday, May 22, 2017 1:14 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Ref constraint: Numeric to Character

Actually I meant have the physical have a new field, which in your parlance would be a "derived column". So you already planned on adding a new column. Just have one trigger on the PF which populates that derived column.

create table rob.myddstable (
mypfkey char(5));

alter table rob.myddstable
add column myDerivedColumn dec(5, 0);

create trigger myddspftrigger
before insert or update of mypfkey on rob.myddstable referencing new as new_row for each row mode db2row set new_row.myDerivedColumn = cast(new_row.mypfkey as dec(5, 0));

insert into rob.myddstable (mypfkey) values('12345'); select * from myddstable; ....+....1....+....2....
MYPFKEY MYDERIVEDCOLUMN
12345 12,345

update myddstable
set mypfkey='45678' where mypfkey='12345'

select * from myddstable
....+....1....+....2....
MYPFKEY MYDERIVEDCOLUMN
45678 45,678

insert into rob.myddstable (mypfkey) values('1A345') SQL trigger MYDDSPFTRIGGER in ROB failed with SQLCODE -420 SQLSTATE 22018, and the message is Character in CAST argument not valid
(note: insert did not happen)




Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Justin Taylor <JUSTIN@xxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 05/22/2017 12:09 PM
Subject: RE: Ref constraint: Numeric to Character
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



:(

The DDS PF has dozens (or more) unofficial dependent PF's. Setting
triggers for every one is almost certainly a non-starter.



-----Original Message-----
From: Rob Berendt [mailto:rob@xxxxxxxxx]
Sent: Monday, May 22, 2017 8:09 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Ref constraint: Numeric to Character

Derived in the PF is not going to happen. Workaround would be a trigger.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com