Member Avatar for Moordoom

In the view below, I need the line "isnull(wwr.packed_qty, isnull((select sum(qty_on_hand) from lot_loc where item = coi.item and loc like 'P%'), 0)) packed_qty, " to include a "or 'S%'", so that both P and S are found, but I get a syntax error when I add it.
What is the correct syntax that I am missing?
This is what I used isnull(wwr.packed_qty, isnull((select sum(qty_on_hand) from lot_loc where item = coi.item and loc like 'P%' or 'S%'), 0)) packed_qty, and got the syntax at 'or' error.
Below is the whole "Current" view.

CREATE            view [dbo].[SII_vwYardsOnHandDueRpt]
as
SELECT  isnull(wwr.greige_item, (select top 1 item from item where family_code = 'GREIGE' and coi.item like item.item + '%')) greige_item, 
        isnull(wwr.greige_yds, 0) greige_yds, 
        isnull(wwr.finished_item, (select top 1 item from item where family_code = 'FINISHED' and coi.item like item.item + '%')) finished_item, 
        isnull(wwr.finished_yds, 0) finished_yds, 
        isnull(wwr.packed_item, coi.item) packed_item, 
        isnull(wwr.packed_desc, it.description) packed_desc, 
        isnull(wwr.packed_qty, isnull((select sum(qty_on_hand) from lot_loc where item = coi.item and loc like 'P%'), 0)) packed_qty, 
        isnull(wwr.pack_uom, it.u_m) pack_uom, 
        coi.co_num, 
        coi.co_line, 
        coi.co_release, 
        coi.due_date, 
        (coi.qty_ordered - coi.qty_shipped) * dbo.SIIItemSalesUOM2(coi.co_num, coi.co_line, coi.co_release) qty_due, 
        coi.u_m sales_uom, 
        ca.[name] customer_name,
	dbo._SIIQtyDueInYards(coi.co_num, coi.co_line, coi.co_release) qty_due_yds
FROM    coitem coi WITH(NOLOCK)
INNER JOIN item it WITH(NOLOCK)
ON      it.item = coi.item 
LEFT OUTER JOIN co WITH(NOLOCK)
ON      co.co_num = coi.co_num 
LEFT OUTER JOIN custaddr ca WITH(NOLOCK)
ON      ca.cust_num = co.cust_num 
    AND ca.cust_seq = co.cust_seq 
LEFT OUTER JOIN SII_vwWipWebReport wwr 
ON      wwr.packed_item = coi.item 
WHERE   co.stat  in ('O', 'S') 
    AND coi.stat in ('O', 'S')
Edited by Ezzaral because: Added [code] [/code] tags. Please use them to format any code that you post.
  • 2 Contributors
  • 1 Reply
  • 99 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by sknake
Member Avatar for sknake 
and (loc like 'P%' or loc like 'S%')
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.