To add or delete any triggers, you need exclusive access to the
file. Your attempt is waiting for file to be available, then times
out.

On May 31, 7:24 am, Graybeard wrote:
> To add or delete any triggers, you need exclusive access to the
> file. Your attempt is waiting for file to be available, then times
> out.

Okay I got the trigger added. Then I fired myself to test the
functionality of the trigger, and sure enough, my EID showed up in my
work table just like I wanted it to.

After quickly re-hiring myself I started working on the 2nd trigger,
which is going to disable the ACS user account of anyone who
terminates/resigns/whatever AND has a valid ACS user account. ACS is
just a higher level cover for os/400.

So I wrote a 2nd trigger and it does not work, and this one has me
stumped. PADATA will contain the name of the user ID assigned to an
employee for as/400 access, if they have one. So step one when a
record gets put into the work table via trigger one is to associate
the employee ID number with the as/400 user id(if there is one). Item
2 is to set the active flag to "N" on anyone who has an as/400 account
and has met the conditions defined by the first trigger.

BEGIN ATOMIC

select padata from store.peis480h where pa# = new.em# and patid =
'AS400';
if padata is not null then update store.pacs260 set usract = 'N'
where usrprf = padata;
END

The 2nd line errors out with a "Variable PADATA not defined or not
usable". If I comment out line 2 with a -- the syntax check completes
normally. What am I doing wrong here?

Chris

il 31/05/2007 16.41, Scrive Hal 40706744:
> On May 31, 7:24 am, Graybeard wrote:
>> To add or delete any triggers, you need exclusive access to the
>> file. Your attempt is waiting for file to be available, then times
>> out.
>
> Okay I got the trigger added. Then I fired myself to test the
> functionality of the trigger, and sure enough, my EID showed up in my
> work table just like I wanted it to.
>
> After quickly re-hiring myself I started working on the 2nd trigger,
> which is going to disable the ACS user account of anyone who
> terminates/resigns/whatever AND has a valid ACS user account. ACS is
> just a higher level cover for os/400.
>
> So I wrote a 2nd trigger and it does not work, and this one has me
> stumped. PADATA will contain the name of the user ID assigned to an
> employee for as/400 access, if they have one. So step one when a
> record gets put into the work table via trigger one is to associate
> the employee ID number with the as/400 user id(if there is one). Item
> 2 is to set the active flag to "N" on anyone who has an as/400 account
> and has met the conditions defined by the first trigger.
>
> BEGIN ATOMIC
>
> select padata from store.peis480h where pa# = new.em# and patid =
> 'AS400';
> if padata is not null then update store.pacs260 set usract = 'N'
> where usrprf = padata;
> END
>
> The 2nd line errors out with a "Variable PADATA not defined or not
> usable". If I comment out line 2 with a -- the syntax check completes
> normally. What am I doing wrong here?
You should put padata somewhere, e.g.
define padata [same type as padata];
select padata into padata from store.peis480h where pa# = new.em# and
patid = 'AS400';

Now padata is defined.

--
Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

