Here is what I just tried. I get an error at the space after firstname.
update MemberNames
set username =
SUBSTRING( firstname 1 FOR 1 ) ||
SUBSTRING( lastname 1 FOR 7 );

jwc


-----Original Message-----
From: Alan McDonald [mailto:alan@...]
Sent: Saturday, December 21, 2002 10:25 PM
To: ib-support@yahoogroups.com
Subject: RE: [ib-support] SQL Error


you got several responses jack,
first
substring does not exist
you ned a UDF to use F_MID or ... from helen

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.

Alan

-----Original Message-----
From: Jack Cane [mailto:jwcane@...]
Sent: Sunday, 22 December 2002 2:20 PM
To: aaIB Support Group
Subject: FW: [ib-support] SQL Error


If this has been answered, I somehow missed the reply. Would very much
like
to get some help with the below problem.

tks,

jwc

-----Original Message-----
From: Jack Cane [mailto:jwcane@...]
Sent: Saturday, December 21, 2002 8:06 AM
To: ib-support@yahoogroups.com
Subject: [ib-support] SQL Error


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.

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

Happy holidays,

jwc



Yahoo! Groups Sponsor
ADVERTISEMENT




To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/




To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]