Why does a get thru my SELECT statement ?

• Digs

  SSCertifiable

  Points: 7333

  More actions

  June 11, 2006 at 10:07 pm

  #168834

  SELECT  CASE

  WHEN CAST(COALESCE ([STKTRNTYPE], '0') AS INT) IN (13, 2)

  THEN CAST(COALESCE ([OUT_QTY], '0') AS DECIMAL(20, 4)) * - 1

  WHEN CAST(COALESCE ([STKTRNTYPE], '0') AS INT) IN (1, 12)

  THEN CAST(COALESCE ([IN_QTY], '0') AS DECIMAL(20, 4))

  WHEN CAST(COALESCE ([STKTRNTYPE], '0') AS INT) = 5 AND CAST(COALESCE ([OUT_QTY], '0') AS DECIMAL(20, 0)) <> 0

  THEN CAST(COALESCE ([OUT_QTY], '0') AS DECIMAL(20, 4)) * - 1

  WHEN CAST(COALESCE ([STKTRNTYPE], '0') AS INT)  = 5 AND CAST(COALESCE ([IN_QTY], '0') AS DECIMAL(20, 0)) <> 0

  THEN CAST(COALESCE ([IN_QTY], '0') AS DECIMAL(20, 4)) END

  AS QUANTITY

  FROM dbo.MYTABLE

   

  MYTABLE

  [STKTRNTYPE] VARCHAR(50)

  [OUT_QTY] VARCHAR(50)

  [IN_QTY] VARCHAR(50)

  DATA SAMPLE

  13, 8,0

  1, 0,7

  12, 15,<Null>    : Fails

  5, <Null>,0       : Fails

  With all the COALESCE statements I would have thought that no NULLS would get thru the select statement, but they do ?? Why ?

• Jeff Moden

  SSC Guru

  Points: 1004638

  More actions

  June 12, 2006 at 8:47 pm

  #643271

  I think the reason for this may be very simple... try this...

   SELECT *
     FROM MyTable
    WHERE In_Qty  = '<Null>'
       OR Out_Qty = '<Null>'

  The literal '<Null>' is not a real NULL and doesn't come close to meaning the same thing.  This situation usually arises when people see <NULL> in Enterprise Manager and type in the literal '<Null>' thinking that's how you make a NULL.  To correctly type a real NULL in Enterprise Manager, tab into the "cell", hold down the {Ctrl} key, and press the digit zero.

  Now, even when you get that fixed, it'll only fix the failure you observed for 12,15,<Null>.  It won't fix the 5,<Null>,0 condition because both of your tests for StkTrnType = 5 test the other columns for a <>0 condition.  Of course, both columns evaluate to "0" in this case so neither case statement picks up on it and you have no ELSE. 

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)