Equivalent of 'DECODE' in sql
-
Hi all,
🙂 Can anyone provide me with correct syntax for' 'DECODE' function equivalent in sql.
Thanks,
kiran.
Kiran
-
Use
CASE ... WHEN ... THEN ... ELSE ... END
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
i have a table called university which have two fields id and name.
i tried this way.
SELECT CASE u.id
WHEN 2 THEN 'GOOD'
WHEN 3 THEN 'POOR'
WHEN 4 THEN 'EXCELLENT'
ELSE 'BAD'
END CASE
from university u;
it is not executing.can u suggest me where my query is wrong.....
Kiran
-
Should be something like this (http://msdn2.microsoft.com/en-us/library/aa258235(SQL.80).aspx) ?
USE pubs
GO
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO
-
Kiran,
You were very close to getting your code to run. The line "END CASE" should just be "END". I wrote a test case demonstration, based on your information.
-- Create university Table.
CREATE TABLE dbo.university
(
[id] NUMERIC(10,0)
); NUMBER(10,0)
);
-- Insert sample data.
INSERT INTO university VALUES (1);
INSERT INTO university VALUES (2);
INSERT INTO university VALUES (3);
INSERT INTO university VALUES (4);
INSERT INTO university VALUES (5);
-- Select the data as a preview.
select [id] from university
-- Build the case statement with the following Rules:
-- ID of 2 = GOOD
-- ID of 3 = POOR
-- ID of 4 = EXCELLENT
-- all other ID values default to = BAD
SELECT
[id], -- Display the original value for [id].
CASE u.[id]
WHEN 2 THEN 'GOOD'
WHEN 3 THEN 'POOR'
WHEN 4 THEN 'EXCELLENT'
ELSE 'BAD'
END AS 'GRADE_REMARK' -- this is just a column label
FROM dbo.university AS u
-- Clean up the demonstration.
-- Drop the university table.
DROP TABLE dbo.university
Let me know if this is what you were expecting.
Best Regards,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP -
Thanks Wilson,
It's working :).
Kiran
-
Kiran,
Thanks for posting that everything is working. See you around the Forums.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP -
hi,
The decode functionality in oracle is something similar to
"if then else...". But what you have stated is "select case...end"
let me clearly explain you a scinario,
table student contains name and total marks
i can say "pass" or "fail" by give a query like
"select name, decode(total > 250, "pass","fail") status from student"
how will u achieve the same scinario in sql-server.
regds
rishi
-
Rishi,
You can do the same with 'select case...end'
SELECT
CASE
WHEN total > 250 THEN 'pass'
ELSE 'fail'
END as 'status'
FROM student
-Vetri
-
Dear vetri,
What you have provided was sounds fine..but actually im looking for any in-built function like how oracle provided the "DECODE"........
-
Just for the record (I know this is an old thread).
You can't do this in Oracle:
"select name, decode(total > 250, "pass","fail") status from student"
The syntax for the decode function is:
decode( expression , search , result [, search , result]... [, default] )
expression is the value to compare.
search is the value that is compared against expression.
result is the value returned, if expression is equal to search.
default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).
So, you can't use > with the decode function. It just evaluates if expression matches (i.e. is equal to) search
-
decode(e.V_Billing_Currency_Code,a.V_CHECK_CURRENCY_CODE,d.N_Applied_Amount,decode(a.N_Exchange_Rate,0,0,(d.N_Domain_Amount/ a.N_Exchange_Rate)))
Could anyone help me in converting this to CASE statement
- rachit.agarwal2011 - Tuesday, January 29, 2019 4:40 AM
I think this is it:
CASE e.V_Billing_Currency_Code
WHEN a.V_CHECK_CURRENCY_CODE THEN d.N_Applied_Amount
ELSE CASE a.N_Exchange_Rate
WHEN 0 THEN 0
ELSE (d.N_Domain_Amount/ a.N_Exchange_Rate)
END
END - Jonathan AC Roberts - Tuesday, January 29, 2019 5:15 AM
This can be simplified to
CASE
WHEN e.V_Billing_Currency_Code = a.V_CHECK_CURRENCY_CODE THEN d.N_Applied_Amount
WHEN a.N_Exchange_Rate = 0 THEN 0
ELSE (d.N_Domain_Amount/ a.N_Exchange_Rate)
ENDPart of the art of translating is knowing when to deviate from a literal translation. Since CASE has two alternate syntaxes, it's tempting to use the syntax that more closely resembles the DECODE syntax, but the other syntax gives a simpler formula.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - Jonathan AC Roberts - Tuesday, January 29, 2019 5:15 AM
Thank a lot Jonathan..!!!!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply