At 02:56 AM 1/03/2005 +0000, you wrote:

>Hi all,
>
>I have a field with BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
>i want to union this field with a string 'Received to-date'.
>I have to cast both of the field to BLOB rite?
>How to do so?
>
>I tried
>SELECT
>FIELDA AS A
>...
>UNION
>SELECT
>'Received to-date' AS A
>....
>
>it gives me error data type unknown
>
>I tried
>SELECT
>CAST(FIELDA AS BLOB SUB_TYPE TEXT SEGMENT SIZE 80)AS A
>...
>UNION
>SELECT
>CAST('Received to-date' AS BLOB SUB_TYPE TEXT SEGMENT SIZE 80) AS A
>....
>
>it gives me error
>Statement failed, SQLCODE = -104
>
>invalid request BLR at offset 285
>-data operation not supported
>
>any suggestion?

Well, there is no CAST operation that will convert a string to a blob or
vice versa. There is also no "implicit conversion" except in one and only
one operation: an INSERT statement can take a string as input to a text
blob and it will be implicitly converted. In all other conditions, you
will need to *convert* one or the other, using a UDF.

Also note that the first SELECT statement in a union sets the data types
for the corresponding fields in the remaining SELECT statements, so what
you do with the first SELECT conditions what you have to do with the others.

-- in fbudf is string2blob, that takes a string as input and returns a text
blob.
-- in FreeUDFLib is f_BlobAsPChar that takes a text blob as input and
returns a null-terminated string

Which one you use depends on what you plan to do with the output...

Note that UDFs (or, more correctly, external functions) are *not* declared
to databases until you actually do so. If you are a first-timer with UDFs,
you'll have a little research to do to get set up for them.

./heLen