Puzzled select statement...

RinoDM · February 17th, 2012, 05:32 PM

Hi to all...

I have a simple select statement that's puzzled me...

From query analyzer i'm accessing AS400 DB

SELECT ASNUM,ASNAME,BYRNUM,BYRNAM
FROM
AS4001.HARTMMS.MM750LIB.TBLBYR,AS4001.HARTMMS.MM75 0LIB.APSUPP
where TBLBYR.BYRNUM = APSUPP.BYRNUM

i'm getting an error
Server: Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "TBLBYR.BYRNUM" could not be bound.
Server: Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "APSUPP.BYRNUM" could not be bound.

can someone help please...

thanking you in advance...

Rino

irProject · February 18th, 2012, 01:02 AM

Hi there
try this one instead:

[code]
SELECT ASNUM,ASNAME,BYRNUM,BYRNAM
FROM
AS4001.HARTMMS.MM750LIB.TBLBYR x,AS4001.HARTMMS.MM750LIB.APSUPP y
where x.BYRNUM = y.BYRNUM
[code]

RinoDM · February 20th, 2012, 03:53 PM

WOW
thank you very much

it worked???

But why, isn't the same thing...

thank you in-advance

Rino

irProject · February 21st, 2012, 04:08 PM

Hi again

your statements will work fine but if completed:

Code:

SELECT ASNUM,ASNAME,BYRNUM,BYRNAM
FROM
AS4001.HARTMMS.MM750LIB.TBLBYR,AS4001.HARTMMS.MM75 0LIB.APSUPP
where AS4001.HARTMMS.MM750LIB.TBLBYR.BYRNUM = AS4001.HARTMMS.MM750LIB.APSUPP.BYRNUM

you have used two parts of full field address:
you had wrote:

Code:

where TBLBYR.BYRNUM = APSUPP.BYRNUM

instead of:

Code:

where AS4001.HARTMMS.MM750LIB.TBLBYR.BYRNUM = AS4001.HARTMMS.MM750LIB.APSUPP.BYRNUM

I just tried to assign an alias to long-path-fieldname

Cheers