IF/THEN within a view

Question

IF/THEN within a view

SSCarpal Tunnel

Points: 4302

July 2, 2019 at 7:59 pm

I have a view below that I need to make my returned field named "OriginalDatabaseID" return an "-I" within the number if another field "instructorInt" has a value of -1, e.g "16535-I"... can that be done and do I need an IF/THEN routine added?

CREATE VIEW [dbo].[GCDFview]
AS
SELECT p.FirstName, p.LastName, c.city, p.instructorInt as 'Instructor',
s.stateAbbrveation AS 'State', 'USA' AS Country, 
'GCDF' AS Certifications, p.PeopleID AS OriginalDatabaseId, '1' AS RegionID, 
'1' AS IsOnCounselorFind, c.postalCode, 'GCDF' AS OriginalDatabase

FROM            dbo.People p    
     LEFT OUTER JOIN
                dbo.certs t ON 
        p.peopleId = t.peopleId
 LEFT OUTER JOIN
                dbo.PeopleContactInfo c ON 
p.peopleId = c.peopleId 
 LEFT OUTER JOIN
                dbo.States s ON 
c.stateId = s.stateId

Viewing 10 posts - 1 through 10 (of 10 total)

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

Michael L John One Orange Chip Points: 27079 More actions · Answer 1

No, you need a CASE statement

CASE WHEN SomeColumn = YourValue
     THEN SomeValue
     ELSE SomeOtherValue
END

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

Not an IF/THEN, but an if and only if:

SELECT OriginalDatabaseId = iif(InstructorInt = -1,-1,p.PeopleId)

Note also that stateAbbrveation appears to have been abbreviated 🙂

DesNorton SSC-Insane Points: 24569 More actions · Answer 3

You can use a CASE statement

CREATE VIEW [dbo].[GCDFview]
AS
SELECT p.FirstName, p.LastName, c.city, p.instructorInt as 'Instructor'
, s.stateAbbrveation AS 'State', 'USA' AS Country, 'GCDF' AS Certifications
, p.PeopleID + CASE WHEN p.instructorInt = -1 THEN '-1' ELSE '' END AS OriginalDatabaseId
, '1' AS RegionID, '1' AS IsOnCounselorFind, c.postalCode, 'GCDF' AS OriginalDatabase
FROM            dbo.People p    
     LEFT OUTER JOIN
                dbo.certs t ON 
        p.peopleId = t.peopleId
 LEFT OUTER JOIN
                dbo.PeopleContactInfo c ON 
p.peopleId = c.peopleId 
 LEFT OUTER JOIN
                dbo.States s ON 
c.stateId = s.stateId

How to post data/code on a forum to get the best help.

DaveBriCam SSCarpal Tunnel Points: 4302 More actions · Answer 4

Like this? The compiler is throwing an error on one of the "=" signs:

SELECT p.FirstName, p.LastName, c.city,
OriginalDatabaseId = iif(InstructorInt = -1,-1,p.PeopleId), 
s.stateAbbrveation AS 'State', 'USA' AS Country, 
'GCDF' AS Certifications, '1' AS RegionID, 
'1' AS IsOnCounselorFind, c.postalCode, 'GCDF' AS OriginalDatabase

FROM            dbo.People p    
     LEFT OUTER JOIN
                dbo.certs t ON 
        p.peopleId = t.peopleId
 LEFT OUTER JOIN
                dbo.PeopleContactInfo c ON 
p.peopleId = c.peopleId 
 LEFT OUTER JOIN
                dbo.States s ON 
c.stateId = s.stateId

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

The code you posted has no syntax issues that I can see.

You should add in the table alias for InstructorInt, otherwise it looks fine.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 6

IIF() is 2012 onwards. What is the compatibility level of your database?

DaveBriCam SSCarpal Tunnel Points: 4302 More actions · Answer 7

Actually this compiles but I need to have "OriginalDatabaseID" returned as text rather than number:

CREATE VIEW [dbo].[GCDFview]
AS

SELECT p.FirstName, p.LastName, c.city, p.instructorInt as 'Instructor'
, s.stateAbbrveation AS 'State', 'USA' AS Country, 'GCDF' AS Certifications

, p.PeopleID + CASE WHEN p.instructorInt = -1 THEN '-I' ELSE '' END AS 'OriginalDatabaseId'

, '1' AS RegionID, '1' AS IsOnCounselorFind, c.postalCode, 'GCDF' AS OriginalDatabase
FROM     dbo.People p
LEFT OUTER JOIN
dbo.certs t ON
p.peopleId = t.peopleId
LEFT OUTER JOIN
dbo.PeopleContactInfo c ON
p.peopleId = c.peopleId
LEFT OUTER JOIN
dbo.States s ON
c.stateId = s.stateId

This reply was modified 6 years, 2 months ago by DaveBriCam.

ScottPletcher SSC Guru Points: 101202 More actions · Answer 8

, CAST(p.PeopleID AS varchar(10)) + CASE WHEN p.instructorInt = -1 THEN '-1' ELSE '' END AS OriginalDatabaseId

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

DaveBriCam SSCarpal Tunnel Points: 4302 More actions · Answer 9

DaveBriCam

SSCarpal Tunnel

Points: 4302

July 2, 2019 at 8:53 pm

#3659524

Thanks all... works perfectly now!