IF/THEN within a view

Question

IF/THEN within a view

SSCarpal Tunnel

Points: 4298

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 9 (of 9 total)

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

Michael L John One Orange Chip Points: 27056 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: 246964 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 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DesNorton SSC-Insane Points: 24387 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: 4298 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: 246964 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DaveBriCam SSCarpal Tunnel Points: 4298 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 5 years, 5 months ago by DaveBriCam.

ScottPletcher SSC Guru Points: 101103 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: 4298 More actions · Answer 9

DaveBriCam

SSCarpal Tunnel

Points: 4298

July 2, 2019 at 8:53 pm

#3659524

Thanks all... works perfectly now!