Solved

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

Posted on 2013-01-17
15
529 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
 

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 34

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 34

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 34

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now