Help with Query... What is most efficient

[fmrock]

Hey everyone,

The following is somewhat a query i am working on.

I am trying to figure out what the best way to get an accounts first payment date if they have one.

I have taken out lots of fields from this query and thats why i can just do the sub query. Would an Inline query be best in the select statement. Any help would be great.

Thanks

SELECT 
	GROUP.CPSTATE "Group_State",
	ACCOUNT.ACCTCPCODE "Group", 
	ACCOUNT.ACCTCODE "Account", 
	u.First_Payment_Date
FROM   
	ACCOUNT ACCOUNT
	JOIN GROUP GROUP ON (ACCOUNT.ACCTCPCODE=GROUP.CPCODE)
	JOIN CHARGES CHARGES ON ((ACCOUNT.ACCTCPCODE=CHARGES.CPCODE) AND (ACCOUNT.ACCTCODE=CHARGES.ACCOUNT) AND (CHARGES.TYPE='C') AND (CHARGES.SPLITFLAG IS  NULL))
	LEFT JOIN (SELECT MCFP.CPCODE, MCFP.ACCOUNT, min(to_date(MCFP.ENTRYDATE,'j')) First_Payment_Date
		FROM ECI_LTD.CHARGES MCFP 
		WHERE (MCFP.TYPE='P') 
			AND (MCFP.SPLITFLAG IS  NULL)
		GROUP BY MCFP.CPCODE, MCFP.ACCOUNT) U ON ((U.CPCODE=ACCOUNT.ACCTCPCODE) AND (U.ACCOUNT=ACCOUNT.ACCTCODE))
