My apology here is the actual query

__________________________________________________________
________________________________________________________

Select distinct

trim(surname)as surname,

trim(name)as name,

trxdate,

source,

job_number,

creditnr,

sourceid,

serviceadvisOR,

(credvat)as credvat,

(invoiceVAT) AS VAT,

(invoiceINC)AS invoicetotalINC,

(invoiceINC-invoicevat+credvat)as invoicetotal,

(credinc+creditnote-(invoiceINCRedo-invoiceVATRedo))as credits,

(invoiceINC-invoicevat+(credinc))+(creditnote)-(invoiceINCRedo)as
nett,

(invcost)as cost,

((invoiceINC-invoicevat+(credinc))+(creditnote))-(invcost)-(invoiceINCRedo)a
s gp,

((((invoiceINC-invoicevat+(credinc))+(creditnote))-(invcost)-(invoiceINCRedo
)))/NULLIF(((invoiceINC-invoicevat+(credinc))+(creditnote)),0)*100 as
gppersent

from (select (jdate)as trxdate, source, sourceid,(source2)as
creditnr,i.job_number,

(select name||' '||surname from staff where staff_id =
j.serviceadvisOR)as serviceadvisOR,

(select name||' '||surname from customer where cid = j.cid)as surname,

(select ca.name from cashcustomer ca

where ca.invnr = j.source AND ca.jobnr = i.job_number)as
name,

(Select COALESCE(sum(debitamount),0 ) from journal

where accnr = '3993100' AND source = j.source AND jtype =1 AND
jdate = j.jdate )as invoiceINC,

(Select COALESCE(sum(creditamount-debitamount),0 ) from journal

where accnr = '5995300' AND source = j.source AND jtype =1 AND
jdate = j.jdate )as invoiceVAT,

(Select COALESCE(sum(creditamount),0 ) from journal

where accnr = '3993100' AND source = j.source AND jtype =1 AND
jdate = j.jdate AND module = 77)as invoiceINCRedo,

(Select COALESCE(sum(debitamount-creditamount),0 ) from journal

where accnr = '5995300' AND source = j.source AND jtype =1 AND
jdate = j.jdate AND module = 77)as invoiceVATRedo,

(Select COALESCE(sum(creditamount-debitamount),0 ) from journal

where accnr = '5995300' AND (module = 77 OR MODULE = 66 )AND
source = j.source AND source2 = j.source2 AND jtype =1 AND jdate = j.jdate)
as credVAT,

(Select COALESCE(sum(creditamount-debitamount),0 ) from journal

where accnr = '5995300' AND (module = 77 OR MODULE = 66 )AND
source = j.source AND source2 = j.source2 AND jtype =4 AND jdate = j.jdate)
as creditnoteVAT,

(Select COALESCE(0-sum(creditamount-debitamount),0 ) from journal

where accnr = '1501500' AND source = j.source AND jtype =1 AND
jdate = j.jdate)as credinc,

(Select COALESCE(0-sum(debitamount-creditamount),0 ) from journal

where accnr = '1501500' AND source = j.source AND jtype =4 AND
jdate = j.jdate)as creditnote,

(Select (COALESCE(SUM(debitamount-CREDitamount),0)) from journal

where accnr like '6%' AND source = j.source AND jtype = 1 AND
jdate = j.jdate)as invcost

from journal j, invoices i

where j.source not like 'STOCK UNIT%'

AND (j.jdate >= '2015/06/01'

AND j.jDATE <= '2015/06/22')

AND j.source not like 'DEPOSIT%'

AND j.cid <> 0

AND i.inv_nr = j.source

AND j.module <> 2

AND (j.source2 not like 'S/D%'

AND j.source2 not like 'P/D%'

OR j.source2 is null)

AND j.description not like 'SPLIT INV%'

AND (j.jtype = 1 OR j.jtype = 4)

-- Below is where my problem lies

-- Why can I not use the result fields

-- i.e (invoiceINC-invoicevat+credvat) <> 0

-- instead of the sql below for my <> 0 condition??

AND (

(Select COALESCE(sum(debitamount),0 )

from journal where accnr = '3993100' AND source = j.source AND
jtype = 1 AND jdate = j.jdate )

-

(Select COALESCE(sum(creditamount-debitamount),0 )

from journal where accnr = '5995300' AND source = j.source AND
jtype = 1 AND jdate = j.jdate )

+

(Select COALESCE(sum(creditamount-debitamount),0 )

from journal where accnr = '5995300'

AND (module = 77 OR MODULE = 66 )AND source = j.source AND
source2 = j.source2 AND jtype =1 AND jdate = j.jdate)

) <> 0

)

Order by 1,3,4

__________________________________________________________
________________________________________________

Regards

Ste

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: 22 June 2015 01:56 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Firebird migration to increase perfomances

At first glance, your plan seems OK. However, it would be better if we also
could see the index definitions as well as get some information about index
selectivity. And, this list removes attachments, so please post the text of
the query as part of the message.

Set

Stef van der Merwe wrote:
I am not a SQL master but do know the basics.
I am have an issue with sub select queries and setting conditions.
Attached is the query I have a problem with, see my comments on the last
condition.

P.S see below is my plan I am sure the query could be done better for faster
performance any advise would be greatly appreciated !!

Plan

PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16))
PLAN (STAFF INDEX (STAFF_IDX1))
PLAN (CUSTOMER INDEX (CUSTOMER_IDX1))
PLAN (CA INDEX (CASHCUSTOMER_IDX2, CASHCUSTOMER_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1))
PLAN SORT (JOIN (J INDEX (JOURNAL_IDX1, JOURNAL_IDX2, JOURNAL_IDX2), I INDEX
(INVOICES_IDX5)))

Adapted Plan

PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX
(JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6,
JOURNAL_IDX16)) PLAN (STAFF INDEX (STAFF_IDX1)) PLAN (CUSTOMER INDEX
(CUSTOMER_IDX1)) PLAN (CA INDEX (CASHCUSTOMER_IDX2, CASHCUSTOMER_IDX1)) PLAN
(JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX
(JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6,
JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN
(JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (JOURNAL INDEX
(JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (JOURNAL INDEX (JOURNAL_IDX6,
JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN
(JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN SORT (JOIN (J INDEX
(JOURNAL_IDX1, JOURNAL_IDX2, JOURNAL_IDX2), I INDEX (INVOICES_IDX5)))

Prepare time = 16ms
Execute time = 15s 828ms
Avg fetch time = 465.53 ms
Current memory = 74 375 188
Max memory = 74 391 660
Memory buffers = 8 192
Reads from disk to cache = 253
Writes from cache to disk = 0
Fetches from cache = 730 569

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]