Presumably usrprf is never null, so couldn't you do something like
this:
UPDATE store.pacs260 SET usract='N'
WHERE usrprf = (SELECT padata FROM store.peis480h
WHERE pa#=new.em# and paid='AS400')

On May 31, 8:26 am, "walker.l2" wrote:
> Presumably usrprf is never null, so couldn't you do something like
> this:
> UPDATE store.pacs260 SET usract='N'
> WHERE usrprf = (SELECT padata FROM store.peis480h
> WHERE pa#=new.em# and paid='AS400')

Well technically it COULD be null, becuase we have lots of employees
who never use our as/400, so the first line of my query would return a
null value.

I've been trying to lookup how to use the define keyword to define the
variable padata but I'm striking out so far. It is a character field,
length of 10.

BEGIN ATOMIC
define padata char(10);
select padata from store.peis480h where pa# = new.em# and patid
='AS400';
if padata is not null then update store.pacs260 set usract = 'N'
where usrprf = padata;
END

This doesn't work, but I'm still playing with it.

Chris

On May 31, 8:26 am, "walker.l2" wrote:
> Presumably usrprf is never null, so couldn't you do something like
> this:
> UPDATE store.pacs260 SET usract='N'
> WHERE usrprf = (SELECT padata FROM store.peis480h
> WHERE pa#=new.em# and paid='AS400')

Well technically it COULD be null, becuase we have lots of employees
who never use our as/400, so the first line of my query would return a
null value.

I've been trying to lookup how to use the define keyword to define the
variable padata but I'm striking out so far. It is a character field,
length of 10.

BEGIN ATOMIC
define padata char(10);
select padata from store.peis480h where pa# = new.em# and patid
='AS400';
if padata is not null then update store.pacs260 set usract = 'N'
where usrprf = padata;
END

This doesn't work, but I'm still playing with it.

Chris

What I meant was presumably usrprf (not padata) is never null. So even
if your (S0ELECT padata...) returns null, no update takes place on
pacs260 (because WHERE usrprf = null would not match any rows). Or do
you mean that usrprf = null would give some row matches on pacs260?

il 31/05/2007 17.06, Scrive Dr.UgoGagliardelli 43932392:
> il 31/05/2007 16.41, Scrive Hal 40706744:
>> On May 31, 7:24 am, Graybeard wrote:
>>> To add or delete any triggers, you need exclusive access to the
>>> file. Your attempt is waiting for file to be available, then times
>>> out.
>>
>> Okay I got the trigger added. Then I fired myself to test the
>> functionality of the trigger, and sure enough, my EID showed up in my
>> work table just like I wanted it to.
>>
>> After quickly re-hiring myself I started working on the 2nd trigger,
>> which is going to disable the ACS user account of anyone who
>> terminates/resigns/whatever AND has a valid ACS user account. ACS is
>> just a higher level cover for os/400.
>>
>> So I wrote a 2nd trigger and it does not work, and this one has me
>> stumped. PADATA will contain the name of the user ID assigned to an
>> employee for as/400 access, if they have one. So step one when a
>> record gets put into the work table via trigger one is to associate
>> the employee ID number with the as/400 user id(if there is one). Item
>> 2 is to set the active flag to "N" on anyone who has an as/400 account
>> and has met the conditions defined by the first trigger.
>>
>> BEGIN ATOMIC
>>
>> select padata from store.peis480h where pa# = new.em# and patid =
>> 'AS400';
>> if padata is not null then update store.pacs260 set usract = 'N'
>> where usrprf = padata;
>> END
>>
>> The 2nd line errors out with a "Variable PADATA not defined or not
>> usable". If I comment out line 2 with a -- the syntax check completes
>> normally. What am I doing wrong here?
> You should put padata somewhere, e.g.
> define padata [same type as padata];
Should be:
declare padata [same type as padata];

> select padata into padata from store.peis480h where pa# = new.em# and
> patid = 'AS400';
>
> Now padata is defined.
>


--
Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

il 31/05/2007 17.37, Scrive Hal 43932392:
> On May 31, 8:26 am, "walker.l2" wrote:
>> Presumably usrprf is never null, so couldn't you do something like
>> this:
>> UPDATE store.pacs260 SET usract='N'
>> WHERE usrprf = (SELECT padata FROM store.peis480h
>> WHERE pa#=new.em# and paid='AS400')
>
> Well technically it COULD be null, becuase we have lots of employees
> who never use our as/400, so the first line of my query would return a
> null value.
>
> I've been trying to lookup how to use the define keyword to define the
> variable padata but I'm striking out so far. It is a character field,
> length of 10.
>
> BEGIN ATOMIC
> define padata char(10);
> select padata from store.peis480h where pa# = new.em# and patid
> ='AS400';
> if padata is not null then update store.pacs260 set usract = 'N'
> where usrprf = padata;
> END
>
> This doesn't work, but I'm still playing with it.

In my opinion even if you "DECLARE PADATA CHAR(10)" and don't use any
method to evaluate it, it will be always emty not null.
So if your select returns a single row, you can use SELECT INTO,
otherwise you shoud fetch a cursor. I do not know other ways.

--
Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

On May 31, 7:41 am, Hal wrote:
> On May 31, 7:24 am, Graybeard wrote:
>
> > To add or delete any triggers, you need exclusive access to the
> > file. Your attempt is waiting for file to be available, then times
> > out.
>
> Okay I got the trigger added. Then I fired myself to test the
> functionality of the trigger, and sure enough, my EID showed up in my
> work table just like I wanted it to.
>
> After quickly re-hiring myself I started working on the 2nd trigger,
> which is going to disable the ACS user account of anyone who
> terminates/resigns/whatever AND has a valid ACS user account. ACS is
> just a higher level cover for os/400.
>
> So I wrote a 2nd trigger and it does not work, and this one has me
> stumped. PADATA will contain the name of the user ID assigned to an
> employee for as/400 access, if they have one. So step one when a
> record gets put into the work table via trigger one is to associate
> the employee ID number with the as/400 user id(if there is one). Item
> 2 is to set the active flag to "N" on anyone who has an as/400 account
> and has met the conditions defined by the first trigger.
>
> BEGIN ATOMIC
>
> select padata from store.peis480h where pa# = new.em# and patid =
> 'AS400';
> if padata is not null then update store.pacs260 set usract = 'N'
> where usrprf = padata;
> END
>
> The 2nd line errors out with a "Variable PADATA not defined or not
> usable". If I comment out line 2 with a -- the syntax check completes
> normally. What am I doing wrong here?


First, you need to create a variable to hold the result:

declare w_padata char(50);

[change the data definition to match whatever it actually is in
peis480h]

Then, change your select to:

set w_padata = select padata from store.peis480h where... ;

On May 31, 2:22 pm, Jonathan Ball wrote:
> On May 31, 7:41 am, Hal wrote:
>
>
>
>
>
> > On May 31, 7:24 am, Graybeard wrote:
>
> > > To add or delete any triggers, you need exclusive access to the
> > > file. Your attempt is waiting for file to be available, then times
> > > out.
>
> > Okay I got the trigger added. Then I fired myself to test the
> > functionality of the trigger, and sure enough, my EID showed up in my
> > work table just like I wanted it to.
>
> > After quickly re-hiring myself I started working on the 2nd trigger,
> > which is going to disable the ACS user account of anyone who
> > terminates/resigns/whatever AND has a valid ACS user account. ACS is
> > just a higher level cover for os/400.
>
> > So I wrote a 2nd trigger and it does not work, and this one has me
> > stumped. PADATA will contain the name of the user ID assigned to an
> > employee for as/400 access, if they have one. So step one when a
> > record gets put into the work table via trigger one is to associate
> > the employee ID number with the as/400 user id(if there is one). Item
> > 2 is to set the active flag to "N" on anyone who has an as/400 account
> > and has met the conditions defined by the first trigger.
>
> > BEGIN ATOMIC
>
> > select padata from store.peis480h where pa# = new.em# and patid =
> > 'AS400';
> > if padata is not null then update store.pacs260 set usract = 'N'
> > where usrprf = padata;
> > END
>
> > The 2nd line errors out with a "Variable PADATA not defined or not
> > usable". If I comment out line 2 with a -- the syntax check completes
> > normally. What am I doing wrong here?
>
> First, you need to create a variable to hold the result:
>
> declare w_padata char(50);
>
> [change the data definition to match whatever it actually is in
> peis480h]
>
> Then, change your select to:
>
> set w_padata = select padata from store.peis480h where... ;

Sorry...you need to put the select statement in parentheses:

set w_padata = (select padata from store.peis480h where... ) ;