How to use Decode statement in SQL

I would like to use a single SQL statement to do the following:

If the digits  in the last two positions of the string are 20, e.g. '200720',  then I  would like the value 'Spring' plus the first 4 digits to be put into the variable TermPrint. E.g. for the value '200720', TermPrint should have the value 'Spring 2007'. If the digits in the last two positions of the string are 10, TermPrint should have the value Fall 2007. If it is 00 then TermPrint should have the value Summer 2007. Finally for 15, the value should be Winter Intersession 2007.

I am not sure how to do that in an SQL script. I have to use SQL and not PL/SQL. Otherwise I could have used the IF statement.
geeta_m9Asked:
Who is Participating?
 
SmilingPixieConnect With a Mentor Commented:
Hi

Try this as a query to see if it gets you the results you are looking for....

SELECT
CASE(SUBSTR(colname,5,2))
WHEN '20' THEN 'Spring '||SUBSTR(colname,1,4)
WHEN '15' THEN 'Winter '||SUBSTR(colname,1,4)
WHEN '10' THEN 'Fall '||SUBSTR(colname,1,4)
WHEN '00' THEN 'Summer '||SUBSTR(colname,1,4)
END CASE
FROM TABLE;
0
 
geeta_m9Author Commented:
The title is a bit misleading. It does not have to use a decode statement. Any SQL statement that can do the job will work.
0
 
jwahlConnect With a Mentor Commented:
maybe you're looking for something like this:
you could also try CASE ...


SELECT DECODE(SUBSTR(my_string, 5, 2), 
    '20', 'Spring ' || SUBSTR(my_string, 1,4),
    '10', 'Fall ' || SUBSTR(my_string, 1,4),
    '00', 'Summer ' || SUBSTR(my_string, 1,4),
    '15', 'WInter ' || SUBSTR(my_string, 1,4)
    )
FROM my_table;

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
geeta_m9Author Commented:
I need to put the result into the variable TermPrint. Should I use a SELECT INTO TermPrint followed by the CASE?
0
 
SmilingPixieCommented:
What kind of variable?  
SELECT INTO can be used if you know your query is only returning one row....
0
 
geeta_m9Author Commented:
Yes, the SQL is embedded in an application which is only going to be looking at one row at a time.
0
 
SmilingPixieCommented:
Then you should be able to do either a SELECT INTO or potentially put the query on the right hand side of your operator as another option if your application allows you to do it.

VARIABLE := <insert select statement here>
0
 
geeta_m9Author Commented:
I presume the INTO part come should after the CASE statement?
0
 
geeta_m9Author Commented:
Ok, I'll try it and get back to you.
0
 
geeta_m9Author Commented:
It works perfectly, thank you!
0
 
geeta_m9Author Commented:
Thank you for a speedy solution!
0
All Courses

From novice to tech pro — start learning today.