March 5, 2007 at 9:28 am
I realize this query is inherently incorrect, but my issue is mainly syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong. I want to ensure that a.order_id is not in the settlement table. So I was thinking something along the lines of "WHEN a.order_id not in (select order_id from settlement)" which I know will cause a slower response time, but I'm willing to deal with it. In any case, that syntax doesn't appear to work.
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (
CASE
WHEN a.order_id <> b.order_id THEN
a.transaction_date
ELSE
b.delivery_date
END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN
a.amount
END) earn_amount_rtp_curr,
Any help here would be hotness!
Thanks!
March 5, 2007 at 10:01 am
Matthew -
If you could post some background of what you're trying to achieve and the relevant code, rather than a small snippet, we might be able to help you toward a better solution where you don't have to be 'willing to deal with it'...
Ade
March 5, 2007 at 10:11 am
Alright, but you won't like it. I was trying to keep it simple looking so I'd get more replies. :p To be honest, I'd really be okay dealing with a simple select because the report will only be run like two-three times a day by different people.
SELECT subq4.*,
subq1.settle_amt,
subq1.settle_current,
subq1.settle_over30,
subq1.settle_over45,
subq1.settle_over60,
subq1.settle_over90,
subq2.earn_amount_rtp,
subq2.reimb_amount_rtp,
subq2.deduct_amount_rtp,
subq2.earn_amount_nrtp,
subq2.reimb_amount_nrtp,
subq2.deduct_amount_nrtp,
subq3.recur_deduct_amount
FROM
(SELECT drs_payee.company_id company_id,
drs_payee.id payee_id,
drs_payee.type_of payee_type,
payee.name payee_name,
drs_payee.taxable_owed taxable_owed
FROM payee,
drs_payee
WHERE payee.company_id = '".$company_id."'
AND payee.company_id = drs_payee.company_id
AND payee.id = drs_payee.id
AND payee.id in ".$payeeCond."
)
subq4
LEFT OUTER JOIN
(SELECT company_id,
payee_id,
sum(total_pay) settle_amt,
sum(
CASE
WHEN datediff(d,delivery_date, ".$cutOffDate.") = 30
AND datediff(d,delivery_date, ".$cutOffDate.") = 45
AND datediff(d,delivery_date, ".$cutOffDate.") = 60
AND datediff(d,delivery_date, ".$cutOffDate.") = 90
THEN total_pay
END) settle_over90
FROM settlement
WHERE company_id = '".$company_id."'
AND ready_to_pay_flag 'V'
AND payee_id in ".$payeeCond." ".$dateCond1."
GROUP BY company_id,
payee_id
)
subq1
ON subq4.company_id = subq1.company_id
AND subq4.payee_id = subq1.payee_id
LEFT OUTER JOIN
(SELECT a.company_id,
a.payee_id,
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y'
AND a.deduction_type = 'E'
THEN a.amount
END) earn_amount_rtp,
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y'
AND a.deduction_type = 'E'
AND ((
CASE
WHEN a.order_id = b.order_id
THEN a.transaction_date
ELSE b.delivery_date
END) used_date) datediff(d,used_date, ".$cutOffDate.") = 30
AND datediff(d,used_date, ".$cutOffDate.") = 45
AND datediff(d,used_date, ".$cutOffDate.") = 60
AND datediff(d,used_date, ".$cutOffDate.") = 90
THEN a.amount
END) earn_amount_rtp_90,
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y'
AND a.deduction_type = 'R'
THEN a.amount
END) reimb_amount_rtp,
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y'
AND a.deduction_type 'E'
AND a.deduction_type 'R'
THEN amount
END) deduct_amount_rtp,
sum(
CASE
WHEN (a.ready_to_pay_flag = 'N'
OR a.ready_to_pay_flag is null)
AND a.deduction_type = 'E'
THEN a.amount
END) earn_amount_nrtp,
sum(
CASE
WHEN (a.ready_to_pay_flag = 'N'
OR a.ready_to_pay_flag is null)
AND a.deduction_type = 'R'
THEN a.amount
END) reimb_amount_nrtp,
sum(
CASE
WHEN (a.ready_to_pay_flag = 'N'
OR a.ready_to_pay_flag is null)
AND (a.deduction_type 'E'
AND a.deduction_type 'R')
THEN a.amount
END) deduct_amount_nrtp
FROM drs_pending_deduct as a,
settlement as b
WHERE a.company_id = '".$company_id."'
AND a.payee_id in ".$payeeCond."
AND a.transaction_date <= ".$dateStr."
AND a.ready_to_pay_flag 'V'
AND a.amount is not null
GROUP BY a.company_id,
a.payee_id) subq2 on subq4.company_id = subq2.company_id and subq4.payee_id = subq2.payee_id left outer join
(SELECT company_id,
payee_id,
sum(amount) recur_deduct_amount
FROM drs_recur_deduct
WHERE drs_recur_deduct.company_id = '".$company_id."'
AND payee_id in ".$payeeCond2."
AND (deduct_start_date = {d '".$cuttoffY."-".$cuttoffM."-".$cuttoffD."'}
OR deduct_end_date is null)
AND ((deduction_type = 'L'
AND loan_balance > 0)
OR deduction_type = 'S'
OR deduction_type is null)
AND ready_to_pay_flag 'V'
GROUP BY company_id,
payee_id
)
subq3 on subq4.company_id = subq3.company_id and subq4.payee_id = subq3.payee_id
March 5, 2007 at 10:19 am
And to have it narrowed down further, here's the distinct subquery with the issue:
SELECT a.company_id,
a.payee_id,
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y'
AND a.deduction_type = 'E'
THEN a.amount
END) earn_amount_rtp,
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y'
AND a.deduction_type = 'E'
AND ((
CASE
WHEN a.order_id = b.order_id
THEN a.transaction_date
ELSE b.delivery_date
END) used_date) datediff(d,used_date, ".$cutOffDate.") = 30 and datediff(d,used_date, ".$cutOffDate.") = 45 and datediff(d,used_date, ".$cutOffDate.") = 60 and datediff(d,used_date, ".$cutOffDate.") = 90 THEN
a.amount
END) earn_amount_rtp_90,
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'R' THEN
a.amount
END) reimb_amount_rtp,
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type 'E' and a.deduction_type 'R' THEN
amount
END) deduct_amount_rtp,
sum(
CASE
WHEN (a.ready_to_pay_flag = 'N' or a.ready_to_pay_flag is null) and a.deduction_type = 'E' THEN
a.amount
END) earn_amount_nrtp,
sum(
CASE
WHEN (a.ready_to_pay_flag = 'N' or a.ready_to_pay_flag is null) and a.deduction_type = 'R' THEN
a.amount
END) reimb_amount_nrtp,
sum(
CASE
WHEN (a.ready_to_pay_flag = 'N' or a.ready_to_pay_flag is null) and (a.deduction_type 'E' and a.deduction_type 'R') THEN
a.amount
END) deduct_amount_nrtp from drs_pending_deduct
as
a,
settlement
as
b where a.company_id = '".$company_id."' and a.payee_id in ".$payeeCond." and a.transaction_date <= ".$dateStr." and a.ready_to_pay_flag 'V' and a.amount is not null group by a.company_id,
a.payee_id
March 6, 2007 at 12:22 am
Do you get any errors or does it just return the wrong result? As far as I can see, the problem is that the CASE statement should be inside the DATEDIFF function:
sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (datediff(d,
CASE
WHEN a.order_id <> b.order_id
THEN a.transaction_date
ELSE b.delivery_date
END, ".$cutOffDate.") < 30)
THEN a.amount
END) AS earn_amount_rtp_curr,
5ilverFox
Consulting DBA / Developer
South Africa
March 6, 2007 at 6:56 am
That has got to be the longest single select statement that I have ever seen! I have nightly scripts that process millions of rows of data that aren't that long!
March 6, 2007 at 8:16 am
Heh. Yeah. I honestly don't know why they need such complex queries. I just ran into it when I took another guys' position. I finally fixed it, if you guys want to see the finished product, just pop me a message and I'll post it up in this thread.
The only reason I'm not posting it now is because ... the thing is even bigger now.
And Japie, you were totally correct. I couldn't figure out that you couldn't alias out that kind of case. I had "(CASE ... END) used_date," but I should've just left out the used_date. I can't believe the fix was so simple.
March 6, 2007 at 8:20 am
"WHEN a.order_id not in (select order_id from settlement) THEN"
the correct syntax for this might be:
WHEN (select order_id from settlement where order_id=a.order_id) IS NULL THEN...
keep in mind that this will generate an error if there is more than record in the settlement table that meets the order_id=a.order_id criteria. This is find if order_id in the settlement table is truly unique.
IF not, you can use a top 1 (prob. slower) or a count(order_id) instead to get around this.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply