October 22, 2009 at 8:19 am
Sure did. Same error.
UPDATE ASWTEST.S1012A5C.IC1480BFPD.XROHUB
SET XUBRNAM =
(SELECT XUBRNAM
FROM [DataWarehouse480].[dbo].[Temp_SalesRep] T1
WHERE T1.XUBCUNO = ASWTEST.S1012A5C.IC1480BFPD.XROHUB .XUBCUNO
AND EXISTS
(SELECT 1 FROM [DataWarehouse480].[dbo].[Temp_SalesRep] T1 WHERE T1.XUBCUNO = ASWTEST.S1012A5C.IC1480BFPD.XROHUB .XUBCUNO))
October 22, 2009 at 9:42 am
UPDATE ASWTEST.S1012A5C.IC1480BFPD.XROHUB
SET XUBRNAM =
(SELECT XUBRNAM
FROM [DataWarehouse480].[dbo].[Temp_SalesRep] T1
WHERE T1.XUBCUNO = ASWTEST.S1012A5C.IC1480BFPD.XROHUB .XUBCUNOAND EXISTS
(SELECT 1 FROM [DataWarehouse480].[dbo].[Temp_SalesRep] T1 WHERE T1.XUBCUNO = ASWTEST.S1012A5C.IC1480BFPD.XROHUB .XUBCUNO))
I don't know if the T1 alias makes a difference, but .XUBCUNO might be an issue.
I'd try Fully qualifying all the tables and columns, removing the T1.
Also work on just the select statement - no update / set - to make sure that will run.
Greg E
October 22, 2009 at 10:13 am
Tried the following SELECT based on your suggestion. Erroring out.
SELECT XUBRNAM
FROM [DataWarehouse480].[dbo].[Temp_SalesRep]
WHERE [DataWarehouse480].[dbo].[Temp_SalesRep].XUBCUNO = ASWTEST.S1012A5C.IC1480BFPD.XROHUB.XUBCUNO
AND EXISTS
(SELECT 1 FROM [DataWarehouse480].[dbo].[Temp_SalesRep] WHERE [DataWarehouse480].[dbo].[Temp_SalesRep].XUBCUNO = ASWTEST.S1012A5C.IC1480BFPD.XROHUB .XUBCUNO)
ERROR:
failed with the following error: "Column qualifier or table undefined. SQLSTATE: 42703, SQLCODE: -5001". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
October 22, 2009 at 10:24 am
BTW, I have removed the spaces between the table qualifier and column. Still same issue.
SELECT XUBRNAM
FROM [DataWarehouse480].[dbo].[Temp_SalesRep]
WHERE [DataWarehouse480].[dbo].[Temp_SalesRep].XUBCUNO = ASWTEST.S1012A5C.IC1480BFPD.XROHUB.XUBCUNO
AND EXISTS
(SELECT 1 FROM [DataWarehouse480].[dbo].[Temp_SalesRep] WHERE [DataWarehouse480].[dbo].[Temp_SalesRep].XUBCUNO = ASWTEST.S1012A5C.IC1480BFPD.XROHUB.XUBCUNO)
October 22, 2009 at 11:52 am
SELECT XUBRNAM
This is still not qualified.
One other thing I would try and verify -
that each query (outer and exists) returns results from each of the sources when run.
The field you are joining on would be a good one to specify a slect criteria with.
Greg E
October 22, 2009 at 12:32 pm
Thanks for your patience working with me. Its been a huge pain. I ran the two pieces seperately and still erroring out
SELECT [DataWarehouse480].[dbo].[Temp_SalesRep].XUBRNAM
FROM [DataWarehouse480].[dbo].[Temp_SalesRep]
WHERE [DataWarehouse480].[dbo].[Temp_SalesRep].XUBCUNO = ASWTEST.S1012A5C.IC1480BFPD.XROHUB.XUBCUNO
ERROR:failed with the following error: "Column qualifier or table undefined.
SELECT [DataWarehouse480].[dbo].[Temp_SalesRep].XUBCUNO FROM [DataWarehouse480].[dbo].[Temp_SalesRep] WHERE [DataWarehouse480].[dbo].[Temp_SalesRep].XUBCUNO = ASWTEST.S1012A5C.IC1480BFPD.XROHUB.XUBCUNO
ERROR:
Token was not valid. Valid tokens: . SQLSTATE: 42601, SQLCODE: -104". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
October 22, 2009 at 12:40 pm
From what I remember accessing an AS400 object can only be done so with a 4-part qualifier. You have 5 specified. You also either need to JOIN the tables or use an EXIST clause.
October 22, 2009 at 1:13 pm
ASWTEST.S1012A5C.IC1480BFPD.XROHUB.XUBCUNO
I think Randy is right.
This doesn't look right.
AS400 Name
DB2 instance name
Table name
Column name
Greg E
October 22, 2009 at 1:27 pm
ASWTEST is servername
S1012A5C is catalog name
IC1480BFPD is Library name
Then Table Name, Column Name.
BTW Librayname.ColumnName is attached.
When I run simple select statement, it runs with 5 qualified names...It doesn't run with 4 qualifers.
It seems like its giving problem in the join. Not sure if we can use ASWTEST.S1012A5CI.C1480BFPD.XROHUB.XUBCUNO direclty in the join since its not used above in the query.
October 22, 2009 at 1:45 pm
It seems like its giving problem in the join. Not sure if we can use ASWTEST.S1012A5CI.C1480BFPD.XROHUB.XUBCUNO direclty in the join since its not used above in the query.
Which is why I suggested where XUBCUNO = something when you split the queries.
In an iSeries driver, you can set your library list.
LIBRARY1 space LIBRARY2 etc.
Greg E
October 22, 2009 at 3:39 pm
In one way or the other I need to use qualifier. If I run on SSIS Execute SQL TASK (AS400 Connection), I don't need to specify ASWTEST.S1012A5C. I can just use Library.Tablename. But the problem is, the update is coming from Datawarehouse table which reside on different server (150-WBSQL-T-001). And this is causing problem. Any workaround on this?
My new update statement:
UPDATE IC1480BFPD.XROHUB
SET XUBRNAM = (SELECT XUBRNAM FROM [150-WBSQL-T-001].[DataWarehouse480].[dbo].[Temp_SalesRep] T1, IC1480BFPD.XROHUB.XUBCUNO T2 WHERE T1.XUBCUNO = T2.XUBCUNO)
WHERE EXISTS
(SELECT 1 FROM [150-WBSQL-T-001].[DataWarehouse480].[dbo].[Temp_SalesRep] T1, IC1480BFPD.XROHUB.XUBCUNO T2 WHERE T1.XUBCUNO = T2.XUBCUNO)
October 23, 2009 at 7:17 am
Any comments/suggestions guys?
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply