Query to combine common fields in two tables and Balance

[Former Member]

Hi Everyone,

Can anyone help me out to combine common fields in two tables as below:

I have joined four tables like OIGN,IGN1, ODLN and DLN1 as: " OIGN T0  INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry, ODLN T2 INNER JOIN DLN1 T3 ON T2.DocEntry = T3.DocEntry "

In OIGN - I changed Ref.2 field as "Customer Code" and given FMS for customer codes. The Card code field in ODLN is also resulting same.

In IGN1 - I have some UDF's like -> U_Lot No, U_sec/size, U_Coil / Pkt No. and U_Quality Status.

In DLN2 - I have the same fields llike -> U_Lot No, U_sec/size, U_Coil / Pkt No. and U_Quality Status.

But, Quantity is different in both the tables.

[

   For Ex: In OIGN i entered Qty = 10 having a Lot No.

               I made 4 Deliveries with 2Qty each for the same item with the same Lot No.

               i.e., 10 - 2(4) = 2 Remaining stock(Balance)

]

And here, WHERE condition OIGN series Name ='GPI' and ODLN series Name = 'SSD" ;

Now i need to make a report which results the "Balance" with these common fields of two tables of the particular document series.

Hope you will do needful.

Thank You,

Regards,

Ravi Teja.G.

[former_member209066]

Hi,

Please Check this

SELECT T0.[DocNum], T0.[DocDate] , T0.[Ref2] , T1.[Dscription],

T1.[Quantity] as InQty, T1.[U_Coil_PktNo], T1.[U_Sec_Size],

T1.[U_Lot_No], T3.[DocNum] , T3.[DocDate] , T4.[Quantity] as OutQty 

FROM [dbo].[OIGN]  T0 INNER JOIN [dbo].[IGN1]  T1 ON T0.DocEntry = T1.DocEntry

  LEFT JOIN ODLN T3 ON T0.Ref2 = T3.CardCode

LEFT JOIN DLN1 T4 ON T3.DocEntry = T4.DocEntry and T1.ItemCode=T4.ItemCode and

T1.[U_Coil_PktNo]=  T4.[U_Coil_PktNo] and T1.[U_Sec_Size] = T4.[U_Sec_Size] and

T1.[U_Lot_No] = T4.[U_Lot_No]

Where T0.DocDate>='[%1]' and T0.DocDate<='[%2]'

Thanks,

Nithi

[kothandaraman_nagarajan]

Try this way,

SELECT *

FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry left join  IGN1 T2 on  T2.[ItemCode]  =  T1.[ItemCode] and T2.[U_Lot No]  = T1.[U_Lot No] INNER JOIN OIGN T3 ON T2.DocEntry = T3.DocEntry

WHERE T0.[CardCode] =  T3.[Ref2] and  T3.[Series] = '47' and T0.[Series] ='27'

[Former Member]

Hi Mr.Nagarajan Sir,

Thanks for your valuable reply..

As per your suggestion, i created the query like:

[

SELECT T0.[DocNum], T0.[DocDate] , T0.[Ref2] , T1.[Dscription], T1.[Quantity] as , T1.[U_Coil_PktNo], T1.[U_Sec_Size], T1.[U_Lot_No], T3.[DocNum] , T3.[DocDate] , T4.[Quantity]  FROM [dbo].[OIGN]  T0 INNER JOIN [dbo].[IGN1]  T1 ON T0.DocEntry = T1.DocEntry INNER JOIN NNM1 T2 ON T0.Series = T2.Series LEFT JOIN ODLN T3 ON T2.Series = T3.Series LEFT JOIN DLN1 T4 ON T3.DocEntry = T4.DocEntry WHERE T3.[CardCode] =  T0.[Ref2] and  T3.[Series] = '6' and T0.[Series] ='19'

]

While executing the above query, there is no result found.May be some changes need to be done for this query.

please help me...

Thank You,

Regards,

Ravi Teja.G

[kothandaraman_nagarajan]

Try this:

SELECT T0.[DocNum], T0.[DocDate] , T0.[Ref2] , T1.[Dscription], T1.[Quantity] as , T1.[U_Coil_PktNo], T1.[U_Sec_Size], T1.[U_Lot_No], T3.[DocNum] , T3.[DocDate] , T4.[Quantity]  FROM [dbo].[OIGN]  T0 INNER JOIN [dbo].[IGN1]  T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN NNM1 T2 ON T0.Series = T2.Series LEFT JOIN ODLN T3 ON T2.Series = T3.Series LEFT JOIN DLN1 T4 ON T3.DocEntry = T4.DocEntry WHERE T3.[CardCode] =  T0.[Ref2] and  T3.[Series] = '6' and T0.[Series] ='19'

[Former Member]

Hi Ravi,

The link to ODLN is not valid. You need find a line level link. Only CardCode and Series would not be enough.

Thanks,

Gordon

[Former Member]

Hi Everyone!

Please help me for the above mentioned query requirement.

Thanks & Regards,

Ravi Teja.G

[kothandaraman_nagarajan]

Hi,

What is exact your exact requirement? Finding common field and linking may or may not give correct result.

Thanks & Regards,

Nagarajan

[Former Member]

Hi Mr. Nagarajan,

Thanks for your kind reply...

Below steps indicates my process.

[

   For Ex: In OIGN i entered Qty = 10 having a Lot No.

               I made 4 Deliveries with 2Qty each for the same item with the same Lot No.

               i.e., 10 - 2(4) = 2 Remaining stock(Balance)

]

Now i want to make a query report for 'Balance stock' for the particular 'Lot No.' as mentioned in the question.

Simply Im trying to create a Inventory status report (Lot No. wise).

Thank You,

Regards,

Ravi Teja.G