Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Assistance

Author  Topic 

mcupples
Starting Member

9 Posts
Posted - 2007-03-05 : 11:25:10
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!

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2007-03-05 : 12:52:41
You can't just post a snippet of your query, with no table structures and no sample data. You simply won't get a response (other than one like this) if you post such obscure questions.
Go to Top of Page

mcupples
Starting Member

9 Posts
Posted - 2007-03-05 : 13:05:03
Okay, sorry about all this. I just wanted the syntax for a nested case statement. I can post the entire subquery here below for you, but the entire query is about 2-3 pages long, I doubt you'd want/need to see all that. If you do though, I'll be more than happy to post it.

The issue I'm having is ensuring that a.order_id is not in the COLUMN of b.order_id. So I'm thinking something like WHEN a.order_id not in (select order_id from settlement). Lemme know what the syntax is, because when I shove that in there, I get nothing but errors.

In fact, I'll post up the errors too:

Warning: mssql_query() [function.mssql-query]: message: Line 54: Incorrect syntax near 'used_date'. (severity 15) in /home/fulmer/public_html/intranet/000settlement2.php on line 214

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'THEN'. (severity 15) in /home/fulmer/public_html/intranet/000settlement2.php on line 214

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'THEN'. (severity 15) in /home/fulmer/public_html/intranet/000settlement2.php on line 214

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'THEN'. (severity 15) in /home/fulmer/public_html/intranet/000settlement2.php on line 214

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'THEN'. (severity 15) in /home/fulmer/public_html/intranet/000settlement2.php on line 214

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'and'. (severity 15) in /home/fulmer/public_html/intranet/000settlement2.php on line 214

Warning: mssql_query() [function.mssql-query]: message: Line 98: Incorrect syntax near 'subq3'. (severity 15) in /home/fulmer/public_html/intranet/000settlement2.php on line 214

Warning: mssql_query() [function.mssql-query]: Query failed in /home/fulmer/public_html/intranet/000settlement2.php on line 214

Warning: mssql_fetch_array(): supplied argument is not a valid MS SQL-result resource in /home/fulmer/public_html/intranet/000settlement2.php on line 229

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)
THEN a.amount
END) earn_amount_rtp_curr,
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 THEN
a.amount
END) earn_amount_rtp_30,
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.") >= 45 and datediff(d,used_date, ".$cutOffDate.") < 60 THEN
a.amount
END) earn_amount_rtp_45,
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.") >= 60 and datediff(d,used_date, ".$cutOffDate.") < 90 THEN
a.amount
END) earn_amount_rtp_60,
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.") >= 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
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2007-03-05 : 13:13:55
OK, the problme you're having is that you don't have a join condition in your query. You need to learn about SQL joins and then most of those case statements are going to go away once your two tables are correctly joined.
Try
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

mcupples
Starting Member

9 Posts
Posted - 2007-03-05 : 13:19:39
Thanks a ton! I believe you're right, but how far can you nest joins? that query is already part of a join.
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2007-03-05 : 13:28:22
You can join as many times as you like, but my point is that you are currently joining incorrectly, you have no criteria in your WHERE clause that specifies how the rows from the first table join to the second table. I would also strongly recommend that you start using the JOIN keyword for your joins, instead of simple listing the tables separated by commas, it makes the query much easier to read and is the correct ANSI syntax for joins. Your query would become something like
SELECT ...
FROM drs_pending_deduct as a 
INNER JOIN settlement as b ON <your join condition here>
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
Go to Top of Page

mcupples
Starting Member

9 Posts
Posted - 2007-03-05 : 13:43:18
Okay, I understand that, I believe, but since I need to return the order_ids in the drs table even if they are null in the settlement table, I should do this:

FROM drs_pending_deduct as a,
LEFT OUTER JOIN settlement as b
ON a.order_id = b.order_id
WHERE a.company_id = 'TMS2'

Correct?

My issue still stands, however, there is some sort of syntax error earlier in my query that I cannot understand. I will go and sift through it and post the lines I believe it to be on.
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2007-03-05 : 13:53:02
That looks good.

As far as your syntax error goes, this is wrong
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


In there you basically have an expression that looks like this

something AND date DATEDIFF()

I'm not exactly sure what you meant to have there, but something like this maybe

something AND date > DATEDIFF()
Go to Top of Page

mcupples
Starting Member

9 Posts
Posted - 2007-03-05 : 14:42:33
Okay, here's what I've tried:

sum(
CASE
WHEN a.ready_to_pay_flag = 'Y'
AND a.deduction_type = 'E'
AND datediff(d,((
CASE
WHEN a.order_id is not in (SELECT order_id FROM settlement)
THEN a.transaction_date
ELSE b.delivery_date
END) used_date), '2007-03-05 23:59:59') >= 90
THEN a.amount
END) earn_amount_rtp_90,

It's telling me:

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'THEN'. (severity 15) in /home/fulmer/public_html/intranet/000settlement2.php on line 468

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'THEN'. (severity 15) in /home/fulmer/public_html/intranet/000settlement2.php on line 468

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'THEN'. (severity 15) in /home/fulmer/public_html/intranet/000settlement2.php on line 468

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'THEN'. (severity 15) in /home/fulmer/public_html/intranet/000settlement2.php on line 468

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'AND'. (severity 15) in /home/fulmer/public_html/intranet/000settlement2.php on line 468

Warning: mssql_query() [function.mssql-query]: message: Line 251: Incorrect syntax near 'subq3'. (severity 15) in /home/fulmer/public_html/intranet/000settlement2.php on line 468

Warning: mssql_query() [function.mssql-query]: Query failed in /home/fulmer/public_html/intranet/000settlement2.php on line 468

Warning: mssql_fetch_array(): supplied argument is not a valid MS SQL-result resource in /home/fulmer/public_html/intranet/000settlement2.php on line 483

Not sure exactly which THEN it's talking about, but I'm pretty sure it's the first one listed in that sample.
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2007-03-05 : 15:50:19
Don't try to give expressions names, only columns need names, so you'd do this
sum(
CASE 
WHEN a.ready_to_pay_flag = 'Y' 
AND a.deduction_type = 'E' 
AND datediff(d, CASE 
WHEN b.order_id IS NULL
THEN a.transaction_date 
ELSE b.delivery_date END, '2007-03-05 23:59:59') >= 90 
THEN a.amount 
END) earn_amount_rtp_90,

I also changed the check between the order_id's to a check for null. b.order_id will be null when you have your join right.
Go to Top of Page

mcupples
Starting Member

9 Posts
Posted - 2007-03-06 : 10:12:33
Very nice. This fixed my issue perfectly. Thank you so much!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site