distinct in sql causes an error

Post Reply
nduvnjak
Posts: 40
Joined: Wed Feb 06, 2008 3:45 pm

distinct in sql causes an error

Post by nduvnjak »

Hello,
I create the table from CL like this:
tsql "create table LUMthreads (forumid counter, threadlink varchar(250), useragent varchar(250), superproxy varchar(200), proxysession varchar(15), lastfetch date)"

then I access it from Vortex script. Currently the table has around 1200 records, so it's not that big.
When I try this SQL query:

<sql "select distinct forumid, useragent, superproxy, proxysession from LUMthreads where lastfetch < $timelimit order by lastfetch">
<$counts=($counts+1)>
</sql>

it returns series of errors like this:
<!-- 011 testkeepalive:10: Cannot insert 3632-byte value `...W....0...................Mo.............1535021033464.......' into B-tree (temp RAM DBF): Value too large in the function btinsert -->

apparently the "distinct" clause and grouping as many as 4 fields, is what cases some internal processes to exceed their limits. Without "distinct" the query works fine. Also if I group only by one field, like this:

<sql "select distinct forumid from LUMthreads
where lastfetch < $timelimit order by lastfetch">

I still get series of errors but different ones:
<!-- 100 testkeepalive:10: Could not find field lastfetch in the function Evaluate -->

My question is - is there some way I can setup the table so it can handle the above query?

thanks!
Nenad
nduvnjak
Posts: 40
Joined: Wed Feb 06, 2008 3:45 pm

distinct in sql causes an error

Post by nduvnjak »

sorry, forgot to mention the version of Texis is Windows, 7.03.1434496242 20150616.

and also that besides creating the table with above tsql command, I didn't do any other setup, no indexes, nothing.
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

distinct in sql causes an error

Post by John »

That will depend a little bit on if all the fields are needed to resolve unique rows, or what you are looking to get back. Since you might be collapsing multiple lastfetch values with the distinct it can't really sort by it. One possibility based on your second SQL:

select forumid, max(lastfetch) lf from LUMthreads where lastfetch < $timelimit group by forumid order by lf;

Or use min instead depending which lastfetch you want to use.

It looks like the problem with the first statement was that some of the text fields were long and the total record size was too big for the DISTINCT.
John Turnbull
Thunderstone Software
nduvnjak
Posts: 40
Joined: Wed Feb 06, 2008 3:45 pm

distinct in sql causes an error

Post by nduvnjak »

John,
yes... to big for distinct, so I reduced the fields just to forumid, which is sufficient to resolve unique rows.
(and I also removed the ORDER BY clause, because you're right, it's unavailable to be used for sort). In short, this works:
<sql "select distinct forumid from LUMthreads
where lastfetch < $timelimit">
however, I need the remaining information, namely
useragent, superproxy, proxysession
so how do I get those?
I need them for further processing, because the incrementing the counter shown above is just a dummy process, for the sake of the demo.
I know this may became a basic SQL tutorial question, but the fact is the limitation I hit with "too big distinct" prevents me to design the solution the only way I know. So the question may again be - is there a way to stretch those limits by some settings in the table or database itself?

thanks!
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

distinct in sql causes an error

Post by John »

The question then becomes, if there were multiple records that have the same forumid that are collapsed by the distinct, which record's values do you want out for user agent, super proxy etc?

Maybe you want something like:

<sql row "select distinct forumid from LUMthreads where lastfetch < $timelimit">
<sql max=1 "select useragent, superproxy, proxysession from LUMthreads where forumid=$forumid order by lastfetch">
</sql>
</sql>
John Turnbull
Thunderstone Software
nduvnjak
Posts: 40
Joined: Wed Feb 06, 2008 3:45 pm

distinct in sql causes an error

Post by nduvnjak »

yes, so I do need to use 2 queries after all... I was hoping to avoid that, but never mind, as long as it works and solves my problem.
thanks a lot John.
Post Reply