RE: Problem with distinct selection and binary/blob values

 [email protected] wrote:
> 
> Hi!
> 
> I seem to experience a small problem with legacy SQL code I'm 
> trying to use.
> Synthetic stripped down test-example is as follows.
> 
> Imagine this small table:
> 
> create table test (id integer, binaryValue long byte)
> 
> You can leave it empty to test:
> 
> SELECT DISTINCT id FROM test WHERE id IS NOT NULL <works>
> SELECT DISTINCT id FROM test WHERE binaryValue IS NOT NULL 
> <doesn't work>
> 
> Any ideas around how to rewrite this to check for non null values for
> the blob but still maintain the distinct id. The result code 
> says, that this
> query is not supported with the type long byte. Changing the type of
> the column is an option, but are there any other suitable 
> blob typings?
> Any "split it up" suggestions?

Oops, this is a bug, the check for LONG-columns is too restrictive.
Will be fixed with the next version (PTS 1123716).
Thank you for reporting.

There is no chance to do it within one statement.
DECLARE c CURSOR FOR SELECT  id FROM test WHERE id IS NOT NULL
+
select distinct id from c
should do
(written in pure SQL, with some clients the naming of the first resultset looks
different, of course)

Elke
SAP Labs Berlin