• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5510
  • Last Modified:

AS400 SQL - Updating records from one file with data from another file

Hi there, can anybody help me with an SQL problem i am having on our iseries?

I am trying to update the std cost of a range of parts.

The file is TSTT2F1/INP60, and the Stockroom is STRC60, the Company is NC and the Standard Cost is SCSU60.

I have created a file with the new prices (JAMES/NEWSTDCOST), and uploaded it to the AS400 (see sample data below)
CONO60      STRC60      ABCPART      STDCOST
NC      IN      3680      24.26
NC      IN      3870      27.13
NC      IN      3871      22.57

but I am not sure how to word the SQL statement to pick up all the different prices for each part. The parts may or may not exist in INP60 so i need to pick up the data for those that do.

The statement I have tried unsuccessfully is :
UPDATE TSTT2F1/INP60 SET SCSU60 = 'STDCOST' WHERE CONO60='NC' AND STRC60='IN' AND EXISTS (SELECT ABCPART FROM JAMES/NEWSTDCOST WHERE ABCPART=PNUM60 AND SCSU='STDCOST')

VALUE FOR COLUMN OR VARIABLE SCSU60 NOT COMPATIBLE.                

Hopefully you can help me with this?

Thanks

JAmes
0
ilikeulike
Asked:
ilikeulike
  • 4
  • 2
2 Solutions
 
momi_sabagCommented:
try

UPDATE TSTT2F1/INP60   t1
SET SCSU60 = (select STDCOST from FROM JAMES/NEWSTDCOST
                     WHERE ABCPART=t1.PNUM60)
WHERE CONO60='NC'
AND STRC60='IN'
AND EXISTS (SELECT ABCPART
                     FROM JAMES/NEWSTDCOST
                     WHERE ABCPART=t1.PNUM60)
0
 
ilikeulikeAuthor Commented:
Hi Momi_sabag
Thanks for your reply. I have tried that but get the error:
Token / was not valid. Valid tokens: ) UNION, with the cursor highligting the / on the "FROM JAMES/NEWSTDCOST"

Just a question, but what does the t1 mean?

Sorry to sound stupid?

Thanks

James
0
 
momi_sabagCommented:
t1 is just an alias for the table so that you could use it in the subquery
i'm not as as400 guy, so don't know what to tell you about the / problem

does this query works?
SELECT ABCPART
                     FROM JAMES/NEWSTDCOST
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
ilikeulikeAuthor Commented:
Yes, the select query worked fine.
0
 
ilikeulikeAuthor Commented:
I have retried as i had a problem in my code. I now get :
UPDATE tstt2f1/inp60 t1     SET SCSU60 = (SELECT STDCOST FROM james/
newstdcost  WHERE ABCPART = t1.PNUM60)                              
                                                                     
WHERE CONO60='NC' and STRC60='IN'                                    
                                                                     
AND EXISTS (SELECT ABCPART FROM james/newstdcost                    
WHERE ABCPART = t1.PNUM60)
 
Result of SELECT more than one row.  

Any thoughts?

Thanks

James
0
 
tliottaCommented:
SET SCSU60 = (SELECT STDCOST FROM james/newstdcost  WHERE ABCPART = t1.PNUM60)

If more than one row is being selected, it means that ABCPART is found more than once in james/newstdcost. In order to narrow it down, you'll need to make the WHERE clause more selective. You probably need to include both CONO60 and STRC60 so that you get the cost for that part only from that Company and that Stock Room.

And if that doesn't do it, you need to verify that there is in fact only one row for each Company/Stock Room/Part combination.

Tom
0
 
ilikeulikeAuthor Commented:
Hi Guys,  thanks for all your help. The solution worked a treat (thanks momi_sabag) once i had removed a duplicate entry from my data (thanks Tom)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now