If statement with multiple SQL select statements PL/SQL (ORACLE APEX)

Question

I was wondering if it is possible to have multiple SQL select statements within an if statement using PL/SQL. I wanted different select statements based on the value of another field.

This is what I tried so far but I'm stuck and can't go on

    BEGIN
    IF
        :P3_COMPAREWITH <> 'OTHER TABLES'
    THEN
        SELECT TABLE_NAME AS TARGET, TABLE_NAME AS TARGETTABLE 
        FROM [email protected] 
        WHERE OWNER = 'TOSAD_2017_2D_TEAM4_TARGET';
    ELSIF
        :P3_COMPAREWITH = 'OTHER TABLES'
    THEN
        SELECT TABLE_NAME AS TARGET, TABLE_NAME AS TARGETTABLE 
        FROM [email protected] 
        WHERE OWNER = 'TOSAD_2017_2D_TEAM4_TARGET' AND TABLE_NAME <> :P3_TARGETTABLE;
    END IF;
    END;

If someone could help me out, thanks in advance.

Just select statement in PL/SQL is meaningless. You need to define where you want to put selected data, and there are a lot of possibilities (variables, records, collections, cursors...) for this. So tell us what do you want to do with selected data. — Dmitriy
– Dmitriy, Commented Jan 18, 2018 at 13:51

hekko · Accepted Answer · 2018-01-18 13:24:08Z

1

if I understand you correctly then you can change the way

DECLARE
    P3_COMPAREWITH VARCHAR2(128) := '';
    P3_TARGETTABLE VARCHAR2(128) := '';
BEGIN

FOR CUR IN ( SELECT TABLE_NAME AS TARGET, TABLE_NAME AS TARGETTABLE
    FROM [email protected] 
    WHERE OWNER = 'TOSAD_2017_2D_TEAM4_TARGET' AND ( TABLE_NAME <> P3_TARGETTABLE OR P3_COMPAREWITH <> 'OTHER TABLES' ) )
LOOP
    --use data from cursor
END LOOP;

END;

edited Jan 18, 2018 at 13:24

answered Jan 18, 2018 at 13:07

hekko

2921 gold badge2 silver badges6 bronze badges

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

Comments

Littlefoot · Accepted Answer · 2018-01-18 21:04:39Z

If I understood you correctly, you want to select from two different tables, depending on P3_COMPAREWITH item value. This is most probably NOT supposed to be a PL/SQL but report source or something like that. Doesn't matter if I'm wrong about it, the principle should be the same - in PL/SQL you'd need the INTO clause.

So: create UNION of two queries, and let P3_COMPAREWITH decide which one of them will be used.

SELECT TABLE_NAME AS TARGET, 
       TABLE_NAME AS TARGETTABLE 
FROM [email protected] 
WHERE OWNER = 'TOSAD_2017_2D_TEAM4_TARGET'
  AND :P3_COMPAREWITH <> 'OTHER TABLES'        --> this
--
UNION                                          --> this
--
SELECT TABLE_NAME AS TARGET, 
       TABLE_NAME AS TARGETTABLE 
FROM [email protected] 
WHERE OWNER = 'TOSAD_2017_2D_TEAM4_TARGET' 
  AND TABLE_NAME <> :P3_TARGETTABLES
  AND :P3_COMPAREWITH = 'OTHER TABLES'         --> this

Collectives™ on Stack Overflow

If statement with multiple SQL select statements PL/SQL (ORACLE APEX)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related