Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: August 18, 2016 - 3:11 pm UTC

Last updated: August 19, 2016 - 3:01 am UTC

Version: 10

Viewed 1000+ times

You Asked

In SQL-Server I could do a

IF <Condition> BEGIN <SQLStatement> END

example:

IF Exists (select 1 from document where id=5) begin select * from sometable END

in an SQL-Statement - notice: SQL-Statement!

In Oracle as far as I know I could only use IF in PL/SQL.

How could I use IF THEN END in a normal SQL?

and Chris said...

You can emulate IF THEN END in SQL using CASE:

select 
  case
    when exists (select null from dual) then 1 
    else 2
  end case1, 
  case
    when dummy = 'X' then 'dummy'
    else 'smart'
  end case2
from dual;

CASE1  CASE2  
1      dummy


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Could also select a cursor

Thomas Brotherton, August 18, 2016 - 4:21 pm UTC

This could be a possibility too

select cursor(select level lv from dual connect by level < 10) c
from dual
where exists (select 1 from dual where dummy = 'X')

A reader, August 18, 2016 - 5:32 pm UTC

Sorry,

I still don't understand. Please look at the following SQL:

IF (NOT EXISTS(SELECT * FROM ALL_TAB_COLUMNS WHERE UPPER(TABLE_NAME) = UPPER('BOLD_MEMBERMAPPING') AND UPPER(COLUMN_NAME) = UPPER('COLUMNINDEX'))
THEN
'ALTER TABLE BOLD_MEMBERMAPPING ADD COLUMNINDEX INTEGER DEFAULT 0 NOT NULL'
END


How could this SQL-Server-SQL transfered to Oracle?

T-SQL

Duke Ganote, August 18, 2016 - 6:45 pm UTC 

SQL> create table BOLD_MEMBERMAPPING ( X NUMBER );

Table created.

SQL> BEGIN
  2    FOR rec IN ( SELECT count(*) cnt
  3                   FROM ALL_TAB_COLUMNS
  4                  WHERE TABLE_NAME = 'BOLD_MEMBERMAPPING'
  5                    AND COLUMN_NAME = 'COLUMNINDEX'
  6               ) LOOP
  7      CASE WHEN rec.cnt = 0
  8           THEN
  9            EXECUTE IMMEDIATE 'ALTER TABLE BOLD_MEMBERMAPPING ADD COLUMNINDEX INTEGER DEFAULT 0 NOT NULL';
 10       END CASE;
 11    END LOOP;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL> desc BOLD_MEMBERMAPPING;
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 X                                            NUMBER
 COLUMNINDEX                         NOT NULL NUMBER(38)

Chris Saxon
August 19, 2016 - 3:01 am UTC

nice input.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.