CODEWITHSUNDEEP
sqloracle-database
user569125
user569125

Reputation: 1463

Decode function in oracle database

Can anyone please explain the below SQL statement from Oracle DB:

select decode(TRIM(to_char(SYSDATE,'Day')),'Monday','3','1') from dual

Upvotes: 11

Views: 80809

Answers (3)

Muhammad Yaseen
Muhammad Yaseen

Reputation: 741

Below is explanation by parts:

SYSDATE

is returning server date like 15-APR-19 and format based on local

to_char(SYSDATE,'Day')

is converting into string and return week day like Monday for date 15-APR-19

TRIM(to_char(SYSDATE,'Day'))

is removing empty spaces before and after of

Decode function is similar to if else statements and hence it simplifies code. For Current example if we write in SQL then it should be like below:

CASE 
  WHEN TRIM(to_char(SYSDATE,'Day')) = 'Monday'  THEN '3'
  WHEN TRIM(to_char(SYSDATE,'Day')) = 'Tuesday' THEN '4'
  ELSE '1' 
END

For complete reference see https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm

Upvotes: 1

nolt2232
nolt2232

Reputation: 2644

First of all, let's start with the to_char. to_char(SYSDATE,'Day') will give you the day of the week it is today. to_char lets you convert a date (in this case today's date since you've specified sysdate) into a string of a certain format. Take a look here for some other examples of date formats you can use:

http://www.techonthenet.com/oracle/functions/to_char.php

trim removes leading and trailing whitespace.

Now for the decode. You can think of decode like an if else statement. Take a look at:

http://www.techonthenet.com/oracle/functions/decode.php

In your particular example, you could read this statement as: if today is Monday return 3 else return 1.

decode also allows you to do things a bit more complex like this:

select decode(TRIM(to_char(SYSDATE,'Day')),'Monday','3','Tuesday',5,'1') 
from dual

This would read: if today is Monday return 3, else if today is Tuesday return 5, else return 1

Upvotes: 29

ruakh
ruakh

Reputation: 183321

That will return 3 if it's currently Monday (and the locale is such that the day is rendered as "Monday"), and 1 otherwise.

DECODE(a, b,c, d,e, f,g, ..., h) will compare a to b, d, f, etc., in turn. If a is b, then DECODE returns c; if a is d, then DECODE returns e; and so on. If a is not any of these, then DECODE returns h. (The h is optional; the default return-value, if h is not given, is NULL.)

Upvotes: 16

Related Questions