Query in AS 400

[l***@hotmail.com]

Hi!

I am trying to accomplish something in a query on the AS400. I have
two fields:

LNAME which is the last name
FNAME which is the first name

how do i create these two as one

is LNAME is SMITH and FNAME is BOB

I would like to create it as SMITH, BOB

I used the || in define views and entered the following:

LNAME || '," || FNAME

but I get SMITH ,BOB

It is taking into consideration all of the spaces for the field
size... how do I over come this.

Any help would be greatly appreciated.

[Jonathan Ball]

Post by l***@hotmail.com
Hi!
I am trying to accomplish something in a query on the AS400. I have
LNAME which is the last name
FNAME which is the first name
how do i create these two as one
is LNAME is SMITH and FNAME is BOB
I would like to create it as SMITH, BOB
LNAME || '," || FNAME
but I get SMITH ,BOB
It is taking into consideration all of the spaces for the field
size... how do I over come this.
Any help would be greatly appreciated.

What kind of query? SQL? That one's easy:

rtrim(LNAME) || ', ' || FNAME

Note there's a space following the comma.


If you mean how to do this in a QUERY/400 query, I don't know a way to
trim trailing blanks.

[l***@hotmail.com]

Hi!
I am trying to accomplish something in a query on the AS400.  I have
LNAME   which is the last name
FNAME   which is the first name
how do i create these two as one
is LNAME is SMITH  and FNAME is BOB
I would like to create it as SMITH, BOB
LNAME || '," || FNAME
but I get SMITH            ,BOB
It is taking into consideration all of the spaces for the field
size...  how do I over come this.
Any help would be greatly appreciated.

   rtrim(LNAME) || ', ' || FNAME
Note there's a space following the comma.
If you mean how to do this in a QUERY/400 query, I don't know a way to
trim trailing blanks.- Hide quoted text -
- Show quoted text -

yes AS400 not SQL - it would be easier in SQL but I have to have this
executable for users on a AS400 enviornment.

Thanks!

[H***@sss-software.de]

Why not creating an SQL-View where the 2 fields are concated and let
the users access this view via Query/400?

Birgitta

[Colin]

Post by l***@hotmail.com
Hi!
I am trying to accomplish something in a query on the AS400. I have
LNAME which is the last name
FNAME which is the first name
how do i create these two as one
is LNAME is SMITH and FNAME is BOB
I would like to create it as SMITH, BOB
LNAME || '," || FNAME
but I get SMITH ,BOB
It is taking into consideration all of the spaces for the field
size... how do I over come this.
Any help would be greatly appreciated.

Query/400 has very limited capabilities for manipulating data and what you
want can't be done in a query. You can download the V4 Query/400 user
manual from http://publib.boulder.ibm.com/html/as400/online/V4R1PDF.HTM.