| Subject | Re: [firebird-support] How can I correctly write this WHERE |
|---|---|
| Author | Venus Software Operations |
| Post date | 2018-06-21T12:30:25Z |
Thanks Set, I had not included anything else because I was having writing the original CASE but like you pointed out my thought of using the CASE was not required a simple WHERE can do what I need.
You are correct about the other parts of the query being the reason for slowness. As I mentioned in my reply the slowness starts as soon as I add the JOIN to the View, even if I do not use any of it's field, eg. cNameCity01. I have also tried to avoid the TRIM() by using the primary key integer field of this view instead but it is still slow.
The view is a CTE (which you had kindly taught me :) ) and this
query is a CTE too. I will paste them below for your reference.
The whole idea was that if the account involved is a Balance Sheet
account (PK 47 in this example now) then all transaction need to
be considered, else a shorter group of transactions are needed
WITH recursive heir(iid, ipk, iaccttreeid) AS
(
SELECT maccounts.iid,
maccounts.iid AS ipk,
vwta.itreeid01 AS iaccttreeid
FROM maccounts
JOIN maccounts gtt
ON gtt.iid = maccounts.iid
JOIN vwtreeaccounts vwta
ON vwta.iid = maccounts.iid
WHERE (
1 = 1)
AND (( (
NOT (
maccounts.lhierarchialaccount = 1
OR EXISTS
(
SELECT 1
FROM maccounts d
WHERE d.ipid = maccounts.iid)) ))) )
SELECT 1 AS iorder ,
heir.iid AS ipid ,
heir.iid AS igpid ,
'(0' AS cpsource ,
macct.cdrcr AS cpdrcr ,
cast(' ' AS srbk) AS cbk ,
cast(0 AS srno) AS ino ,
cast('2018-04-01 00:00:00' AS srdt) AS tdt ,
heir.iid AS iacctid ,
iif(LEFT(macct.ctypeid, 2) IN ('DR',
'CR'), LEFT(macct.ctypeid, 2), macct.cdrcr) AS cdrcr ,
sum(iif(ssifooter.cdrcr = 'DR' , ssifooter.bamt , cast(0 AS amt))) AS bdebit ,
sum(iif(ssifooter.cdrcr = 'CR' , ssifooter.bamt , cast(0 AS amt))) AS bcredit ,
'Opening Balance' AS mnarration ,
cast('Opening' AS docno) AS cdocno ,
cast('2018-04-01 00:00:00' AS srdt) AS tdocdt ,
cast(0 AS id) AS idocacctid
FROM tsaleinvoice tsi
JOIN ssaleinvoicefooter ssifooter
ON ssifooter.ipid = tsi.iid
AND ssifooter.bamt <> 0
LEFT JOIN maccounts macct
ON macct.iid = ssifooter.iaccountid
LEFT JOIN mlookups mcity
ON mcity.iid = macct.icityid
JOIN heir
ON ssifooter.iaccountid = heir.ipk
WHERE (
heir.iaccttreeid = 47
AND cast(tsi.tdt AS date) < '2018-04-01' )
OR (
heir.iaccttreeid <> 47
AND cast(tsi.tdt AS date) BETWEEN '2018-03-01' AND '2018-03-31' )
AND tsi.cdoctype NOT LIKE 'PROFORMA%'
AND tsi.cdoctype NOT LIKE 'CANCELLED%'
AND ssifooter.cdrcr IN ('DR',
'CR')
GROUP BY heir.iid,
iif(LEFT(macct.ctypeid, 2) IN ('DR',
'CR'), LEFT(macct.ctypeid, 2), macct.cdrcr),
macct.cdrcr
The view:
CREATE VIEW vwtreeaccounts
(
iid,
ipid,
inegbalpid,
ilevel,
ichildren,
isrno,
itreeid01,
itreeid02,
itreeid03,
itreeid04,
itreeid05,
itreeid06,
itreeid07,
itreeid08,
itreeid09,
itreeid10,
cnamecity01,
cnamecity02,
cnamecity03,
cnamecity04,
cnamecity05,
cnamecity06,
cnamecity07,
cnamecity08,
cnamecity09,
cnamecity10,
cnamecity
)
AS
WITH recursive maccountsordered
(
iid,
ipid,
inegbalpid,
isrno,
cname,
ccity
)
AS
(
SELECT maccounts.iid ,
maccounts.ipid ,
maccounts.inegbalpid ,
maccounts.isrno ,
maccounts.cname ,
COALESCE(mlookups.vvalue, cast(' ' AS char(50))) AS ccity
FROM maccounts
LEFT JOIN mlookups
ON mlookups.iid = maccounts.icityid
ORDER BY maccounts.isrno ,
maccounts.cname ,
COALESCE(mlookups.vvalue, '')
)
,
cte
(
iid,
ipid,
inegbalpid,
ilevel,
isrno,
itreeid01,
itreeid02,
itreeid03,
itreeid04,
itreeid05,
itreeid06,
itreeid07,
itreeid08,
itreeid09,
itreeid10,
cnamecity01,
cnamecity02,
cnamecity03,
cnamecity04,
cnamecity05,
cnamecity06,
cnamecity07,
cnamecity08,
cnamecity09,
cnamecity10
)
AS
(
SELECT levelthis.iid ,
levelthis.ipid ,
levelthis.inegbalpid ,
cast(0 AS integer) ,
levelthis.isrno ,
levelthis.iid ,
cast(NULL AS integer) ,
cast(NULL AS integer) ,
cast(NULL AS integer) ,
cast(NULL AS integer) ,
cast(NULL AS integer) ,
cast(NULL AS integer) ,
cast(NULL AS integer) ,
cast(NULL AS integer) ,
cast(NULL AS integer) ,
cast(trim(levelthis.cname)
|| iif(COALESCE(levelthis.ccity, '') = '', '', ', '
|| trim(COALESCE(levelthis.ccity, ''))) AS char(200)) ,
cast(' ' AS char(200)) ,
cast(' ' AS char(200)) ,
cast(' ' AS char(200)) ,
cast(' ' AS char(200)) ,
cast(' ' AS char(200)) ,
cast(' ' AS char(200)) ,
cast(' ' AS char(200)) ,
cast(' ' AS char(200)) ,
cast(' ' AS char(200))
FROM maccountsordered levelthis
WHERE levelthis.ipid = 0
UNION ALL
SELECT levelcurr.iid ,
levelcurr.ipid ,
levelcurr.inegbalpid ,
levelparent.ilevel + 1 ,
levelcurr.isrno ,
levelparent.itreeid01 ,
iif(levelparent.ilevel = 0, levelcurr.iid, levelparent.itreeid02) ,
iif(levelparent.ilevel = 1, levelcurr.iid, levelparent.itreeid03) ,
iif(levelparent.ilevel = 2, levelcurr.iid, levelparent.itreeid04) ,
iif(levelparent.ilevel = 3, levelcurr.iid, levelparent.itreeid05) ,
iif(levelparent.ilevel = 4, levelcurr.iid, levelparent.itreeid06) ,
iif(levelparent.ilevel = 5, levelcurr.iid, levelparent.itreeid07) ,
iif(levelparent.ilevel = 6, levelcurr.iid, levelparent.itreeid08) ,
iif(levelparent.ilevel = 7, levelcurr.iid, levelparent.itreeid09) ,
iif(levelparent.ilevel = 8, levelcurr.iid, levelparent.itreeid10) ,
levelparent.cnamecity01 ,
iif(levelparent.ilevel = 0, trim(levelcurr.cname)
|| iif(COALESCE(levelcurr.ccity, '') = '', '', ', '
|| trim(COALESCE(levelcurr.ccity, ''))), levelparent.cnamecity02) ,
iif(levelparent.ilevel = 1, trim(levelcurr.cname)
|| iif(COALESCE(levelcurr.ccity, '') = '', '', ', '
|| trim(COALESCE(levelcurr.ccity, ''))), levelparent.cnamecity03) ,
iif(levelparent.ilevel = 2, trim(levelcurr.cname)
|| iif(COALESCE(levelcurr.ccity, '') = '', '', ', '
|| trim(COALESCE(levelcurr.ccity, ''))), levelparent.cnamecity04) ,
iif(levelparent.ilevel = 3, trim(levelcurr.cname)
|| iif(COALESCE(levelcurr.ccity, '') = '', '', ', '
|| trim(COALESCE(levelcurr.ccity, ''))), levelparent.cnamecity05) ,
iif(levelparent.ilevel = 4, trim(levelcurr.cname)
|| iif(COALESCE(levelcurr.ccity, '') = '', '', ', '
|| trim(COALESCE(levelcurr.ccity, ''))), levelparent.cnamecity06) ,
iif(levelparent.ilevel = 5, trim(levelcurr.cname)
|| iif(COALESCE(levelcurr.ccity, '') = '', '', ', '
|| trim(COALESCE(levelcurr.ccity, ''))), levelparent.cnamecity07) ,
iif(levelparent.ilevel = 6, trim(levelcurr.cname)
|| iif(COALESCE(levelcurr.ccity, '') = '', '', ', '
|| trim(COALESCE(levelcurr.ccity, ''))), levelparent.cnamecity08) ,
iif(levelparent.ilevel = 7, trim
(Message over 64 KB, truncated)