cancel
Showing results for 
Search instead for 
Did you mean: 

Query problem

Former Member
0 Kudos

Hellow,

Again I have a little problem with my query. Now i'm making an overall view around an accountcode. The only problem is that I'm trying to make a join between the 2 tables, but for some reason it seems not to work since it gives me like 11000 results when it should be more like around 80.....

This is because it's adding each memo to each transactioncode, while there just should be 1 memo for each transactioncode.

This is the code:

SELECT T0.TransId, T0.Account, T0.ContraAct, T0.Debit, T0.Credit, T0.LineMemo, T0.SourceId, T0.DueDate, T1.Memo

FROM JDT1 T0 LEFT OUTER JOIN OBNK T1 ON T0.Account = T1.U_BP_Glacc

WHERE T0.Account = '[%0]'

ORDER BY T0.DueDate

Will it be Adele again who will help me out?

Thanks in advance!

Martijn

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member201110
Active Contributor
0 Kudos

Hi Martijn,

The reason you are getting a much larger recordset than you are expecting is because you are only linking the 2 tables by GL account code. This is not specific enough to return the results you want and you need to also link by GL Journal Entry number. In the JDT1 table, this is the TransID column. However, the OBNK table does not store a journal number as this data relates to external bank information. You might be able to get around this by adding another UDF to the OBNK table and storing the journal number in this.

Hope this helps,

Owen

barend_morkel2
Active Contributor
0 Kudos

Try this query,

This will show records with Memo's that is not null

SELECT T0.TransId, T0.Account, T0.ContraAct, T0.Debit, T0.Credit, T0.LineMemo, T0.SourceId, T0.DueDate, T1.Memo

FROM JDT1 T0,

OBNK T1

WHERE T0.Account = T1.U_BP_Glacc

AND T0.Account = '[%0]'

ORDER BY T0.DueDate

(T1.U_BP_Glacc could be replaced with T1.AcctCode)

Message was edited by: Barend Morkel

Former Member
0 Kudos

Hey Barend,

Thanks for your quick reply! I've tried yours but it wasn't working. The query u gave to me has same effect as the one I have so far. The only difference you gave me is that I can replace T1.U_BP_Glacc with T1.AcctCode. I tried that and than I get 147 results, but none with a Memo while there should be a memo though.

Any other suggestions?

Thanks in advance!

Martijn

Former Member
0 Kudos

Hi Martjin,

I don't have any records in my OBNK table, so can't test it for you. The only thing I can think of is that there are multiple records in OBNK for each record in JDT1. This will have the result you are referring to. Maybe you can post some sample records here so I can better understand.

Hope it helps,

Adele

Former Member
0 Kudos

Yup that's indeed true Adele. Cause as you can see on the screenshots there are 2 different things. On the first screenshot you see that there are all the memo's for 1 transactoncode, wich obviously isn't possible, cause there can only be 1 memo for each transactioncode.

http://www.comm-co.com/query1.jpg

Than when I use the T1.AcctCode I get all the transactioncodes, 147, wich is good, but there is no memo after it... so that's still missing. As you can see on screenshot 2.

http://www.comm-co.com/query2.jpg

Owen, ok I thought that 1 join would be enough, also cause there is only 1 table that makes you able to connect both tables. I will see if I can do what you tell me to try. Hopefully I'm capable in doing it

Thanks so far and keep advising me, really appreciated!

Martijn

Former Member
0 Kudos

I think I've almost solved it..... Now i'm on 88 results, Without the T1.memo and T1 table I've get 147 results so getting closer. I didn't add in UDF.

SELECT DISTINCT T0.TransId, T0.Account, T0.ContraAct, T0.Debit, T0.Credit, T0.LineMemo, T0.SourceId, T0.DueDate, T1.Memo

FROM JDT1 T0 LEFT OUTER JOIN OBNK T1 ON T0.Account = T1.U_BP_Glacc

WHERE T0.Account = '[%0]'

AND T0.Credit = T1.CredAmnt

AND T0.Debit = T1.DebAmount

ORDER BY T0.DueDate

Could I be right?

Thanks in advance!

Message was edited by: Martijn Ivens