condition in query

[tonyvee1973]

hi everyone.
I have a query below and need to add a condition in but stuck where to add it. Query below:

SELECT dbo.STK_STOCK.STKCODE,
'' AS Spare,
dbo.STK_STOCK.STKNAME,
'' AS UnitOfMeasure,
'' AS UnitOfSales,
dbo.STK_STOCK.STK_BASEPRICE,
dbo.STK_STOCK3.STK_USRNUM1 AS RRP,
'' AS PriceCode,
--StockLocation.STK_PHYSICAL,
(SELECT CASE WHEN SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) IS NULL THEN 0 ELSE SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) END
FROM dbo.STK_LOCATION WHERE dbo.STK_LOCATION.LOC_STOCK_CODE = dbo.STK_STOCK.STKCODE AND dbo.STK_LOCATION.LOC_CODE NOT IN ('CREDIT','RETURN','REJECT')) AS Stk_Physical,
PopDetails.POD_QTYRESERVED AS QuantityDue,

--POPDetails AS QuantityDue,

CONVERT(CHAR(8), PopDetails.POD_REQDATE, 112) AS NextDelivery,

-- (SELECT TOP 1 CONVERT(CHAR(8), POD_REQDATE, 112)
-- FROM POP_DETAIL
-- WHERE POD_STOCK_CODE = dbo.STK_STOCK.STKCODE
-- GROUP BY POD_STOCK_CODE, POD_REQDATE
-- ORDER BY POD_REQDATE ASC
-- ) AS NextDelivery,

'' AS ColourCode,
'' AS ProductGroup,
CASE dbo.STK_STOCK3.STK_USRFLAG2 when 1 then 'Y' ELSE 'N' END as Discontinued,
CASE dbo.STK_STOCK3.STK_USRFLAG1 WHEN 1 then 'Y' ELSE 'N' END as Dead,
dbo.STK_STOCK.STK_BARCODE AS BarCode,
'' AS MinimumMultiples,
'' AS GenericCode5,
'' AS GenericCode6,
dbo.STK_STOCK.STK_SORT_KEY,
dbo.STK_STOCK.STK_SORT_KEY1,
'' AS FreeType3,
'' AS FreeType4,
'' AS FreeType5,
'' AS FreeType6,
'' AS FreeType7,
'' AS FreeType8,
'' AS BreakCode,
ROUND(dbo.STK_STOCK.STK_COSTPRICE / dbo.STK_STOCK_2.STK_BUY_UNIT1, 2) AS STK_COSTPRICE,
'' AS MinimumPrice,
'' AS RangeCode,
'' AS MinimumPromotional,
'' AS TaxCode,
'' AS MinimumOrderQuantity,
'' AS FutureStock
FROM dbo.STK_STOCK
INNER JOIN dbo.STK_STOCK_2
ON dbo.STK_STOCK.STKCODE = dbo.STK_STOCK_2.STKCODE2
INNER JOIN dbo.STK_STOCK3
ON dbo.STK_STOCK_2.STKCODE2 = dbo.STK_STOCK3.STKCODE3
-- INNER JOIN (
-- Select dbo.STK_LOCATION.LOC_STOCK_CODE,
-- SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL
-- From dbo.STK_LOCATION
-- Group By dbo.STK_LOCATION.LOC_STOCK_CODE
-- ) As StockLocation
-- On dbo.STK_STOCK.STKCODE = StockLocation.LOC_STOCK_CODE and dbo.STK_LOCATION.LOC_CODE NOT IN ('CREDIT','RETURN','REJECT')
INNER JOIN (
Select POD_STOCK_CODE,
POD_QTYRESERVED,
POD_REQDATE
From (
Select POP_DETAIL.POD_STOCK_CODE,
POP_DETAIL.POD_QTYRESERVED,
POP_DETAIL.POD_REQDATE,
Row_Number() Over (Partition By POD_STOCK_CODE Order By POD_QTYRESERVED DESC, POD_REQDATE ASC) As RowId
From POP_DETAIL


) As ALL_POP_DETAILS
Where RowId = 1
) As PopDetails
On dbo.STK_STOCK.STKCODE = PopDetails.POD_STOCK_CODE
-- LEFT OUTER JOIN
-- (SELECT TOP 1 POD_STOCK_CODE, SUM(ISNULL(POD_QTYRESERVED, 0)) AS QTY
-- FROM POP_DETAIL
-- WHERE POD_STOCK_CODE = dbo.STK_STOCK.STKCODE
-- GROUP BY POD_STOCK_CODE, POD_REQDATE
-- ORDER BY QTY DESC, POD_REQDATE ASC
-- )AS POPDetails ON POPDetails.POD_STOCK_CODE = dbo.STK_STOCK.STKCODE
WHERE dbo.STK_STOCK3.STK_USRFLAG1 <> 1



The condition i need to add is where POD_QTYRESERVED = 0 then i dont want no date showing in POD_REQDATE (This can either show NULL or 0)
Any pointers would be great
Thanks

 

[TamarGranor]

Would CASE give you what you need?

CASE POD_QTYRESERVED WHEN 0 THEN null ELSE POD_REQDATE

Tamar

 

[TheBugSlayer]

There is 2 or 3 places you could use it. You need to be more precise.

From POP_DETAIL WHERE POD_QTYRESERVED = 0 AND (POD_REQDATE IS NULL OR POD_REQDATE = 0

?

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[tonyvee1973]

Hi Guys
tried both these and get the errors below:

Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'From'.
Msg 156, Level 15, State 1, Line 75
Incorrect syntax near the keyword 'As'.

What other info would be needed to assist with this?
Thanks

 

[TamarGranor]

TheBugSlayer and I had different understandings of what you're looking for. When you said "The condition i need to add is where POD_QTYRESERVED = 0 then i dont want no date showing in POD_REQDATE (This can either show NULL or 0)," what did you mean?

I interpreted this as meaning that you wanted to have a field in the query which contains POD_REQDATE when POD_QTYRESERVED is not 0 and null when it's 0. TheBugSlayer interpreted it as wanting to add something to the WHERE clause to limit what records come back.

In words, what are you trying to do? Or show a little sample data and the desired result (though that may be hard, since you seem to have a lot of tables and fields involved here).

Tamar