Hi,

I am getting the above error when I try and run the query below on
bb.tab_urn. However, removing this group by then gives the same error,
but at bb.transaction_date. Unless I have missed something completely,
I am certain I have defined everything correctly. Thoughts? Any help
would be very much appreciated!

Cheers

Tim

select com.name
, curr.quantity
, prev.quantity
from ( select count(*) as quantity
, a.tab_urn
from v_rep_vehicle_check_3m_trans a
where a.CLASS_CODE in ('TDCP', 'TDCC')
and a.TRANSACTION_DATE between
to_date('2007-01-01000000','YYYY-MM-DDHH24MISS')
and
to_date('2007-01-31235929','YYYY-MM-DDHH24MISS')
and a.tab_urn not in (0,10)
group by a.tab_urn) curr
full outer join
( select count(*) as quantity
, bb.tab_urn
from v_rep_vehicle_check_3m_trans bb
where bb.CLASS_CODE in ('TDCP', 'TDCC')
and bb.transaction_date between
add_months(to_date('2007-01-01000000','YYYY-MM-DDHH24MISS'),-1)
and
add_months(to_date('2007-01-31235929','YYYY-MM-DDHH24MISS'),-1)
and bb.tab_urn not in (0,10)
group by bb.TAB_URN) prev
on prev.tab_urn = curr.tab_urn
, rep_curr_company com
where curr.tab_urn = com.tab_urn
OR prev.tab_urn = com.tab_urn
order by
com.name;

On Feb 20, 8:33 am, timothy.h...@gmail.com wrote:
> Hi,
>
> I am getting the above error when I try and run the query below on
> bb.tab_urn. However, removing this group by then gives the same error,
> but at bb.transaction_date. Unless I have missed something completely,
> I am certain I have defined everything correctly. Thoughts? Any help
> would be very much appreciated!
>
> Cheers
>
> Tim
>
> select com.name
> , curr.quantity
> , prev.quantity
> from ( select count(*) as quantity
> , a.tab_urn
> from v_rep_vehicle_check_3m_trans a
> where a.CLASS_CODE in ('TDCP', 'TDCC')
> and a.TRANSACTION_DATE between
> to_date('2007-01-01000000','YYYY-MM-DDHH24MISS')
> and
> to_date('2007-01-31235929','YYYY-MM-DDHH24MISS')
> and a.tab_urn not in (0,10)
> group by a.tab_urn) curr
> full outer join
> ( select count(*) as quantity
> , bb.tab_urn
> from v_rep_vehicle_check_3m_trans bb
> where bb.CLASS_CODE in ('TDCP', 'TDCC')
> and bb.transaction_date between
> add_months(to_date('2007-01-01000000','YYYY-MM-DDHH24MISS'),-1)
> and
> add_months(to_date('2007-01-31235929','YYYY-MM-DDHH24MISS'),-1)
> and bb.tab_urn not in (0,10)
> group by bb.TAB_URN) prev
> on prev.tab_urn = curr.tab_urn
> , rep_curr_company com
> where curr.tab_urn = com.tab_urn
> OR prev.tab_urn = com.tab_urn
> order by
> com.name;

I am not the most observant person so naturally I did not spot the
error. But what version of Oracle is this?

Try changing quantity to a unique name every where it appears in an
inline view and prefix the one or two columns you did not prefix. If
doin this does not identify and fix the problem I suggest rewriting
the query to use traditional syntax.

HTH -- Mark D Powell --

