?
Solved

AS400 SQL - Updating a file based on contents of a driver file

Posted on 2013-01-17
15
Medium Priority
?
552 Views
Last Modified: 2013-01-21
Hi All

I'm trying to generate 2 SQL strings to run in an AS400 STRSQL command. First one is to update existing records based on the contents of a driver file and the second one is to actually insert new records based on a driver file. I've always struggled with the correct syntax and really want the procedures to be very resource efficient (There's a lot of records!).

So <MASTERFILE1> holds records which need to be updated with field data contained in <DRIVER1>. An example of file format for both files would be:

Field 1 (Key field)
Field 2 (Key field)
Field 3 (Key field)
Field 4 (Key field)
Field 5 (Key field)
Field 6 (data to update)
Field 7 (data to update)

If you were tackling this, would you simply say:

UPDATE MASTERFILE1 SET Field6=DRIVER1.Field6 WHERE ...... i got lost at this bit :(


The second query is simply to insert all records from DRIVER2 file (Same formats as before) into MASTERFILE1. I'm really unsure about how to even start on this.

My knowledge of SQL is basic in terms of, I can do select statements, nested selects etc but when I need to update records based on others... i'm lost :(

Any pointers?

James
0
Comment
Question by:Delerium1978
  • 5
  • 4
  • 3
  • +1
15 Comments
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38786935
Not a problem ... just provide simple syntax (or plain english) what conditions you want in where clause

UPDATE MASTERFILE1
SET Field6=DRIVER1.Field6, Field7=DRIVER1.Field7
WHERE ......
0
 

Author Comment

by:Delerium1978
ID: 38786949
Hi Yousaf, Not sure I follow.

For every record in the DRIVER1 file, I need to update the corresponding record in the MASTERFILE1.

In query 2, for every record in DRIVER2 file, i need to insert a record into MASTERFILE1.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 38787136
An example of file format for both files would be...

I don't understand the format. Is each line a separate record in the driver file? Is it always FIELD6 and FIELD7 that will be updated?

Tom
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Delerium1978
ID: 38787325
Ah ok, I was trying to generalise it hoping it would make it easier.

MASTERFILE1

EOCICD
EOB6CD
EOBDCD
EOBUCD
EOBODT
EOPNNB
EOA5VA

DRIVER1

EOCICD
EOB6CD
EOBDCD
EOBUCD
EOBODT
EOPNNB
EOA5VA

DRIVER2

EOCICD
EOB6CD
EOBDCD
EOBUCD
EOBODT
EOPNNB
EOA5VA

The first 6 fields in each file identify it as a unique record. The last field is what needs to be amended in the first query. DRIVER2 contains records we just want to insert directly into MASTERFILE1.

Is this any clearer?

James
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38787680
So what you mean to say is that update field EOA5VA of masterfile1 from EOA5VA field of driver1 where the following fields match for both tables

EOCICD
EOB6CD
EOBDCD
EOBUCD
EOBODT
EOPNNB
0
 
LVL 36

Assisted Solution

by:Gary Patterson
Gary Patterson earned 400 total points
ID: 38787700
-- Will only work if first 5 key fields define a unique key.
update masterfile set fld6 =
(select d1.fld6 from masterfile m join driver1 d on
   m.fld1=d.fld1 and
   ...
   m.fld5=d.fld5)

-- Assuming file layouts match exactly
insert into masterfile (select * from driver2)

- Gary Patterson
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38787711
if thats the case then you can try the follwoing code

UPDATE 
	m
SET
	m.EOA5VA = d1.EOA5VA
FROM
	masterfile1 m
	INNER JOIN driver1
		ON m.EOCICD = d.EOCICD
			m.EOB6CD = d.EOB6CD
			m.EOBDCD = d.EOBDCD
			m.EOBUCD = d.EOBUCD
			m.EOBODT = d.EOBODT
			m.EOPNNB = d.EOPNNB

Open in new window

0
 
LVL 5

Accepted Solution

by:
RehanYousaf earned 600 total points
ID: 38787730
and for the sencod request you can try the following code

INSERT INTO 
	masterfile1
SELECT
	*
FROM
	driver2

Open in new window

0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38787744
sorry forgot to put and in join condition

UPDATE 
	m
SET
	m.EOA5VA = d1.EOA5VA
FROM
	masterfile1 m
	INNER JOIN driver1
		ON m.EOCICD = d.EOCICD
			AND m.EOB6CD = d.EOB6CD
			AND m.EOBDCD = d.EOBDCD
			AND m.EOBUCD = d.EOBUCD
			AND m.EOBODT = d.EOBODT
			AND m.EOPNNB = d.EOPNNB

Open in new window

0
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 38788002
-- That's not right.  This should do it for the first one.

update masterfile m set fld6 =
coalesce((select d1.fld6 from driver1 d1 where
   m.fld1=d.fld1 and
   ...
   m.fld5=d.fld5),m.fld6)

For every row in masterfile, run the SELECT query.   If there is a matching row, the SELECT will return d1.fld6.  If not, it will return NULL.

The coalesce statement returns the first non-null value in the list.  SO if the SELECT returns a value, that is what gets SET to m.fld6.  If the SELECT return null, then the current value (m.fld6) is assigned.

- Gary Patterson
0
 

Author Comment

by:Delerium1978
ID: 38792292
Hi Guys, thanks for the solutions, im in the process of getting a dataset put on our AS400 for me to test a few of these solutions against

Gary - the coalesce function, am i right in assuming this is only needed if there are null values in the join? The statement you've written looks like it is going through the entire masterfile and for each record, testing whether this is in the driver file, if not updating it with its current value, if it is, updating with new value. This seems a very resource intensive approach?

Yousaf - your approach looks to be whats desired as its only going to process those that exactly join, but let me confirm. AS400 SQL isnt full SQL so lets see if it accepts the parameters.

Thanks

James
0
 
LVL 27

Expert Comment

by:tliotta
ID: 38792769
What is the OS release on your AS/400?

Tom
0
 

Author Comment

by:Delerium1978
ID: 38792916
@Tom - how can i tell that?
0
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 38793668
FWIW, I actually created the tables and tested that last query I sent you.  It looks like it works.

Coalesce is used in the query I supplied to allow you to set a default value in place of a null.  In this case, the default value is the current value - effectively saying "no change".  

The important thing to understand is that the subquery is repeatedly "called" and executed over an over - one time for each row in the outside query.

So rows that have a "match" between the master and driver table will return a valid value: driver2.fld6.

But rows that don't have a match will return null, so you have to handle the null.  that's why the coalesce function is in there.

So if you ever have the case where there is a "no match" condition, you need the COALESCE.  It is there to handle the "no match found" condition.

Hope that helps.

- Gary
0
 
LVL 27

Expert Comment

by:tliotta
ID: 38795417
The OS release helps us know how new (or old) the DB2 release is. Each release added features, so we'll know what queries and formats are available.

Using iSeries Navigator, you can right-click a Connection icon and look at Properties.
Using a telnet session, you can run the WRKLICINF command and look at the first listed License Term value. Or you can run DSPDTAARA QSS1MRI and take the first six characters from the displayed value.

There are other ways, but those are usually easiest.

Tom
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 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