need oact.acctname in report

[vinayak_chavan]

i creating report on GL but havent get OACT.ACCTNAME IN QUERY . it gives only first row data.

find attachment for your reference

query as below

SELECT DISTINCT T0.[DocNum], T0.[DocType], T0.[DocDate], T0.[Comments], T0.[CardName], T0.BPACT, T0.[NoDocSum] , T0.[TransId] ,T0.DOCTOTALSY,T3.SERIESNAME,T0.JRNLMEMO,T4.REF3,T5.DEBIT FROM OVPM T0  INNER JOIN OACT T1 ON T0.CashAcct = T1.AcctCode INNER JOIN OJDT T4 ON T0.TransId = T4.TransId INNER JOIN [dbo].[JDT1]  T5 ON T4.TransId = T5.TransId

INNER JOIN nnm1 T3 ON T0.SERIES = T3.SERIES WHERE T3.SERIES IN ('167','115','172 ',' 114')

[Former Member · Accepted solution]

Hi,

Try:

SELECT DISTINCT T0.[DocNum], T0.[DocType], T0.[DocDate], T0.[Comments], T0.[CardName], T0.BPACT, T0.[NoDocSum] , T0.[TransId] ,T0.DOCTOTALSY,T3.SERIESNAME,T0.JRNLMEMO,T4.REF3,T5.DEBIT, T1.AcctName

FROM OVPM T0

INNER JOIN OJDT T4 ON T0.TransId = T4.TransId

INNER JOIN [dbo].[JDT1] T5 ON T4.TransId = T5.TransId

INNER JOIN OACT T1 ON T5.Account = T1.AcctCode

INNER JOIN nnm1 T3 ON T0.SERIES = T3.SERIES

WHERE T3.SERIES IN ('167','115','172 ',' 114')

Thanks,

Gordon

[former_member188586]

hi vinayak

try with this condition

from  JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId INNER JOIN OACT T2 ON T0.Account = T2.AcctCode INNER JOIN OCRD T3 ON T2.AcctCode = T3.BoEPrsnt

[former_member188586]

hi

try with this one

SELECT DISTINCT T0.[DocNum], T0.[DocType], T0.[DocDate], T0.[Comments], T0.[CardName],T1.[AcctCode], T1.[AcctName], T0.BPACT, T0.[NoDocSum] , T0.[TransId] ,T0.DOCTOTALSY,T3.SERIESNAME,T0.JRNLMEMO,T4.REF3,T5.DEBIT FROM OVPM T0  INNER JOIN VPM4 T1 ON T0.DocEntry = T1.DocNum INNER JOIN OJDT T4 ON T0.TransId = T4.TransId INNER JOIN [dbo].[JDT1]  T5 ON T4.TransId = T5.TransId

INNER JOIN nnm1 T3 ON T0.SERIES = T3.SERIES WHERE T3.SERIES IN ('167','115','172 ',' 114')

[vinayak_chavan]

hi AndakondaRamudu A

query is right but its give only doctype=A

i wan vendor type alos that is doctype='S'

PLEASE HELP.

THX IN ADVANCE

[former_member188586]

wait Vinayak

i'll check and update you

[former_member205766]

Hi

Try the below query

JDT1 T0  INNER JOIN OACT T1 ON T0.[Account] = T1.[AcctCode]

With Regards

Balaji

[vinayak_chavan]

BALAJI ITS SHOW ONLY FIRST ROW

SELECT DISTINCT T0.[DocNum], T0.[DocType], T0.[DocDate], T0.[Comments], T0.[CardName], T0.BPACT, T0.[NoDocSum] , T0.[TransId] ,T0.DOCTOTALSY,T3.SERIESNAME,T0.JRNLMEMO,T4.REF3,T5.DEBIT ,T1.ACCTNAME FROM OVPM T0  INNER JOIN OACT T1 ON T0.CashAcct = T1.AcctCode INNER JOIN OJDT T4 ON T0.TransId = T4.TransId INNER JOIN [dbo].[JDT1]  T5 ON  T5.[Account] = T1.[AcctCode]

INNER JOIN nnm1 T3 ON T0.SERIES = T3.SERIES WHERE T3.SERIES IN ('167','115','172 ',' 114')