cancel
Showing results for 
Search instead for 
Did you mean: 

LINK TABLES

Former Member
0 Kudos

Hai Guys,

Will anybody give me some solution to link the below tables to make a query.


OJDT
JDT1
OCRD
OCRG
OINV
OITM
OITB

AND INPUT WOULD BE POSTING DATE RANGE(FROM OJDT OR OINV) AND ITEMGROUPNAME(OITB)

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

Your requirements may not be easy to understand. Try this first:


SELECT distinct T2.CardCode, T2.[CardName],T1.[RefDate],T0.[BaseRef],
T0.[Debit],T0.[Credit],T0.[BalDueDeb], T0.[BalDueCred]
FROM JDT1 T0  
INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
INNER JOIN OCRD T2 on T2.CardCode = T0.ShortName 
INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode 
INNER JOIN OINV T4 ON T2.CardCode = T4.CardCode 
INNER JOIN INV1 T5 ON T5.DocEntry = T4.DocEntry 
left JOIN OITM T6 ON T6.ItemCode = T5.ItemCode
left JOIN OITB T7 ON T7.ItmsGrpCod = T6.ItmsGrpCod 
WHERE T0.[RefDate] BETWEEN '[%0]' AND '[%1]' AND T0.[BalDueDeb] != 0 
AND T3.GroupName != 'Vendors' and T7.[ItmsGrpNam] = '[%2]'

Thanks,

Gordon

Answers (1)

Answers (1)

Former Member
0 Kudos

Hello,

Yo can use query generator to get a link between these tables. Try this;

SELECT * FROM OCRD T0 INNER JOIN OCRG T1 ON T0.GroupCode = T1.GroupCode INNER JOIN OINV T2 ON T0.CardCode = T2.CardCode INNER JOIN OJDT T3 ON T2.TransId = T3.TransId INNER JOIN JDT1 T4 ON T3.TransId = T4.TransId INNER JOIN OITM T5 ON T0.CardCode = T5.CardCode INNER JOIN OITB T6 ON T5.ItmsGrpCod = T6.ItmsGrpCod

Thanks,

Joseph

Former Member
0 Kudos

Hai Joseph,

Based on the linking u have posted i have used the below query and when i run it its showing no data.


SELECT distinct T2.CardCode, T2.[CardName],T1.[RefDate],T0.[BaseRef],
T0.[Debit],T0.[Credit],T0.[BalDueDeb], T0.[BalDueCred]
FROM JDT1 T0  
INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
INNER JOIN OCRD T2 on T2.CardCode = T0.ShortName 
INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode 
INNER JOIN OINV T4 ON T2.CardCode = T4.CardCode 
left JOIN OITM T5 ON T2.CardCode = T5.CardCode 
left outer JOIN OITB T6 ON T5.ItmsGrpCod = T6.ItmsGrpCod 
WHERE T0.[RefDate] BETWEEN '[%0]' AND '[%1]' AND T0.[BalDueDeb]<>'0' AND 
T3.GroupName != 'Vendors' and T6.[ItmsGrpNam] = '[%2]'

Pls tell me wats wrong in the above.

former_member1269712
Active Contributor
0 Kudos

Hi,

Try this,

select T0.Docnum from OINV T0
inner join inv1 T01 on T0.Docentry=T01.Docentry
inner join OJDT T1 on T0.transid=T1.transid
inner join JDT1 T2 on T1.transid=T2.transid
inner join OCRD T3 on T0.Cardcode=T3.Cardcode
inner join OCRG T4 on T3.Groupcode=T4.Groupcode
inner join OITM T5 on T01.itemcode=T5.Itemcode
select * from OITB T6 on T5.ItmsGrpCod = T6.ItmsGrpCod
WHERE T1.[BalDueDeb]!='0' AND T3.GroupName != 'Vendors' AND T0.[DocDate] BETWEEN '[%0]' AND '[%1]' 
AND T6.[ItmsGrpNam] = '[%2]'

Thanks

Sachin

Former Member
0 Kudos

Hi Sachin,

I runned the query and its showing the below error

"1). [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near the keyword 'on'. 2). [Microsoft][SQL Native Client][SQL Server]Statement 'User-Defined Values' (CSHS) (s) could not be prepared."

Former Member
0 Kudos

Hello,

Try this;

SELECT distinct T2.CardCode, T2.[CardName],T1.[RefDate],T0.[BaseRef],
T0.[Debit],T0.[Credit],T0.[BalDueDeb], T0.[BalDueCred]
FROM JDT1 T0  
INNER JOIN OJDT T1 ON T0.TransId = T1.TransId
INNER JOIN OCRD T2 on T2.CardCode = T0.ShortName 
INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode 
INNER JOIN OINV T4 ON T2.CardCode = T4.CardCode 
left  outer JOIN OITM T5 ON T2.CardCode = T5.CardCode 
left outer JOIN OITB T6 ON T5.ItmsGrpCod = T6.ItmsGrpCod 
WHERE T0.[RefDate] BETWEEN '[%0]' AND '[%1]' AND T0.[BalDueDeb] !='0' AND 
T3.GroupName != 'Vendors' 

I just removed the item grp selection and it was workin fine

thanks,

Joseph

Former Member
0 Kudos

Hai joseph,

But in this report the Item group name needs to be mandatory and based on the item group only we need the data.

I want to run the Debtors outstanding based on ItemGroups so i am using that query .

Pls give some solution

former_member1269712
Active Contributor
0 Kudos

HI,

Try this.. I checked showing docnos

select T0.Docnum from OINV T0
inner join inv1 T01 on T0.Docentry=T01.Docentry
inner join OJDT T1 on T0.transid=T1.transid
inner join JDT1 T2 on T1.transid=T2.transid
inner join OCRD T3 on T0.Cardcode=T3.Cardcode
inner join OCRG T4 on T3.Groupcode=T4.Groupcode
inner join OITM T5 on T01.itemcode=T5.Itemcode
inner join OITB T6 on T5.ItmsGrpCod = T6.ItmsGrpCod
WHERE T2.[BalDueDeb]!=0 AND T4.GroupName != 'Vendors' AND T0.[DocDate] BETWEEN '[%0]' AND '[%1]' 
AND T6.[ItmsGrpNam] = '[%2]'

Thanks

Sachin