Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Equivalent of 'DECODE' in sql Expand / Collapse
Author
Message
Posted Thursday, March 27, 2008 5:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 27, 2008 7:16 AM
Points: 19, Visits: 66
Hi all,

:) Can anyone provide me with correct syntax for' 'DECODE' function equivalent in sql.

Thanks,
kiran.


Kiran
Post #475321
Posted Thursday, March 27, 2008 5:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:11 PM
Points: 1,681, Visits: 19,596
Use
CASE ... WHEN ... THEN ... ELSE ... END


____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #475329
Posted Thursday, March 27, 2008 11:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 27, 2008 7:16 AM
Points: 19, Visits: 66
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
Post #475887
Posted Thursday, March 27, 2008 11:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:03 AM
Points: 79, Visits: 211
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
Post #475892
Posted Friday, March 28, 2008 7:29 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 26, 2009 3:02 PM
Points: 515, Visits: 655
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
Post #476084
Posted Monday, March 31, 2008 10:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 27, 2008 7:16 AM
Points: 19, Visits: 66
Thanks Wilson,

It's working :).



Kiran
Post #477412
Posted Tuesday, April 1, 2008 6:36 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 26, 2009 3:02 PM
Points: 515, Visits: 655
Kiran,

Thanks for posting that everything is working. See you around the Forums.



"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Post #477561
Posted Friday, June 27, 2008 2:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 6:34 AM
Points: 7, Visits: 22
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
Post #524830
Posted Thursday, July 17, 2008 10:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 19, 2009 2:57 PM
Points: 5, Visits: 8
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



Post #536143
Posted Friday, July 18, 2008 12:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 6:34 AM
Points: 7, Visits: 22
Dear vetri,

What you have provided was sounds fine..but actually im looking for any in-built function like how oracle provided the "DECODE"........
Post #536485
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse