How to Convert VARCHAR to a number using CASE?

Question

I am trying in the below query (which is going to be a subquery of another larger query) to convert a VARCHAR to a value which can be measured as either <0 or >0 - not sure exactly what value to use. I am getting the error "operand data type is invalid in the sum operator" this is the very first time I encountered this. The ultimate goal is to say "when the value is 'L' then return 0, else return 1" additionally DEL_INDICATOR is a field in thePO_ITEM table but I omitted in the SELECT.

Here is the Updated code:

 SELECT    G.order_no AS 'GPS_ORDER_#', 
          G.order_status AS 'GPS_ORDER_STATUS', 
          G.cst_order_no AS 'GPS_CUSTOMER_PO_#',
          H.PO_NUMBER AS 'SAP_PO_#',
          P.PO_ITEM_NUMBER, 
          P.DEL_INDICATOR 

FROM   

          (SELECT    order_no, 
                     order_status, 
                     cst_order_no

           FROM      asagdwpdx_prod.dbo.SimoxOrder1

           UNION ALL

           SELECT    order_no, 
                     order_status, 
                     cst_order_no

           FROM      asagdwpdx_prod.dbo.SimoxOrder2

           UNION ALL 

           SELECT    order_no, 
                     order_status, 
                     cst_order_no

           FROM      asagdwpdx_prod.dbo.SimoxOrder3) G 

JOIN      PDX_SAP_USER.dbo.VW_PO_HEADER H ON G.order_no = H.AHAG_NUMBER

JOIN      PDX_SAP_USER.dbo.VW_PO_ITEM P ON H.PO_NUMBER = P.PO_NUMBER 

WHERE     G.order_status = '10'

AND       NOT EXISTS  ( 
                                SELECT P1.PO_NUMBER,
                                       P1.PO_ITEM_NUMBER, 
                                       SUM(CASE 
                                            WHEN CAST(P1.DEL_INDICATOR AS INT) = 'L' 
                                            THEN '0'
                                            ELSE '1'
                                       END AS [SUM_DEL]) AS [SUM]

                                FROM   PDX_SAP_USER.dbo.VW_PO_ITEM P1

                                WHERE  P1.PO_NUMBER = H.PO_NUMBER

                                GROUP BY P1.PO_NUMBER,
                                         P1.PO_ITEM_NUMBER,
                                         CASE 
                                            WHEN P1.DEL_INDICATOR = 'L' 
                                            THEN '0'
                                            ELSE '1'
                                       END

                                HAVING SUM  (CASE 
                                            WHEN CAST(P1.DEL_INDICATOR AS INT) = 'L' 
                                            THEN '0'
                                            ELSE '1'
                                       END)  > '0')

Answer 1

I do not think you have adequately described your desired results, but here are two options that might get you on the right path:

Using group by with the having clause to only return po_type > 0:

select 
    po_number
  , po_item_number
  , po_type = sum(case when del_indicator = 'L' then 0 else 1 end)
from vw_po_item
group by 
    po_number
  , po_item_number
having sum(case when del_indicator = 'L' then 1 else 0 end) > 0

Using a common table expression (a derived table would work just as well) with the sum() over() aggregation window function:

;with cte as (
  select 
      po_number
    , po_item_number
    , del_indicator_calc = case when del_indicator = 'L' then 0 else 1 end
    , po_type = sum(case when del_indicator = 'L' then 0 else 1 end)
        over (partition by po_number, po_item_number)
  from vw_po_item
)
select *
from cte
where po_type > 0

---

Depending on how you plan on using this as a "subquery of another larger query" you may want to consider using an exists() or not exists() clause instead:

select ...
from ...
where ...
   /* only return records that have an item with del_indicator = 'L' */
  and exists (
    select 1 
    from po_item i
    where i.po_number = t.po_number -- `t` being an alias for a table in your `from` clause
      -- if you are using the subquery per item instead of just `po_number`
      and i.po_item_number = t.po_item_number  

      and del_indicator = 'L'
    )

---

Example of using not exists()

select 
    G.order_no        as [gps_order_#]
  , G.order_status    as [gps_order_status]
  , G.cst_order_no    as [gps_customer_po_#]
  , H.po_number       as [sap_po_#]
  , P.po_item_number
  , P.del_indicator
from (
  select order_no, order_status, cst_order_no 
  from asagdwpdx_prod.dbo.SimoxOrder1
  union all
  select order_no, order_status, cst_order_no 
  from asagdwpdx_prod.dbo.SimoxOrder2
  union all
  select order_no, order_status, cst_order_no 
  from asagdwpdx_prod.dbo.SimoxOrder3
  ) G
  inner join pdx_sap_user.dbo.vw_po_header H
    on G.order_no = H.ahag_number
  inner join pdx_sap_user.dbo.vw_po_item P
    on H.po_number = P.po_number
where G.order_status = '10'
   and not exists (
    select 1
    from pdx_sap_user.dbo.vw_po_item i
    where i.po_number = H.po_number
      and (i.del_indicator <> 'L' or i.del_indicator is null)
  )

Answer 2

1) You need to convert the VARCHAR to numeric BEFORE trying to sum the value. 2) You need a GROUP BY clause if you want to perform an aggregation on just 1 column. 3) You cannot reference an expression alias in the WHERE clause, you'll need to use a HAVING clause instead. 4) You DO NOT want to use the FLOAT data type unless you are aware of the problems associated with use of "approximate" number data type. Depending in the data in DEL_INDICATOR_CALC, consider using either INT, DECIMAL or NUMERIC.

With that in mind, see how the following works for you...

SELECT
    vpi.PO_NUMBER,
    vpi.PO_ITEM_NUMBER,
    CASE WHEN vpi.DEL_INDICATOR = 'L' THEN '0' ELSE '1' END AS 'DEL_INDICATOR_CALC',
    SUM(TRY_CONVERT(INT, vpi.DEL_INDICATOR_CALC)) AS 'PO_TYPE'
FROM
    dbo.VW_PO_ITEM vpi
GROUP BY
    vpi.PO_NUMBER,
    vpi.PO_ITEM_NUMBER,
    CASE WHEN vpi.DEL_INDICATOR = 'L' THEN 0 ELSE 1 END
HAVING 
    SUM(TRY_CONVERT(INT, vpi.DEL_INDICATOR_CALC)) = 1;