Results 1 to 10 of 10

Thread: Out Join Syntax

  1. #1
    Join Date
    Mar 2005
    Location
    boulder
    Posts
    28

    Unanswered: Out Join Syntax

    Hello,

    I need to write and Ansi Outer Join that has 2 columns from the same table.

    I keep getting

    Server: Msg 4104, Level 16, State 1, Line 3
    The multi-part identifier "dim_person.person_key" could not be bound.

    here is what the code looks like. any help is appreciated.

    select ...

    from dim_day_of_year
    , dim_company
    , dim_currency
    , dim_account
    , dim_person
    , ods_conference
    left outer join fact_usage_alloc fua1 on ods_conference.conf_key=fua1.conf_key
    left outer join fact_usage_alloc fua2
    on dim_person.person_key=fua2.requestor_person_key

    where...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
      from ...
         , ods_conference
    left outer 
      join fact_usage_alloc fua 
        on fua.conf_key = ods_conference.conf_key
       and fua.requestor_person_key = dim_person.person_key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2005
    Location
    boulder
    Posts
    28

    syntax error

    That certainly makes sense to me but produces a syntax error

    left outer join fact_usage_alloc fua
    on fua.conf_key=ods_conference.conf_key
    and on fua.requestor_person_key=dim_person.person_key
    where fua.company_key = dim_company.company_key..............

    Thanks for the reply

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're welcome

    you can't say "and on"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2005
    Location
    boulder
    Posts
    28

    join

    hmmm. Still getting the bind error. I even cut and pasted your example in and I get the same thing. I didn't realize mssql was so particular

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, here's the deal: re-write your other comma-delimited table joins as INNER JOINs

    i bet the error goes away
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Mixing non ansi and ansi forms is just a bad idea

    Plus, unless I missing it, your outter join tables don't seem to be joing to anything

    Plus, you really should get into good formatting, it will make your like so much easier

    Code:
        SELECT ColumnList
          FROM dim_day_of_year	a
    INNER JOIN dim_company		b
    	ON b.col		= ?.col
    INNER JOIN dim_currency		c
    	ON c.col		= ?.col
    INNER JOIN dim_account		d
    	ON d.col		=?.col
    INNER JOIN dim_person		e
    	ON e.col		= ?.col
    INNER JOIN ods_conference	f
    	ON f.col		= ?.col
     LEFT JOIN fact_usage_alloc	g
    	ON e.conf_key		= f.conf_key			-- This one makes no sense
     LEFT JOIN fact_usage_alloc	h
    	ON d.person_ke		= g.requestor_person_key	-- Nor does this one
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    okay, here's the deal: re-write your other comma-delimited table joins as INNER JOINs

    i bet the error goes away
    You funny



    The message you have enetered is too short
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    You funny
    no, i was serious -- i bet the error goes away because of how the FROM clause is parsed

    the explicit JOINS are probably being interpreted first, before being combined with the comma-delimited tables

    sort of like * and / are done before + and -

    thus, in this local context:
    Code:
          ods_conference
    left outer 
      join fact_usage_alloc fua 
        on fua.conf_key = ods_conference.conf_key
       and fua.requestor_person_key = dim_person.person_key
    this explains the error message -- "dim_person.person_key" could not be bound

    dim_person hasn't been parsed as a table yet
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2005
    Location
    boulder
    Posts
    28

    Mssql

    Thanks for the effort guys. This was a quick test to see how easy it would be to convert a legacy system to MSSQL. I think we got a portion of the answer.


    Thanks Again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •