August 14, 2009 at 7:40 am
Hi All,
please, i need your help cuz i am stuck here..
i am trying to run a query and ultimateley inserting the result into a known table.
here is my code :
use db1
Declare @Report_Date VARCHAR(11);
SET @Report_Date = CONVERT(varchar,GETDATE()-1,101);
Declare @Grp_TTCount int;
SET @Grp_TTCount =
(
SELECT Top(10) CR_GP, COUNT(DISTINCT TID) AS TTCount
FROM table1.dbo.db1
WHERE (CONVERT(nvarchar, DATEADD(s, CREATE_DATE, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)), 101) = @Report_Date)
GROUP BY CR_GP
ORDER BY COUNT(DISTINCT TID) DESC
)
INSERT INTO table2 (Report_Date,CR_GP,Cr_Grp_TTCount)
VALUES (@Report_Date,@CR_GP,@Grp_TTCount);
here is the error i am getting :
Msg 116, Level 16, State 1, Line 24
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
*****
The Query runs successfully without the set and INSERT Statement...
as soon as i introduce the set or the insert statement..it fails..
any help will be appreciated..
thanks
August 14, 2009 at 7:53 am
SET @Grp_TTCount =
(
SELECT Top(10) CR_GP, COUNT(DISTINCT TID) AS TTCount
FROM table1.dbo.db1
WHERE (CONVERT(nvarchar, DATEADD(s, CREATE_DATE, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)), 101) = @Report_Date)
GROUP BY CR_GP
ORDER BY COUNT(DISTINCT TID) DESC
)
This code seems to try to assign to a variable something that comes from a query, but I see two errors:
1) You are selecting two columns: which one should go into the variable?
2) The query returns 10 rows: which one should go into the variable?
-- Gianluca Sartori
August 14, 2009 at 7:56 am
I'm just guessing, maybe you wanto to do this instead:
SELECT Top(1) @Grp_TTCount = COUNT(DISTINCT TID) AS TTCount
FROM table1.dbo.db1
WHERE (CONVERT(nvarchar, DATEADD(s, CREATE_DATE, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)), 101) = @Report_Date)
AND CR_GP = @someVauleToFilterFor
GROUP BY CR_GP
ORDER BY COUNT(DISTINCT TID) DESC
-- Gianluca Sartori
August 14, 2009 at 7:58 am
Try to modify your code like this:
Declare @Report_Date VARCHAR(11);
SET @Report_Date = CONVERT(varchar,GETDATE()-1,101);
INSERT INTO table2 (Report_Date,CR_GP,Cr_Grp_TTCount)
SELECT Top(10) @Report_Date, CR_GP, COUNT(DISTINCT TID) AS TTCount
FROM table1.dbo.db1
WHERE (CONVERT(nvarchar, DATEADD(s, CREATE_DATE, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)), 101) = @Report_Date)
Your mistake was that in this line:
SET @Grp_TTCount =
(
SELECT Top(10) CR_GP, COUNT(DISTINCT TID) AS TTCount
FROM table1.dbo.db1
WHERE (CONVERT(nvarchar, DATEADD(s, CREATE_DATE, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)), 101) = @Report_Date)
You are trying to set a value into an integer variable, but you supply 10 rows instead of one value. Try to think about it like trying to set a variable in this way:
SET @Grp_TTcount = ‘my first groupt’, 3
‘My other group’, 4
‘My last group 8
Can you tell which value should be in the variable that can only hold one integer value?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 14, 2009 at 8:21 am
Hi,
thank you all for your replies..were very helpfull..
Adi special thanks..that what i was looking for..cuz ultimately i do not need the variable..
I know that we can not assign multiple vallues to a single variable ,,,but do you know how can i assign those values to an array ?
thanks
August 14, 2009 at 9:09 am
There is no such thing as array in SQL. The closest thing can be a temporary table or a table variable.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 14, 2009 at 9:33 am
Great help and Great thanks for you all.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply