SQL Query using As

Dear expert

I got a query problem, code below: I would it to do the Person as column name and list below all names.

Error:
Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
?
Regards
Wei

USE Prod
declare @Time datetime = ( SELECT TOP 1 [Time]
                            FROM Update.time
                            order by Time desc)

SELECT (
SELECT dataff.CustomerID	 
FROM 
	 data.person dataff 
	 join data.Update sparffa 
	 on dataff.SDD = sparffa.SDD
	 WHERE [Time] >= @Time AND Termination = 'S') As Person

Open in new window

LVL 1
WeTiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
When you write subqueries in a SELECT clause like SELECT (a subquery here) as Person, then it expects a single value for each row.  Since values must be atomic and not multiple values, it throws this error.  In other words, the subquery can only return one value.  

Try highlighting the subquery T-SQL and executing (after replacing @time with a hard-coded value), note that it returns more than one value, and then figure out what you intend to do here.

Also please post the entire query T-SQL here.
0
ste5anSenior DeveloperCommented:
First of all: Don't use reserved words like update or data.

Do you mean this:

USE Prod;
GO

DECLARE @Time DATETIME = (   SELECT   TOP 1 [Time]
                             FROM     [Update].time
                             ORDER BY Time DESC );

SELECT dataff.CustomerID AS Person
FROM   [data].person dataff
       JOIN [data].[Update] sparffa ON dataff.SDD = sparffa.SDD
WHERE  [Time] >= @Time
       AND Termination = 'S';

Open in new window

1
Mark WillsTopic AdvisorCommented:
The error is in
SELECT (
SELECT dataff.CustomerID	 
FROM 
	 data.person dataff 
	 join data.Update sparffa 
	 on dataff.SDD = sparffa.SDD
	 WHERE [Time] >= @Time AND Termination = 'S') As Person

Open in new window


If yiur subquery is returning multiple rows then it is a derived table, not an inline subquery

An inline subquery can only return a single value - thats why your TOP 1 worked.

A subquery which returns multiple values (either multi-rows or multi columns) generates a derived table. You have to SELECT ... FROM a derived table.

To select from a derived table you have to do
SELECT * from (
SELECT dataff.CustomerID	 
FROM 
	 data.person dataff 
	 join data.Update sparffa 
	 on dataff.SDD = sparffa.SDD
	 WHERE [Time] >= @Time AND Termination = 'S') As Person

-- and use the table alias for [time] and termination

Open in new window

Or as ste5an has suggested, maybe you dont need a subquery and simply do
SELECT dataff.CustomerID AS Person
FROM   [data].person dataff
JOIN [data].[Update] sparffa ON dataff.SDD = sparffa.SDD
WHERE  [Time] >= @Time                                                                  -- use the table alias for time and for termination.
AND Termination = 'S'

Open in new window

0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

WeTiAuthor Commented:
I did it Mark, but the result is not different from what you just do:
SELECT dataff.CustomerID	 
FROM 
	 data.person dataff 
	 join data.Update sparffa 
	 on dataff.SDD = sparffa.SDD
	 WHERE [Time] >= @Time AND Termination = 'S' 

Open in new window

What is the point of SELECT * FROM ( )As Person? I need the result to be in a sub table tho.
0
ste5anSenior DeveloperCommented:
There is no such thing like a "sub table". Can you rephrase your question and give us more context?
0
WeTiAuthor Commented:
I mean sub table is when I do this:
Select (
Select Count (*) From... Where... ) As First
(Select Count (*) From... Where.... ) As Second
This maybe called sub query...
Anyway what I really want is for subquery to show more than one results which is impossible without if-statement that Mark Wills informed before.
Query looks like this:
Select (
Select Count (*) From... Where... ) As First
(Select Count (*) From... Where.... ) As Second
(Select table From... Where...) As Person
Now First and Second show each one result and Person shows many... They cant be combined without some null statement... Or is there anyway to combine them without null statement just show empty space?
0
ste5anSenior DeveloperCommented:
When I understand this correctly, it's a simple JOIN. Using your outline:

SELECT  (SELECT COUNT (*) FROM... WHERE... ) As [First],
        (SELECT COUNT (*) FROM... WHERE.... ) As [Second],
        Person.someValue
FROM    someTables
    CROSS JOIN  (   SELECT someValue
                    FROM... WHERE...
                ) As Person;

Open in new window


But honestly, a concise and complete sample would seriously help. Post table DDL and sample data INSERT statements as runnable T-SQL script.

Cause the JOIN could be a {CROSS|INNER|OUTER} JOIN depending on your data model. Even a {CROSS|OUTER} APPLY is an option.
1
Mark WillsTopic AdvisorCommented:
OK, you cannot do
SELECT (
SELECT dataff.CustomerID	 
FROM 
	 data.person dataff 
	 join data.Update sparffa 
	 on dataff.SDD = sparffa.SDD
	 WHERE [Time] >= @Time AND Termination = 'S') As Person

