Thread: Query Slow in Postgres 8.4.3 than Postgres 8.1.5
The following query is very slow in Postgres 8.4.3 as compared to Postgres 8.1.5. Please reply. Thanx in advance..... select f.finance_company_name, b.brokerage_name, bc.quote_no as ContractNumber, cl.first_name as ClientFirstName, cl.last_name as ClientLastName, mcsh.status_type_cd as ContractStatus, (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) as due_amount, (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) as received_amount, (gl.ds - gl.cs - gl.d + gl.c) as ledger, (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as active_ledger, (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as active_due_amount, (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as active_received_amount, (case when mcsh.status_type_cd = 'ACTIVE' THEN 1 else 0 end) as active_count, (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as cancelled_ledger, (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as cancelled_due_amount, (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as cancelled_received_amount, (case when mcsh.status_type_cd = 'CANCELLED' THEN 1 else 0 end) as cancelled_count, (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as default_ledger, (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as default_due_amount, (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as default_received_amount, (case when mcsh.status_type_cd = 'DEFAULTED' THEN 1 else 0 end) as default_count, (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as payout_ledger, (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as payout_due_amount, (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as payout_received_amount, (case when mcsh.status_type_cd = 'PAIDOUT' THEN 1 else 0 end) as payout_count, (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as expired_ledger, (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as expired_due_amount, (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as expired_received_amount, (case when mcsh.status_type_cd = 'EXPIRED' THEN 1 else 0 end) as expired_count, (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as writeoff_ledger, (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as writeoff_due_amount, (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as writeoff_received_amount, (case when mcsh.status_type_cd = 'WRITEOFF' THEN 1 else 0 end) as writeoff_count, (case when mcsh.status_type_cd = 'RESCIND' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as rescind_ledger, (case when mcsh.status_type_cd = 'RESCIND' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as rescind_due_amount, (case when mcsh.status_type_cd = 'RESCIND' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as rescind_received_amount, (case when mcsh.status_type_cd = 'RESCIND' THEN 1 else 0 end) as rescind_count from nq_finance_company f inner join nq_group g on (f.finance_company_id = g.group_id and g.group_id =3299) inner join nq_group ug on (g.left_index < ug.left_index and g.right_index > ug.right_index) inner join nq_brokerage b on (b.brokerage_id = ug.parent_id) inner join nq_base_contract bc on (bc.group_id = ug.group_id and bc.quote_type_cd = 'CONTRACT') inner join nq_client cl on (cl.client_id = bc.client_id) left outer join nq_retained_pmts rp on (bc.quote_id = rp.quote_id) inner join (select csh.quote_id, csh.status_type_cd from nq_contract_status_history csh where (csh.quote_id, csh.status_history_id) in (select quote_id, max(status_history_id) from nq_contract_status_history where DATE_TRUNC( 'DAY',entry_date) <= to_date(' 06/02/2010', 'mm/dd/yyyy') group by quote_id)) mcsh on (mcsh.quote_id = bc.quote_id and mcsh.status_type_cd in ('ACTIVE', 'CANCELLED', 'DEFAULTED', 'EXPIRED', 'PAIDOUT', 'RESCIND')) inner join (select t.transaction_relation_id, sum (case when (e.debit_id != 1100 and e.credit_id >= 2000 and e.credit_id < 3000) then amount else 0 end) as cs, sum (case when (e.credit_id != 1100 and e.debit_id >= 2000 and e.debit_id < 3000 ) then amount else 0 end) as ds, sum (case when (e.debit_id = 1100 and e.credit_id >= 2000 and e.credit_id < 3000 ) then amount else 0 end) as d, sum (case when (e.credit_id = 1100 and e.debit_id >= 2000 and e.debit_id < 3000) then amount else 0 end) as c from nq_gl_account_entry e inner join nq_transaction t on (e.transaction_id = t.transaction_id) where DATE_TRUNC( 'DAY',transaction_date) <= to_date(' 06/02/2010', 'mm/dd/yyyy') group by t.transaction_relation_id) gl on (gl.transaction_relation_id = bc.transaction_relation_id) order by f.finance_company_name, b.brokerage_name, bc.quote_no
View this message in context: Query Slow in Postgres 8.4.3 than Postgres 8.1.5
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
View this message in context: Query Slow in Postgres 8.4.3 than Postgres 8.1.5
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Thought I'd reformat your query for readability:
SELECT f.finance_company_name,
b.brokerage_name,
bc.quote_no AS contractnumber,
cl.first_name AS clientfirstname,
cl.last_name AS clientlastname,
mcsh.status_type_cd AS contractstatus,
( gl.ds - gl.cs + ( CASE
WHEN rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) ) AS due_amount,
( gl.d - gl.c + ( CASE
WHEN rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) ) AS received_amount,
( gl.ds - gl.cs - gl.d + gl.c ) AS ledger,
( CASE
WHEN mcsh.status_type_cd = 'ACTIVE' THEN (
gl.ds - gl.cs - gl.d + gl.c )
ELSE 0
END ) AS active_ledger,
( CASE
WHEN mcsh.status_type_cd = 'ACTIVE' THEN ( gl.ds - gl.cs + (
CASE
WHEN rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS active_due_amount,
( CASE
WHEN mcsh.status_type_cd = 'ACTIVE' THEN ( gl.d - gl.c + (
CASE
WHEN rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS active_received_amount,
( CASE
WHEN mcsh.status_type_cd = 'ACTIVE' THEN 1
ELSE 0
END ) AS active_count,
( CASE
WHEN mcsh.status_type_cd = 'CANCELLED' THEN (
gl.ds - gl.cs - gl.d + gl.c )
ELSE 0
END ) AS cancelled_ledger,
( CASE
WHEN mcsh.status_type_cd = 'CANCELLED' THEN ( gl.ds - gl.cs + (
CASE
WHEN
rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS cancelled_due_amount,
( CASE
WHEN mcsh.status_type_cd = 'CANCELLED' THEN ( gl.d - gl.c + (
CASE
WHEN
rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS cancelled_received_amount,
( CASE
WHEN mcsh.status_type_cd = 'CANCELLED' THEN 1
ELSE 0
END ) AS cancelled_count,
( CASE
WHEN mcsh.status_type_cd = 'DEFAULTED' THEN (
gl.ds - gl.cs - gl.d + gl.c )
ELSE 0
END ) AS default_ledger,
( CASE
WHEN mcsh.status_type_cd = 'DEFAULTED' THEN ( gl.ds - gl.cs + (
CASE
WHEN
rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS default_due_amount,
( CASE
WHEN mcsh.status_type_cd = 'DEFAULTED' THEN ( gl.d - gl.c + (
CASE
WHEN
rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS default_received_amount,
( CASE
WHEN mcsh.status_type_cd = 'DEFAULTED' THEN 1
ELSE 0
END ) AS default_count,
( CASE
WHEN mcsh.status_type_cd = 'PAIDOUT' THEN (
gl.ds - gl.cs - gl.d + gl.c )
ELSE 0
END ) AS payout_ledger,
( CASE
WHEN mcsh.status_type_cd = 'PAIDOUT' THEN ( gl.ds - gl.cs + (
CASE
WHEN rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS payout_due_amount,
( CASE
WHEN mcsh.status_type_cd = 'PAIDOUT' THEN ( gl.d - gl.c + (
CASE
WHEN rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS payout_received_amount,
( CASE
WHEN mcsh.status_type_cd = 'PAIDOUT' THEN 1
ELSE 0
END ) AS payout_count,
( CASE
WHEN mcsh.status_type_cd = 'EXPIRED' THEN (
gl.ds - gl.cs - gl.d + gl.c )
ELSE 0
END ) AS expired_ledger,
( CASE
WHEN mcsh.status_type_cd = 'EXPIRED' THEN ( gl.ds - gl.cs + (
CASE
WHEN rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS expired_due_amount,
( CASE
WHEN mcsh.status_type_cd = 'EXPIRED' THEN ( gl.d - gl.c + (
CASE
WHEN rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS expired_received_amount,
( CASE
WHEN mcsh.status_type_cd = 'EXPIRED' THEN 1
ELSE 0
END ) AS expired_count,
( CASE
WHEN mcsh.status_type_cd = 'WRITEOFF' THEN (
gl.ds - gl.cs - gl.d + gl.c )
ELSE 0
END ) AS writeoff_ledger,
( CASE
WHEN mcsh.status_type_cd = 'WRITEOFF' THEN ( gl.ds - gl.cs + (
CASE
WHEN
rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS writeoff_due_amount,
( CASE
WHEN mcsh.status_type_cd = 'WRITEOFF' THEN ( gl.d - gl.c + (
CASE
WHEN
rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS writeoff_received_amount,
( CASE
WHEN mcsh.status_type_cd = 'WRITEOFF' THEN 1
ELSE 0
END ) AS writeoff_count,
( CASE
WHEN mcsh.status_type_cd = 'RESCIND' THEN (
gl.ds - gl.cs - gl.d + gl.c )
ELSE 0
END ) AS rescind_ledger,
( CASE
WHEN mcsh.status_type_cd = 'RESCIND' THEN ( gl.ds - gl.cs + (
CASE
WHEN rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS rescind_due_amount,
( CASE
WHEN mcsh.status_type_cd = 'RESCIND' THEN ( gl.d - gl.c + (
CASE
WHEN rp.num_retained IS NOT NULL THEN
rp.num_retained * monthly_amt
ELSE 0
END ) )
ELSE 0
END ) AS rescind_received_amount,
( CASE
WHEN mcsh.status_type_cd = 'RESCIND' THEN 1
ELSE 0
END ) AS rescind_count
FROM nq_finance_company f
inner join nq_group g
ON ( f.finance_company_id = g.group_id
AND g.group_id = 3299 )
inner join nq_group ug
ON ( g.left_index < ug.left_index
AND g.right_index > ug.right_index )
inner join nq_brokerage b
ON ( b.brokerage_id = ug.parent_id )
inner join nq_base_contract bc
ON ( bc.group_id = ug.group_id
AND bc.quote_type_cd = 'CONTRACT' )
inner join nq_client cl
ON ( cl.client_id = bc.client_id )
left outer join nq_retained_pmts rp
ON ( bc.quote_id = rp.quote_id )
inner join (SELECT csh.quote_id,
csh.status_type_cd
FROM nq_contract_status_history csh
WHERE ( csh.quote_id, csh.status_history_id ) IN (SELECT
quote_id,
MAX(status_history_id)
FROM
nq_contract_status_history
WHERE
Date_trunc('DAY', entry_date) <=
To_date(' 06/02/2010', 'mm/dd/yyyy')
GROUP BY quote_id)) mcsh
ON ( mcsh.quote_id = bc.quote_id
AND mcsh.status_type_cd IN ( 'ACTIVE', 'CANCELLED', 'DEFAULTED',
'EXPIRED',
'PAIDOUT', 'RESCIND' ) )
inner join (SELECT t.transaction_relation_id,
SUM (CASE
WHEN ( e.debit_id != 1100
AND e.credit_id >= 2000
AND e.credit_id < 3000 ) THEN amount
ELSE 0
END) AS cs,
SUM (CASE
WHEN ( e.credit_id != 1100
AND e.debit_id >= 2000
AND e.debit_id < 3000 ) THEN amount
ELSE 0
END) AS ds,
SUM (CASE
WHEN ( e.debit_id = 1100
AND e.credit_id >= 2000
AND e.credit_id < 3000 ) THEN amount
ELSE 0
END) AS d,
SUM (CASE
WHEN ( e.credit_id = 1100
AND e.debit_id >= 2000
AND e.debit_id < 3000 ) THEN amount
ELSE 0
END) AS c
FROM nq_gl_account_entry e
inner join nq_transaction t
ON ( e.transaction_id = t.transaction_id )
WHERE Date_trunc('DAY', transaction_date) <=
To_date(' 06/02/2010', 'mm/dd/yyyy')
GROUP BY t.transaction_relation_id) gl
ON ( gl.transaction_relation_id = bc.transaction_relation_id )
ORDER BY f.finance_company_name,
b.brokerage_name,
bc.quote_no
On 3 Jun 2010, at 24:42, federalbird wrote:
>
> The following query is very slow in Postgres 8.4.3 as compared to Postgres
> 8.1.5. Please reply. Thanx in advance.....
Did you check the output of EXPLAIN ANALYSE to see if the plans are different between the two? Are your database
settingsidentical?
To improve your chances on an answer, does the problem still occur if you strip out all the irrelevant crud (like all
thoseCASE's) from that query? I bet it does and it would make the query a whole lot more readable.
I left the plain text version of your message below, as the HTML version was just one big blob of text and people may
notrealise the plain text version was less badly formatted:
> select f.finance_company_name, b.brokerage_name, bc.quote_no as
> ContractNumber, cl.first_name as ClientFirstName, cl.last_name as
> ClientLastName, mcsh.status_type_cd as ContractStatus,
> (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained
> * monthly_amt ELSE 0 END)) as due_amount,
> (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained
> * monthly_amt ELSE 0 END)) as received_amount,
> (gl.ds - gl.cs - gl.d + gl.c) as ledger,
> (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as active_ledger,
> (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as active_due_amount,
> (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as active_received_amount,
> (case when mcsh.status_type_cd = 'ACTIVE' THEN 1 else 0 end) as
> active_count,
> (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as cancelled_ledger,
> (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as cancelled_due_amount,
> (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as cancelled_received_amount,
> (case when mcsh.status_type_cd = 'CANCELLED' THEN 1 else 0 end) as
> cancelled_count,
> (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as default_ledger,
> (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as default_due_amount,
> (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as default_received_amount,
> (case when mcsh.status_type_cd = 'DEFAULTED' THEN 1 else 0 end) as
> default_count,
> (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as payout_ledger,
> (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as payout_due_amount,
> (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as payout_received_amount,
> (case when mcsh.status_type_cd = 'PAIDOUT' THEN 1 else 0 end) as
> payout_count,
> (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as expired_ledger,
> (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as expired_due_amount,
> (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as expired_received_amount,
> (case when mcsh.status_type_cd = 'EXPIRED' THEN 1 else 0 end) as
> expired_count,
> (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as writeoff_ledger,
> (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as writeoff_due_amount,
> (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as writeoff_received_amount,
> (case when mcsh.status_type_cd = 'WRITEOFF' THEN 1 else 0 end) as
> writeoff_count,
> (case when mcsh.status_type_cd = 'RESCIND' THEN (gl.ds - gl.cs - gl.d +
> gl.c) else 0 end) as rescind_ledger,
> (case when mcsh.status_type_cd = 'RESCIND' THEN (gl.ds - gl.cs+(CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as rescind_due_amount,
> (case when mcsh.status_type_cd = 'RESCIND' THEN (gl.d - gl.c + (CASE WHEN
> rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END))
> else 0 end) as rescind_received_amount,
> (case when mcsh.status_type_cd = 'RESCIND' THEN 1 else 0 end) as
> rescind_count
> from nq_finance_company f inner join nq_group g on (f.finance_company_id =
> g.group_id and g.group_id =3299)
> inner join nq_group ug on (g.left_index < ug.left_index and g.right_index >
> ug.right_index)
> inner join nq_brokerage b on (b.brokerage_id = ug.parent_id)
> inner join nq_base_contract bc on (bc.group_id = ug.group_id and
> bc.quote_type_cd = 'CONTRACT')
> inner join nq_client cl on (cl.client_id = bc.client_id)
> left outer join nq_retained_pmts rp on (bc.quote_id = rp.quote_id)
> inner join (select csh.quote_id, csh.status_type_cd from
> nq_contract_status_history csh where (csh.quote_id, csh.status_history_id)
> in (select quote_id, max(status_history_id) from nq_contract_status_history
> where DATE_TRUNC( 'DAY',entry_date) <= to_date('
> 06/02/2010', 'mm/dd/yyyy') group by quote_id)) mcsh
> on (mcsh.quote_id = bc.quote_id and mcsh.status_type_cd in ('ACTIVE',
> 'CANCELLED', 'DEFAULTED', 'EXPIRED', 'PAIDOUT', 'RESCIND'))
> inner join
> (select t.transaction_relation_id,
> sum (case when (e.debit_id != 1100 and e.credit_id >= 2000 and
> e.credit_id < 3000) then amount else 0 end) as cs,
> sum (case when (e.credit_id != 1100 and e.debit_id >= 2000 and
> e.debit_id < 3000 ) then amount else 0 end) as ds,
> sum (case when (e.debit_id = 1100 and e.credit_id >= 2000 and
> e.credit_id < 3000 ) then amount else 0 end) as d,
> sum (case when (e.credit_id = 1100 and e.debit_id >= 2000 and
> e.debit_id < 3000) then amount else 0 end) as c
> from nq_gl_account_entry e inner join nq_transaction t on (e.transaction_id
> = t.transaction_id)
> where DATE_TRUNC( 'DAY',transaction_date) <= to_date('
> 06/02/2010', 'mm/dd/yyyy') group by t.transaction_relation_id) gl on
> (gl.transaction_relation_id = bc.transaction_relation_id)
> order by f.finance_company_name, b.brokerage_name, bc.quote_no
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4c07a96310151389715979!