I'm trying to do the following using an ADODB.Connection object in ASP:
1) declare a temporary table,
2) select years into the table from another table,
3) use the temporary table to join with an existing table to get my results.
I've tried doing this in separate queries, which results in the second step complaing about not recognising the table.
I've also tried doing the whole lot in one query, which results in an empty record set.
The query I've got works in Query analyzer, so it's not that.
Help,
Graham.
Code:
RS1=DBConn.Execute(
"declare @yrs table(yr integer); insert into @yrs select distinct yr=datepart(year, reviewdate) from mediareview; select yr, num=count(*), av=avg(cast(stars as decimal(6,2))) from mediareview, @yrs where datepart(year,reviewdate)=yr and datepart(year, getdate())-yr<5 group by yr;"
;
1) declare a temporary table,
2) select years into the table from another table,
3) use the temporary table to join with an existing table to get my results.
I've tried doing this in separate queries, which results in the second step complaing about not recognising the table.
I've also tried doing the whole lot in one query, which results in an empty record set.
The query I've got works in Query analyzer, so it's not that.
Help,
Graham.
Code:
RS1=DBConn.Execute(
"declare @yrs table(yr integer); insert into @yrs select distinct yr=datepart(year, reviewdate) from mediareview; select yr, num=count(*), av=avg(cast(stars as decimal(6,2))) from mediareview, @yrs where datepart(year,reviewdate)=yr and datepart(year, getdate())-yr<5 group by yr;"