On 20 Feb, 13:47, "Mark D Powell" <Mark.Pow...@eds.com> wrote:
> On Feb 20, 8:33 am, timothy.h...@gmail.com wrote:
>
>
>
> > Hi,
>
> > I am getting the above error when I try and run the query below on
> > bb.tab_urn. However, removing this group by then gives the same error,
> > but at bb.transaction_date. Unless I have missed something completely,
> > I am certain I have defined everything correctly. Thoughts? Any help
> > would be very much appreciated!
>
> > Cheers
>
> > Tim
>
> > select com.name
> > , curr.quantity
> > , prev.quantity
> > from ( select count(*) as quantity
> > , a.tab_urn
> > from v_rep_vehicle_check_3m_trans a
> > where a.CLASS_CODE in ('TDCP', 'TDCC')
> > and a.TRANSACTION_DATE between
> > to_date('2007-01-01000000','YYYY-MM-DDHH24MISS')
> > and
> > to_date('2007-01-31235929','YYYY-MM-DDHH24MISS')
> > and a.tab_urn not in (0,10)
> > group by a.tab_urn) curr
> > full outer join
> > ( select count(*) as quantity
> > , bb.tab_urn
> > from v_rep_vehicle_check_3m_trans bb
> > where bb.CLASS_CODE in ('TDCP', 'TDCC')
> > and bb.transaction_date between
> > add_months(to_date('2007-01-01000000','YYYY-MM-DDHH24MISS'),-1)
> > and
> > add_months(to_date('2007-01-31235929','YYYY-MM-DDHH24MISS'),-1)
> > and bb.tab_urn not in (0,10)
> > group by bb.TAB_URN) prev
> > on prev.tab_urn = curr.tab_urn
> > , rep_curr_company com
> > where curr.tab_urn = com.tab_urn
> > OR prev.tab_urn = com.tab_urn
> > order by
> > com.name;
>
> I am not the most observant person so naturally I did not spot the
> error. But what version of Oracle is this?
>
> Try changing quantity to a unique name every where it appears in an
> inline view and prefix the one or two columns you did not prefix. If
> doin this does not identify and fix the problem I suggest rewriting
> the query to use traditional syntax.
>
> HTH -- Mark D Powell --

Hi Mark,

Thanks for your quick reply. The version I am using is Oracle 10g. I
have tried what you suggested, but no joy. You say traditional syntax
- could you give me an example? Whilst I can use SQL to a certain
degree, I don't think I am as competent as I would like (illustrated
by my query I think!) So any help anyone can offer would be great!

Cheers

Tim

On Feb 20, 2:07 pm, timothy.h...@gmail.com wrote:
> On 20 Feb, 13:47, "Mark D Powell" <Mark.Pow...@eds.com> wrote:
>
>
>
> > On Feb 20, 8:33 am, timothy.h...@gmail.com wrote:
>
> > > Hi,
>
> > > I am getting the above error when I try and run the query below on
> > > bb.tab_urn. However, removing this group by then gives the same error,
> > > but at bb.transaction_date. Unless I have missed something completely,
> > > I am certain I have defined everything correctly. Thoughts? Any help
> > > would be very much appreciated!
>
> > > Cheers
>
> > > Tim
>
> > > select com.name
> > > , curr.quantity
> > > , prev.quantity
> > > from ( select count(*) as quantity
> > > , a.tab_urn
> > > from v_rep_vehicle_check_3m_trans a
> > > where a.CLASS_CODE in ('TDCP', 'TDCC')
> > > and a.TRANSACTION_DATE between
> > > to_date('2007-01-01000000','YYYY-MM-DDHH24MISS')
> > > and
> > > to_date('2007-01-31235929','YYYY-MM-DDHH24MISS')
> > > and a.tab_urn not in (0,10)
> > > group by a.tab_urn) curr
> > > full outer join
> > > ( select count(*) as quantity
> > > , bb.tab_urn
> > > from v_rep_vehicle_check_3m_trans bb
> > > where bb.CLASS_CODE in ('TDCP', 'TDCC')
> > > and bb.transaction_date between
> > > add_months(to_date('2007-01-01000000','YYYY-MM-DDHH24MISS'),-1)
> > > and
> > > add_months(to_date('2007-01-31235929','YYYY-MM-DDHH24MISS'),-1)
> > > and bb.tab_urn not in (0,10)
> > > group by bb.TAB_URN) prev
> > > on prev.tab_urn = curr.tab_urn
> > > , rep_curr_company com
> > > where curr.tab_urn = com.tab_urn
> > > OR prev.tab_urn = com.tab_urn
> > > order by
> > > com.name;
>
> > I am not the most observant person so naturally I did not spot the
> > error. But what version of Oracle is this?
>
> > Try changing quantity to a unique name every where it appears in an
> > inline view and prefix the one or two columns you did not prefix. If
> > doin this does not identify and fix the problem I suggest rewriting
> > the query to use traditional syntax.
>
> > HTH -- Mark D Powell --
>
> Hi Mark,
>
> Thanks for your quick reply. The version I am using is Oracle 10g. I
> have tried what you suggested, but no joy. You say traditional syntax
> - could you give me an example? Whilst I can use SQL to a certain
> degree, I don't think I am as competent as I would like (illustrated
> by my query I think!) So any help anyone can offer would be great!
>
> Cheers
>
> Tim

I can't see an error either but it might be worth putting that final
join into ANSI syntax rather than switching back to a where-clause
join. You might also try moving the inline views into WITH clauses
e.g.

