Query Assistance - using NOT IN with a CASE statement

[Samalie]

Hi there...

I'm trying to write a query where I (AFAIK) need to use NOT IN with a case statement...see the code snippet below:

 	SUM(CASE 		WHEN YEAR(t.umonth) = (YEAR('12/01/2007')-1) 
				AND MONTH(t.umonth) = 12 
				AND t.ibook = 1
				AND p.scode NOT IN (CASE	WHEN p2.scode = '.slp' 
								AND YEAR('12/01/2007')-1 = 2006
								THEN ('003','014','023','026','059','069','070','073','077','079','080','087','storwp')
								WHEN p2.scode = '.pvlp' 
								AND YEAR('12/01/2007')-1 = 2006
								THEN ('003','004','008','015','023','024','041','059','063','064','068','069','070','073','077','079','080','storwp')
								ELSE ('02346986123461986')
								END)
				THEN CONVERT(decimal(14,2),(ISNULL(t.sbegin,0) * ((isnull(lp.dpercent,100) / 100)))) 
					+ CONVERT(decimal(14,2),(ISNULL(t.smtd,0) * ((isnull(lp.dpercent,100) / 100)))) 
				ELSE 0 
				END)

Now I'm getting the "standard" "Incorrect syntax near ',' on the THEN line. Unless I'm truly braindead this morning I cant find a comma formatting error, so it must be in the use of the list after the THEN. I just can't seem to find another way to do this. I thought about using a subquery, but in the same notion I need the SUM(), and you can't put a subquery in the aggregate.

For the record, I need to do this in the actual statement & not in the WHERE clause as the options directly affect this one part of the query, not the full statement.

Thanks for your assistance.

Samalie

 

[bborissov]

That is something above me
WHY you have:

...
AND YEAR('12/01/2007')-1 = 2006
...

After you know that this ALWAYS will be true?

(I just checking other things)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

>> it must be in the use of the list after the THEN

Yes. That's the problem.

I tried changing it to...

THEN (Select '003'
Union All Select '014'
Union All Select '023'
Union All Select '026'
Union All Select '059'
Union All Select '069'
Union All Select '070'
Union All Select '073'
Union All Select '077'
Union All Select '079'
Union All Select '080'
Union All Select '087'
Union All Select 'storwp')

That god rid of the syntax error near ',' problem, but then I got 'Cannot perform an aggregate function on an expression containing an aggregate or a subquery.'

So then I tried...

     Sum(CASE         WHEN YEAR(t.umonth) = (YEAR('12/01/2007')-1)
                AND MONTH(t.umonth) = 12
                AND t.ibook = 1
                AND ',' + p.scode + ',' NOT Like (CASE    WHEN p2.scode = '.slp'
                                AND YEAR('12/01/2007')-1 = 2006
                                THEN (',003,014,023,026,059,069,070,073,077,079,080,087,storwp,')
                                WHEN p2.scode = '.pvlp'
                                AND YEAR('12/01/2007')-1 = 2006
                                THEN (',003,004,008,015,023,024,041,059,063,064,068,069,070,073,077,079,080,storwp,')
                                ELSE ('02346986123461986')
                                END)
                THEN CONVERT(decimal(14,2),(ISNULL(t.sbegin,0) * ((isnull(lp.dpercent,100) / 100))))
                    + CONVERT(decimal(14,2),(ISNULL(t.smtd,0) * ((isnull(lp.dpercent,100) / 100))))
                ELSE 0
                END)

This seems to parse, but I cannot tell if it is returning the right data either. Anyway, give it a try, but test it REALLY well to make sure it's doing what you want.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Samalie]

It processes gmmastros, but its definetely not returning a correct recordset. Gonna keep experimenting.

And to answer your question bborissov, the '12/01/2007' is a variable in the overall final product, but I insert the date for testing the parsing

 

[fredericofonseca]

I do not know exactly what you are trying to do, as the strings the case are returning are kind of inconsistent.

But have a look at common_table_expression.
quick sample.

with tbl1(scode,t) as
(select '.pvlp','003'
union
select '.pvlp','004'
union
select '.pvlp','008'
union
select '.pvlp','015'
union
select '.pvlp','023'
union
select '.pvlp','024'
)
select * from test1
where scode not in (select scode
from tbl1
where tbl1.scode = test1.scode)
union all
select '02346986123461986' )


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[gmmastros]

Try this...

     Sum(CASE         WHEN YEAR(t.umonth) = (YEAR('12/01/2007')-1)
                AND MONTH(t.umonth) = 12
                AND t.ibook = 1
                AND (CASE    WHEN p2.scode = '.slp'
                                AND YEAR('12/01/2007')-1 = 2006
                                THEN (',003,014,023,026,059,069,070,073,077,079,080,087,storwp,')
                                WHEN p2.scode = '.pvlp'
                                AND YEAR('12/01/2007')-1 = 2006
                                THEN (',003,004,008,015,023,024,041,059,063,064,068,069,070,073,077,079,080,storwp,')
                                ELSE (',02346986123461986,')
                                END) NOT Like '%,' + p.scode + ',%' 
                THEN CONVERT(decimal(14,2),(ISNULL(t.sbegin,0) * ((isnull(lp.dpercent,100) / 100))))
                    + CONVERT(decimal(14,2),(ISNULL(t.smtd,0) * ((isnull(lp.dpercent,100) / 100))))
                ELSE 0
                END)

Here's what I'm thinking.

You want to see if an item appears in a list, right? Well, if the list was in rows of a table, then IN is the right way to go. In this situation, that is problematic because you are doing some aggregate calculations. Another approach would be to use the LIKE keyword.

I was just playing around with this a little. My test code is:

[COLOR=blue]Select[/color] 1
[COLOR=blue]Where[/color]   [COLOR=red]',003,004,'[/color] Like  [COLOR=red]'%,003,%'[/color]

If you run this code, it will return a 1. However, notice that the list of values is on the left side, and we need to have % symbols on the right. The code I posted at the top of this reply has this structure, so I feel reasonably confident that it will work for. I encourage you to try it.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Samalie]

George,

Thanks for the attempt, but its still not returning the correct dataset.

I think I might go about this in a completely different fashion. But if I solve this problem in the method I posted here I'll definetely leave a responce stating the fix.

Samalie

 

[SQLSister]

try writing what you want as a union (with each branch of the current case statement as part of the where clasue for the union) then making it a derived table and doing the sum on the derived table column.

"NOTHING is more important in a database than integrity." ESquared