CODEWITHSUNDEEP
sqlif-statementselectplsqloracle-apex
Marwan Bella
Marwan Bella

Reputation: 1

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

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.

Upvotes: 0

Views: 1540

Answers (2)

Littlefoot
Littlefoot

Reputation: 142710

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

Upvotes: 0

hekko
hekko

Reputation: 292

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;

Upvotes: 1

Related Questions