At 09:32 PM 20-12-02 -0500, you wrote:

>I am having trouble with the following update query on an IB table:
>
>update membernames
> set username =
> concat( substring( firstname, 1, 1 ),
> substring( lastname, 1, 7 ))
> where mbrnr = 1;
>
>This gives Error -104 at the comma after firstname.
>
>Any hints appreciated. I used Oracle's SQL reference for the concat syntax.
>I also tried the || operator.

Oracle SQL is not IB SQL!

IB doesn't have either concat(.) or substring(.) in its internal
language. (Firebird has substring(.) but its syntax is not the same as
Oracle's...)

You can declare the SUBSTR(.) user-defined function to your database from
the IB_UDF library and use it like this:

update membernames
set username =
SUBSTR(firstname, 1, 1) || SUBSTR(lastname, 1, 7)
where mbrnr = 1;

With Firebird, you would do

update membernames
set username =
SUBSTRING(firstname 1 FOR 1) || SUBSTRING(lastname 1 FOR 7)
where mbrnr = 1;

If either firstname or lastname in your expression is null, then the value
which is set will be null also. That won't be overly useful as a
username... Also, I seem to recall that that the InterBase version of that
UDF will return NULL if the second argument of SUBSTR(.) is longer than the
length of the first argument. I believe Claudio fixed this in the FB
version of ibudf.dll.

>If there is a better SQL reference for use with IB I would appreciate a
>pointer.

The IB Language Reference manual. While not perfect, it's exponentially
more useful for InterBase than the Oracle SQL Reference could be.

heLen