cancel
Showing results for 
Search instead for 
Did you mean: 

Help on Ledger query report

Former Member
0 Kudos


Hi All

Please help me in query report wherein I want to have all the entries where account name has TDS in it.

T0.[TransId], T0.[BtfStatus], T0.[TransType], T0.[BaseRef], T0.[RefDate], T0.[Memo],


.[Ref1], T0.[Ref2], T0.[Ref2], T0.[Number], T1.[Line_ID], T1.[Account],T2.CardName,T3.AcctName, T1.[ShortName],


.[Debit], T1.[Credit] FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId 


JOIN OCRD T2 ON T1.[Account]=T2.[CardCode] 


JOIN OACT T3 ON  T1.[Account]=T3.[AcctCode]  WHERE T0.[RefDate] = '20140331'


  T0.[TransId]=(Select T0.[TransId] from JDT1 where T3.AcctName LIKE '%TDS%')

But the above query gives only that line which has this account name but I want to have complete it (i.e. the corresponding accounts also which has been debited or credit with this account).

Thanks In Advance

Thanks & Regards

Preeti

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this:

select T0.[TransId], T0.[BtfStatus], T0.[TransType], T0.[BaseRef], T0.[RefDate], T0.[Memo], t0.[Ref1], T0.[Ref2], T0.[Ref2], T0.[Number], T1.[Line_ID], T1.[Account],T2.CardName,T3.AcctName, T1.[ShortName], t1.[Debit], T1.[Credit]

FROM OJDT T0  left  JOIN JDT1 T1 ON T0.TransId = T1.TransId

left  JOIN OCRD T2 ON T1.[Account] =T2.[CardCode]

left  JOIN OACT T3 ON  T1.[Account]=T3.[AcctCode]

WHERE T0.[RefDate] = '20140331' 

and T1.[TransId] in (Select  a.[TransId] from JDT1 a left join OACT b on a.Account=b.AcctCode  where  b.AcctName LIKE '%TDS%') 

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hi Nagarajan

Thank you very much it works. One small condition also I need to add-

I want BP PAN no.

I am trying below but could not get the desired result as PAN no in getting multiple in CRD7 table

with address.

Select
T0.[TransId] AS [JE NO], T0.[RefDate] as [Posting Date],  T0.[Number], T1.[Line_ID],
Account=(CASE WHEN T2.cardname IS NULL THEN t3.AcctName ELSE T2.cardname END), (Isnull(T4.taxid1,'')),
t1.[Debit], T1.[Credit],T0.[Memo] as [Details], t0.[Ref1], T0.[Ref2], T0.[Ref2]

FROM OJDT T0  left  JOIN JDT1 T1 ON T0.TransId = T1.TransId

left  JOIN OCRD T2 ON T1.[ShortName] =T2.[CardCode]

left  JOIN OACT T3 ON  T1.[Account]=T3.[AcctCode]
LEFT JOIN CRD7 T4 ON T2.CardCode=T4.CardCode AND T2.cardcode= (Select Top 1 Cardcode from CRD7 where T1.[ShortName] =T2.[CardCode])

WHERE T0.[RefDate] = '20141121'  AND T0.[TransType]='30' 

--AND T1.[TransId] in (Select  a.[TransId] from JDT1 a left join OACT b on a.Account=b.AcctCode  where  b.AcctName LIKE '%TDS%') 
--AND (T0.[RefDate]  >= [%0] AND  T0.[RefDate] <=[%1]) OR =T3.AcctName =[%2]

Many Thanks

Regards

Preeti

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Please close this thread with correct and helpful answer. For the above requirement create new discussion.

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Thanks !!

Answers (3)

Answers (3)

former_member184146
Active Contributor
0 Kudos

Hi,

Try this

Select T0.[TransId], T0.[BtfStatus], T0.[TransType], T0.[BaseRef], T0.[RefDate], T0.[Memo],

t0.[Ref1], T0.[Ref2], T0.[Ref2], T0.[Number], T1.[Line_ID], T1.[Account],T3.AcctName, T1.[ShortName],

t1.[Debit], T1.[Credit] FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer JOIN OCRD T2 ON T1.[ShortName]=T2.[CardCode]

inner JOIN OACT T3 ON  T1.[Account]=T3.[AcctCode]

WHERE T0.[RefDate] = '20140331'

and T0.[TransId]=(Select T0.[TransId] from JDT1 where T3.AcctName LIKE '%TDS%')

Regards,

Manish

Former Member
0 Kudos


Hi Manish

I am getting error :

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


Thanks & Regards

Preeti

former_member184146
Active Contributor
0 Kudos

Hi,

     First try this

Select T0.[TransId], T0.[BtfStatus], T0.[TransType], T0.[BaseRef], T0.[RefDate], T0.[Memo],

t0.[Ref1], T0.[Ref2], T0.[Ref2], T0.[Number], T1.[Line_ID], T1.[Account],T3.AcctName, T1.[ShortName],t1.[Debit], T1.[Credit]


FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

INNER JOIN OCRD T2 ON T1.[ShortName]=T2.[CardCode]

inner JOIN OACT T3 ON  T1.[Account]=T3.[AcctCode]

WHERE T0.[RefDate] = '20140331'

If above query is correct then let me know.

Regards,

Manish

Former Member
0 Kudos

Hi ,

Try This ,

select T0.[TransId], T0.[BtfStatus], T0.[TransType], T0.[BaseRef], T0.[RefDate], T0.[Memo], t0.[Ref1], T0.[Ref2], T0.[Ref2], T0.[Number], T1.[Line_ID], T1.[Account],T2.CardName,T3.AcctName, T1.[ShortName], t1.[Debit], T1.[Credit]

FROM OJDT T0  left  JOIN JDT1 T1 ON T0.TransId = T1.TransId

left  JOIN OCRD T2 ON T1.[Account] =T2.[CardCode]

left  JOIN OACT T3 ON  T1.[Account]=T3.[AcctCode]

WHERE T0.[RefDate] = '20140331' 

and T1.[TransId]in (Select  a.[TransId] from JDT1 a left join OACT b on a.Account=b.AcctCode  where  b.AcctName LIKE ''%TDS%') 

-Rajesh NS

former_member188586
Active Contributor
0 Kudos

hi

check bellow thread

Thanks&Regards

Andakondaramudu

Former Member
0 Kudos

Hi Andakondaramuda

Thanks for your reply.

This does not help me as I need filter on Acctname also and need all the entries corresponding to it.

Like it appear in document journal report.

Thanks & Regards

Preeti