R: SQL or Logical file equivalent for this code

[Finucci Domenico <Domenico.Finucci@xxxxxxxxxxxx>]

Try this:
SELECT
        case
                when                    locate(',', cmbana) <> 0
                then
                        substr(cmbana, 1, locate(',', cmbana)-1) !!
                        ' '    !! substr(cmbana, locate(',' , cmbana)+1, 40)
                else
                        cmbana !! '.'
                end
FROM YourFile

Sincerely
Domenico Finucci
Fiditalia , Milano, 02- 4301-2494


-----Messaggio originale-----
Da: Mark Allen [mailto:allenmark@nu-z.net]
Inviato: martedì 2 aprile 2002 15.21
A: midrange-l@midrange.com
Oggetto: SQL or Logical file equivalent for this code


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.