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

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

I bet you think we should never use the RPG cycle, too.  ;-)

At 10:02 AM 4/3/02 -0500, you wrote:
>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.
>
>
>
>_______________________________________________
>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.