WITH curr AS ( SELECT COUNT(*) AS quantity ... )
, prev AS ( SELECT COUNT(*) AS quantity ... )
SELECT com.name
, curr.quantity
, prev.quantity ...
FROM curr
, prev
, rep_curr_company com
WHERE ...

However I'm afraid this is a case of nudging it and seeing what it
does rather than getting to the root of the problem. Possibly the CBO
is rewriting the query in some way and running into a bug.

Possibly I'm missing something but the join oerder seems a bit odd.
You are full outer joining two inline views and then inner joining
then to a third table, with an 'or' condition. Shouldn't you start
with rep_curr_company and then left outer join each of the two views
to that?

On Feb 20, 9:07 am, timothy.h...@gmail.com wrote:
> On 20 Feb, 13:47, "Mark D Powell" <Mark.Pow...@eds.com> wrote:
>
>
>
>
>
> > On Feb 20, 8:33 am, timothy.h...@gmail.com wrote:
>
> > > Hi,
>
> > > I am getting the above error when I try and run the query below on
> > > bb.tab_urn. However, removing this group by then gives the same error,
> > > but at bb.transaction_date. Unless I have missed something completely,
> > > I am certain I have defined everything correctly. Thoughts? Any help
> > > would be very much appreciated!
>
> > > Cheers
>
> > > Tim
>
> > > select com.name
> > > , curr.quantity
> > > , prev.quantity
> > > from ( select count(*) as quantity
> > > , a.tab_urn
> > > from v_rep_vehicle_check_3m_trans a
> > > where a.CLASS_CODE in ('TDCP', 'TDCC')
> > > and a.TRANSACTION_DATE between
> > > to_date('2007-01-01000000','YYYY-MM-DDHH24MISS')
> > > and
> > > to_date('2007-01-31235929','YYYY-MM-DDHH24MISS')
> > > and a.tab_urn not in (0,10)
> > > group by a.tab_urn) curr
> > > full outer join
> > > ( select count(*) as quantity
> > > , bb.tab_urn
> > > from v_rep_vehicle_check_3m_trans bb
> > > where bb.CLASS_CODE in ('TDCP', 'TDCC')
> > > and bb.transaction_date between
> > > add_months(to_date('2007-01-01000000','YYYY-MM-DDHH24MISS'),-1)
> > > and
> > > add_months(to_date('2007-01-31235929','YYYY-MM-DDHH24MISS'),-1)
> > > and bb.tab_urn not in (0,10)
> > > group by bb.TAB_URN) prev
> > > on prev.tab_urn = curr.tab_urn
> > > , rep_curr_company com
> > > where curr.tab_urn = com.tab_urn
> > > OR prev.tab_urn = com.tab_urn
> > > order by
> > > com.name;
>
> > I am not the most observant person so naturally I did not spot the
> > error. But what version of Oracle is this?
>
> > Try changing quantity to a unique name every where it appears in an
> > inline view and prefix the one or two columns you did not prefix. If
> > doin this does not identify and fix the problem I suggest rewriting
> > the query to use traditional syntax.
>
> > HTH -- Mark D Powell --
>
> Hi Mark,
>
> Thanks for your quick reply. The version I am using is Oracle 10g. I
> have tried what you suggested, but no joy. You say traditional syntax
> - could you give me an example? Whilst I can use SQL to a certain
> degree, I don't think I am as competent as I would like (illustrated
> by my query I think!) So any help anyone can offer would be great!
>
> Cheers
>
> Tim- Hide quoted text -
>
> - Show quoted text -

Similar to
from table1 a, table2 b, table3 c
where a.key = b.key(+)
and a.key = c.key(+)

You just list the tables in the FROM clause and you just list the ON
conditions in the WHERE clause. The plus sign in () is the
traditional outer join symbol in Oracle. The plus goes on the side
(table) which returns NULL when there is no match so that the
receiving table on the other side of the relational operator alwyas
returns a row.

HTH -- Mark D Powell --

On Feb 20, 8:33 am, timothy.h...@gmail.com wrote:
> Hi,
>
> I am getting the above error when I try and run the query below on
>
> select com.name
> , curr.quantity
> , prev.quantity
> from ( select count(*) as quantity
> , a.tab_urn

Would the second and third columns not be named the same (ie
QUANTITY).
How about providing a column alias, as in ...
select com.name,curr.quantity "Curr_Quantity", prev.quantity
"Prev_Quantity" from ...

Hth