Skip to Content

need oact.acctname in report

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')

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Jan 15, 2015 at 02:01 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 14, 2015 at 08:30 AM

    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')

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 14, 2015 at 07:03 AM

    Hi

    Try the below query

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

    With Regards

    Balaji

    Add comment
    10|10000 characters needed characters exceeded

    • 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')

  • Jan 14, 2015 at 12:34 PM

    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

    Add comment
    10|10000 characters needed characters exceeded