Access & iSeries AS400 Tbl and Query Issues Error Code 5001

1,185 pts.
Tags:
Access Datasheet
AS/400
DB2
iSeries Access
I am in Access and I have (A) a passthrough queries=(INMSP300) and (B) table queries=(dbo_ECC_APP.OEM),I want to join. In the dbo_ECC_APP.OEM there are number identifiers that have no associate number were in INMSP300 there are both and they are associated. So I need to have all the number in the INMSP300 showing and only those in the dbo_ECC_APp.OEM matching. I am tying to join the table query and AS400/Acess query by using the Access passthrough query to join together to form a complete new query.

The table is {[em]dbo_ECC_APP.OEM] [/em]and the passthrough query is [[strong]INMSP300[/strong]]. When I try to run the query the error I code below>>

      [IBM][System i Access ODBC Drive][DB2 for i5/OS]sql5001. Column qualifier or table dbo_edd_ppp undefined. (#-5001).

  What does that mean? At the same time is the SQL statement below adequate for the above to work? Below is the statement.

[strong] Select

 

[/strong] INMSP300.M3OE# as "OEM_NO", INMSP300.M3OEC as "ComprsOE", INMSP300.M3PRD as "PN", INMSP300.M3CLS as "PrdLine", [strong]dbo_ECC_APP.OEM[/strong]_NO, dbo_ECC_APP.CARDONE_NO, dbo_ECC_APP.OE_FORMER2, dbo_ECC_APP.OE_REPLACED_BY, dbo_ECC_APP.ID_NO, dbo_ECC_APP.RELEASE [strong]From[/strong] RDB CARF1.INMSP300 INMSP300l LEFT join RDB dbo_ECC_APP dbo_ECC_APP, ComprsOE=OEM_N0 [strong]Where[/strong] INMSP300.M3CLS ='78' and INMSP300 is NULL;

Software/Hardware used:
Access 2003, AS400 iSeries, passthrough queries
ASKED: August 20, 2009  1:44 PM
UPDATED: August 20, 2009  5:11 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Select INMSP300.M3OE# as “OEM_NO”, INMSP300.M3OEC as “ComprsOE”, INMSP300.M3PRD as “PN”, INMSP300.M3CLS as “PrdLine”, dbo_ECC_APP.OEM[/strong]_NO, dbo_ECC_APP.CARDONE_NO, dbo_ECC_APP.OE_FORMER2, dbo_ECC_APP.OE_REPLACED_BY, dbo_ECC_APP.ID_NO, dbo_ECC_APP.RELEASE
From RDB CARF1.INMSP300 INMSP300l LEFT join RDB dbo_ECC_APP dbo_ECC_APP
, ComprsOE=OEM_N0 ————— I think this should be something like
ON ComprsOE=OEM_N0 ————– this …

Where INMSP300.M3CLS =’78′ and INMSP300 is NULL;

C

Do you get any text with the error message?
I would make a very simple select first, then add one item at a time …
Phil

Found the text up above
SQL thinks you are refering to: dbo_edd_ppp
I don’t see it in this code but since this is a query on a query ????

______________________________________

Below is the code that I get.

[IBM][System i Access ODBC Drive][DB2 for i5/OS]sql5001 – Column qualifier or table DBO_ECC_APP undefined. (#-5001)

What I did is I went into accees and the designed a link by joining the different queries together and allowed the software to develop the SQL statement and this is what it came up with.

SELECT dbo_ECC_APP.CARDONE_NO, dbo_ECC_APP.OEM_NO, [78IN0140wOE].M3PRD, [78IN0140wOE].M3PRD
FROM dbo_ECC_APP INNER JOIN 78IN0140wOE ON dbo_ECC_APP.OEM_NO = [78IN0140wOE].[M3OE#];

Why the are there [ ] and what does that do? Why is it only on the table being joined and not the feild?

—————–
Access will put [ ] around a name that would otherwise confuse it —
- In access you can name a column with a space in it like EMPLOYEE NUMBER to clarify that for SQL it gets
the [EMPLOYEE NUMBER] treatment. So our editor has done something here [78IN0140wOE].[M3OE#] — it’s saying [78IN0140wOE] is a name of a file or column that SQL might have a problem understanding (the wOE in that name probably isn’t really wOE and it starts with a digit so it encloses it in [].
It does the same for [M3OE#] — here it might be the # sign that gets Access a bit excited. As you said it works.
Phil

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following