Re: SQL or Logical file equivalent for this code

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

This can be coded directly into an SQL statement, or as an SQL user-defined
funciton, SQL-type. There is a POSITION (or POSSTR) function that is like
the SCAN - returns starting position of a search string (like ',') in a
source string (CMBANA)

This is available at least in v4r4.

Here's a select that returns both types in converted form

select case when posstr(CMBANA, ',') = 0 then
CMBANA else substr(CMBANA, posstr(CMBANA, ',') + 2,
length(strip(CMBANA)) - posstr(CMBANA, ',') - 1) ||
' ' || substr(CMBANA, 1, posstr(CMBANA, ',') - 1) end
from vern/posstr

This should update just the rows that hav ethe comma

update cstmst set cmbana = substr(CMBANA, posstr(CMBANA, ',') + 2,
length(strip(CMBANA)) - posstr(CMBANA, ',') - 1) ||
' ' || substr(CMBANA, 1, posstr(CMBANA, ',') - 1)
where posstr(CMBANA, ',') > 0

At 08:20 AM 4/2/02 -0500, you wrote:
>Our customer master file has one field for customer name.  For business'
>it is the Company name i.e.  John Smith Realty, for individuals its
>their name in the format  Last Name, First name.
>
>(Yeah I know, bad design but I am stuck with it)
>
>We use the following snippet of code to reverse the Last Name, First
>name in programs where we need to where CMBANA is the customer name
>field
>
>
>C                     CLEARADRA
>C*** IF COMMA EXIST REVERSE FIRST AND LAST NAME ...
>C           ','       SCAN CMBANA    X
>C                     ADD  1         X
>C           X         IFGT 1
>C                     SUBSTCMBANA:X  WRK32F 32 P
>C                     SUB  2         X
>C           X         SUBSTCMBANA    WRK32L 32 P
>C           WRK32F    CAT  WRK32L:1  CMBANA    P
>C                     ENDIF
>C/SPACE
>
>
>We do lots of queries to generate name and address files for various
>marketing and informational purposes.  I then end up running the final
>generated output file thru a program with the following code to "fix"
>the name field.
>
>Is there a way to accomplish the above using either SQL or in a logical
>file… or is there a better way than what I am doing now?
>
>
>Mark Allen
>I.S. Manager
>Wilkes Telephone & Electric
>A Dycom Company
>Phone: (706) 678-9565
>Email: allenmark@nu-z.net
>http://www.nu-z.net
>
>
>
>
>
>_______________________________________________
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@midrange.com
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>or email: MIDRANGE-L-request@midrange.com
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.