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

Totti wrote:
"ORA-00920 invalid relational operator" is not the message you received.
It is a small portion of the message you received. Please post your full
version number and the full and complete error message: Especially the
part with the asterisk and the line and column number.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Totti wrote:
"ORA-00920 invalid relational operator" is not the message you received.
It is a small portion of the message you received. Please post your full
version number and the full and complete error message: Especially the
part with the asterisk and the line and column number.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Totti wrote:
"ORA-00920 invalid relational operator" is not the message you received.
It is a small portion of the message you received. Please post your full
version number and the full and complete error message: Especially the
part with the asterisk and the line and column number.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Totti wrote:
"ORA-00920 invalid relational operator" is not the message you received.
It is a small portion of the message you received. Please post your full
version number and the full and complete error message: Especially the
part with the asterisk and the line and column number.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thnak you Mr. Morgan for your notice;
here is the whole thing:

SQL> SELECT TO_CHAR(purinv.Inv_Date,'mm')
MONTH ,TO_CHAR(purinv.Inv_Date,'YYYY') YEAR,
2 SUM(DECODE(PROD.category,'Special',prod.unit_price * sales.qty ,
0)) AS "Special",
3 SUM(DECODE(PROD.category,'Base',prod.unit_price * sales.qty , 0))
AS "Base",
4 SUM(DECODE(PROD.category,'Common',prod.unit_price * sales.qty ,
0)) AS "Common"
5 from prod, sales, purinv
6 where sales.prod_code = prod.code and Prod.category ('Special',
'Base', 'Common')
7 and purch.inv_code = purinv.code
8 group by
TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY')
9 order by
TO_CHAR(purinv.Inv_Date,'YYYY'),TO_CHAR(purinv.Inv _Date,'mm');
and purch.inv_code = purinv.code
*
ERROR at line 7:
ORA-00920: invalid relational operator

Thnak you Mr. Morgan for your notice;
here is the whole thing:

SQL> SELECT TO_CHAR(purinv.Inv_Date,'mm')
MONTH ,TO_CHAR(purinv.Inv_Date,'YYYY') YEAR,
2 SUM(DECODE(PROD.category,'Special',prod.unit_price * sales.qty ,
0)) AS "Special",
3 SUM(DECODE(PROD.category,'Base',prod.unit_price * sales.qty , 0))
AS "Base",
4 SUM(DECODE(PROD.category,'Common',prod.unit_price * sales.qty ,
0)) AS "Common"
5 from prod, sales, purinv
6 where sales.prod_code = prod.code and Prod.category ('Special',
'Base', 'Common')
7 and purch.inv_code = purinv.code
8 group by
TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY')
9 order by
TO_CHAR(purinv.Inv_Date,'YYYY'),TO_CHAR(purinv.Inv _Date,'mm');
and purch.inv_code = purinv.code
*
ERROR at line 7:
ORA-00920: invalid relational operator

Thnak you Mr. Morgan for your notice;
here is the whole thing:

SQL> SELECT TO_CHAR(purinv.Inv_Date,'mm')
MONTH ,TO_CHAR(purinv.Inv_Date,'YYYY') YEAR,
2 SUM(DECODE(PROD.category,'Special',prod.unit_price * sales.qty ,
0)) AS "Special",
3 SUM(DECODE(PROD.category,'Base',prod.unit_price * sales.qty , 0))
AS "Base",
4 SUM(DECODE(PROD.category,'Common',prod.unit_price * sales.qty ,
0)) AS "Common"
5 from prod, sales, purinv
6 where sales.prod_code = prod.code and Prod.category ('Special',
'Base', 'Common')
7 and purch.inv_code = purinv.code
8 group by
TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY')
9 order by
TO_CHAR(purinv.Inv_Date,'YYYY'),TO_CHAR(purinv.Inv _Date,'mm');
and purch.inv_code = purinv.code
*
ERROR at line 7:
ORA-00920: invalid relational operator

Thnak you Mr. Morgan for your notice;
here is the whole thing:

SQL> SELECT TO_CHAR(purinv.Inv_Date,'mm')
MONTH ,TO_CHAR(purinv.Inv_Date,'YYYY') YEAR,
2 SUM(DECODE(PROD.category,'Special',prod.unit_price * sales.qty ,
0)) AS "Special",
3 SUM(DECODE(PROD.category,'Base',prod.unit_price * sales.qty , 0))
AS "Base",
4 SUM(DECODE(PROD.category,'Common',prod.unit_price * sales.qty ,
0)) AS "Common"
5 from prod, sales, purinv
6 where sales.prod_code = prod.code and Prod.category ('Special',
'Base', 'Common')
7 and purch.inv_code = purinv.code
8 group by
TO_CHAR(purinv.Inv_Date,'mm') ,TO_CHAR(purinv.Inv_Date,'YYYY')
9 order by
TO_CHAR(purinv.Inv_Date,'YYYY'),TO_CHAR(purinv.Inv _Date,'mm');
and purch.inv_code = purinv.code
*
ERROR at line 7:
ORA-00920: invalid relational operator

On Jan 4, 9:44*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
The error is pointing at the line:
"and purch.inv_code = purinv.code"

Your FROM clause is "from prod, sales, purinv". Oracle does not know
where to find purch.inv_code, as purch is not one of the tables in the
FROM clause.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.