Post reply

incorrect syntax near 'if'

  • May 18, 2010 at 12:12 am

    #232606

    what am i missing...

    update R

    set WorkType = case when WorkTypeID in(4,5,6) AND

    UPPER(OPNumber) LIKE '%S' AND UPPER(OPNumber) NOT LIKE 'GS%S'

    then 'SSR Enhancements' else if

    WorkTypeID in(4,5,6) AND

    ((UPPER(OPNumber) LIKE '%U' AND UPPER(OPNumber) NOT LIKE 'GS%U')

    OR

    (UPPER(OPNumber) LIKE '%P' AND UPPER(OPNumber) NOT LIKE 'GS%P'))

    then 'Application Support' else if

    WorkTypeID in(4,5,6) AND

    UPPER(OPNumber) LIKE 'GS%' AND UPPER(OPNumber) NOT LIKE 'GS450A'

    then 'General Support' else if

    WorkTypeID in (1,2,3,4,5,6,7,8,9) AND

    LEN(OPNumber)=6 AND ISNUMERIC(OPNumber)=1 AND OPNumber <>'999999'

    then 'Project' else if

    WorkTypeID in (1,2,3,4,5,6,7,8,9) AND OPNumber = '999998'

    then 'Absence' else if

    WorkTypeID =7 AND ((OPNumber = '999999') OR (UPPER(OPNumber) = 'GS450A'))

    then 'Non-Billable'

    end,

    from xyz R

    its throwing incorrect syntax error...i think am exhausted...need help...

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • May 18, 2010 at 12:21 am

    #1168599

    NewBee, replace all of your ELSE IF to WHEN.. That syntax u used is not supported..

    Hope this helps you!

  • May 18, 2010 at 12:24 am

    #1168600

    To learn more about the CASE syntax, please go through the following BOL link, especially the Examples part of it

    CASE (Transact-SQL)

    😎

  • May 18, 2010 at 12:28 am

    #1168601

    Thanks...its works..your help always ontime for me..

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • May 18, 2010 at 12:34 am

    #1168604

    update R

    set WorkType = case

    when WorkTypeID in(4,5,6) AND UPPER(OPNumber) LIKE '%S' AND UPPER(OPNumber) NOT LIKE 'GS%S' then 'SSR Enhancements'

    when WorkTypeID in(4,5,6) AND ((UPPER(OPNumber) LIKE '%U' AND UPPER(OPNumber) NOT LIKE 'GS%U') OR (UPPER(OPNumber) LIKE '%P' AND UPPER(OPNumber) NOT LIKE 'GS%P')) then 'Application Support'

    when WorkTypeID in(4,5,6) AND UPPER(OPNumber) LIKE 'GS%' AND UPPER(OPNumber) NOT LIKE 'GS450A' then 'General Support'

    when WorkTypeID in (1,2,3,4,5,6,7,8,9) AND LEN(OPNumber)=6 AND ISNUMERIC(OPNumber)=1 AND OPNumber <>'999999' then 'Project'

    when WorkTypeID in (1,2,3,4,5,6,7,8,9) AND OPNumber = '999998' then 'Absence'

    when WorkTypeID =7 AND ((OPNumber = '999999') OR (UPPER(OPNumber) = 'GS450A')) then 'Non-Billable'

    end

    from xyz R

  • May 18, 2010 at 2:09 am

    #1168629

    You're welcome, NewBee..:-)

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

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