HELP : Problem with 'Not In'

From: Arnaud <mail.boulbes_at_laposte.net>
Date: 17 Mar 2004 02:41:37 -0800
Message-ID: <ef3c7794.0403170241.5a13a96a@posting.google.com>

Hi,

I have a problem with this request :

SQL> SELECT T3.PRCORT, T4.WICDU,

  2         substr(T4.WIDATE_OF,1,2)||'/'||substr(T4.WIDATE_OF,3,2)||'/'||substr(T4.WIDATE_OF,5,2)
  3         ||' '||substr(T4.WIDATE_OF,8,2)||':'||substr(T4.WIDATE_OF,10,2),
  4         substr(T4.WIDLUO,1,2)||'/'||substr(T4.WIDLUO,3,2)||'/'||substr(T4.WIDLUO,5,2)
  5         ||' '||substr(T4.WIDLUO,8,2)||':'||substr(T4.WIDLUO,10,2)
  6         ||lpad(SUM(T4.WIQTE),6)||rpad(to_char(sysdate,'DD/MM/YY

HH24:Mi'),14)
  7 FROM wiof T4, wiprcort T3
  8 WHERE T4.WIART=T3.PRCO
  9 AND T4.WICDU not in
 10 (select substr(lfgden,9,3)
 11 from werr, cs021_at_db_w_cstk
 12 where
 13 TRAITEMENT like 'cs01%' and
 14 substr(TRAITEMENT,5,2) = CSID and
 15 WID IS NOT NULL and
 16 paco = substr(TRAITEMENT,7,2)
 17 )
 18 GROUP BY T3.PRCORT , T4.WICDU , T4.WIDATE_OF , T4.WIDLUO ; (select substr(lfgden,9,3)

                        *

ERROR at line 10:
ORA-00920: invalid relational operator
ORA-02063: preceding line from DB_W_CSTK

But if run the sub-querie :

SQL> select substr(lfgden,9,3)
  2 from werr, cs021_at_db_w_cstk
  3 where
  4 TRAITEMENT like 'cs01%' and
  5 substr(TRAITEMENT,5,2) = CSID and
  6 WID IS NOT NULL and
  7 paco = substr(TRAITEMENT,7,2);

SUB

---
219



=> it works


and if I write '219' in the main request instead of the sub-querie :

SQL> SELECT T3.PRCORT, T4.WICDU, 
  2         substr(T4.WIDATE_OF,1,2)||'/'||substr(T4.WIDATE_OF,3,2)||'/'||substr(T4.WIDATE_OF,5,2)
  3         ||' '||substr(T4.WIDATE_OF,8,2)||':'||substr(T4.WIDATE_OF,10,2),
  4         substr(T4.WIDLUO,1,2)||'/'||substr(T4.WIDLUO,3,2)||'/'||substr(T4.WIDLUO,5,2)
  5         ||' '||substr(T4.WIDLUO,8,2)||':'||substr(T4.WIDLUO,10,2)
  6         ||lpad(SUM(T4.WIQTE),6)||rpad(to_char(sysdate,'DD/MM/YY
HH24:Mi'),14)
  7  FROM  wiof T4, wiprcort T3 
  8  WHERE  T4.WIART=T3.PRCO 
  9    AND  T4.WICDU not in ('219')
 10  GROUP BY T3.PRCORT , T4.WICDU , T4.WIDATE_OF , T4.WIDLUO ;

PRCORT WIC SUBSTR(T4.WIDA SUBSTR(T4.WIDLUO,1,2)||'/'||SUBSTR
------ --- -------------- ----------------------------------
000086 050 22/03/04 00:00 03/06/04 00:00    6117/03/04 10:34
000346 001 26/03/04 00:00 13/09/04 00:00  392517/03/04 10:34
000375 001 24/03/04 00:00 13/09/04 00:00  392017/03/04 10:34
 ...
429 rows selected.



=> It's works ...


I think it's a problem with the NOT IN and a sub-querie but i don't
understand why !

Do you have any idea about this ?

Arnaud

Received on Wed Mar 17 2004 - 04:41:37 CST