ORA-00920: invalid relational operator 2

[missmis44]

More details in thread1177-1136650
I am getting the following error: ORA-00920: invalid relational operator with this script:

SELECT J.BOOK_ID, A.FEL_MIS_WT, J.BOOKDATE, A.ARRE_ID 
FROM JMMAIN J, ARMAIN A        
WHERE  (J.BOOK_ID,DECODE(A.FEL_MIS_WT, 'F',1,'W',2,'M',3)||J.BOOKDATE,A.ARRE_ID) IN
(SELECT J.BOOK_ID, MIN(DECODE(A.FEL_MIS_WT, 'F',1,'W',2,'M',3)||J.BOOKDDATE,A.ARRE_ID)) 
AND J.BOOK_ID = A.BOOK_ID AND TO_CHAR(J.BOOKDATE,'YYYYMMDD') BETWEEN '20040101' AND '20041231'
ORDER BY J.BOOK_ID

 

[BJCooperIT]

Helps a bit when spaced out:

select J.BOOK_ID, A.FEL_MIS_WT, J.BOOKDATE, A.ARRE_ID
from   JMMAIN J, ARMAIN A
where  (J.BOOK_ID, DECODE(A.FEL_MIS_WT,'F',1,'W',2,'M',3)
         || J.BOOKDATE, A.ARRE_ID) 
        in
       (select J.BOOK_ID, 
           min(DECODE(A.FEL_MIS_WT,'F',1,'W',2,'M',3)
                      || J.BOOKDDATE,A.ARRE_ID)
         [COLOR=RED]-- from? need different aliases[/COLOR]
         [COLOR=RED]-- group by?[/COLOR]
       )
and    J.BOOK_ID = A.BOOK_ID
and    TO_CHAR(J.BOOKDATE,'YYYYMMDD')
         between '20040101' and '20041231'
order  by J.BOOK_ID

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[SantaMufasa]

Great catch, Barb! Don't I feel silly that I didn't catch that.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[missmis44]

I am now getting the following error:
MIN(DECODE(R.FEL_MIS_WT,'F',1,'W',2,'M',3)
*
ERROR at line 7:
ORA-00920: invalid relational operator

SELECT J.BOOK_ID, A.FEL_MIS_WT, J.BOOKDATE, A.ARRE_ID
FROM   JMMAIN J, ARMAIN A
WHERE  (J.BOOK_ID, DECODE(A.FEL_MIS_WT,'F',1,'W',2,'M',3)
         || J.BOOKDATE, A.ARRE_ID) 
        IN
       (SELECT M.BOOK_ID, 
           MIN(DECODE(R.FEL_MIS_WT,'F',1,'W',2,'M',3)
                      || M.BOOKDDATE,R.ARRE_ID)
         FROM JMMAIN M, ARMAIN R
       )
AND   J.BOOK_ID = A.BOOK_ID
AND    TO_CHAR(J.BOOKDATE,'YYYYMMDD')
         BETWEEN '20040101' AND '20041231'
ORDER BY J.BOOK_ID

 

[SantaMufasa]

MissMis,

Your attempt to incorporate "R.ARRE_ID" in the subquery produced a syntax error because your closing paren on the DECODE is misplaced. Also, as a second issue, if you need to incorporate the "ARMAIN" table in the subquery, you should also have a WHERE clause that defines how you wish to join the tables, else you receive a "Cartesian Product" of the two tables: every row in "JMMAIN" matched with every row in "ARMAIN"...definitely not what you want.

Relocating the misplaced closing paren and inclusion of a joining WHERE clause causes your subquery to read something like:

...    (SELECT M.BOOK_ID, 
           MIN(DECODE(R.FEL_MIS_WT,'F',1,'W',2,'M',3)
                      || M.BOOKDDATE[b])[/b],R.ARRE_ID
         FROM JMMAIN M, ARMAIN R
         [b]WHERE <some condition>[/b]
       )

Let us know if this improves your results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[BJCooperIT]

Do not forget that it also needs a group by clause.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[SantaMufasa]

Yet another great catch, Barb ! (When we get this all resolved, you deserve a Purple Star for your great Quality Assurance.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[missmis44]

Thanks Mufasa and Barb!!! You two are awesome. Here is my final solution.

SELECT J.BOOK_ID, A.FEL_MIS_WT, J.BOOKDATE
     FROM JMMAIN J, ARMAIN A  
     WHERE (A.BOOK_ID,DECODE(A.FEL_MIS_WT, 'F',1,'W',2,'M',3)||A.ARRE_ID) IN
     (SELECT A.BOOK_ID, MIN(DECODE(A.FEL_MIS_WT, 'F',1,'W',2,'M',3)||A.ARRE_ID)
     FROM ARMAIN A 
     GROUP BY A.BOOK_ID) AND J.BOOK_ID = A.BOOK_ID AND 
     TO_CHAR(J.BOOKDATE,'YYYYMMDD') BETWEEN '20040101' AND '20041231'
ORDER BY J.BOOK_ID;