Alternatives to CASE ??? - SQL Server Forums

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

Alternatives to CASE ???

Author

Topic

niels.holm
Starting Member

1 Post

Posted - 2005-09-30 : 07:26:20

Hi all

Hope someone in here can help me solve a problem im having with a view.
I'm tring to build a view that show me the sales history for any given machine in a Machine Database (MDB). Each machine is handled as a seperate "project".

My query works fine as long as a machine has only been sold once ... but as soon as the machine is being sold as used to another customer the view fails because of my "subqueries" return more then one value.
(Error message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.)

I hope someone in here has a ideer on how to get the information I need out - because my CASE statements clearly dosn't do the trick.

--------------------------------------------------------
SELECT crm5.contact.name AS Customer,
crm5.pmembtype.name AS Ownership,
crm5.pv_salespress.InstallationDate AS [Install Date],
CASE
WHEN (SELECT crm5.pv_salespress.comptr1_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.pv_salespress.comptr1_Used FROM crm5.pv_salespress)
WHEN (SELECT crm5.pv_salespress.comptr2_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.pv_salespress.comptr2_Used FROM crm5.pv_salespress)
WHEN (SELECT crm5.pv_salespress.comptr3_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.pv_salespress.comptr3_Used FROM crm5.pv_salespress)
ELSE (SELECT crm5.pv_salespress.Used FROM crm5.pv_salespress)
END AS Used,
CASE
WHEN (SELECT crm5.pv_salespress.comptr1_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.comptr.name FROM crm5.comptr WHERE crm5.comptr.comptr_id = crm5.pv_salespress.comptr1_seller)
WHEN (SELECT crm5.pv_salespress.comptr2_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.comptr.name FROM crm5.comptr WHERE crm5.comptr.comptr_id = crm5.pv_salespress.comptr2_seller)
WHEN (SELECT crm5.pv_salespress.comptr3_Winner FROM crm5.pv_salespress) = 1 THEN (SELECT crm5.comptr.name FROM crm5.comptr WHERE crm5.comptr.comptr_id = crm5.pv_salespress.comptr3_seller)
ELSE (SELECT crm5.comptr.name FROM crm5.comptr WHERE crm5.comptr.comptr_id = 35)
END AS [Installed By],
crm5.pv_salespress.RemovalDate AS [Removal Date],
crm5.pv_salespress.RemovalReason AS Reason,
crm5.pv_salespress.ActionBy AS [Removed By],
crm5.project.project_id,
crm5.sale.sale_id
FROM crm5.contact INNER JOIN
crm5.sale INNER JOIN
crm5.project INNER JOIN
crm5.projectmember ON crm5.project.project_id = crm5.projectmember.project_id INNER JOIN
crm5.pmembtype ON crm5.projectmember.mtype_idx = crm5.pmembtype.PMembType_id INNER JOIN
crm5.projstatus ON crm5.project.status_idx = crm5.projstatus.ProjStatus_id ON crm5.sale.project_id = crm5.project.project_id ON
crm5.contact.contact_id = crm5.projectmember.contact_id RIGHT OUTER JOIN
crm5.pv_salespress ON crm5.sale.sale_id = crm5.pv_salespress.Sale_idx

----------------------

Thanks in advance
Niels

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-30 : 23:43:11

Niels, read this:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

If you post like that, it would really help us a lot in answering your questinos. It looks like you don't need all the sub-selects you're trying to do in that WHERE clause, but without some kind of sample data, we're going to just be guessing.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-03 : 22:02:05

look at your subqueries in your where clause, i reckon they're returning more than one value because
1) you don't have a where clause
2) you might want to specify a top 1 probably or an aggregate function

quote:
Originally posted by niels.holm

My query works fine as long as a machine has only been sold once ... but as soon as the machine is being sold as used to another customer the view fails because of my "subqueries" return more then one value.
(Error message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.)

HTH

--------------------
keeping it simple...

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-10-04 : 13:12:23

You have 14 subqueries in there. I can't imagine you are getting any semblance of decent performance out of that. You might consider modifying your query to JOIN the tables that you use in your subqueries into the main query. You might need to include some Additional Criteria in the JOIN Clause to get the right results.

And as mentioned before, maybe you need a WHERE clause on your main SELECT statement. You might even need to join the pv_salespress table to itself, although it's hard to tell from what you have posted.

---------------------------
EmeraldCityDomains.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources