How to use IF in designing a view?

Question

How to use IF in designing a view?

Love2Learn

SSC Rookie

Points: 28
More actions
September 9, 2010 at 6:46 am

#223658

I have designed a view as below,
SELECT dbo.cardholder.c_fname AS [First Name], dbo.cardholder.c_lname AS [Last Name], dbo.cardholder.c_nick_name AS [Emp Id],
dbo.badge.b_number_str AS [Card No], dbo.badge.b_disabled AS [Disable Sts], dbo.cardholder.c_s_timestamp AS [Start Date],
dbo.cardholder.c_t_timestamp AS [End Date], dbo.dept.dept_name AS [Dept Name]
FROM dbo.badge INNER JOIN
dbo.badgesite ON dbo.badge.b_guid = dbo.badgesite.bs_b_guid INNER JOIN
dbo.cardholder ON dbo.badge.b_cardholder_id = dbo.cardholder.c_id INNER JOIN
dbo.dept ON dbo.cardholder.c_dept_id = dbo.dept.dept_id
ORDER BY [First Name]
Now I need to use 'IF' in this view to show the dbo.badge.b_disabled status as 'YES' or 'NO'.
Can any body help me out on this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19254 More actions · Answer 1

Vasudev Tantry (9/9/2010)
I have designed a view as below,
SELECT dbo.cardholder.c_fname AS [First Name], dbo.cardholder.c_lname AS [Last Name], dbo.cardholder.c_nick_name AS [Emp Id],
dbo.badge.b_number_str AS [Card No], dbo.badge.b_disabled AS [Disable Sts], dbo.cardholder.c_s_timestamp AS [Start Date],
dbo.cardholder.c_t_timestamp AS [End Date], dbo.dept.dept_name AS [Dept Name]
FROM dbo.badge INNER JOIN
dbo.badgesite ON dbo.badge.b_guid = dbo.badgesite.bs_b_guid INNER JOIN
dbo.cardholder ON dbo.badge.b_cardholder_id = dbo.cardholder.c_id INNER JOIN
dbo.dept ON dbo.cardholder.c_dept_id = dbo.dept.dept_id
ORDER BY [First Name]
Now I need to use 'IF' in this view to show the dbo.badge.b_disabled status as 'YES' or 'NO'.
Can any body help me out on this?

What you actually need is the CASE expression. Books Online (BOL) has several examples, but something along these lines in your SELECT statement (assumes badge.b_disabled is a numeric of some type):

CASE dbo.badge.b_disabled

WHEN 1 THEN 'Yes'

ELSE 'No'

END

BrainDonor

Steve Hall
Linkedin
Blog Site

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Use CASE.

IF enables you to distinguish between statements, CASE enables you to distinguish between values.

SELECT

c.c_fname AS [First Name],

c.c_lname AS [Last Name],

c.c_nick_name AS [Emp Id],

b.b_number_str AS [Card No],

CASE b.b_disabled WHEN 1 THEN 'YES' WHEN 0 THEN 'NO' ELSE NULL END AS [Disable Sts],

c.c_s_timestamp AS [Start Date],

c.c_t_timestamp AS [End Date],

d.dept_name AS [Dept Name]

FROM dbo.badge b

--INNER JOIN dbo.badgesite s ON b.b_guid = s.bs_b_guid -- not used

INNER JOIN dbo.cardholder c ON b.b_cardholder_id = c.c_id

INNER JOIN dbo.dept d ON c.c_dept_id = d.dept_id

ORDER BY c.c_fname

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Love2Learn SSC Rookie Points: 28 More actions · Answer 3

Love2Learn

SSC Rookie

Points: 28

September 9, 2010 at 7:53 am

#1218602

Thanks Crazy..

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Vasudev Tantry (9/9/2010)
Thanks Crazy..

You're welcome, Forum Newbie 😛

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

slakhanp Valued Member Points: 51 More actions · Answer 5

Use this Query

SELECT dbo.cardholder.c_fname AS [First Name], dbo.cardholder.c_lname AS [Last Name], dbo.cardholder.c_nick_name AS [Emp Id],

dbo.badge.b_number_str AS [Card No],(CASE WHEN ISNULL(LTRIM(RTRIM(dbo.badge.b_disabled)),0)='1' THE 'YES' ELSE 'NO' END) AS [Disable Sts], dbo.cardholder.c_s_timestamp AS [Start Date],

dbo.cardholder.c_t_timestamp AS [End Date], dbo.dept.dept_name AS [Dept Name]

FROM dbo.badge INNER JOIN

dbo.badgesite ON dbo.badge.b_guid = dbo.badgesite.bs_b_guid INNER JOIN

dbo.cardholder ON dbo.badge.b_cardholder_id = dbo.cardholder.c_id INNER JOIN

dbo.dept ON dbo.cardholder.c_dept_id = dbo.dept.dept_id

ORDER BY [First Name]

]