• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7407
  • Last Modified:

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'

0
surajguptha
Asked:
surajguptha
  • 2
  • 2
  • 2
  • +4
3 Solutions
 
boriskalavskyCommented:
0
 
surajgupthaAuthor Commented:
Yeah i can use Case to solve the problem. I am wondering if there is an IF, if so how to use that.
0
 
earth man2Commented:
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'
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
kumaran100Commented:
Hi ,
Here is the solution........  Hope this will Help You

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') SYS_TM_STMP
                   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






0
 
kumaran100Commented:
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
0
 
boriskalavskyCommented:
you can use case or decode.
0
 
randydCommented:
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.
0
 
SMartinHamburgCommented:
I you really want to use IF you can go for PLSQL. But unless you have samething really
special in mind and your example is just an over-simplification I see no good reason
why who would want to do this.
Kind Regards
0
 
surajgupthaAuthor Commented:
Kumaran100,
                  When i run that query it says missing keyword. Please let me know what to do.
0
 
JankovskyCommented:
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'
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now