Outgoing payment

[Former Member]

hi All,

I have a query which is repeating data in case multiple checks are used for payment;

SELECT DISTINCT T0.[DocDate], T1.[DueDate], T1.[CheckNum]as 'Cheque', T1.[BankCode] AS 'Bank Code' , T1.[Branch], T1.[AcctNum], T2.[AcctCode],T2.[Descrip], T2.[SumApplied], T0.[DocTotal] FROM OVPM T0 INNER JOIN VPM1 T1 ON T0.DocNum = T1.DocNum INNER JOIN VPM4 T2 ON T0.DocNum = T2.DocNum WHERE T0.[DocType] = 'A' and T0.[DocDate] >=[%0]

AND

T0.[DocDate] <=[%1] and T0.[DocNum] = [%3]

Let's say I selected doc type as accounts and at row level i selected 9 accounts. While on the payment means, i used 9 different checks for the payment. Now while viewing the data from this query, its repeating the data 81 times. How do i get to show only 9 accounts and 9 check number in the query? Please assist.

Thanks,

joseph

[Former Member · Accepted solution]

Hi Joseph,

Check your query result. For those 81 lines, one or more columns must have different values from each other. You need to omit those columns from your query.

Thanks,

Gordon

[Former Member · Accepted solution]

Hi gordon,

Yes you are right. When i removed T1.[CheckNum]as 'Cheque', from the query, the output was correct. However, I also need the check info in this report! How do i get that?

Thanks,

Joseph

[Former Member · Accepted solution]

You may need 2 queries instead of 1. The other simple query could get details for Check numbers only with reference to the Outgoing payment number.

[Former Member · Accepted solution]

Hi Gordon,

I have tried this query ;

SELECT T1.[CheckNum] FROM [dbo].[OVPM] T0 INNER JOIN VPM1 T1 ON T0.DocNum = T1.DocNum where T0.docnum = '[%0]'

with union all but it is giving errors. How do i join these queries?

Thanks,

Joseph

[Former Member · Accepted solution]

You can not union the query with different numbers of columns.

[Former Member]

Closing the thread..

Thanks,

Joseph

[former_member583013]

I think your query is correct but for a few small corrections. The query below was test by me and it works. Check it out.

SELECT DISTINCT T0.DocDate, T1.DueDate, T1.CheckNum as 'Cheque', T1.BankCode AS 'Bank Code', T1.Branch, 
T1.AcctNum, T2.AcctCode, T2.Descrip, T2.SumApplied, T0.DocTotal 
FROM [dbo].[OVPM] T0 INNER JOIN [dbo].[VPM1] T1 ON T0.DocNum = T1.DocNum INNER JOIN [dbo].[VPM4] T2 ON T0.DocNum = T2.DocNum 
WHERE T0.DocType = 'A' AND T0.DocDate BETWEEN '[%0]' AND '[%1]' AND T0.DocNum = '[%2]'

Regards

Suda

[Former Member]

Hi Suda,

i'm trying to make a qpld here. The query that you have suggested is still repeating the data!

As I have mentioned in the first post, 9 accounts were selected and 9 checks were used to do the payment. Now, this query is showing the data 81 times. please assist.

Thanks,

Joseph

[former_member583013]

Joseph,

I did not create 9 accounts with 9 checks but tried it with 4 and 4 and it works perfectly well.

Are you seeing 81 rows when you view the query on the screen or on the QPLD result

Suda

[Former Member]

hi Suda,

In both the cases this query as well as the qpld returns 81 rows.

Thanks,

Joseph

[former_member583013]

Then I am really not sure why because I use SBO 2007A SP1 PL5 and it works fine.

[Former Member]

Hi!

Try this

SELECT DISTINCT T0.DocDate, T1.DueDate, T1.CheckNum as 'Cheque', T1.BankCode AS 'Bank Code', T1.Branch, 
T1.AcctNum, T2.AcctCode, T2.Descrip, T2.SumApplied, T0.DocTotal 
FROM [dbo].[OVPM] T0 INNER JOIN [dbo].[VPM1] T1 ON T0.DocNum = T1.DocNum INNER JOIN [dbo].[VPM4] T2 ON 
(T0.DocNum = T2.DocNum and T1.DocNum = T2.DocNum )
WHERE T0.DocType = 'A' AND T0.DocDate BETWEEN '[%0]' AND '[%1]' AND T0.DocNum = '[%2]'

[Former Member]

Hi Thanga Raj,

even this query is not working in my case as its giving the same result.

@ Suda, we are on SAP 2005B, PL34. Could this be a cause?

Thanks,

Joseph