Help with this query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with this query

Author

Topic

vadmcse
Starting Member

10 Posts

Posted - 2008-06-19 : 07:10:52

Hi,

I have this query and works OK:

SELECT T0.U_SEIArt, T0.U_SEINumSe,
(SELECT T2.ItemName
FROM OITM T2
WHERE T2.ItemCode = T0.U_SEIArt) AS Descripcion,
'---' AS Num_Serie

FROM [dbo].[@SEIPEDNS] T0
INNER JOIN ORDR T1 ON T0.U_SEIEntry = T1.DocEntry

WHERE T1.DocNum =[%0] AND T0.U_SEINumSe ='999'

And i have this query and also works OK:

SELECT T0.U_SEIArt, T0.U_SEINumSe,
(SELECT T2.ItemName
FROM OITM T2
WHERE T2.ItemCode = T0.U_SEIArt) AS Descripcion,
(SELECT T3.SuppSerial
FROM OSRI T3
WHERE (T3.ItemCode + T3.IntrSerial) = (T0.U_SEIArt + T0.U_SEINumSe)) AS Num_Serie

FROM [dbo].[@SEIPEDNS] T0
INNER JOIN ORDR T1 ON T0.U_SEIEntry = T1.DocEntry

WHERE T1.DocNum =[%0] AND T0.U_SEINumSe <>'999'

and finally, i "UNION" both queries and get the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

Any idea?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 07:14:12

quote:
Originally posted by vadmcse

Hi,

I have this query and works OK:

SELECT T0.U_SEIArt, T0.U_SEINumSe,
(SELECT T2.ItemName
FROM OITM T2
WHERE T2.ItemCode = T0.U_SEIArt) AS Descripcion,
'---' AS Num_Serie

FROM [dbo].[@SEIPEDNS] T0
INNER JOIN ORDR T1 ON T0.U_SEIEntry = T1.DocEntry

WHERE T1.DocNum =[%0] AND T0.U_SEINumSe ='999'

And i have this query and also works OK:

SELECT T0.U_SEIArt, T0.U_SEINumSe,
(SELECT T2.ItemName
FROM OITM T2
WHERE T2.ItemCode = T0.U_SEIArt) AS Descripcion,
(SELECT T3.SuppSerial
FROM OSRI T3
WHERE (T3.ItemCode + T3.IntrSerial) = (T0.U_SEIArt + T0.U_SEINumSe)) AS Num_Serie
FROM [dbo].[@SEIPEDNS] T0
INNER JOIN ORDR T1 ON T0.U_SEIEntry = T1.DocEntry

WHERE T1.DocNum =[%0] AND T0.U_SEINumSe <>'999'

and finally, i "UNION" both queries and get the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

Any idea?

Thanks

its because of the two subqueries used. Subqueries cant return more than one value when used in UNION . If you could tell us what your reuirement is with some sample data & output you want , we might be able to rewrite this query for you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 07:18:31

May be this:-

SELECT T0.U_SEIArt, 
T0.U_SEINumSe, 
T2.ItemName AS Descripcion,
'---' AS Num_Serie

FROM [dbo].[@SEIPEDNS] T0 
INNER JOIN ORDR T1 ON T0.U_SEIEntry = T1.DocEntry 
INNER JOIN OITM T2 ON T2.ItemCode = T0.U_SEIArt
WHERE T1.DocNum =[%0] AND T0.U_SEINumSe ='999'

UNION

SELECT T0.U_SEIArt, T0.U_SEINumSe, 
T2.ItemName AS Descripcion,
T3.SuppSerial AS Num_Serie
FROM [dbo].[@SEIPEDNS] T0 
INNER JOIN ORDR T1 ON T0.U_SEIEntry = T1.DocEntry 
INNER JOIN OITM T2 ON T2.ItemCode = T0.U_SEIArt
INNER JOIN OSRI T3 ON T3.ItemCode + T3.IntrSerial=T0.U_SEIArt + T0.U_SEINumSe
WHERE T1.DocNum =[%0] AND T0.U_SEINumSe <>'999'

vadmcse
Starting Member

10 Posts

Posted - 2008-06-19 : 07:29:43

Hi VISAKH16,

thanks for your answer.

I tried your query and get the same error.
How is it possible? Your query doesn't contain any SUBQUERY.

It is complicated to explain what i want, but now that i know that i can`t use union with subquery, i'll use INNER JOIN instead (I hate INNER JOIN)

Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 07:41:57

quote:
Originally posted by vadmcse

Hi VISAKH16,

thanks for your answer.

I tried your query and get the same error.
How is it possible? Your query doesn't contain any SUBQUERY.

It is complicated to explain what i want, but now that i know that i can`t use union with subquery, i'll use INNER JOIN instead (I hate INNER JOIN)

Thank you very much

Not sure how you got same error as the posted code doesnt contain any subqueries. I'm doubting whether the errror is caused by some part of your query batch which is not posted.

vadmcse
Starting Member

10 Posts

Posted - 2008-06-19 : 07:48:58

Sorry, you are right.

If i run the query in the QUERY ANALIZER, it works fine.

I'll investigate it since i'm running in SAP and i get the error.

Thank you very much.

vadmcse
Starting Member

10 Posts

Posted - 2008-06-19 : 08:01:49

Ok, i found the problem.

The "[%0]" is used in SAP to open a window and wait for external data, if i replace this with the data, both queries work fine.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 10:15:33

quote:
Originally posted by vadmcse

Ok, i found the problem.

The "[%0]" is used in SAP to open a window and wait for external data, if i replace this with the data, both queries work fine.

Thanks

i havent worked in SAP. Thanks for the info anyways.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources