Problem using select stt within case

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

SQL Server 2000 Forums

Transact-SQL (2000)

Problem using select stt within case

Author

Topic

sharmikanna
Starting Member

5 Posts

Posted - 2004-09-27 : 12:53:59

here is my query
SELECT QDP.MANUFACTURERPRODID AS ManufProdID,
QDP.PRODHEIGHT AS ProdHt,
QDP.PRODWEIGHT AS ProdWt,
CASE QDP.CNETSPECIND
WHEN 1 THEN
(SELECT
QCP.PRODIMAGE AS ProdImage,
QCP.MARKETINGDESCRIPTION AS Descr
FROM QMCNETPRODINFO QCP
WHERE QDP.PRODID = QCP.PRODID)
ELSE
(SELECT
'' AS ProdImage,
'' AS Descr)
END
FROM QMDATAPAQ QDP
WHERE QDP.MANUFACTURERPRODID = '0-7356-1158-0'

The error message i get is

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-27 : 13:41:12

(SELECT
QCP.PRODIMAGE AS ProdImage,
QCP.MARKETINGDESCRIPTION AS Descr
FROM QMCNETPRODINFO QCP
WHERE QDP.PRODID = QCP.PRODID)

This is giving multiple values for a single QDP.PRODID - which one tdo you want in the row?
Try
(SELECT top 1
QCP.PRODIMAGE AS ProdImage,
QCP.MARKETINGDESCRIPTION AS Descr
FROM QMCNETPRODINFO QCP
WHERE QDP.PRODID = QCP.PRODID)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-27 : 13:46:41

quote:
Originally posted by sharmikanna

here is my query
SELECT QDP.MANUFACTURERPRODID AS ManufProdID,
QDP.PRODHEIGHT AS ProdHt,
QDP.PRODWEIGHT AS ProdWt,
CASE QDP.CNETSPECIND
WHEN 1 THEN
(SELECT
QCP.PRODIMAGE AS ProdImage,
QCP.MARKETINGDESCRIPTION AS Descr
FROM QMCNETPRODINFO QCP
WHERE QDP.PRODID = QCP.PRODID)
ELSE
(SELECT
'' AS ProdImage,
'' AS Descr)
END
FROM QMDATAPAQ QDP
WHERE QDP.MANUFACTURERPRODID = '0-7356-1158-0'

You could also use multiple select statements to separate out the multi-field return values.

CASE QDP.CNETSPECIND
WHEN 1 THEN
(SELECT
QCP.PRODIMAGE AS ProdImage
FROM QMCNETPRODINFO QCP
WHERE QDP.PRODID = QCP.PRODID)
ELSE
(SELECT
'' AS ProdImage)
END,

CASE QDP.CNETSPECIND
WHEN 1 THEN
(SELECT
QCP.MARKETINGDESCRIPTION AS Descr

FROM QMCNETPRODINFO QCP
WHERE QDP.PRODID = QCP.PRODID)
ELSE
(SELECT
'' AS Descr)
END

Surf On Dude!

sharmikanna
Starting Member

5 Posts

Posted - 2004-09-27 : 15:15:05

The definition for CASE is
Evaluates a list of conditions and returns ONE of multiple possible result expressions.

So i cant expect more than one column as result.
So have to go by the answer posted by clarkbaker1964
ie. use 2 case stt.
Thanks for the reply

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources