At 07:03 AM 19/05/2006, you wrote:

>I got an address table. Now I'd like to create a VIEW with a field
>with the contcatenated ADR_NAME and ADR_FIRSTNAME => ADR_FULLNAME. As
>far a I read in the documentation the string concatanation operator ¦¦
>is not supported in a SELECT statement.

Not true. You can define an output field with any valid expression.

>So I tried something like
>TST_FULLNAME COMPUTED BY (ADR_NAME¦¦" "¦¦ADR_FIRSTNAME) in the CREATE
>VIEW statement like in a CREATE TABLE statement. But unfortunatly this
>doesn't work either.

One, maybe two problems here with syntax. The
concatenation symbol is a pair of pipe symbols
(||), ascii 124. Maybe your sample is a
representation of asc 124, maybe not. The main
problem is that the string delimiter is not the
double quote (asc 34) but the single quote (asc
39). Make sure you are using an ascii editor for
defining your statements. Smart quotes and other
WP decorations in word processors are going to get you unstuck real fast.

>CREATE VIEW TEST (TST_NAME, TST_FIRSTNAME, TST_FULLNAME COMPUTED BY
>(ADR_NAME¦¦" "¦¦ADR_FIRSTNAME))
>AS
>SELECT ADR_NAME, ADR_FIRSTNAME
>FROM ADDRESS
>WHERE ADR_TYPE=4;
>
>Can somebody tell me how to create my VIEW with the concatenated
>ADR_FULLNAME? Thanks in advance.

Further problems here: this view definition
isn't valid (even with the expression syntax
corrected). A view definition has to have the
same number of input fields as those defined in
the output; and COMPUTED BY is valid only in a table definition.

Try this:

CREATE VIEW TEST (
TST_NAME,
TST_FIRSTNAME,
TST_FULLNAME)
AS
SELECT
ADR_NAME,
ADR_FIRSTNAME,
ADR_NAME || ' ' || ADR_FIRSTNAME
FROM ADDRESS
WHERE ADR_TYPE=4;

Furthermore, if either ADR_NAME or ADR_FIRSTNAME
is nullable, you will get null output in
TST_FULLNAME if either is null in the table. So,
decide what output you want in those cases and
modify the expression to suit, using COALESCE() or CASE() to handle the nulls.

./heLen