Posts Tagged ‘Oracle SQL’

Oracle SQL – Remove Trailing Zeroes From Phone Columns

 

Requirement

I designed the sql to extract Contacts from Siebel Database (Oracle Database in this case) including phone columns. The challenge that I faced with phone columns:

  • Trailing zeroes (Used by Siebel to capture phone number format)

My extract was not supposed to contain them.

 

Concept

Siebel puts a Newline character between actual phone data and its format.

Also, in my case, Siebel database was installed on a Unix server. In Unix, Newline character is represented by LineFeed (LF) character (\n), Its ASCII code is 10

 

Solution

I put up three in-built SQL library functions to use as follow:

Select
  PC.WORK_PH_NUM “Full WorkPhone#”,
  Case When INSTR(PC.WORK_PH_NUM,CHR(10)) > 0
     Then SUBSTR(PC.WORK_PH_NUM,0,INSTR(PC.WORK_PH_NUM,CHR(10))-1)
     Else PC.WORK_PH_NUM
  End “Updated WorkPhone#”
From Siebel.S_Contact PC;

 

Oracle SQL – Date Difference In Days

 

Scenario

It was required to take a full extract of Service Requests from database. But there was a calculated field “Days Open” as follows:

IIf ([Status] = LookupValue(“SR_STATUS”, “Open”), Timestamp() – [Created],[Updated] – [Created])

Now the target was to design an Oracle SQL for calculating number of days a Service Request was open.

 

Solution

select s.row_id
, s.SR_STAT_ID
, To_char(s.created, ‘MM/DD/YYYY HH24:MI:SS’) “Created”
, To_char(s.last_upd, ‘MM/DD/YYYY HH24:MI:SS’) “Last_Updated”
, To_char(sysdate, ‘MM/DD/YYYY HH24:MI:SS’) “Today”
, Case
      When s.SR_STAT_ID = ‘Open’ Then to_char (sysdate – s.created, ‘999999D99’)
      Else to_char (s.last_upd – s.created, ‘999999D99’)
   End “Days Open”
from SIEBEL.S_SRV_REQ s;

Download SQL – Date Difference

Please note that I have utilized To_Char function with an explicit format specification.