Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

using CASE or DECODE

Status
Not open for further replies.
loveday

loveday

Programmer
Sep 19, 2004
106
US
hello Oracle gurus,

It's been quite awhile since I have worked with Oracle.

Today, I have been asked to modify an existing code but I am stumped as to how to proceed.

Per code below, The changes that I am asked to make says that there is a parameter called new_fresh.

If new_fresh = 'R' ( R for Refresh and N for New) then

delete all records from ALTIDINDX
WHERE Taxyr = Param Taxyr and idname = 'RPID'

elseif new_fresh = 'N' then

just execute entire code without deleting anything.

Since this code is not a stored procedure, I can't use an IF statement and I have a hard time with either decode or case statement.

I was wondering if anyone would be kind enough to assist.

Thanks in advance

Here is the code I am trying to modify to add the case or decode statements.

Code: 
SET TRIMSPOOL ON

SET VERIFY OFF
SET SERVEROUTPUT ON SIZE 1000000

col prtque new_value filename
col tyr    new_value taxyr
col id1    new_value pid1
col id2    new_value pid2
col param1 new_value rolltypes
col param2 new_value new_refresh

select   prtque,
         jur,
         taxyr tyr,
         id1,
         id2,
         multiparam( 'IN', null, param1 ) param1,
         NVL( param2, 'N' ) param2
from     param
where    rptname='PP504GA';

spool &&filename

DECLARE
   CURSOR c IS
      SELECT   a.jur, a.taxyr, a.parid
      FROM     ppadmn a
      WHERE    ( ( '&&pid1' <> 'IDLIST' and a.parid between '&&pid1' and '&&pid2' ) or
               ( '&&pid1' = 'IDLIST' and a.parid in ( select parid from idlist where setname = '&&pid2' ) ) )
      AND      a.taxyr = &&taxyr
      AND      a.rolltype in ( &&rolltypes )
      AND      ( ( '&&new_refresh' = 'R' ) or
                 ( '&&new_  ' = 'N' and not exists ( select 'x'
                                                           from altidindx
                                                           where parid = a.parid
                                                           and taxyr = a.taxyr
                                                           and idname='RPID' ) )
               )
      AND      a.cur = 'Y';


               l_count     NUMBER := 0;
               l_adrno     ADDRINDX.ADRNO%TYPE;
               l_adrstr    ADDRINDX.ADRSTR%TYPE;
               l_cityname  ADDRINDX.CITYNAME%TYPE;
               l_taxyr     ADDRINDX.TAXYR%TYPE;
               l_loc2      ADDRINDX.LOC2%TYPE;
               l_parid     PARDAT.PARID%TYPE;
               v_cnt       NUMBER := 0;
               l_upd       NUMBER := 0;
               l_continue  VARCHAR2(1) := 'Y';
               l_dashparid VARCHAR2(50);
               v_sqlerrm      VARCHAR2(4000);

BEGIN
   FOR r IN c
   LOOP
         l_continue := 'Y';
      BEGIN

         SELECT   adrno, adrstr, cityname, taxyr, loc2, dashx(parid)
         INTO     l_adrno, l_adrstr, l_cityname, l_taxyr, l_loc2, l_dashparid
         FROM     addrindx
         WHERE    parid = r.parid
         AND      taxyr = r.taxyr
         AND      jur = '000'
         AND      cur = 'Y';
      EXCEPTION
         WHEN others THEN
            dbms_output.put_line( 'No Address record for this account with parid=' || r.parid || ' and taxyr=' || r.taxyr );
            l_continue := 'N';
      END;

      IF ( l_continue = 'Y' ) THEN
           IF (l_adrno is not null AND l_adrstr is not null)
           THEN
            BEGIN
               SELECT PARID
               INTO   l_parid
               FROM   PARDAT
               WHERE  cityname = l_cityname
               AND    adrstr   = l_adrstr
               AND    adrno    = l_adrno
               AND    taxyr    = l_taxyr
               AND    cur      = 'Y';
            EXCEPTION
               WHEN others THEN
                  dbms_output.put_line( 'No current PARDAT record for parid=' || l_dashparid || ' and taxyr=' || r.taxyr );
                  l_parid := null;
            END;
            end if;

      ELSIF (l_adrno is null AND l_adrstr is null AND l_loc2 is not null)
             --l_continue := 'Y';
             THEN

             BEGIN
                SELECT PARID
                INTO   l_parid
                FROM   PARDAT
                WHERE  loc2  = l_loc2
                AND    taxyr = l_taxyr
                AND    cur   = 'Y';
             EXCEPTION
                WHEN others THEN
                   dbms_output.put_line( 'No current PARDAT record for parid=' || l_dashparid || ' and taxyr=' || r.taxyr );
                   l_parid := null;
             END;

      END IF;

      BEGIN
         UPDATE ppadmn
         SET    realref = l_parid
         WHERE  taxyr   = l_taxyr
         AND    parid   = r.parid
         AND    cur     = 'Y';

          l_upd := l_upd + SQL%ROWCOUNT;
         EXCEPTION
         WHEN others THEN
            dbms_output.put_line( 'No record exist for update...');
         END;

      BEGIN
            INSERT INTO ALTIDINDX(JUR, PARID, TAXYR, IDNAME, ALTID, WHO, WEN)
            VALUES               (r.jur, r.parid, r.taxyr, 'RPID', l_parid, 'CONVERT', sysdate);

         v_cnt := v_cnt + 1;

      EXCEPTION
          WHEN dup_val_on_index THEN
            UPDATE   altidindx
            SET      altid = l_parid
            WHERE    jur = r.jur
            AND      parid = r.parid
            AND      taxyr = r.taxyr
            AND      idname = 'RPID';

            v_cnt := v_cnt + sql%rowcount;
          WHEN others THEN
            v_sqlerrm := substr(sqlerrm,1,4000);

            dbms_output.put_line(r.jur||'-'||r.parid||'-'||r.taxyr||v_sqlerrm);
         END;
   END LOOP;

