I suggest you try removing the quotes in '$reply'
> if ( UPPER('$reply') = 'Y' )
Vince Laurent <vlaurent_at_NOSPAMPLEASE.networkusa.net> wrote in message
news:lsvbst04ofr8g6qsnr56klkl2gfbjrhpfc_at_4ax.com...
> I am inches away from getting this to work. It seems to work but it
> does not change the table after the user selects 'Y' to do so. (I
> have just my user id coded in there now so I don't hose the entire
> user record). Am I using a conditonal correctly? Maybe something
> with PL/SQL and SQL? I am running Oracle 8.0.6.2...
>
> --------------------------------------------------------------------------
> set heading on;
> set feedback off;
>
> /* Get the existing client numbers from the database */
> column mandt heading "Existing Client Numbers" format A25;
> select distinct mandt from usr02;
>
> clear;
> set heading off;
> select '-----------------------------------' from dual;
> set heading on;
>
> /* Ask the user what client they would like to do the work on */
> accept cnum PROMPT 'What client do you need to zero out the times for?
> '
>
> column mandt heading Client;
> column trdat heading Date;
> column ltime heading Time;
> column bname heading Username;
>
> select mandt, trdat, ltime, bname from usr02
> where mandt = &cnum;
>
> clear;
> set heading off;
> select ' 'from dual;
> set heading on;
>
> /* If the reply is good verify the users intent */
> clear;
> set heading off;
> accept reply prompt 'Are you sure you want to do this? (Y/[N])'
>
> set feedback on;
> set heading on;
>
> begin
> if ( UPPER('$reply') = 'Y' )
> then
> /* change the last login date and time to all zeros */
> /* use only MY userid for testing... */
> update usr02
> set ltime = '000000', trdat = '00000000'
> where mandt = &cnum
> and bname = 'VLAURE';
> commit;
> end if;
> end;
> /
>
> --------------------------------------------------------------------------
> Sample output:
>
> SQL> @zero
>
> Existing Client Numbers
> -------------------------
> 000
> 001
> 010
> 066
> 220
> 230
> 240
> 330
>
> -----------------------------------
> What client do you need to zero out the times for? 000
>
> Client Date Time Username
> ------------------------- -------- ------ ------------
> 000 00000000 000000 BATCH_USER
> 000 20011001 190929 DDIC
> 000 20011002 161120 DKNICK
> 000 20010130 142453 OSSUSER
> 000 20010518 143351 RAGRAW
> 000 20011001 184203 SAP*
> 000 19960903 110701 SAPCPIC
> 000 20011002 134813 TKISSE
> 000 00000000 000000 TMSADM
> 000 20011011 141659 VLAURE
>
>
> Are you sure you want to do this? (Y/[N])y
>
> PL/SQL procedure successfully completed.
>
> --------------------------------------------------------------------------
> But when I go look at the table again I see the result has not been
> changed:
>
> Client Date Time Username
> ------ --------- ------- --------
> 000 20011011 141659 VLAURE <-------------
> 010 20011011 084635 VLAURE
> 220 20010201 181951 VLAURE
> 230 20010214 154702 VLAURE
> 240 20010103 140410 VLAURE
> 330 20010308 174658 VLAURE
>
> Ideas?
>
Received on Thu Oct 11 2001 - 20:18:40 CDT
