Re: R: SQL or Logical file equivalent for this code

[rob@xxxxxxxxx]

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
If this works, you could use this in a sql CREATE VIEW statement to create
a logical file.  Then your Query/400 can query this.

Doesn't DDS stand for Dead Data Script?

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




Finucci Domenico <Domenico.Finucci@Fiditalia.it>
Sent by: midrange-l-admin@midrange.com
04/02/2002 08:50 AM
Please respond to midrange-l


        To:     "'midrange-l@midrange.com'" <midrange-l@midrange.com>
        cc:
        Fax to:
        Subject:        R: SQL or Logical file equivalent for this code


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.
_______________________________________________
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.