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)
  )