July 12, 2012 at 3:22 am
Hello
I am trying to run this query:
INSERT INTO #MyTable
VALUES ('Store','CustomerID','int','10','Sales','CustomerID','PK_Store_CustomerID',
(SELECT CONVERT(NVARCHAR(MAX),
(SELECT TOP(1) [CustomerID]
FROM [Sales].[Store]
ORDER BY [CustomerID] DESC)),
(SELECT TOP 1 [CustomerID]
FROM
(SELECT DISTINCT TOP 2 [CustomerID]
FROM [Sales].[Store]
ORDER BY [CustomerID] DESC) A
ORDER BY [CustomerID])),
(SELECT TOP 1 [CustomerID]
FROM
(SELECT DISTINCT TOP 3 [CustomerID]
FROM [Sales].[Store]
ORDER BY [CustomerID] DESC) A
ORDER BY [CustomerID]))
but it fails with the following error
'Msg 116, Level 16, State 1, Line 15
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'
Can you please help ?
Thank you
Kol
July 12, 2012 at 3:52 am
Hi all,
It works now, I just had to fix the parenthesis (brackets?)
Thanks !
July 12, 2012 at 7:30 am
I presume this was just an example to illustrate your point, but overall, what was it you were trying to do? Get the top 3 Custumors' IDs, ordered by CustumorID into three columns into one row in a table? Just wondering what the business reason would be for that? Surely that can't be it?
July 12, 2012 at 9:59 am
I think this is what you are looking for.
-- Sample data
DECLARE @salesStore TABLE([CustomerID] VARCHAR(20));
DECLARE @MyTable TABLE(val varchar(20));
INSERT INTO @salesStore VALUES ('xxx'),('yyy'),('zzz');
-- Insert statement
INSERT INTO @MyTable
SELECT * FROM
(
VALUES ('Store'),('CustomerID'),('int'),('10'),('Sales'),('CustomerID'),('PK_Store_CustomerID')
) x(c)
UNION ALL
SELECT CONVERT(NVARCHAR(MAX),
(
SELECT TOP(1) [CustomerID]
FROM @salesStore
ORDER BY [CustomerID] DESC)
) AS [CustomerID]
UNION ALL
SELECT TOP 1 [CustomerID]
FROM
(
SELECT TOP 1 [CustomerID]
FROM
(
SELECT DISTINCT TOP 2 [CustomerID]
FROM @salesStore
ORDER BY [CustomerID] DESC
) x
ORDER BY [CustomerID]
) AS [CustomerID]
UNION ALL
SELECT TOP 1 [CustomerID]
FROM
(
SELECT TOP 1 [CustomerID]
FROM
(
SELECT DISTINCT TOP 3 [CustomerID]
FROM @salesStore
ORDER BY [CustomerID] DESC
) x
ORDER BY [CustomerID]
) AS [CustomerID];
-- Resultset
SELECT * FROM @MyTable
-- Itzik Ben-Gan 2001
July 12, 2012 at 10:27 am
The whole series of subselects sure makes that far more complicated than it needs to be.
Not sure if what you posted produces what the OP wants but this produces the same as your code. It a bit less complicated. 🙂
-- Sample data
DECLARE @salesStore TABLE([CustomerID] VARCHAR(20));
DECLARE @MyTable TABLE(val varchar(20));
INSERT INTO @salesStore VALUES ('xxx'),('yyy'),('zzz');
-- Insert statement
INSERT INTO @MyTable
SELECT * FROM
(
VALUES ('Store'),('CustomerID'),('int'),('10'),('Sales'),('CustomerID'),('PK_Store_CustomerID')
) x(c)
UNION ALL
select CustomerID from
(
SELECT DISTINCT TOP 3 [CustomerID]
FROM @salesStore
ORDER BY [CustomerID] DESC
) x
-- Resultset
SELECT * FROM @MyTable
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2012 at 10:41 am
Sean Lange (7/12/2012)
The whole series of subselects sure makes that far more complicated than it needs to be.Not sure if what you posted produces what the OP wants but this produces the same as your code. It a bit less complicated. 🙂
-- Sample data
DECLARE @salesStore TABLE([CustomerID] VARCHAR(20));
DECLARE @MyTable TABLE(val varchar(20));
INSERT INTO @salesStore VALUES ('xxx'),('yyy'),('zzz');
-- Insert statement
INSERT INTO @MyTable
SELECT * FROM
(
VALUES ('Store'),('CustomerID'),('int'),('10'),('Sales'),('CustomerID'),('PK_Store_CustomerID')
) x(c)
UNION ALL
select CustomerID from
(
SELECT DISTINCT TOP 3 [CustomerID]
FROM @salesStore
ORDER BY [CustomerID] DESC
) x
-- Resultset
SELECT * FROM @MyTable
That is definately better. Nice.
-- Itzik Ben-Gan 2001
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply