Left joining issues.

[DarkWorlds]

Ok this is something very similar to what I am facing, but dumbed down. None of these columns or tables names exist in real life (Very paranoid company I work for, understandable though) but the fundamental problem I am having is like below.

Basically I know I could have done something similar to this is MS SQL (Or am I dreaming?). If I am right or wrong I need to know a way around this.

Obviously if you comment out the "CAL.WEEK_SINCE_2005" and "AND CUST.week_since_2005 = CAL.WEEK_SINCE_2005" it would work. But I really need it to display the date as well

I will be joining other statements to this. I am hoping on doing this in one select statement instead of creating multiple tables as I am now. All the other joined tables will follow a VERY similar layout to this.

I look forward to learning what I did wrong and how I can fix it.

select ORG.ORGANIZATION_NAME,
CUST.CUST_COUNT,
CAL.WEEK_SINCE_2005

FROM organization ORG,
calendar CAL
LEFT JOIN (


SELECT CAP.CURRENT_STORE,
CALEN.week_since_2005,
count(CAP.inactive_date) CUST_COUNT
FROM CUST_AGREE_PAST CAP,
calendar CALEN

WHERE CAP.active_date is not null
and CAP.inactive_code in ('T')
and CAP.inactive_date between '01-sep-07' and sysdate
and CAP.INACTIVE_DATE = CALEN.CALENDAR_DATE
and CAP.RSN_CODE_ID in (select rsn_code_id from reasons where title in ('FAIL', 'NO CALL'))

GROUP BY CAP.CURRENT_STORE,
CALEN.week_since_2005) CUST
ON PO.CURRENT_STORE = ORG.ORGANIZATION_NAME
AND CUST.week_since_2005 = CAL.WEEK_SINCE_2005

 

[SantaMufasa]

I look forward to learning what I did wrong and how I can fix it.

We, too, Dark, look forward to learning what went wrong, as well. <grin>


We cannot tell from your posting if your code is producing:[ul][li]Syntax error(s),[/li][li]Run-time error(s), or[/li][li]Logic error(s).[/li][/ul]If you can post either the computer-generated syntax or run-time error(s) or a description of the unwanted/unexpected results (i.e., "logic error"), we can, hopefully, give you the help you need.

Also, it would be very helpful to us if you can post SQL to CREATE some (bogus) tables, along with INSERT statements to produce (bogus) rows, then we can quickly simulate your environment. (You can probably use the "bogus" code for us to copy-and-paste the errors here that you are seeing.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DarkWorlds]

ORA-00904: "ORG"."ORGANIZATION_NAME": invalid identifier

Its there, because if I comment out the lines I said above it works like a charm. Is there no way to compair it to a second table?

 

[SantaMufasa]

Is there no way to compair it to a second table?

There will absolutely be ways to compare the tables, but first we must resolve the "invalid identifier" error.


It would be helpful if you could post a DESCRIBE of, at least, the "organization" table, but preferrably all the tables involved.

Secondly, you have this line of code:

...
ON          PO.CURRENT_STORE = ORG.ORGANIZATION_NAME
...

I cannot see the alias "PO" on any table (or in-line VIEW) from which you are SELECTing. Am I just missing its definition?

Looking forward to your next post.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DarkWorlds]

PO.CURRENT_STORE = ORG.ORGANIZATION_NAME

should be CAP.CURRENT_STORE = ORG.ORGANIZATION_NAME

Sorry

I am trying to come up with a demo and everything. But I have to do it on the side, I guess in the mean time I will just work with 5 other tables and one of those being a recursive table (every store have 52 weeks, well that time 3000+ stores = fun)

Heres something extreemly similar and yet it doesnt run too. Where is the problem?

select a.store_id as store
b.week as week_of_purchase
c.cust_delv_count as delevery_count
d.cust_item_count as number_items_purchased
from store a, cal b
left join (
select store,
date,
count(cust_delv) cust_delv_count
from cust_buy
group by store,
date) as c
on c.store = a.store_id
and c.date = b.date
left join (
select store,
date,
count(cust_item_buy) cust_item_count
from cust_buy
group by store,
date) as d
on d.store = a.store_id
and d.date = b.date

same error as last time but on a.store_id

 

[SantaMufasa]

If you are receiving an "invalid identifier" error on store.store_id, then please run the following and post the results:

select count(store_id) from store;

If the results are what you expected, then there must be a problem with some other "nearby" column in your code.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DarkWorlds]

Everything runs fine. The select without the left join works and the select in the join its self works. Everything as far as i can tell is there. All the table names exsist and all the colomns called are there with info.

 

[SantaMufasa]

Dark,

When you get time to provide the SQL CREATE/INSERT statements that your query needs, I'll commit to finding and posting a resolution for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

It seems like this SQL is mixing ANSI syntax with the older pre version 9 Oracle syntax. Can you stay with the ANSI syntax.

from store a, cal b
left join (


instead.
from store a
inner join cal b
on a.someforeignkey = b.someprimarykey
left join (

 

[Dagon]

I think cmmrfds is right. You can reproduce the problem with something like:

select t1.col1,t2.col2 from
(select 1 as col3 from dual) t3,
(select 1 as col1 from dual) t1
left join
(select 1 as col2 from dual) t2
on t1.col1=t2.col2
and t3.col3=t2.col2

 

[SantaMufasa]

Dagon,

Thanks for posting the simplified code that illustrates the issue.

The "invalid identifier" error occurs because of Oracle scoping. Identifiers in Oracle SQL and PL/SQL have a scope. That is, identifiers are visible only within certain boundaries of code.

In the excellent simplified example that you posted...

select t1.col1,t2.col2 from
(select 1 as col3 from dual) t3,
(select 1 as col1 from dual) t1
left join
(select 1 as col2 from dual) t2
on t1.col1=t2.col2
and t3.col3=t2.col2

...the scope for the "left join" begins with the table just prior to the "left join" (i.e., "t1") and ends with the "on" condition(s) just following the "left join". Therefore, because the definition of "t3" is outside the scope for the "left join", any identifiers from "t3" are invalid for use in the "left join" condition.

Additionally, a left outer joined table (in this case, "t2") can be left joined to, at most, one other table. In the example, above, and in your original "a,b,c,d" posting, the left-joined tables are trying to join to two other tables (in Dark's case: table c left joined to a and b; also table d left joined to a and b. In Dagon's simplification, t2 left joined to t1 and t3). Such is illegal.

In the past, when I have needed to left join one table to two or more other tables, I have been able to restructure the join using UNION (or UNION ALL):

select t1.col1,t2.col2
  from ((select 1 as col1 from dual) t1
           left join
        (select 1 as col2 from dual) t2
             on t1.col1=t2.col2)
union all
select t3.col3,t2.col2
  from ((select 1 as col3 from dual) t3
           left join
        (select 1 as col2 from dual) t2
             on t3.col3=t2.col2)
/

      COL1       COL2
---------- ----------
         1          1
         1          1

I hope that explaining this limitation helps to resolve your left-join need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]