Solved

SCAN/REPLACE

Posted on 2008-09-30
13
846 Views
Last Modified: 2010-04-21
I have a SQLSTMTSTR that has the entire SQL script and is built dynamically in the SQLRPGLEW program.  Once the build is complete, I have to SCAN for the WILDCARD CHARS "???"  and replace with the field name EMSCEM in ALL occurances in the SQLSTMTSTR.  I have to do this because there are multiple tables to join and the SCHEMA name has to precede EACH table name, so it will point to the correct DB on the UNIX server.
0
Comment
Question by:bcloring08
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
13 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22605912

Hi bcloring08,

Is there a question here?


Kent
0
 

Author Comment

by:bcloring08
ID: 22606036
Yes, I need to be able to SCAN and REPLACE the "???"  in the SQL script and replace the "???" with  a field name EMSCEM that has the MDM DB name in there.

Here is the idea that I am trying. I don't know if it will process ALL occurances or just the first one though, since I can't connect to the DB yet to ry it out....

C                   Z-ADD     0             Position          3 0    
C                   eval      Position = %scan('???':SqlStmtStr)      
C                   If        Position <> 0                          
C                   eval      SqlStmtStr = %replace(EMSCEM:SqlStmtStr:
C                             position:1)                            
C                   endif                                            




i.e.
SPR_V (this is the MDM DB view) AL1 (is the table name Alias)
           FROM  ???.SPR_V AL1, ???.TIME_FSC_DY_V AL2, ???.CP_DLY_SLS_V AL6,
???.CP_INV_LCN_V AL8, ???.BSN_UN_PDT_A_V AL9, ???.PDT_CNSGN_IND_V AL10, ???.BSN_
UN_LCN_V AL11                                                                  
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22606180
What's your host O/S?
0
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 

Author Comment

by:bcloring08
ID: 22606386
HOST O/S      =   AS400 Db2-400  
TARGET O/S  =   Oracle MDM DB on Unix server
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22607238

If you can move the source to the unix box, changing it from there is almost trivial.

sed 's/\?\?\?/EMSCEM/g' oldfilename > newfilename



Kent
0
 

Author Comment

by:bcloring08
ID: 22607347
Kdo,

     Unfortunately, because this is an As400 BATCH job, moving the source to the Unix system is not an option.  

    The goal of this project is to connect to the Unix MDM DB, extract the inventory and sales information in the tables there, return to the AS400 with the SQL statement built, then I load it to the 852 EDI transaction set and send the information to the trading partners.  I think the only 2 pieces I am missing is CONNECTING to the UNIX from the iSeries and then once the SQL statement is built (SQLSTMTSTR)  then I have to replace the wildcard chars (???) with the Schema name in multiple locations in the script.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22607445

Dave's probably our most knowledgeable AS/400 geek, er guru.  Let's see if he can help.

I have no idea how you would connect to an Oracle database from the AS/400.  I don't even know if it can be done, but I suspect that it can.

If the data volume is modest, I'd consider firing up DB2 on your unix server and federating it to the AS/400 and the Oracle systems.  You can then run all of the SQL from the federated DB2 server and most of this will just go away.


Kent
0
 
LVL 27

Accepted Solution

by:
tliotta earned 125 total points
ID: 22609980
bcloring08:

This question should be in the RPG forum:

http://www.experts-exchange.com/Programming/Languages/Main-Frame/RPG/

However --

C                   eval      Position = %scan('???':SqlStmtStr)      
C                   If        Position <> 0                          
C                   eval      SqlStmtStr = %replace(EMSCEM:SqlStmtStr:
C                             position:1)                            
C                   endif                                            

That's a decent start. It will only replace a single occurrence, though, so some arranging is called for:

C                   eval      Position = %scan('???':SqlStmtStr)      
C                   If        Position <> 0                          
C                   dow       Position <> 0                          
C                   eval      SqlStmtStr = %replace(EMSCEM:SqlStmtStr:
C                             position:1)                            
C                   eval      Position = %scan('???':SqlStmtStr)      
C                   enddo
C                   endif

That's a little closer. Enclosing the inner statements in a dowhile-group and rechecking the %scan() will cause each subsequent occurrence to be found.

However...

C                   eval      SqlStmtStr = %replace(EMSCEM:SqlStmtStr:
C                             position:1)

...will be a problem because it explicitly replaces just a single [1] character and your search pattern is three characters [???] in length. Also, it's not clear what the data types are of EMSCEM and SqlStmtStr. If both are varying-length strings and the lengths are properly set and there's no likelihood of extending beyond the maximum length of SqlStmtStr, then simply changing [1] to [3] might work okay.

If these are fixed-length, however, then you _might_ have a problem when EMSCEM has trailing blanks. Perhaps use this instead:

C                   eval      SqlStmtStr = %replace(%trim(EMSCEM):SqlStmtStr:
C                             position:3)             <=== And remember the [3]

I think the %trim() function should work as the first parameter for %replace().

Tom
0
 

Author Closing Comment

by:bcloring08
ID: 31501545
Thank you that worked perfectly. Now all I have to figure out is how to connect to MDM DB from iSeries, which is in another question I posed to the experts here.  Thanks again everyone!
Bob Loring
0
 
LVL 27

Expert Comment

by:tliotta
ID: 22618612
Minor added note... Kent's mention of sed brings up regular-expressions and Qshell utilities. Those are accessible from ILE RPG, so _potentially_ useful. However, native bifs like %scan() and %replace() will perform better in almost every case. They're good to keep in mind for particularly tricky cases though.

Tom
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22618704
Hi Tom,

My apologies in forgetting about you earlier.  I hadn't seen you for a while and had tried to defer to Dave, forgetting that you too are quite the resource for AS/400 related items.

  :)


Kent
0
 
LVL 27

Expert Comment

by:tliotta
ID: 22619620
Kent:

No problem. Every once in a while, somebody has to work. I suspect that's where Dave is!

Tom
0
 

Author Comment

by:bcloring08
ID: 22623024
Thank you again Tom and Kent. I am just waiting for the EDW department to send me the JBDC Net drivers so I can connect from the iSeries to the Unix box.
Bob
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question