Home » SQL & PL/SQL » SQL & PL/SQL » oracle sql short
oracle sql short [message #263938] Fri, 31 August 2007 05:33 Go to next message
harini.ss
Messages: 3
Registered: August 2007
Location: chennai
Junior Member
SELECT DISTINCT DECODE(SUBSTR(DOCDATE,4,3),'JAN', 'JANUARY',DECODE(SUBSTR(DOCDATE,4,3),'FEB','FEBRUARY',DECODE(SUBSTR(DOCDATE,4,3),'MAR', 'MARCH',DECODE(SUBSTR(DOCDATE,4,3),'APR','APRIL',DECODE(SUBSTR(DOCDATE,4,3),
'MAY', 'MAY',DECODE(SUBSTR(DOCDATE,4,3),'JUN','JUN',DECODE(SUBSTR(DOCDATE,4,3),'JULY', 'JULY',DECODE(SUBSTR(DOCDATE,4,3),'AUG','AUG','DEC')))))))) FROM EXINVBASIC
WHERE SUBSTR(DOCDATE,4,3) IS NOT NULL

this is my sql

How can i short this sql

Harini
Re: oracle sql short [message #263940 is a reply to message #263938] Fri, 31 August 2007 05:34 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Please read OraFAQ Forum Guide before posting.

By
Vamsi
Re: oracle sql short [message #263942 is a reply to message #263938] Fri, 31 August 2007 05:40 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi

Why dont you try this???

SQL> select to_char(docdate,'MONTH') from EXINVBASIC;

Thanks
NATESH
Re: oracle sql short [message #263945 is a reply to message #263938] Fri, 31 August 2007 05:51 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
DECODE(SUBSTR(DOCDATE,4,3),'JUN','JUN'
Quote:
DECODE(SUBSTR(DOCDATE,4,3),'AUG','AUG','DEC'
Is it a typo?

By
Vamsi
Re: oracle sql short [message #263949 is a reply to message #263938] Fri, 31 August 2007 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read http://www.orafaq.com/forum/t/88451/102589/

Regards
Michel
Re: oracle sql short [message #263971 is a reply to message #263938] Fri, 31 August 2007 07:01 Go to previous messageGo to next message
lenin_babu55
Messages: 12
Registered: August 2007
Junior Member
hi

it is not necessary to write decode function that many times
refer the following code ..........

SELECT product_id,
DECODE (warehouse_id, 1, 'Southlake',
2, 'San Francisco',
3, 'New Jersey',
4, 'Seattle',
'Non-domestic')
quantity_on_hand FROM inventories;
Re: oracle sql short [message #263972 is a reply to message #263971] Fri, 31 August 2007 07:04 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
lenin_babu55 wrote on Fri, 31 August 2007 14:01
hi

it is not necessary to write decode function that many times
refer the following code ...
The word in red should give you a clue: use code tags when posting code. It's not the first time we ask you.

MHE
Re: oracle sql short [message #264085 is a reply to message #263938] Fri, 31 August 2007 13:13 Go to previous message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
First of all, what datatype is DOCDATE? I'm assuming since you are using SUBSTR, it is character of some type and not DATE.

Second, this condition will never be true
Quote:
SUBSTR(DOCDATE,4,3),'JULY'


And third, do you really want the mixed results (i.e. some months spelled out, some months abbreviated, and a default of 'DEC' for all months after 'AUG'), or were you being lazy? I'm okay with that, but mentioning that in your post would be nice.
Previous Topic: nested table
Next Topic: Problem with updating
Goto Forum:
  


Current Time: Wed Dec 07 18:53:57 CST 2016

Total time taken to generate the page: 0.09427 seconds