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.
| Author |
Topic |
|
snufse
Constraint Violating Yak Guru
469 Posts |
 Posted - 2010-08-05 : 11:27:12
|
I have a temp table that I am trying to update with a join to a db2/400 table, getting following error:Column qualifier or table F1 undefinedSET @SQLSTRING = 'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, ''update #TempTable set t1.quantity_budget = sum(f1.gbapcyr) from #Temptable t1 inner join VGIPRDDTA.F0902 as f1 on f1.gbmcu = t1.job_number and f1.gbctry = 20 and f1.gbfy = 10 and f1.gblt = ''''AU'''' and f1.gbobj = '''' '''' '')' |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-05 : 11:57:56
|
Did you perhaps mean something like:SET @SQLSTRING = 'update t1 set t1.quantity_budget = AS400_f1.SUM_gbapcyr from #Temptable t1 inner join OPENQUERY( AS400SRV_MSDASQL, ''SELECT f1.gbmcu, sum(f1.gbapcyr) AS SUM_gbapcyr FROM VGIPRDDTA.F0902 as f1WHERE f1.gbctry = 20 and f1.gbfy = 10 and f1.gblt = ''''AU'''' and f1.gbobj = '''' '''' '') AS AS400_f1 ON AS400_f1.gbmcu = t1.job_number' but even then I'm not sure that #TempTable will be in scope when you EXEC that @SQLSTRING (we normally use ##TEMP tables when doing things that relate to OPENQUERY() when using dynamic SQL via EXEC) |
 |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2010-08-05 : 14:41:36
|
| Seems to me this is simple...Looks like you are trying to use f1 before you declare it.sum(f1.gbapcyr) is being called before VGIPRDDTA.F0902 is scoped as f1. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-06 : 02:28:26
|
| revdnrdy: I think its because f1 is a local table and VGIPRDDTA.F0902 is on a remote AS400 box ... if both tables were on the same server the statement would be fine. |
|
|
|
snufse
Constraint Violating Yak Guru
469 Posts |
Posted - 2010-08-07 : 08:40:28
|
| I think Kristen is right, both tables need to be on the remote machine, in this case the AS400. I was able to work around this loading table from the AS400 to a temp table on the server and then do my query. Thank you guys. |
|
|
|
|
|
|
[Resolved] Syntax Error - Not able to solve