Nested select query help - 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)

Nested select query help

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-11-18 : 11:22:16

I have a table variable that I am joining to actual tables to return some data, which is inserted into a second table variable. The select statement is as follows:


INSERT INTO
  @tmpTable2
SELECT
  r.*
FROM
  @tmpTable1 r INNER JOIN
  (SELECT ... FROM .....)
  UNION
  SELECT ... FROM .....

I then declare two variables that use the data from the second table:


DECLARE @level NUMERIC(5,2)
DECLARE @allTrue BIT
SET @level = (SELECT ISNULL(sum((r.colName1/ 100) * r.colName2), 0) FROM @tmpTable2)
SET @allTrue = (SELECT CASE WHEN SUM(CASE WHEN r.colName3 = 0 THEN 1 ELSE 0 END) >0 THEN 0 ELSE 1 END FROM @tmpTable2)

I am wondering if there is a way to streamline the code so that I don't have to use the second table variable, but I can't get the syntax correct. Can anyone advise whether it is actually possible to run these 2 queries as a single statement??

Thank you.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-18 : 11:32:26

[code]DECLARE @level NUMERIC(5,2)
DECLARE @allTrue BIT

SELECT @level=ISNULL(sum((r.colName1/ 100) * r.colName2), 0)
FROM @tmpTable1 r INNER JOIN
(SELECT ... FROM .....
UNION
SELECT ... FROM .....)

SELECT @allTrue= CASE WHEN SUM(CASE WHEN r.colName3 = 0 THEN 1 ELSE 0 END) >0 THEN 0 ELSE 1 END
FROM
@tmpTable1 r INNER JOIN
(SELECT ... FROM .....
UNION
SELECT ... FROM .....)[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:33:18

[code]

DECLARE @level NUMERIC(5,2)
DECLARE @allTrue BIT
SELECT @level = ISNULL(sum((t.colName1/ 100) * t.colName2), 0),
@allTrue =CASE WHEN SUM(CASE WHEN t.colName3 = 0 THEN 1 ELSE 0 END) >0 THEN 0 ELSE 1 END
FROM (SELECT
r.*
FROM
@tmpTable1 r INNER JOIN
(SELECT ... FROM .....)
UNION
SELECT ... FROM .....)t
[/code]

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-11-18 : 11:34:53

Hi

Thanks for the reply. I see you are running the query twice, once to set each variable. As they are both the same query, can both values be set in the same operation?

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-11-18 : 11:35:43

Ah visakh sorry I didn't see your reply until after I posted. Let me try that one out...

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-11-18 : 11:52:31

quote:

Originally posted by visakh16


DECLARE @level NUMERIC(5,2)
DECLARE @allTrue BIT
SELECT @level =  ISNULL(sum((t.colName1/ 100) * t.colName2), 0), 
@allTrue =CASE WHEN SUM(CASE WHEN t.colName3 = 0 THEN 1 ELSE 0 END) >0 THEN 0 ELSE 1 END 
FROM (SELECT
  r.*
FROM
  @tmpTable1 r INNER JOIN
  (SELECT ... FROM .....)
  UNION
  SELECT ... FROM .....)t

That works wonderfully. Thank you very much visakh (for both times you've helped out today

)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:55:45

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources