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 🙂

    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

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

    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

  • 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

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

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