cancel
Showing results for 
Search instead for 
Did you mean: 

query for serial number for items sold

Former Member
0 Kudos

hi,

I want a query which would show serial numbers for Product A and Product B which were sold. I know that following tables will be used

SELECT T0.[CardCode], T0.Docnum,T0.[DocDate], T0.[CardName],T1.Quantity,T1.[ItemCode],T6.DistNumber

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

inner join DLN1 T4 on T1.DocEntry=T4.BaseEntry

inner join ODLN T7 on T4.DocEntry=T7.DocEntry

inner join OSRN T6 on T4.itemcode=t6.itemcode

WHERE T1.[ItemCode] in ('ProductA','ProductB') and T0.[CardName] like'% naaveol%' and T0.CANCELED<>'Y'

group by T0.[CardCode],T0.[CardName], T0.[DocDate],T1.[ItemCode] ,T0.Docnum,T6.DistNumber,T1.Quantity

Howerver I am doing some thing wrong and it is showing alot more records than it should. basically what I was trying to do was to get

item code

serial num

quantity

for the items mentioned sold to the particular business partner.

Your help will be greatly appreciated

View Entire Topic
Former Member

Hi again.......

Please try this.......

SELECT T0.CardCode, T0.Docnum,T0.DocDate, T0.CardName,T1.Quantity,T1.ItemCode,T4.DistNumber 
from ODLN T0 inner join DLN1 T1 on T0.DocEntry=T1.DocEntry
left outer join SRI1 I1 on T1.ItemCode=I1.ItemCode   and (T1.DocEntry=I1.BaseEntry and T1.ObjType=I1.BaseType)
left outer join OSRN T4 on T4.ItemCode=I1.ItemCode and I1.SysSerial=T4.SysNumber

Regards,

Rahul

Former Member
0 Kudos

Thank you so much for answering my question :). This solved my issue..

Former Member
0 Kudos

I have sent you email on your gmail account, please check.