July 26, 2004 at 4:31 am
Need to do some processing with different queries and get a single resultset out of a view. Since temp tables are not allowed in Views...Can someone suggest some way to overcome this...
Insert into #temp () Select Col1, Col2, Col3 from T1
Insert into #temp () Select Col1, Col2, Datediff(HH, Col5, Col6) From T1
Select Col1, Col2, Sum(Col3) From #temp group by Col1, Col2
This is what i want...can someone suggest how i can achieve this in a view.
Cheers!
Arvind
July 26, 2004 at 5:11 am
Create 2 different views. 1st to provide the hours difference and the 2nd to pull the information from the 1st view
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 26, 2004 at 7:21 am
Select Col1, Col2, Sum(Col3)
From
(
Select Col1, Col2, Col3 from T1
UNION
Select Col1, Col2, Datediff(HH, Col5, Col6) as COL3 From T1
)
group by Col1, Col2
i think that should do it
MVDBA
July 26, 2004 at 7:37 am
Thx for the quick response both of you. Good one mike.....I totally lost my plot today and needed some help desperately.
Cheers m8!
Arvind
July 26, 2004 at 7:43 am
just thinking ahead - you may have to use OPTION (MAXDOP,1) in there somewhere - it may get a bit confused with the union on the same table.
not sure whether you'd have to do it like this
Select Col1, Col2, Sum(Col3)
From
(
Select Col1, Col2, Col3 from T1
UNION
Select Col1, Col2, Datediff(HH, Col5, Col6) as COL3 From T1
OPTION (MAXDOP,1)
)
group by Col1, Col2
or
Select Col1, Col2, Sum(Col3)
From
(
Select Col1, Col2, Col3 from T1
UNION
Select Col1, Col2, Datediff(HH, Col5, Col6) as COL3 From T1
)
group by Col1, Col2 OPTION (MAXDOP,1)
i think the first.
i've had this type of query before, and if you have multiple processors you'll get a locking error where the multiple proessors split it up and attack the 2 parts of the union at the same time.
MVDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply