Skip to Content
author's profile photo Former Member
Former Member

Query problem

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Dec 06, 2005 at 02:19 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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

  • Posted on Dec 06, 2005 at 10:00 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.