Home » SQL & PL/SQL » SQL & PL/SQL » Oracle SQL Query (10.2.0.3.0)
Oracle SQL Query [message #406244] Wed, 03 June 2009 00:46 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have written the folowing query.
SELECT DECODE (SUBSTR (PHNUM, 1, 1),
                     '2', '0'
                      || SUBSTR (PHNUM, 1, 1)
                      || '/'
                      || SUBSTR (PHNUM, 2, 3)
                      || '.'
                      || SUBSTR (PHNUM, 5, 2)
                      || '.'
                      || SUBSTR (PHNUM, 7, 2),
                     '3', '0'
                      || SUBSTR (PHNUM, 1, 1)
                      || '/'
                      || SUBSTR (PHNUM, 2, 3)
                      || '.'
                      || SUBSTR (PHNUM, 5, 2)
                      || '.'
                      || SUBSTR (PHNUM, 7, 2),
                     '4', DECODE (SUBSTR (PHNUM, 2, 1),
                                  '7', '0'
                                   || SUBSTR (PHNUM, 1, 3)
                                   || '/'
                                   || SUBSTR (PHNUM, 4, 2)
                                   || '.'
                                   || SUBSTR (PHNUM, 6, 2)
                                   || '.'
                                   || SUBSTR (PHNUM, 8, 2),
                                  '8', '0'
                                   || SUBSTR (PHNUM, 1, 3)
                                   || '/'
                                   || SUBSTR (PHNUM, 4, 2)
                                   || '.'
                                   || SUBSTR (PHNUM, 6, 2)
                                   || '.'
                                   || SUBSTR (PHNUM, 8, 2),
                                  '9', '0'
                                   || SUBSTR (PHNUM, 1, 3)
                                   || '/'
                                   || SUBSTR (PHNUM, 4, 2)
                                   || '.'
                                   || SUBSTR (PHNUM, 6, 2)
                                   || '.'
                                   || SUBSTR (PHNUM, 8, 2),
                                     '0'
                                  || SUBSTR (PHNUM, 1, 1)
                                  || '/'
                                  || SUBSTR (PHNUM, 2, 3)
                                  || '.'
                                  || SUBSTR (PHNUM, 5, 2)
                                  || '.'
                                  || SUBSTR (PHNUM, 7, 2)
                                 ),
                     '8', DECODE (SUBSTR (PHNUM, 3, 1),
                          '0','0'
                          || SUBSTR (PHNUM, 1, 3)
                          || '/'
                          || SUBSTR (PHNUM, 4, 2)
                          || '.'
                          || SUBSTR (PHNUM, 6, 3),
                          '0'
                          || SUBSTR (PHNUM, 1, 2)
                          || '/'
                          || SUBSTR (PHNUM, 3, 2)
                          || '.'
                          || SUBSTR (PHNUM, 5, 2)
                          || '.'
                          || SUBSTR (PHNUM, 7, 2)
                          ),
                     '9', DECODE (SUBSTR(PHNUM, 2,1),
                          '0','0'
                          || SUBSTR (PHNUM, 1, 3)
                          || '/'
                          || SUBSTR (PHNUM, 4, 2)
                          || '.'
                          || SUBSTR (PHNUM, 6, 3),
                          '0'
                          || SUBSTR (PHNUM, 1, 1)
                          || '/'
                          || SUBSTR (PHNUM, 2, 3)
                          || '.'
                          || SUBSTR (PHNUM, 5, 2)
                          || '.'
                          || SUBSTR (PHNUM, 7, 2)
                          ),
                     '0', SUBSTR (PHNUM, 1, 3)
                      || '/'
                      || SUBSTR (PHNUM, 4, 2)
                      || '.'
                      || SUBSTR (PHNUM, 6, 2)
                      || '.'
                      || SUBSTR (PHNUM, 8, 2),
                        '0'
                     || SUBSTR (PHNUM, 1, 2)
                     || '/'
                     || SUBSTR (PHNUM, 3, 2)
                     || '.'
                     || SUBSTR (PHNUM, 5, 2)
                     || '.'
                     || SUBSTR (PHNUM, 7, 2)
                    )
        FROM dual;



Teh O/P is as follows.

9914558500-9914558505||9914558550||9914558555

But required O/P to be

014/55.85.00||014/55.85.05||014/55.85.50||014/55.85.55

OR

014/55.85.00-014/55.85.05||014/55.85.50||014/55.85.55

so please let me know what are the required modifications I need to do in the above mentioned query.

Thank you
Re: Oracle SQL Query [message #406248 is a reply to message #406244] Wed, 03 June 2009 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Clearly explain your specifications with WORDS and not just an output.
Post a working Test case: create table and insert statements along with the result you want with these data.
If I execute your statement I get:
                     || SUBSTR (PHNUM, 7, 2)
                                *
ERROR at line 98:
ORA-00904: "PHNUM": invalid identifier

Regards
Michel
Re: Oracle SQL Query [message #406252 is a reply to message #406244] Wed, 03 June 2009 01:10 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
What is wrong with the excellent solution that BluShadow gave you on OTN?
Are you unwilling to use his solution? Can you explain why?
Re: Oracle SQL Query [message #406254 is a reply to message #406248] Wed, 03 June 2009 01:12 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,

Please find the following.
Table contains column called PHNUM .It contains Phone numbers as follows.

PHNUM
-------
9987656566
9977776554
9976543676
9988656487
.....
.....
....
After writing the above mentioned query I got the O/P as follows.

DECODE(SUBSTR(PHNUM,1,3),'2','0'||SUBSTR(PHNUM,1,2)||'/'||SUBSTR(PHNUM,2,3)||'.'||SUBSTR(PHNUM,5,2)||'.'||SUBSTR(PHNUM,7,2),'3','0'|| SUBSTR(PHNUM,1,1)||'/'||SUBSTR(PHNUM,2,3)||'.'||SUBSTR(PHNUM,5,2)||'.'||SUBSTR(PHNUM,7,2),'4',DECODE(SUB
---------------------------------------------------------------
09/987.65.65
09/977.77.65
09/976.54.36
....
....

But Required O/P is

--------------------
014/87.65.65
014/77.77.65
014/76.54.36

Thank you
Re: Oracle SQL Query [message #406261 is a reply to message #406252] Wed, 03 June 2009 01:19 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi Pab,

Yes It's a great solution but we are not supposed to use that solution we must write a query using SUBSTR function as mentioned above.

Thank you
Re: Oracle SQL Query [message #406268 is a reply to message #406254] Wed, 03 June 2009 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please find the following.
Table contains column called PHNUM .It contains Phone numbers as follows.

This is NOT what I asked.
Note: create table and insert statements along with the result you want with these data.
Note: Clearly explain your specifications with WORDS and not just an output.
How does "014/87.65.65" comes from (maybe?) "9987656566"?

Regards
Michel


Re: Oracle SQL Query [message #406286 is a reply to message #406261] Wed, 03 June 2009 02:52 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
we must write a query using SUBSTR function as mentioned above

From that, we can only surmise that this is, in fact, a homework assignment. In that case, can you post the text (in English) of the task that you have been assigned? You certainly seem to have made some attempt this time, so I reckon that if you can actually post your real requirement (preferably with a test case) then someone will actually be able to help you out.
Previous Topic: How to extend data in select table
Next Topic: table columns populating value
Goto Forum:
  


Current Time: Fri Dec 09 23:33:57 CST 2016

Total time taken to generate the page: 0.08848 seconds