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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
jwahlCommented:
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
SmilingPixieCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.