Professional Web Applications Themes

select statement help - Microsoft SQL / MS SQL Server

Hello All, Question: The statment below returns a -1,1 or a 0 for the Returned, Invoiced and Voided columns. I'd like to sum these 3 values in a forth column inside the same select statment....can't seem to figure it out. Any thoughts are appreciated. R SELECT RETURNED = (CASE WHEN TableA.TYPE = 4 THEN '-1' ELSE '0' END), INVOICED = (CASE WHEN TableA.TYPE = 3 THEN '1' ELSE '0' END), VOIDED = (CASE WHEN TableA.VSTTS = 1 THEN '-1' ELSE '0' END), TableA.CUSTNMB, TableB.ITEMNMB, TableB.INVNUMBE FROM TableA INNER Join TableB ON TableA.INVNUMBE = TableB.INVNUMBE ORDER BY RETURNED ASC...

  1. #1

    Default select statement help

    Hello All,

    Question:
    The statment below returns a -1,1 or a 0 for the
    Returned, Invoiced and Voided columns.
    I'd like to sum these 3 values in a forth column inside
    the same select statment....can't seem to figure it out.
    Any thoughts are appreciated.
    R

    SELECT RETURNED = (CASE WHEN TableA.TYPE = 4 THEN '-1'
    ELSE '0' END),
    INVOICED = (CASE WHEN TableA.TYPE = 3 THEN '1' ELSE '0'
    END),
    VOIDED = (CASE WHEN TableA.VSTTS = 1 THEN '-1' ELSE '0'
    END),
    TableA.CUSTNMB, TableB.ITEMNMB, TableB.INVNUMBE
    FROM TableA INNER Join
    TableB ON TableA.INVNUMBE = TableB.INVNUMBE
    ORDER BY RETURNED ASC
    robert Guest

  2. #2

    Default Re: select statement help

    If I understood, your intent, then this might help:

    SELECT
    RETURNED = (CASE WHEN TableA.TYPE = 4 THEN -1 ELSE 0 END),
    INVOICED = (CASE WHEN TableA.TYPE = 3 THEN 1 ELSE 0 END),
    VOIDED = (CASE WHEN TableA.VSTTS = 1 THEN -1 ELSE 0 END),

    RETURNED + INVOICED + VOIDED as TheSum,

    TableA.CUSTNMB,
    TableB.ITEMNMB,
    TableB.INVNUMBE
    FROM TableA
    INNER Join TableB
    ON TableA.INVNUMBE = TableB.INVNUMBE
    ORDER BY RETURNED ASC

    If you must have the first three as chars, then you'll after to cast them as
    numbers to do the sum.


    J Guest

  3. #3

    Default Re: select statement help

    SELECT returned, invoiced, voided,
    returned + invoiced + voided,
    custnmb, itemnmb, invnumbe
    FROM
    (SELECT CASE WHEN A.TYPE = 4 THEN -1 ELSE 0 END AS returned,
    CASE WHEN A.TYPE = 3 THEN 1 ELSE 0 END AS invoiced,
    CASE WHEN A.VSTTS = 1 THEN -1 ELSE 0 END AS voided,
    A.custnmb, B.itemnmb, B.invnumbe
    FROM TableA AS A
    JOIN TableB AS B
    ON A.invnumbe = B.invnumbe) AS X
    ORDER BY returned ASC

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  4. #4

    Default Re: select statement help

    Hello Robert!

    Sorry, but you have to repeat the colums in order to achieve this or use a
    SUb-Select Query to calculate.

    Try this:

    Select S.*,(SReturned+S.INVOICED +S.VOIDED) AS SumOfAll from
    (SELECT RETURNED = (CASE WHEN TableA.TYPE = 4 THEN '-1'
    ELSE '0' END),
    INVOICED = (CASE WHEN TableA.TYPE = 3 THEN '1' ELSE '0'
    END),
    VOIDED = (CASE WHEN TableA.VSTTS = 1 THEN '-1' ELSE '0'
    END),
    TableA.CUSTNMB, TableB.ITEMNMB, TableB.INVNUMBE
    FROM TableA INNER Join
    TableB ON TableA.INVNUMBE = TableB.INVNUMBE
    ORDER BY RETURNED ASC) S

    Jens Süßmeyer.


    Jens Guest

  5. #5

    Default Re: select statement help

    select returned, invoiced,voided, (returned + invoiced + voided) as sm
    from
    (SELECT RETURNED = (CASE WHEN TableA.TYPE = 4 THEN '-1'
    ELSE '0' END),
    INVOICED = (CASE WHEN TableA.TYPE = 3 THEN '1' ELSE '0'
    END),
    VOIDED = (CASE WHEN TableA.VSTTS = 1 THEN '-1' ELSE '0'
    END),
    TableA.CUSTNMB, TableB.ITEMNMB, TableB.INVNUMBE
    FROM TableA INNER Join
    TableB ON TableA.INVNUMBE = TableB.INVNUMBE) a
    ORDER BY RETURNED ASC

    --
    -Vishal
    "robert" <com> wrote in message
    news:05c501c3668e$68036cf0$gbl... 


    Vishal Guest

  6. #6

    Default Re: select statement help

    You can't reference derived column names in the same SELECT list that
    creates them.
    See my earlier post for how to do it with a derived table.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  7. #7

    Default Re: select statement help

    Thanks for the help guys,
    Awesome.

    A few adjustments and it works great.

    Thanks again,
    R

     
    SELECT list that 
    table. 
    robert Guest

Similar Threads

  1. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  2. help with SELECT statement
    By Chris Hohmann in forum ASP
    Replies: 2
    Last Post: August 19th, 05:10 PM
  3. possible to run a select with an if statement in it?
    By Jim in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 17th, 04:48 PM
  4. Need some help on a SELECT statement
    By Sydney in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 16th, 01:42 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