Open in new window

it will error every time unless you make it select only 1 possible value - like a count(*) or select top 1

When the result set returns multiple values you MUST change your approach. The problem is not the inline subqueries, they return a single value (effectively returning a column). So add them into your select statement.
SELECT  (SELECT COUNT (*) FROM... WHERE... ) As [First],
        (SELECT COUNT (*) FROM... WHERE.... ) As [Second],
       dataff.CustomerID AS Person
FROM   [data].person dataff
JOIN [data].[Update] sparffa ON dataff.SDD = sparffa.SDD
WHERE  [Time] >= @Time                                                                  -- use the table alias for time and for termination.
AND Termination = 'S'

Open in new window


Just as ste5an has indicated above.
0
PortletPaulEE Topic AdvisorCommented:
is there anyway to combine them without null statement just show empty space?
Lets say there are 4 "persons" Fred, Barney, Wilma, Betty and this query:
   select person from persons produces the following result
person
Fred
Barney
Wilma
Betty
Now, if you add "first" and "second" subqueries to create this query:
   select
            person
           , (Select Count (*) From... Where... ) As First
           , (Select Count (*) From... Where.... ) As Second
        from persons
        order by person

The result will REPEAT the subqueries on each row
person      First   Second
Barney      1        2
Betty       1        2
Fred        1        2
Wilma       1        2

Open in new window

What it seems you are looking for is some (arbitrary) way of suppressing the "first" and "second" subquery results and this is not something SQL is really designed to do, but it can be achieved by using row_number() and case expressions
select
        Person
      , case when rn = 1 then (SELECT COUNT (*) FROM... WHERE ... ) end As [First]
      , case when rn = 1 then (SELECT COUNT (*) FROM... WHERE ... ) end As [Second]
from (
    select
          person
        , row_number() over(order by person) as rn
    from persons
     ) as sq
order by rn

Open in new window

Note, using the order is required in both the over clause and in the outer query
person      First   Second
Barney      1        2
Betty
Fred
Wilma

Open in new window


SELECT
        Person
      , case when rn = 1 then (SELECT COUNT (*) FROM... WHERE ... ) end As [First]
      , case when rn = 1 then (SELECT COUNT (*) FROM... WHERE ... ) end As [Second]
FROM (
    SELECT 
           dataff.CustomerID AS Person
          , row_number() over(order by dataff.CustomerID) as rn
    FROM   [data].person dataff
    JOIN [data].[Update] sparffa ON dataff.SDD = sparffa.SDD
    WHERE  [Time] >= @Time                                                                  -- use the table alias for time and for termination.
    AND Termination = 'S'
      ) AS sq
ORDER BY rn

Open in new window

0
Mark WillsTopic AdvisorCommented:
The same answer was given in another thread. Now recognising this thread.

I was using sys.triggers as an example
select something.*, case when rn=1 then (select count(*) from sys.triggers where 1=1) else NULL end as something_else, 
                   case when rn=1 then (select count(*) from sys.triggers where 1=1) else NULL end as Another_Thing
 
from (select row_number() over (order by [name]) as rn, [name] from sys.triggers where 1=1) as something

Open in new window


https://www.experts-exchange.com/questions/29087279/SQL-query-with-Multi-Select-column-and-multi-result.html#a42488768

Now if the problem was NULL but want blanks, then we have to change the CASE statement a little (and going back to the sys.triggers example)
select something.*, case when rn=1 then cast((select count(*) from sys.triggers where 1=1) as varchar(10)) else '' end as something_else, 
                   case when rn=1 then cast((select count(*) from sys.triggers where 1=1) as varchar(10)) else '' end as Another_Thing
 
from (select row_number() over (order by [name]) as rn, [name] from sys.triggers where 1=1) as something

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeTiAuthor Commented:
Hard to delicate the point here, but yes it would be the same solution Mark mentioned in the question before, here is the reason why i need query in subt query format is I need to export and analyzing the result in Powershell script, and in powershell I use:  select-object syntax for getting the subquery in the programming code it works like this:
$htmlout = $sqlArr | select-object 'Person', 'CustomerID', Date | convertto-html -head $a
Now those 'Person' and 'CustomerID' is from the SQL query ) As Person, )As CustomerID that was why I need the query inside the subquery form... Now I will try Marks way again, thanks for the help all.
0
ste5anSenior DeveloperCommented:
hmm, you should have started your post by saying this.. Why not using XML and maybe a XSLT transformation? Or directly creating the HTML in T-SQL?

e.g.

SELECT SCHEMA_NAME(T.schema_id) AS [td] ,
       NULL ,
       T.name AS [td] ,
       NULL ,
       T.object_id AS [td] ,
       NULL ,
       T.type_desc AS [td]
FROM   sys.tables T
FOR XML PATH('tr'), ROOT('table'), TYPE;

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.