amend a query

[tonyvee1973]

Hi All

I have a query i am trying to amend slightly but having difficulties.
The query is below, i have a field of STK_PHYSICAL which is highlighted. I also have a field named FREETYPE8 (Highlighted) which i need to show the words "LOW" if the STK_PHYSICAL is less than 6.
Any help really would be appreciated
Many thanks


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,
[highlight #FCE94F](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','DEESET')) AS Stk_Physical,[/highlight]
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,
[highlight #FCE94F]'' AS FreeType8,[/highlight]
'' 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 ('CR','RE','REJ')
INNER JOIN (
Select POD_STOCK_CODE,
POD_QTYRESERVED,
POD_REQDATE
From (
Select POD_STOCK_CODE,
POD_QTYRESERVED,
POD_REQDATE,
Row_Number() Over (Partition By POD_STOCK_CODE Order By CASE WHEN POD_QTYRESERVED > 0 THEN 0 ELSE 1 END, POD_REQDATE) 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 and dbo.STK_STOCK.STK_SORT_KEY not in ('BILLY')