Skip to Content
0
Former Member
Apr 11, 2009 at 06:12 AM

Query Help

13 Views

Hi Experts, I'm using the following query to get the batch no details with expiry date. I need to pass the item property as parameter which is not working. Eventhough i choose particular property it is displaying list of all items irrespective of property. Please do suggest me on this issue.

SELECT T0.[ItemCode],  T0.[ItemName],T0.[BatchNum]as 'Batch No',  MIN(T0.[ExpDate]) AS 'Expiry Date', t0.Quantity, T0.[WhsCode], 
T1.BASENUM AS PurchaseBillRef
, T1.DOCDATE AS PurchaseBillDate, t4.itmsgrpnam FROM [dbo].[OIBT] T0  
INNER  JOIN [dbo].[IBT1] T1  ON  T1.[ItemCode] = T0.ItemCode  AND  T1.[BatchNum] = 
T0.BatchNum  AND  T1.[WhsCode] = T0.WhsCode   
INNER  JOIN [dbo].[OITM] T2  ON  T2.[ItemCode] = T1.ItemCode   
INNER JOIN OITB T3 ON T3.ITMSGRPCOD=T2.ITMSGRPCOD 
, OITG T4 
WHERE T2.[InvntItem] = 'Y'
AND  T2.[Canceled] <> 'Y'  and t1.direction=0 AND T0.QUANTITY>0
AND T0.ITEMCODE>=[%0] AND T0.ITEMCODE<=[%1] 
AND T1.WHSCODE>=[%2] AND T1.WHSCODE<=[%3] AND T3.ITMSGRPNAM >=[%4] 
AND T3.ITMSGRPNAM <=[%5] 
and (T2.[QryGroup1] = 'Y' or
T2.[QryGroup2]= 'Y' or
T2.[QryGroup3]= 'Y' or
T2.[QryGroup4]= 'Y' or
T2.[QryGroup5]= 'Y' or
T2.[QryGroup6]= 'Y' or
T2.[QryGroup7]= 'Y' or
T2.[QryGroup8]= 'Y' or
T2.[QryGroup9]= 'Y' or
T2.[QryGroup10]= 'Y' or
T2.[QryGroup11]= 'Y' or
T2.[QryGroup12]= 'Y' or
T2.[QryGroup13]= 'Y' or
T2.[QryGroup14]= 'Y' or
T2.[QryGroup15]= 'Y' or
T2.[QryGroup16]= 'Y' or
T2.[QryGroup17]= 'Y' or
T2.[QryGroup18]= 'Y' or
T2.[QryGroup19]= 'Y' or
T2.[QryGroup20]= 'Y' or
T2.[QryGroup21]= 'Y' or
T2.[QryGroup22]= 'Y' or
T2.[QryGroup23]= 'Y' or
T2.[QryGroup24]= 'Y' or
T2.[QryGroup25]= 'Y' or
T2.[QryGroup26]= 'Y' or
T2.[QryGroup27]= 'Y' or
T2.[QryGroup28]= 'Y' or
T2.[QryGroup29]= 'Y' or
T2.[QryGroup30]= 'Y' or
T2.[QryGroup31]= 'Y' or
T2.[QryGroup32]= 'Y' or
T2.[QryGroup33]= 'Y' or
T2.[QryGroup34]= 'Y' or
T2.[QryGroup35]= 'Y' or
T2.[QryGroup36]= 'Y' or
T2.[QryGroup37]= 'Y' or
T2.[QryGroup38]= 'Y' or
T2.[QryGroup39]= 'Y' or
T2.[QryGroup40]= 'Y' or
T2.[QryGroup41]= 'Y' or
T2.[QryGroup42]= 'Y' or
T2.[QryGroup43]= 'Y' or
T2.[QryGroup44]= 'Y' or
T2.[QryGroup45]= 'Y' or
T2.[QryGroup46]= 'Y' or
T2.[QryGroup47]= 'Y' or
T2.[QryGroup48]= 'Y' or
T2.[QryGroup49]= 'Y' or
T2.[QryGroup50]= 'Y' or
T2.[QryGroup51]= 'Y' or
T2.[QryGroup52]= 'Y' or
T2.[QryGroup53]= 'Y' or
T2.[QryGroup54]= 'Y' or
T2.[QryGroup55]= 'Y' or
T2.[QryGroup56]= 'Y' or
T2.[QryGroup57]= 'Y' or
T2.[QryGroup58]= 'Y' or
T2.[QryGroup59]= 'Y' or
T2.[QryGroup60]= 'Y' or
T2.[QryGroup61]= 'Y' or
T2.[QryGroup62]= 'Y' or
T2.[QryGroup63]= 'Y' or
T2.[QryGroup64]= 'Y')
AND  T4.[ITMSGRPNAM] >=[%6] AND T4.[ITMSGRPNAM] <=[%7] 
GROUP BY T0.[ItemCode], T0.[BatchNum],T1.BASENUM, T1.DOCDATE, 
T0.[WhsCode], T0.[ItemName], T0.QUANTITY, t4.itmsgrpnam 
Thank You