""andycolson"" <andycolson@...> wrote in message
news:a545iq+npkk@......

> Seems my sql statement has a little error, but it does not change the
> error. The statement groups by remarks but does not include remarks
> in the return list. Even when remarks is in the return list however
> the error is the same.
>
> I also noted that this was kinda stupid to group by remarks sense
> they'll all be different. I changed the 'sum(hours)' to just 'hours'
> and removed the group by entirly. It works fine that way.
>
> Appears group by blob may be the problem??

Have you read the manuals? They state you can't sort by blobs and probably
say that you can't group by blobs. Anyway, since grouping is achieved
through sorting, grouping is implicitly banned. Furthermore, you can't index
a blob, so the only way for a group to work would be a raw sorting instead
of index walking.

Now, in practice, you can order by blobs, but the result is unmeaningful for
you. A blob_id is a quad. The high longint contains the table's id. The low
longint contains the slot id for the blob inside the table. If you use isql,
you will have this output:

SQL> select remarks from times;
REMARKS
=================
81:0
followed by the blob's contents if it's sub_type text (you can tell isql to
show blobs of another sub_type if you want). When you sort by this field,
you are sorting by an implementation detail, the blob_id. In the example
above, 81 is the table id in hex (128) and zero is the slot for this blob.
So, even though you can sort, the result is not meaningful to you. In the
future, you may be able to do this:

SQL> create view v(h,m) as select hours, substring(remarks from 1 for 50)
from times;
SQL> select sum(h) from v group by m;

But for now you will get a cryptic message:

SUM
=====================
Statement failed, SQLCODE = -901
feature is not supported
-BLOB and array data types are not supported for move operation

(I know, I know, every corner is full of bugs and the engine takes several
paths to do essentially the same operation on the same data type, so this
explains why it becomes confused here.) Let's make the thing more
inefficient until the engine gives up:

SQL> set term ^;
SQL> create procedure p returns(h numeric(15,2), m varchar(50)) as begin for
select h,m from v into :h, :m do suspend; end^
SQL> set term ;^
SQL> set plan;
SQL> select sum(h) from p group by m;
PLAN SORT ((V TIMES NATURAL))
SUM
=====================
2.00
11.55

This time the command succeeds. Indeed, the view can be skipped, simply
select from the table, using substring, inside the procedure.

Another option for Firebird is Neil McCalden's GROUP BY UDF feature. If you
use a udf that takes a blob and returns a string, then you can sort and
group by the blob field.

Several months ago, Ann asked me to write the code to compare two blobs,
doing a binary comparison. But I wanted to handle text blobs, charsets and
the like and ended up writing a flawed kludge. Fortunately, that code almost
never executes. You can use it for basic things like comparing a blob v/s a
literal string:

SQL> select empid, idate, substring(remarks from 1 for 15) from times where
remarks='blobblob';

PLAN (TIMES NATURAL)

EMPID IDATE SUBSTRING
============ =========== ===============
1 2001-10-10 blobblob

See, I didn't need to apply substring() to "remarks" to compare it to the
literal string. AFAIK, you can't do that in IB.

This is the part that isn't guaranteed to work, blob v/s blob, you have been
warned. I guess it will go out of sync as soon as one of the blobs hits the
end of its first segment and both blobs don't use the same segment size:

SQL> select t.empid, t2.empid, substring(t.remarks from 1 for 15) from times
t join times t2 on t. remarks=t2.remarks;

PLAN MERGE (SORT (T2 NATURAL),SORT (T NATURAL))

EMPID EMPID SUBSTRING
============ ============ ===============
1 1 blobblob
2 2 2blob

BTW, I don't know if the SQL standard has something to say... I wrote
t<dot><space>remarks
and can write also
t<space><dot>remarks
and our parser doesn't complain. Maybe it shouldn't complain here?

C. (Scratching head because the view can't be sorted directly.)
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing