CODEWITHSUNDEEP
sql-serveroracle-databaseif-statementselectplsql
All Mezzi
All Mezzi

Reputation: 3

Execute select statement within an IF clause Oracle

This is a query from SQL Server, what I want to ask is, how do I want to get the same result in Oracle, what would be the query?

I need result like this: 'this is query on SQL Server'

declare @a int = 6

if @a = 1
begin
    select * 
    from table_A
end
else
begin
    select * 
    from table_B
end

I have tried like this on oracle but failed

    declare 
    type FF_rec is record(
        st_Value number
        );
        var_ff FF_rec;
begin
var_ff.st_Value:=1;
    if 1<=0 then
    select * from Table_A;
    else
    select * from Table_B;
    end if;
end;

Upvotes: 0

Views: 176

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

If it has to be at SQL level, then maybe you can use such a code: depending on a value passed to PAR_WHAT substitution variable, you'll get result from EMP or DEPT table.

Because of UNION set operator, select column lists must match in number and datatype, i.e.

  • you can't select e.g. two columns from the 1st select and 5 columns from the 2nd;
  • nor can they not be matched in datatype, e.g. 1st column of 1st select is DATE, while 1st column of 2nd select is NUMBER

So:

SQL> select empno, ename, job
  2  from emp
  3  where &&par_what = 1
  4  union
  5  select deptno, dname, loc
  6  from dept
  7  where &&par_what = 2;
Enter value for par_what: 1

     EMPNO ENAME          JOB
---------- -------------- -------------
      7369 SMITH          CLERK
      7499 ALLEN          SALESMAN
      7521 WARD           SALESMAN
      7566 JONES          MANAGER
      7654 MARTIN         SALESMAN
      <snip>

14 rows selected.

SQL> undefine par_what
SQL> /
Enter value for par_what: 2

     EMPNO ENAME          JOB
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

If you're OK with PL/SQL, then one option is to create a function that returns refcursor. Which one? Depends on parameter you pass to it:

SQL> create or replace function f_test (par_what in number)
  2    return sys_refcursor
  3  is
  4    l_rc sys_refcursor;
  5  begin
  6    if par_what = 1 then
  7       open l_rc for select deptno, ename, job, sal, hiredate from emp;
  8    elsif par_what = 2 then
  9       open l_rc for select deptno, dname, loc from dept;
 10    end if;
 11
 12    return l_rc;
 13  end;
 14  /

Function created.

Testing:

SQL> select f_test(1) from dual;

F_TEST(1)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO ENAME      JOB              SAL HIREDATE
---------- ---------- --------- ---------- ----------
        20 SMITH      CLERK            800 17.12.1980
        30 ALLEN      SALESMAN        1600 20.02.1981
        30 WARD       SALESMAN        1250 22.02.1981
        20 JONES      MANAGER         2975 02.04.1981
        30 MARTIN     SALESMAN        1250 28.09.1981
        30 BLAKE      MANAGER         2850 01.05.1981
<snip>

14 rows selected.


SQL> select f_test(2) from dual;

F_TEST(2)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON


SQL>

Upvotes: 1

Related Questions