Hello,

Svein, fantastic, that works like a charm, the statistics below for my
test case (Date range changed to Full September 2009):

My Original Query:
Prepare Time: 0.125sec
Process Time: 26.734sec
Rows: 1253

Your solution:
Prepare Time: 0.109sec
Process Time: 0.688sec
Rows: 1253

Thank you very much for your assistance.

What is this called that you make use of and where can I read more about
this way of executing queries. I have never used this before (the with
section).

Regards
Marius

Svein Erling Tysvær wrote:

>
>
> Huh? Since the subselect had the same criteria for JOURNALHEADER as
> JOURNAL and that the JOIN criteria of the subselect where the exact
> same field as the field of interest, I was certain that the two
> queries should produce the exact same result.
>
> You can of course try:
>
> WITH LINECODES(LINECODE) AS
> (SELECT DISTINCT J2.LINECODE
> FROM JOURNALHEADER JH2
> INNER JOIN JOURNALS J2 ON JH2.LINECODE = J2.LINECODE
> WHERE JH2.JVDATE between '29 Sep 2009 00:00:00' AND '29 Sep 2009
> 23:00:00' AND
> JH2.POSTED = 'Yes' AND
> JH2.EFFECTSLEDGER = 'Yes' AND
> J2.INTRECNO_LEDGER = 170
> )
> SELECT
> JH.JVDATE,
> JH.DESCRIPTION,
> JH.VOUCHER,
> J.DEBITAMOUNT,
> J.CREDITAMOUNT,
> LA.ACCOUNTNO,
> LA.ACCOUNTNAME
> FROM
> JOURNALHEADER JH
> INNER JOIN JOURNALS J ON JH.LINECODE = J.LINECODE
> INNER JOIN LEDGERACCOUNTS LA ON J.INTRECNO_LEDGER = LA.INTRECNO
> INNER JOIN LINECODES LC ON J.LINECODE = LC.LINECODE
> WHERE JH.JVDATE between '29 Sep 2009 00:00:00' AND '29 Sep 2009
> 23:00:00' AND
> JH.POSTED = 'Yes' AND
> JH.EFFECTSLEDGER = 'Yes'
>
> which is an identical rewrite of your original query (but with the
> excess JH2 alias).
>
> One thing that I simply didn't think of and that I have no idea
> whether matters: You don't by any chance have a table or view named
> LINECODES? If so, just use a different name for the above WITH and
> JOIN clause in the main select.
>
> HTH,
> Set
>
>