Link to home
Start Free TrialLog in
Avatar of surajguptha
surajgupthaFlag for United States of America

asked on

Oracle SQL If Statement

Is it possible to use a if statement in a oracle sql query ??

I am trying to implement something like

select if (ADDRESS.SYS_TM_STMP = null) then TO_CHAR(ADDRESS.SYS_TM_STMP, 'yyyy-mm-dd') || 'T' || TO_CHAR(ADDRESS.SYS_TM_STMP, 'hh24:mi:ss') SYS_TM_STMP else (SYS_TM_STMP) from organization where orgid='10'

SOLUTION
Avatar of boriskalavsky
boriskalavsky
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of surajguptha
surajguptha
Flag of United States of America image

ASKER

Yeah i can use Case to solve the problem. I am wondering if there is an IF, if so how to use that.
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
select replace( to_char( nvl(  ADDRESS.SYS_TM_STMP , SYSDATE ),  'yyyy-mm-dd hh24:mi:ss', ' ', 'T') ) AS SYS_TM_STMP from organization where orgid='10'
ASKER CERTIFIED SOLUTION
Avatar of kumaran100
kumaran100
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kumaran100
kumaran100
Hi,


1.
select case  when  ADDRESS.SYS_TM_STMP is null then
                          TO_CHAR(ADDRESS.SYS_TM_STMP, 'yyyy-mm-dd') || 'T' ||
                                 TO_CHAR(ADDRESS.SYS_TM_STMP, 'hh24:mi:ss')                  
                    else
                           SYS_TM_STMP
                   end
from            organization
where          orgid = '10';

2.
select           decode( ADDRESS.SYS_TM_STMP , NULL  ,
                          TO_CHAR(ADDRESS.SYS_TM_STMP, 'yyyy-mm-dd') || 'T' ||
                          TO_CHAR(ADDRESS.SYS_TM_STMP, 'hh24:mi:ss')  ,  SYS_TM_STMP                  
                   end
from            organization
where          orgid = '10';




Rdgs,
kums
Avatar of boriskalavsky
boriskalavsky
you can use case or decode.
Avatar of randyd
randyd
case or decode is the typical response to this type of question - earthman2 shows the SQL solution without resorting to the recent more programmatic extensions.

i think there is almost always a sql solution without the need for an IF.
SOLUTION
Avatar of SMartinHamburg
SMartinHamburg
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of surajguptha
surajguptha
Flag of United States of America image

ASKER

Kumaran100,
                  When i run that query it says missing keyword. Please let me know what to do.
Avatar of Jankovsky
Jankovsky
Flag of Czechia image
Teh described functionality you can solve such way:

select NVL(SYS_TM_STMP,TO_CHAR(ADDRESS.SYS_TM_STMP, 'yyyy-mm-dd') || 'T' || TO_CHAR(ADDRESS.SYS_TM_STMP, 'hh24:mi:ss')) from organization where orgid='10'

The statement could work just if there is a package ADDRESS.
But I affraid there shoud be some join of the ADDRESS table in the statement, like this:

select NVL(organization.SYS_TM_STMP,TO_CHAR(ADDRESS.SYS_TM_STMP, 'yyyy-mm-dd') || 'T' || TO_CHAR(ADDRESS.SYS_TM_STMP, 'hh24:mi:ss')) from organization join address on(address.<key>=organization.<key> )
where orgid='10'