Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQLPlus Mystery
If there is a NULL-Value in one of your Join-Columns of the join tables you have to use NVL-Function for comparing.
Try:
... where NVL(a.doc_id, 0) = NVL(b.doc_id,0) and ...
Reinhard
Tansel Ozkan schrieb:
>
> We are having a strange problem:
>
> The same query is returning different number of records when
> run from SQLPlus and SQLWorksheet. The SQL statement below
> is returning 180 records when run from SQLPlus, and 194 records
> from SQLWorksheet.
>
> select a.doc_id||'|'||a.doc_title||'|'||b.stock_qty||'|'||
> b.committed_qty||'|'||a.reorder_qty||'|'||a.samp_cutoff_qty
> from documents a,doc_stock b, doc_int_mapping c
> where a.doc_id=b.doc_id and
> b.doc_id=c.doc_id and
> c.int_code='PACK'
>
> The interesting is that if I run the same query from SQLPlus
> with count(*) instead of a select field, I get the same number of records
> as run from SQLWorksheet.
>
> select count(*)
> from documents a,doc_stock b, doc_int_mapping c
> where a.doc_id=b.doc_id and
> b.doc_id=c.doc_id and
> c.int_code='PACK'
>
> Any ideas to help me solve this mystery?
>
> Tansel
-- MfG R.Wahl (ZV-807) ---------------------------------------------------- E-Mail_at_work: mailto:wahl_at_zv.fhg.de E-Mail_at_home: mailto:ReinhardWahl_at_online.de ---------------------------------------------------- Website http://www.online.de/homepages/ReinhardWahl ---------------------------------------------------- Hiermit widerspreche ich der Nutzung oder Uebermittlung meiner Daten fuer Werbezwecke oder fuer die Markt- oder Meinungsforschung gemaess Par. 28 Abs. 3 Bundesdatenschutzgesetz. ---------------------------------------------------- Please do not use my E-Mail address for advertising! ----------------------------------------------------Received on Thu Dec 17 1998 - 00:00:00 CST