Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQLPlus Mystery

Re: SQLPlus Mystery

From: Reinhard Wahl <wahl_at_zv.fhg.de>
Date: 1998/12/17
Message-ID: <367924E0.D879E720@zv.fhg.de>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US