on 12-06-2005 2:09 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
User | Count |
---|---|
89 | |
7 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.