Re: invalid relational error, please help

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sat, 5 Jan 2008 06:30:47 -0800 (PST)
Message-ID: <6f7d04df-c905-4bf9-8707-5bdc9e0b28a3@j20g2000hsi.googlegroups.com>


On Jan 5, 5:11 am, "shakespeare" <what..._at_xs4all.nl> wrote:
> "Totti" <saliba.toufic.geo..._at_gmail.com> schreef in berichtnews:497200a1-e933-418b-a5c1-db8d2b28141f_at_e10g2000prf.googlegroups.com...
> > hi all i am having the following error :
>
> > ORA-00920 invalid relational operator
>
> > i read the quotes of other people who had the same problem but i didnt
> > understand how this ties to my situation, since it is about memory and
> > relative things, since the formula when working with other tables in
> > my DB shows no problems at all;
>
> > the formula is the following :
>
> > SELECT TO_CHAR(purinv.Inv_Date,'mm')
> > MONTH ,TO_CHAR(purinv.Inv_Date,'YYYY') YEAR,
> > SUM(DECODE(PROD.category,'Special',prod.unit_price * sales.qty , 0))
> > AS "Special",
> > SUM(DECODE(PROD.category,'Base',prod.unit_price * sales.qty , 0)) AS
> > "Base",
> > SUM(DECODE(PROD.category,'Common',prod.unit_price * sales.qty , 0)) AS
> > "Common"
> > from prod, sales, purinv
> > where sales.prod_code = prod.code and Prod.category ('Special',
> > 'Base', 'Common')
> > and purch.inv_code = purinv.code
> > group by
> > TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY')
> > order by TO_CHAR(purinv.Inv_Date,'YYYY'),TO_CHAR(purinv.Inv_Date,'mm')
>
> > any help appreciated
>
> and Prod.category ('Special',
>
> > 'Base', 'Common')
>
> is also suspect, unless it is a boolean function.
> I guess you meant and Prod.category IN ('Special',
>
> > 'Base', 'Common')
>
> Shakespeare

Thanks for pointing out the missing "IN", which is very likely the source of the Invalid Relational Operator error. Oracle had not yet returned the error message "Error: ORA-00904: "purch"."inv_code": invalid identifier".

To the OP: it is often helpful to place only one WHERE clause item on each line, and also do the same in the SELECT clause, as Oracle will often help pinpoint the line triggering the syntax error.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Jan 05 2008 - 08:30:47 CST

Original text of this message