June 28, 2011 at 8:01 am
Hey everyone, I am running this query & I have multiple records which I am trying to combine into 1, but given the query I am not sure on how to achieve that:
Query:
DECLARE
@user-id int,
@RandomCol varchar (100),
SET
@user-id = 12101
SET
@RandomCol = (Select RandomCol from TableA where UserID = @user-id AND RandomCol = 'YES')
IF @RandomCol = 'YES'
BEGIN
Select Total - (Select
datediff(d,StartDate,EndDate) - (select count(*))
from table_b
where table_b.datecol >= table_a.StartDate
and table_b.datecol <= table_a.EndDate) As TotalDays
from Table_A) AS TotalRemain from Table_B
Where UserID = @user-id
END
The error I receive is:
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.
I cannot sum the YES column because it's a varchar, any ideas?
June 28, 2011 at 8:09 am
June 28, 2011 at 8:12 am
calvo (6/28/2011)
DDL scripts, sample data, and expected output would be helpful.
Would be useful. But a quick look at your query suggests that this
Select RandomCol from TableA where UserID = @user-id AND RandomCol = 'YES'
is the culprit. Looks like it returns more than 1 result - hence the error.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 28, 2011 at 8:14 am
Personally I would try to do this using a join rather than a subquery but you also need to break it down to a one to one relationship. What relationship can be formed that would create the one to one case. In you example query you have no relationship from the subquery to the main query.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 28, 2011 at 8:19 am
Sample Data:
MS SQL
---------------------------------------------------------------
UserID | StartDate | EndDate | RandomColumn |
----------------------------------------------------------------
12101 2011-06-16 2011-06-30 YES
12101 2011-07-01 2011-07-15 YES
12101 2011-08-01 2011-08-11 YES
So the Output would reference a table with Holidays&Weekends & get a sum of all the business days between the date ranges in the the 3 rows where the Value is 'YES' & just return in one record. Such as
-----------------------------
UserID | TotalDays
-----------------------------
12101 45
But Phil, I know that looks like the culprit, As I said I cannot sum a varchar column so that's what makes it difficult. Yeah I don't know I am out of ideas lol
June 28, 2011 at 8:31 am
Since the @randomCol declared variable is never used and you're only performing an action if a user's field = 'yes',
instead of:
declare @randomCol varchar(100)
set @randomCol =
(Select RandomCol
from TableA
where UserID = @user-id
AND RandomCol = 'YES')
IF @randomCol = 'Yes'
BEGIN
...
END
Try:
IF EXISTS(select randomCol
from table_a
where userId = @user-id
and randomcol = 'yes')
BEGIN
...
END
It evaluates a user's [randomCol] field and performs an action if true.
June 28, 2011 at 8:42 am
Am I Replacing the IF Statement where it says
IF @RandomCol = 'YES'
or am I relacing the @RandomCol Varaible's SET select statement?
because I tried replacing it with the first IF statement & it gave me a subquery error again
June 28, 2011 at 8:58 am
Could you explain a bit more in detail what you are trying to do and give us table definitions for the tables you are using?
In your sample data, how do you get to 45 business days?
June 28, 2011 at 9:16 am
Rank1_nj (6/28/2011)
Am I Replacing the IF Statement where it saysIF @RandomCol = 'YES'
or am I relacing the @RandomCol Varaible's SET select statement?
because I tried replacing it with the first IF statement & it gave me a subquery error again
Remove these lines
@RandomCol varchar (100),
SET
@RandomCol = (Select RandomCol from TableA where UserID = @user-id AND RandomCol = 'YES')
Replace the IF @randomCol = 'YES' statement with the IF EXISTS statement. Use the same select statement inside the BEGIN/END.
June 28, 2011 at 9:30 am
It gave me a subquery error again, I took out "Select Total" where I have
Select Total - (select statement)
& just ran the select statement with datediff, I get totals of multiple records & not only where it says 'Yes' but totals of the records where it says 'No' and 'Other'
So it partially runs, just not with what I am trying to do.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply