Professional Web Applications Themes

if statement in query - Microsoft SQL / MS SQL Server

I had an access query that used a select statement with an IIf function to evaluate some values and want to do the same type of thing but using a sql server query. This is what the access query looks like: SELECT Sum((IIf(DBO_REQUIREMENT.PART_ID Like "8500*",0.8*dbo_requirement.qty_per,IIf (DBO_REQUIREMENT.PART_ID Like "8509*",0.6*dbo_requirement.qty_per,IIf (DBO_REQUIREMENT.PART_ID Like "8507*",1.6*dbo_requirement.qty_per,IIf (DBO_REQUIREMENT.PART_ID Like "8519*",0.8*dbo_requirement.qty_per,IIf (DBO_REQUIREMENT.PART_ID Like "8555*",0.8*dbo_requirement.qty_per,IIf (DBO_REQUIREMENT.PART_ID Like "8512*",0.5*dbo_requirement.qty_per,0)))))))) AS HEIGHT What would be the equivalent in sql server syntax? This is what I started but not sure how to evaluate for different part numbers SELECT SUM(QTY_PER * .8) AS TOTAL_HEIGHT FROM REQUIREMENT WHERE (PART_ID LIKE '8500%') Thanks....

  1. #1

    Default if statement in query

    I had an access query that used a select statement with an
    IIf function to evaluate some values and want to do the
    same type of thing but using a sql server query. This is
    what the access query looks like:

    SELECT Sum((IIf(DBO_REQUIREMENT.PART_ID
    Like "8500*",0.8*dbo_requirement.qty_per,IIf
    (DBO_REQUIREMENT.PART_ID
    Like "8509*",0.6*dbo_requirement.qty_per,IIf
    (DBO_REQUIREMENT.PART_ID
    Like "8507*",1.6*dbo_requirement.qty_per,IIf
    (DBO_REQUIREMENT.PART_ID
    Like "8519*",0.8*dbo_requirement.qty_per,IIf
    (DBO_REQUIREMENT.PART_ID
    Like "8555*",0.8*dbo_requirement.qty_per,IIf
    (DBO_REQUIREMENT.PART_ID
    Like "8512*",0.5*dbo_requirement.qty_per,0)))))))) AS
    HEIGHT

    What would be the equivalent in sql server syntax?

    This is what I started but not sure how to evaluate for
    different part numbers

    SELECT
    SUM(QTY_PER * .8) AS TOTAL_HEIGHT
    FROM
    REQUIREMENT
    WHERE
    (PART_ID LIKE '8500%')


    Thanks.
    Chris Guest

  2. #2

    Default if statement in query

    This should do the trick. Good luck!
    --Angel

    SELECT Sum(CASE
    WHEN DBO_REQUIREMENT.PART_ID Like "8500*"
    THEN 0.8*dbo_requirement.qty_per
    WHEN DBO_REQUIREMENT.PART_ID Like "8509*"
    THEN 0.6*dbo_requirement.qty_per
    WHEN DBO_REQUIREMENT.PART_ID Like "8507*"
    THEN 1.6*dbo_requirement.qty_per
    WHEN DBO_REQUIREMENT.PART_ID Like "8519*"
    THEN 0.8*dbo_requirement.qty_per
    WHEN DBO_REQUIREMENT.PART_ID Like "8555*"
    THEN 0.8*dbo_requirement.qty_per
    WHEN DBO_REQUIREMENT.PART_ID Like "8512*"
    THEN 0.5*dbo_requirement.qty_per,0
    END) AS HEIGHT
    FROM REQUIREMENT

     
    an 
    AngelWPB Guest

  3. #3

    Default Re: if statement in query

    You want to change LIKE "8500*" to LIKE '8500%' for this to work properly
    in sqlserver.


    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "AngelWPB" <com> wrote in message
    news:6a8701c3584b$571a51c0$gbl... 
    > an [/ref]


    oj Guest

  4. #4

    Default if statement in query

    A if structure in sql is (BEGIN,END optional)
    IF
    BEGIN

    END

    ELSE
    BEGIN

    END

    So now you can do it 
    an 
    laurent Guest

Similar Threads

  1. Using form values in query statement
    By semi in forum Coldfusion Database Access
    Replies: 11
    Last Post: March 8th, 06:13 PM
  2. INSERT Statement Query
    By Murdoc in forum MySQL
    Replies: 9
    Last Post: August 20th, 02:37 PM
  3. Help! What's wrong with my QUERY statement??
    By RuBot in forum Coldfusion Database Access
    Replies: 5
    Last Post: August 13th, 09:05 AM
  4. If then statement / mysql query: NEWBIE
    By phatnugs420@comcast.net in forum PHP Development
    Replies: 1
    Last Post: August 11th, 10:18 AM
  5. Replies: 9
    Last Post: October 24th, 02:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139