Outer Join problems

This may or may not be due to my lack of knowledge on the use of outer join. I am getting the error:

Server: Msg 208, Level 16, State 1, Line 1

Invalid object name 'IncidentId'.

From my query:

SELECT Incident_Incidents.RelatedIncidentId,

Incident_Incidents.RelatedType,

Incident.Subject,

Incident.RecordId

From Incident_Incidents, Incident

left outer join IncidentId on Incident.IncidentId = Incident_Incidents.IncidentId

where Incident.IncidentId = 12838

Any ideas?

SELECT

Y.RelatedIncidentId,

X.RelatedType,

X.Subject,

X.RecordId

From

Incident_Incidents X

LEFT OUTER JOIN Incident Y

ON

X.IncidentId = Y.IncidentId

where X.IncidentId = 12838

|||Sorry not to clear on what you've got going on here, Tried running it with no luck|||

Here is a sample of some real code; I might of have mistaken some of your field name. It looks like your trying to left outer join on a column name instead of a table name, which will not work.

SELECT

P.iPLANID

FROM

PLANS P

INNER JOIN Units M

ON M.iPlanid = P.iPlanid

|||

my guess:

SELECT Incident_Incidents.RelatedIncidentId,

Incident_Incidents.RelatedType,

Incident.Subject,

Incident.RecordId

From Incident_Incidents

left outer join Incident on Incident.IncidentId = Incident_Incidents.RelatedIncidentId

where Incident.IncidentId = 12838

Outer Join in AS2005 cube

I have three tables for the 'Store' dimension...

Store City State

When I include the state name from the State table in the dimension and process the cube, I am missing many stores. Those stores missing don't have a state code in the city table. So, because the cube is generating an inner join between the three tables, I am missing some stores. I need to model an 'outer join' in the relationship between City and State tables. How can I do this in AS 2005?

You can create a named query (in the DataSourceView editor) to include the 3 tables with the wanted outer/inner joins (the named query is the "SELECT ... FROM ..." statement). Then build the 'Store' dimension on it.

Adrian Dumitrascu

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...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|||That certainly makes sense to me but produces a syntax error :mad:

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|||you're welcome

you can't say "and on"|||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 :S|||okay, here's the deal: re-write your other comma-delimited table joins as INNER JOINs

i bet the error goes away ;)|||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

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|||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|||You funnyno, 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:
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_keythis explains the error message -- "dim_person.person_key" could not be bound

dim_person hasn't been parsed as a table yet|||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.sql