COMMIT;

IF ( l_upd > 0 or v_cnt > 0 ) THEN
   dbms_output.put_line( 'Number of PPADMN    records updated        : ' || l_upd );
   dbms_output.put_line( 'Number of ALTIDINDX records created/updated: ' || v_cnt );
ELSE
   dbms_output.put_line( 'No records were processed.' );
END IF;
END;
/

spool off
 
Sort by date Sort by votes
Hello, Love,

I am puzzled:
LoveDay said:
Since this code is not a stored procedure, I can't use an IF statement
Click to expand...

Where did that come from? There is nothing about an anonymous block (your code, above) that prevents you from using an "IF" statement. In fact, your code already is using three "IF" statements (two of them nested). In your code, above, you can use "IF" and "CASE", but "DECODE" can appear within a SQL statement only.

So, given my clarification, could you please restate your challenge?

(Note: I've widened the screen page so that your code doesn't wrap in distracting places. [wink])

*************************************************************************************************************************************

Cheers,

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
@ 05:36 (07Jan05) UTC (aka "GMT" and "Zulu"),
@ 22:36 (06Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.
 
Upvote 0 Downvote
hi SantaMufasa,

Thanks very much for your kind response.

How would I use the If statement here?

I guess my confusion is where to use the IF statement?

Should I use it after record has already been inserted (with the insert statement above)?

oR Should it be used before the insert statement?

Can you please guide me?

When I posted this thread, I was thinking that the If statement would start at the top.

 
Upvote 0 Downvote
Love,

There are several ways to programmatically achieve your results. For best visibility and to cause the least disruption to your existing code, I made your existing main-line logic a "local procedure" (not stored procedure) within your anonymous block. So, the following alterations I made to your code should do what you want. My alterations are in bold type. (I include your full script for ease of copying and pasting into a script with which you may test.):

Code: 
SET TRIMSPOOL ON

SET VERIFY OFF
SET SERVEROUTPUT ON SIZE 1000000

col prtque new_value filename
col tyr    new_value taxyr
col id1    new_value pid1
col id2    new_value pid2
col param1 new_value rolltypes
col param2 new_value new_refresh

select   prtque,
         jur,
         taxyr tyr,
         id1,
         id2,
         multiparam( 'IN', null, param1 ) param1,
         NVL( param2, 'N' ) param2
from     param
where    rptname='PP504GA';

spool &&filename

DECLARE
   CURSOR c IS
      SELECT   a.jur, a.taxyr, a.parid
      FROM     ppadmn a
      WHERE    ( ( '&&pid1' <> 'IDLIST' and a.parid between '&&pid1' and '&&pid2' ) or
               ( '&&pid1' = 'IDLIST' and a.parid in ( select parid from idlist where setname = '&&pid2' ) ) )
      AND      a.taxyr = &&taxyr
      AND      a.rolltype in ( &&rolltypes )
      AND      ( ( '&&new_refresh' = 'R' ) or
                 ( '&&new_  ' = 'N' and not exists ( select 'x'
                                                           from altidindx
                                                           where parid = a.parid
                                                           and taxyr = a.taxyr
                                                           and idname='RPID' ) )
               )
      AND      a.cur = 'Y';


               l_count     NUMBER := 0;
               l_adrno     ADDRINDX.ADRNO%TYPE;
               l_adrstr    ADDRINDX.ADRSTR%TYPE;
               l_cityname  ADDRINDX.CITYNAME%TYPE;
               l_taxyr     ADDRINDX.TAXYR%TYPE;
               l_loc2      ADDRINDX.LOC2%TYPE;
               l_parid     PARDAT.PARID%TYPE;
               v_cnt       NUMBER := 0;
               l_upd       NUMBER := 0;
               l_continue  VARCHAR2(1) := 'Y';
               l_dashparid VARCHAR2(50);
               v_sqlerrm      VARCHAR2(4000);
[B]PROCEDURE NEW_FRESH_N IS[/B]
BEGIN
   FOR r IN c
   LOOP
         l_continue := 'Y';
      BEGIN

         SELECT   adrno, adrstr, cityname, taxyr, loc2, dashx(parid)
         INTO     l_adrno, l_adrstr, l_cityname, l_taxyr, l_loc2, l_dashparid
         FROM     addrindx
         WHERE    parid = r.parid
         AND      taxyr = r.taxyr
         AND      jur = '000'
         AND      cur = 'Y';
      EXCEPTION
         WHEN others THEN
            dbms_output.put_line( 'No Address record for this account with parid=' || r.parid || ' and taxyr=' || r.taxyr );
            l_continue := 'N';
      END;

      IF ( l_continue = 'Y' ) THEN
           IF (l_adrno is not null AND l_adrstr is not null)
           THEN
            BEGIN
               SELECT PARID
               INTO   l_parid
               FROM   PARDAT
               WHERE  cityname = l_cityname
               AND    adrstr   = l_adrstr
               AND    adrno    = l_adrno
               AND    taxyr    = l_taxyr
               AND    cur      = 'Y';
            EXCEPTION
               WHEN others THEN
                  dbms_output.put_line( 'No current PARDAT record for parid=' || l_dashparid || ' and taxyr=' || r.taxyr );
                  l_parid := null;
            END;
            end if;

      ELSIF (l_adrno is null AND l_adrstr is null AND l_loc2 is not null)
             --l_continue := 'Y';
             THEN

             BEGIN
                SELECT PARID
                INTO   l_parid
                FROM   PARDAT
                WHERE  loc2  = l_loc2
                AND    taxyr = l_taxyr
                AND    cur   = 'Y';
             EXCEPTION
                WHEN others THEN
                   dbms_output.put_line( 'No current PARDAT record for parid=' || l_dashparid || ' and taxyr=' || r.taxyr );
                   l_parid := null;
             END;

      END IF;

      BEGIN
         UPDATE ppadmn
         SET    realref = l_parid
         WHERE  taxyr   = l_taxyr
         AND    parid   = r.parid
         AND    cur     = 'Y';

          l_upd := l_upd + SQL%ROWCOUNT;
         EXCEPTION
         WHEN others THEN
            dbms_output.put_line( 'No record exist for update...');
         END;

      BEGIN
            INSERT INTO ALTIDINDX(JUR, PARID, TAXYR, IDNAME, ALTID, WHO, WEN)
            VALUES               (r.jur, r.parid, r.taxyr, 'RPID', l_parid, 'CONVERT', sysdate);

         v_cnt := v_cnt + 1;

      EXCEPTION
          WHEN dup_val_on_index THEN
            UPDATE   altidindx
            SET      altid = l_parid
            WHERE    jur = r.jur
            AND      parid = r.parid
            AND      taxyr = r.taxyr
            AND      idname = 'RPID';

            v_cnt := v_cnt + sql%rowcount;
          WHEN others THEN
            v_sqlerrm := substr(sqlerrm,1,4000);

            dbms_output.put_line(r.jur||'-'||r.parid||'-'||r.taxyr||v_sqlerrm);
         END;
   END LOOP;

COMMIT;

IF ( l_upd > 0 or v_cnt > 0 ) THEN
   dbms_output.put_line( 'Number of PPADMN    records updated        : ' || l_upd );
   dbms_output.put_line( 'Number of ALTIDINDX records created/updated: ' || v_cnt );
ELSE
   dbms_output.put_line( 'No records were processed.' );
END IF;
END;
[B]BEGIN
   IF UPPER('&&new_refresh') = 'R' THEN
	delete ALTIDINDX
            WHERE Taxyr = Param Taxyr and idname = 'RPID';
   ELSIF UPPER('&&new_refresh') = 'N' THEN
        NEW_FRESH_N;
   ELSE
        RAISE_APPLICATION_ERROR
          (-20000,'Error: "NEW_REFRESH" (current value: "&&new_refresh") '||
           'must have a value of either "R" or "N".');
   END IF;
END;[/b]

/

spool off

Let us know if this resolves your need.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
@ 17:34 (07Jan05) UTC (aka "GMT" and "Zulu"),
@ 10:34 (07Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Livia8
  • Locked
  • Question
SQL SELECT - where date is null or greater than 2
Replies
9
Views
4K
carp
carp
Rasheed Shaik
  • Locked
  • Question
\Orant\DBS\FMCUS.MSG not found
Replies
0
Views
891
Rasheed Shaik
Rasheed Shaik
Olutee360
  • Locked
  • Question
Group by using values in a range 1
Replies
4
Views
201
Olutee360
Olutee360
sa0309
  • Locked
  • Question
Case expression in the WHERE clause of a delete statment
Replies
2
Views
157
carp
carp
hipath4k
  • Locked
  • Question
Share link to download Orcale 10 G
Replies
0
Views
160
hipath4k
hipath4k

Part and Inventory Search

Sponsor

Back
Top