Unanswered: select from temp table with where clause broken? help plz!
I'm trying to restrict (using a where clause) the rows selected from a temporary table. The number of rows returned is correct, each row is only "0". If I try to select from the temp table specifying top, or a trivial where clause ("where rnum=rnum"), it works fine, but that doesn't help me achieve what I want. Here is my sql statement:
select identity(int, 1, 1) as rnum, cgeneorf into #tb_result2 from
gene; select cgeneorf from #tb_result2 where rnum between 10 and 20
Please note, the above is a very simplified version of the actual query I would like to get working in the end, and I'm only using the one above to try to fix this problem.
The database is SqlServer 2000 running on Windows 2000 adv server. I'm using perl's DBI (DBD:ybase). I'm not getting this problem when I run using the SQLserver Query Analyser, but I need to have this in perl because I'm retrieving the data for a web page.
Below is the chunk of code that's supposed to retrieve the records. If you replace "cgeneorf" and "gene", with whatever non-identity column and it's associated table respectively, and execute the code (adding in all of the code to actually connect to your database of course), you should see the problem I'm getting.
Anyone have any idea what's wrong with this? I've been trying to figure this out since yesterday but haven't had any luck. I would really appreciate any help or suggestions.
Here's the code:
my $stmnt =
" select identity(int, 1, 1) as rnum, cgeneorf into #tb_result2 from \n" .
"gene; " .
"select cgeneorf from #tb_result2 where rnum between 10 and 20\n";
$sth = $dbh->prepare(
die "Unable for connect to server $DBI::errstr" unless $sth;
print "array, $DBI::errstr\t";