Select statement in If block in Oracle PL/SQL

Question

Following is my PL/SQL block:

DROP TYPE t_tf_tab;
DROP TYPE t_tf_row;

CREATE TYPE t_tf_row AS OBJECT (
  zipzone_id NUMBER, 
       beginzipcode NUMBER, 
       endzipcode NUMBER, 
       cu_rate number,
       st_rate number,
       su_rate number
);


CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
        ----------------------------------------------------------------------------------------------
create or replace
FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED IS
    zip_id1 number;
    zip_id2 number;
    begin_zip number;
    end_zip number;
    rowcount number;
    rec  t_tf_row;
BEGIN

 select count(*) into rowcount from GSW_ZIP_ZONES;
    select min(ZIPZONE_ID) into zip_id1 from GSW_ZIP_ZONES;
    zip_id2 :=zip_id1 +1;

     select ENDZIPCODE into end_zip from GSW_ZIP_ZONES where ZIPZONE_ID=zip_id1;
    select BEGINZIPCODE into begin_zip from GSW_ZIP_ZONES where ZIPZONE_ID=zip_id2;

  FOR counter in 1..rowcount 

   LOOP 
    if((begin_zip-end_zip)>1) THEN    
    SELECT z.ZIPZONE_ID, z.BEGINZIPCODE, z.ENDZIPCODE, z.TAXING_CODE_ID, g.su_rate
    into rec
    from SBXEXT.GSW_ZIP_ZONES z
    join gsw_geocodes g on z.taxing_code_id= g.taxing_code_id where ZIPZONE_ID in (zip_id1,zip_id2);  

    PIPE ROW (rec);
    end if;

    zip_id1 :=zip_id1 + 1;
    zip_id2 :=zip_id2 + 1;


    END LOOP;

  RETURN;
END;

I am getting the error after If statement. The error is in the Select statement telling that the select statement would be ignored. I don't know how to use the Select statement in If block. Please help.

Share us the error message please!

Maheswaran Ravisankar
– Maheswaran Ravisankar

2014-10-03 20:33:56 +00:00
Commented Oct 3, 2014 at 20:33 — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Oct 3, 2014 at 20:33

Maheswaran Ravisankar · Accepted Answer · 2014-10-03 20:45:44Z

2

I see you use INTO keyword at last.. It has to be next to SELECT.

Add this to DECLARE

rec  t_tf_row;

And then,

SELECT z.ZIPZONE_ID, z.BEGINZIPCODE, z.ENDZIPCODE, z.TAXING_CODE_ID, g.su_rate
into rec
from SBXEXT.GSW_ZIP_ZONES z
join gsw_geocodes g on z.taxing_code_id= g.taxing_code_id 
where ZIPZONE_ID in (zip_id1,zip_id2)

edited Oct 3, 2014 at 20:45

answered Oct 3, 2014 at 20:34

Maheswaran Ravisankar

17.9k6 gold badges51 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Sormita Chakraborty Over a year ago

I created the types like below:

Sormita Chakraborty Over a year ago

DROP TYPE t_tf_tab; DROP TYPE t_tf_row; CREATE TYPE t_tf_row AS OBJECT ( zipzone_id NUMBER, beginzipcode NUMBER, endzipcode NUMBER, cu_rate number, st_rate number, su_rate number ); CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;

Sormita Chakraborty Over a year ago

Still it is giving error Error(2,48): PLS-00201: identifier 'T_TF_TAB' must be declared

Sormita Chakraborty Over a year ago

I have updated my code block, it is giving error in T_TF_TAB, i dont understand why

Maheswaran Ravisankar Over a year ago

You create the TYPE and FUNCTION with same user or schema?

|

Collectives™ on Stack Overflow

Select statement in If block in Oracle PL/SQL

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related