Solved

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

Posted on 2013-01-17
15
538 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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 35

Assisted Solution

by:Gary Patterson
Gary Patterson earned 200 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 300 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 35

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 35

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

832 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