Monster Query Assistance

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!

Keep in mind that the effect of the CASE structure is to return a value, in this case a date value. That value is then place inside these parentheses.

Can you spot the syntax problem simply by replacing the entire CASE (inside the parens) with a date value?

and ( CASE
WHEN a.order_id <> b.order_id THEN a.transaction_date
ELSE b.delivery_date
END
) used_date

|||Erm. I'm sure I don't follow you. used_date will be a.transaction_date if a.order_id is not b.order_id. Correct? Are you saying that my syntax is incorrect inside of the case statement? If it is, I just don't see where.

Also, can I simply use

and ( CASE
WHEN a.order_id not in (select order_id from settlement) THEN a.transaction_date
ELSE b.delivery_date
END
) used_date

?|||

This statement makes no sense: (the substituted date value is immaterial)

and ( '01/01/2007' ) used_date datediff(d,used_date, ".$cutOffDate.") < 30)

|||Hrm, so you're saying that I need some sort of separator. I tried parentheses, but those did not work. To quote your example:

and (( CASE ... END) used_date) datediff(d,used_date, ".$cutOffDate.") < 30)

I also tried adding a comma after "used_date)" and just after used_date and removed the parentheses. Can you simply not nest cases in this way or am I completely destroying the query?|||

You can next CASE structures, BUT each nested level MUST be a syntactically complete CASE structure.

I have not attempted to work out your structure, you have not provided any sample data, etc. (See: http://www.aspfaq.com/5006)

I am attempting to point out that (( CASE ... END) used_date) seems meaningless. There is no evaluation, assignment, or other indication about how these values are to be used. And then somehow there is this magical continuation of the line into the datediff function. I don't get what you are attempting with this line.

I suggest that you separately TEST each CASE structure, determining the return value, and then add that value to the outer CASE structure. ONLY when the entire CASE structure works with those substituted values, then you can carefully replace the substituted value with the CASE structure that derived that value.

|||Awesome. Yeah, all I needed to do was remove the "used_date" from that. I had assumed that I needed to alias out the result of the case statement and use the alias in the query later on. I guess I just have to constantly use the same case statement. Oh well, still works so I'm happy.

Thanks much for your help!