WHERE  
	(ACCOUNT.ACCTCPCODE='1000000')
	AND (to_date(ACCOUNT.ACCTLOGDATE,'j')>=TO_DATE ('01-01-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
	AND to_date(ACCOUNT.ACCTLOGDATE,'j')<TO_DATE ('31-01-2007 00:00:01', 'DD-MM-YYYY HH24:MI:SS')) 
ORDER BY 
	ACCOUNT.ACCTCPCODE, 
	ACCOUNT.ACCTLOGDATE, 
	ACCOUNT.ACCTCODE

 

[ChrisHunt]

Could you not just join normally to ECI_LTD.CHARGES and select min(to_date(MCFP.ENTRYDATE,'j')) in the main query? I don't see why you need a subquery at all.

Could you give a bit more information about what's in each table and what you're trying to achieve.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[fmrock]

The charges table contains Payments, charges, and adjustments all in one table.

In the report i have to display some of the charge information, which i removed to make this query smaller and more readable.

Also, if you use the Min in the select statement, dont you have to use a group by statment? and I have around 20 other fields in the select that would all have to be in the group by also?

 

[ChrisHunt]

If you use the Min in the select statement, dont you have to use a group by statment?

Yes. So what?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[fmrock]

Ok, I got that to work, using the following query for the most part.

SELECT 
	GROUP.CPSTATE "Group_State",
	ACCOUNT.ACCTCPCODE "Group", 
	ACCOUNT.ACCTCODE "Account", 
	to_date(ACCOUNT.ACCTLOGDATE,'j') "Log_Date",
	ACCOUNT.ACCTEXCEPTION "Ex_Code", 
	ACCOUNT.ACCTBALANCE "Balance", 
	ACCOUNT.ACCTBILLSTATUS "FC", 
	ACCOUNT.ACCTCPS "CPS",
	ACCOUNT.ACCTEAREA ||'-'|| ACCOUNT.ACCTEPRE || '-' || ACCOUNT.ACCTEEXT "PT_Phone",
	ACCOUNT.ACCTADDR1 "Pt_Addr1", 
	ACCOUNT.ACCTADDR2 "Pt_Addr2", 
	ACCOUNT.ACCTCITY  "Pt_City", 
	ACCOUNT.ACCTSTATE "Pt_State", 
	ACCOUNT.ACCTZIP "PT_Zip", 
	ACCOUNT.ACCTZIPPLUS "PT_ZipPlus", 
	ACCOUNT.ACCTEMPLOYER "Employer",
	CHARGES.PRCODE "Charge Code", 
	CHARGES.PRAMOUNT "Charge Amount",
	ACCOUNT.ACCTSTATEMENTS "Num_Statements",
	min(to_date(PTP.ENTRYDATE,'j')) "First_Payment_Date", 
	Count(DISTINCT PTP.SEQNO) "Num_of_PT_Payments", 
	sum(PTP.PRAMOUNT) "Total_PT_Payments",
	min(to_date(DSTC.ENTRYDATE,'j')) "Date_to_collections"
FROM   
	ACCOUNT ACCOUNT
	JOIN GROUP GROUP ON (ACCOUNT.ACCTCPCODE=GROUP.CPCODE)
	JOIN CHARGES CHARGES ON ((ACCOUNT.ACCTCPCODE=CHARGES.CPCODE) AND (ACCOUNT.ACCTCODE=CHARGES.ACCOUNT) AND (CHARGES.TYPE='C') AND (CHARGES.SPLITFLAG IS  NULL))
	LEFT JOIN CHARGES PTP ON ((ACCOUNT.ACCTCPCODE=PTP.CPCODE) AND (ACCOUNT.ACCTCODE=PTP.ACCOUNT) AND (PTP.TYPE='P') AND (PTP.SPLITFLAG IS  NULL) AND (PTP.PRCODE in ('90','92','93')))
	LEFT JOIN CHARGES DSTC ON ((ACCOUNT.ACCTCPCODE=DSTC.CPCODE) AND (ACCOUNT.ACCTCODE=DSTC.ACCOUNT) AND (DSTC.TYPE='A') AND (DSTC.SPLITFLAG IS  NULL) AND (DSTC.PRCODE in ('70','76','77','78')))
WHERE  
	(ACCOUNT.ACCTCPCODE='1000000')
	AND (ACCOUNT.ACCTBILLSTATUS='S')
	AND (to_date(ACCOUNT.ACCTLOGDATE,'j')>=TO_DATE ('01-01-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
	AND to_date(ACCOUNT.ACCTLOGDATE,'j')<=TO_DATE ('30-06-2008 00:00:01', 'DD-MM-YYYY HH24:MI:SS')) 
	AND (PTP.CPCODE IS NOT NULL)
	AND (PTP.ACCOUNT IS NOT NULL)
GROUP BY
	GROUP.CPSTATE,
	ACCOUNT.ACCTCPCODE, 
	ACCOUNT.ACCTCODE, 
	ACCOUNT.ACCTLOGDATE,
	ACCOUNT.ACCTEXCEPTION, 
	ACCOUNT.ACCTBALANCE, 
	ACCOUNT.ACCTBILLSTATUS, 
	ACCOUNT.ACCTCPS, 
	ACCOUNT.ACCTEAREA ||'-'|| ACCOUNT.ACCTEPRE || '-' || ACCOUNT.ACCTEEXT,
	ACCOUNT.ACCTADDR1, 
	ACCOUNT.ACCTADDR2, 
	ACCOUNT.ACCTCITY, 
	ACCOUNT.ACCTSTATE, 
	ACCOUNT.ACCTZIP, 
	ACCOUNT.ACCTZIPPLUS, 
	ACCOUNT.ACCTEMPLOYER,
	CHARGES.PRCODE, 
	CHARGES.PRAMOUNT,
	ACCOUNT.ACCTSTATEMENTS
ORDER BY 
	ACCOUNT.ACCTCPCODE, 
	ACCOUNT.ACCTLOGDATE, 
	ACCOUNT.ACCTCODE

Right now my sum of Payments sum(PTP.PRAMOUNT) "Total_PT_Payments", is being multiplied because there are multiple charges/payments/adjustments for each account. Any suggestion on how to get the sum correct?

 

[cmmrfrds]

What is wrong with the inline view named U that you have in the first query?

 

[fmrock]

Its super slow... hours vs minutes.

 

[cmmrfrds]

So, if you leave out the inline view the rest of the query processes in minutes?

How many records in the table ECI_LTD.CHARGES?

How long to run the inline view just by itself as an independent query? Does that take hours?

 

[fmrock]

Last time I checked the Charges table had over 50million rows.... and growing.

I have also played aroudnn with only joining charges once with out the type field added and using case statements in the select to display in the format i was looking for, but have not got tehre yet either.

I will run them seperate tomarrow when back in the office.

 

[Thargy]

just to check, have statistics been gathered for this table lately? Is the CBO being given the best information to work with?

Regards

T