Decode statement in MS SQL

In MS SQL, is there a similar function as Decode in Oracle?

I have the below code where the field Marutal Status is integer in Table EMP.

1 means Single, 2 means Married . Is there a better way than the below:

SELECT  
        EMP.EMPLOYID + EMP.FRSTNAME+ ISNULL(EMP.MIDLNAME,'')+EMP.LASTNAME "Employee No.& Name" ,  
        Dep.DSCRIPTN Department,
        TITLE.DSCRIPTN "JOB",
        EDUCATION.DEGREE Education,
        EDUCATION.MAJOR Major,
        EDUCATION.UNIVERSITY UNIVERISTY,
        Convert(char(11),EMP.BRTHDATE,13) DOB,
        PAYCODE.DSCRIPTN DESCR,
        SAL.PAYRTAMT Basic,
        Replace(Replace(REPLACE(EMP.MARITALSTATUS,1,'SINGLE'),2,'Married'),3,'N/A') "Marital Status"
FROM UPR00100 EMP
        LEFT JOIN UPR10203 SAL ON EMP.EMPLOYID = SAL.EMPLOYID
        LEFT JOIN UPR40600 PAYCODE ON SAL.PAYRCORD = PAYCODE.PAYRCORD
        LEFT JOIN UPR40300 Dep ON EMP.DEPRTMNT = Dep.DEPRTMNT
        LEFT JOIN UPR00112 EDUCATION ON EMP.EMPLOYID = EDUCATION.EMPLOYID
        LEFT JOIN UPR40301 Title ON EMP.JOBTITLE = TITLE.JOBTITLE
ORDER BY EMP.EMPLOYID




LVL 1
ramziabkAsked:
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.

gothamiteCommented:
Yes there is a preferable way - use the CASE statement:

SELECT  
        EMP.EMPLOYID + EMP.FRSTNAME+ ISNULL(EMP.MIDLNAME,'')+EMP.LASTNAME "Employee No.& Name" ,  
        Dep.DSCRIPTN Department, 
        TITLE.DSCRIPTN "JOB", 
        EDUCATION.DEGREE Education, 
        EDUCATION.MAJOR Major,
        EDUCATION.UNIVERSITY UNIVERISTY, 
        Convert(char(11),EMP.BRTHDATE,13) DOB,
        PAYCODE.DSCRIPTN DESCR,
        SAL.PAYRTAMT Basic,
        CASE WHEN EMP.MARITALSTATUS = 1 THEN 'SINGLE' ELSE 
             WHEN EMP.MARITALSTATUS = 2 THEN 'Married' ELSE 'N/A' END AS "Marital Status"
FROM UPR00100 EMP
        LEFT JOIN UPR10203 SAL ON EMP.EMPLOYID = SAL.EMPLOYID 
        LEFT JOIN UPR40600 PAYCODE ON SAL.PAYRCORD = PAYCODE.PAYRCORD
        LEFT JOIN UPR40300 Dep ON EMP.DEPRTMNT = Dep.DEPRTMNT 
        LEFT JOIN UPR00112 EDUCATION ON EMP.EMPLOYID = EDUCATION.EMPLOYID 
        LEFT JOIN UPR40301 Title ON EMP.JOBTITLE = TITLE.JOBTITLE 
ORDER BY EMP.EMPLOYID

Open in new window

0
Ryan ChongCommented:
you can also use CASE WHEN statement, like:


CASE WHEN EMP.MARITALSTATUS = 1 THEN 'SINGLE' ELSE CASE WHEN EMP.MARITALSTATUS = 2 THEN 'Married' ELSE 'N/A' END "Marital Status"
0
Ryan ChongCommented:
shoud be as:

CASE WHEN EMP.MARITALSTATUS = 1 THEN 'SINGLE' ELSE CASE WHEN EMP.MARITALSTATUS = 2 THEN 'Married' ELSE 'N/A' END END "Marital Status"
0
Ultimate Tool Kit for Technology Solution Provider

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 now.

gothamiteCommented:
made a typo in my original. See below for the correct one:
SELECT  
        EMP.EMPLOYID + EMP.FRSTNAME+ ISNULL(EMP.MIDLNAME,'')+EMP.LASTNAME "Employee No.& Name" ,  
        Dep.DSCRIPTN Department, 
        TITLE.DSCRIPTN "JOB", 
        EDUCATION.DEGREE Education, 
        EDUCATION.MAJOR Major,
        EDUCATION.UNIVERSITY UNIVERISTY, 
        Convert(char(11),EMP.BRTHDATE,13) DOB,
        PAYCODE.DSCRIPTN DESCR,
        SAL.PAYRTAMT Basic,
        CASE WHEN EMP.MARITALSTATUS = 1 THEN 'SINGLE' 
             WHEN EMP.MARITALSTATUS = 2 THEN 'Married' ELSE 'N/A' END AS "Marital Status"
FROM UPR00100 EMP
        LEFT JOIN UPR10203 SAL ON EMP.EMPLOYID = SAL.EMPLOYID 
        LEFT JOIN UPR40600 PAYCODE ON SAL.PAYRCORD = PAYCODE.PAYRCORD
        LEFT JOIN UPR40300 Dep ON EMP.DEPRTMNT = Dep.DEPRTMNT 
        LEFT JOIN UPR00112 EDUCATION ON EMP.EMPLOYID = EDUCATION.EMPLOYID 
        LEFT JOIN UPR40301 Title ON EMP.JOBTITLE = TITLE.JOBTITLE 
ORDER BY EMP.EMPLOYID

Open in new window

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
Ryan ChongCommented:
hmmm, i think gothamite's approach may be better.
0
mkeiwuaCommented:
Hi ramziabk,

I think the CASE function works as well e.g. (a very basic example)

SELECT Sport,
CASE Sport
WHEN 'Rugby' THEN 'Australia'
WHEN 'Hockey' THEN 'India'
WHEN 'Soccer' THEN 'Brazil'
ELSE NULL
END AS 'Current Champions'
FROM Sports

B Rgds,

Mkeiwua

0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Yep, I would write that part more like
CASE EMP.MARITALSTATUS
   WHEN 1 THEN 'SINGLE'
   WHEN 2 THEN 'Married'
   ELSE 'N/A'
END AS "Marital Status"
since the same field is asked for in each condition.
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
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.