Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003

    Exclamation 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(

    print "\n$stmnt\n";

    die "Unable for connect to server $DBI::errstr" unless $sth;
    print "array, $DBI::errstr\t";

    if($sth->execute) {
    my @dat = $sth->fetchrow #;
    ) {
    print "|@dat|\n";
    print "\nerror: $DBI::errstr\n";

  2. #2
    Join Date
    Jan 2003
    Geneva, Switzerland
    You have a syntax error in your SQL - you should not separate the two queries with the semi-colon. A space or carriage return between the two queries is all it takes.


  3. #3
    Join Date
    Sep 2003
    I tried changing the semi-colon to a space and carriage return and it's giving the same result.

    However, I've found that if I change the 2nd part of the query (the part after the semi-colon) to this:

    select * from #tb_result where rnum between (rnum - rnum + 10) and (rnum - rnum + 20)

    the query runs just fine, which is really odd. Any idea why this would be happening?


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts