IF/THEN within a view

  • 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
  • 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/

  • 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 🙂


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


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


  • 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.
  • , 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".

  • Thanks all... works perfectly now!

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

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