Home » SQL & PL/SQL » SQL & PL/SQL » Decode function in select statement
Decode function in select statement [message #378550] Tue, 30 December 2008 23:01 Go to next message
sathyam2627
Messages: 52
Registered: November 2006
Member
Hi All,

We are using oracle release 9.2.0.1.0.

I have executed below query and i am expecting the output as if the hiredate is MONDAY then it should return 1 vice versa.

But for even monday and tuesday also this query is returning default value.

SQL> ed
Wrote file afiedt.buf

  1  Select To_Char(HireDate,'DAY') WDay,
  2          Decode(To_Char(HireDate,'DAY'),
  3                     'MONDAY',1,
  4                     'TUESDAY',2,
  5                      3) Val
  6  From Emp
  7   Order by Decode(To_Char(HireDate,'DAY'),
  8                     'MONDAY',1,
  9                     'TUESDAY',2,
 10*                     3)
SQL> /

WDAY             VAL
--------- ----------
WEDNESDAY          3
FRIDAY             3
SUNDAY             3
THURSDAY           3
MONDAY             3
FRIDAY             3
TUESDAY            3
SUNDAY             3
TUESDAY            3
TUESDAY            3
SATURDAY           3

WDAY             VAL
--------- ----------
THURSDAY           3
THURSDAY           3
SATURDAY           3

14 rows selected.


Kindly correct me.

Thanks.
Re: Decode function in select statement [message #378552 is a reply to message #378550] Tue, 30 December 2008 23:17 Go to previous messageGo to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

Try using trim() ..
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL> select To_Char(HireDate,'DAY') WDay,
  2         Decode(trim(To_Char(HireDate,'DAY')),
  3                'MONDAY',1,
  4                'TUESDAY',2,
  5                3) val
  6  from  emp
  7  order by Decode(trim(To_Char(HireDate,'DAY')),
  8         'MONDAY',1,
  9         'TUESDAY',2,
 10         3);

WDAY                               VAL
--------------------------- ----------
MONDAY                               1
TUESDAY                              2
TUESDAY                              2
TUESDAY                              2
TUESDAY                              2
FRIDAY                               3
THURSDAY                             3
THURSDAY                             3
SATURDAY                             3
WEDNESDAY                            3
WEDNESDAY                            3

WDAY                               VAL
--------------------------- ----------
SUNDAY                               3
THURSDAY                             3
THURSDAY                             3
FRIDAY                               3

15 rows selected.


Regards,
Ram.

I don't know your exact requirement but you can try this also..

SQL> select empno,HireDate,to_char(HireDate,'DAY') HIRE_DAY,
  2         decode(to_char(HireDate,'D'),1,7,to_char(HireDate,'D') - 1) val
  3  from   emp
  4  order by 4;

     EMPNO HIREDATE  HIRE_DAY                           VAL
---------- --------- --------------------------- ----------
      7654 28-SEP-81 MONDAY                               1
      7839 17-NOV-81 TUESDAY                              2
      7844 08-SEP-81 TUESDAY                              2
      7782 09-JUN-81 TUESDAY                              2
         1 01-JAN-08 TUESDAY                              2
      7369 17-DEC-80 WEDNESDAY                            3
      7876 12-JAN-83 WEDNESDAY                            3
      7566 02-APR-81 THURSDAY                             4
      7788 09-DEC-82 THURSDAY                             4
      7902 03-DEC-81 THURSDAY                             4
      7900 03-DEC-81 THURSDAY                             4

     EMPNO HIREDATE  HIRE_DAY                           VAL
---------- --------- --------------------------- ----------
      7698 01-MAY-81 FRIDAY                               5
      7499 20-FEB-81 FRIDAY                               5
      7934 23-JAN-82 SATURDAY                             6
      7521 22-FEB-81 SUNDAY                               7

15 rows selected.

[Updated on: Wed, 31 December 2008 00:31]

Report message to a moderator

Re: Decode function in select statement [message #378577 is a reply to message #378550] Wed, 31 December 2008 00:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Another option is to use the correct format-mask. 'DAY' pads the result with spaces up till 9 positions.
To get rid of those, use the fm prefix:

SQL> select to_char(hiredate, 'fmDAY') wday
  2  ,      decode(to_char(hiredate, 'fmDAY')
  3               , 'MONDAY', 1
  4               , 'TUESDAY', 2
  5               , 3) val
  6  ,      length(to_char(hiredate, 'DAY')) untrimmed
  7  ,      length(to_char(hiredate, 'fmDAY')) trimmed
  8  from   scott.emp
  9  order  by decode(to_char(hiredate, 'fmDAY')
 10                  , 'MONDAY', 1
 11                  , 'TUESDAY', 2
 12                  , 3)
 13  /

WDAY             VAL  UNTRIMMED    TRIMMED
--------- ---------- ---------- ----------
MONDAY             1          9          6
TUESDAY            2          9          7
TUESDAY            2          9          7
TUESDAY            2          9          7
WEDNESDAY          3          9          9
FRIDAY             3          9          6
SATURDAY           3          9          8
THURSDAY           3          9          8
FRIDAY             3          9          6
THURSDAY           3          9          8
WEDNESDAY          3          9          9
THURSDAY           3          9          8
SUNDAY             3          9          6
THURSDAY           3          9          8

14 rows selected.
Re: Decode function in select statement [message #378589 is a reply to message #378552] Wed, 31 December 2008 01:01 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DAY and D format depends on your NLS settings:
SQL> select to_char(hiredate, 'fmDAY') wday
  2  ,      decode(to_char(hiredate, 'fmDAY')
  3               , 'MONDAY', 1
  4               , 'TUESDAY', 2
  5               , 3) val
  6  from   scott.emp
  7  order  by decode(to_char(hiredate, 'fmDAY')
  8                  , 'MONDAY', 1
  9                  , 'TUESDAY', 2
 10                  , 3)
 11  /
WDAY            VAL
-------- ----------
MERCREDI          3
VENDREDI          3
DIMANCHE          3
JEUDI             3
LUNDI             3
VENDREDI          3
SAMEDI            3
DIMANCHE          3
MARDI             3
MARDI             3
SAMEDI            3
JEUDI             3
JEUDI             3
MARDI             3

14 rows selected.

So you have to precise it in TO_CHAR function:
SQL> select to_char(hiredate, 'fmDAY', 'NLS_DATE_LANGUAGE=AMERICAN') wday
  2  ,      decode(to_char(hiredate, 'fmDAY', 'NLS_DATE_LANGUAGE=AMERICAN')
  3               , 'MONDAY', 1
  4               , 'TUESDAY', 2
  5               , 3) val
  6  from   scott.emp
  7  order  by decode(to_char(hiredate, 'fmDAY', 'NLS_DATE_LANGUAGE=AMERICAN')
  8                  , 'MONDAY', 1
  9                  , 'TUESDAY', 2
 10                  , 3)
 11  /
WDAY             VAL
--------- ----------
MONDAY             1
TUESDAY            2
TUESDAY            2
TUESDAY            2
WEDNESDAY          3
FRIDAY             3
SATURDAY           3
SUNDAY             3
FRIDAY             3
THURSDAY           3
SATURDAY           3
THURSDAY           3
SUNDAY             3
THURSDAY           3

14 rows selected.

Regards
Michel
Previous Topic: Deleting a large number of rows...
Next Topic: Explanation needed for a Sql query
Goto Forum:
  


Current Time: Sun Dec 11 04:08:10 CST 2016

Total time taken to generate the page: 0.09358 seconds