[Resolved] Update temp table from a db2/400 table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Update temp table from a db2/400 table

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-12-14 : 11:22:17

Have created a temp table and have already populated the table with employee_name and employee_id (from a previous select)

CREATE TABLE #Work_Table
(
employee_name varchar(100),
employee_id varchar(20),
employee_rate decimal(9,2),
)

Now I need to update the employee_rate in the temp table by reading a db2/400 table using the employee_id as key, I have
the code for getting the rate from the db2/400 file below but not sure how to put everything together.

SELECT ytphrt
FROM OPENQUERY(AS400SRV_MSOLEDB, 'SELECT * FROM vgixxx.f060116')
WHERE ytan8 = employee_id

ytphrt is the employee_rate and ytan8 is the employee_id from the db2/400 table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-14 : 13:20:43

Not sure if this will work, but give it a try:

UPDATE w
SET employee_rate = db2.ytphrt
FROM #Work_Table t
JOIN OPENQUERY(AS400SRV_MSOLEDB, 'SELECT * FROM vgixxx.f060116') db2
ON t.employee_id = db2.ytan8

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-12-15 : 09:04:29

That worked great. Thank you.

Now, one more thing that I need is to condition the the field yaphrt (which is the hourly rate) to be update ONLY if it falls within the authorized business units (from / to) for user running the query.

I have following code which updates a temp table. This is based upon user running the query and which business units user are authorized to see:

insert into	#Authority_Table
	         (
		       busunit_from,
		       busunit_to
	          )

select			msmcuf, msmcut
from			OPENQUERY(AS400SRV_MSOLEDB, 'SELECT * FROM vgisec.f0001')
where			msuser = @UserID and isnumeric(msmcuf)  <> 0 and isnumeric(msmcut)  <> 0

Now taking the code snippet:

update #Work_Table
set employee_rate = yaphrt / 1000, business_unit = yamcu
from #Work_Table 
join openquery(AS400SRV_MSOLEDB, 'SELECT * FROM vgiprdhrp.f060116') 
on #Work_Table.employee_id = yaan8

I need to update the hourly rate ONLY if user is authorized to see it like:

INNER JOIN	#Authority_Table AS at ON at.busunit_from <= yamcu  and at.busunit_to >= yamcu

Not sure how to add this to the code

Note: A user may have following authorization list:

User Id                       Business      Business
                              units from     unists to  

RGWGMEV                       350150        350150  
RGWGMEV                       350535        350650  
RGWGMEV                       360010        360015  
RGWGMEV                       360080        360087  
RGWGMEV                       640001        640999  
RGWGMEV                       650001        650999  
RGWGMEV                      3200000       3209999  
RGWGMEV                      3500000       3509999  
RGWGMEV                      3600000       3609999  
RGWGMEV                      6400000       6409999  
RGWGMEV                      6500000       6509999

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-12-15 : 11:59:38

Got it working this way:

update #Work_Table
set employee_rate = yaphrt / 1000, business_unit = yamcu
from #Work_Table 
join openquery(AS400SRV_MSOLEDB, 'SELECT * FROM vgiprdhrp.f060116') 
on #Work_Table.employee_id = yaan8
inner join #Authority_Table AS at ON at.busunit_from <= yamcu  and at.busunit_to >= yamcu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-15 : 12:34:50

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources