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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Question

Re: Question

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Fri, 21 Feb 2003 17:24:29 GMT
Message-ID: <MPG.18c000aa2965f07b9896c3@news.la.sbcglobal.net>


frengo_at_fide.org said...
> Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.18b6b7b24d1cd9709896a6_at_news.la.sbcglobal.net>...
> > thechessPUSSAVIASPAMMERplayer_at_tin.it said...
> > >
>
> > > but even if in some similar cases it worked, in this case (and in other), I
> > > receive the error message:
> > > ORA-01722 Invalid number ....
> > >
> > >
> > > Why is this happening ?
> > >
> > > Thanks in advance,
> > > Francesco
> > >
> >
> > I assume that v_dep1_v is a numeric field? If so, you can't put an alpha
> > literal ('NA') in it, and you might need to replace your nvl with
> > something like:
> >
> > decode(cvtest_1_sen.v_dep1_v, null, 'NA', cvtest_1_sen.v_dep1_v)
>
> I'd have another variation on this theme. I'd like first of all to
> apologize for all these questions, but databases is not my job, and
> moreover I've to deal with a database which is not made by me ...
> Essentially, the question is this one (it's slightly different from my
> previous one, I think, at least ..).
> I've N tables , which are all made with a field object_id and some
> other fields in it.
> If all was perfect, I'd have to perform this very simple query:
> select tableA.dataA, tableB.dataB .... from tableA, tableB ... where
> tableA.object_id=666 and tableB.object_id = tableA.object_id ... AND
> tableA.property='boh' AND tableB.property='pippo' ....
>
> The problem is that we don't live in a perfect world: I can have two
> kinds of problems:
> 1) In one table (maybe also in the tableA) there is no record for
> object_id 666 (but there are records for it in other tables) ...
> 2) In some table, there could be a record for it, but not with the
> right property field.
>
> My dream is to get in any case a list dataA, dataB, with the data if
> it exists, or with a string like 'N.A.' in cases 1) or 2)
>
> If anyone could let this dream come true, I'll offer him (or even
> better, her :)) a dinner when he comes in Italy.
>
> Thanks in advance,
> Francesco
>

What you need is an OUTER JOIN, which is done by adding a "(+)" (without quotes) in your WHERE clause. Search http://tahiti.oracle.com for OUTER JOIN for examples. An outer join will return "null" column values when the row exists in one table, but not the other.

Since you'll get a NULL in the column that doesn't exist, you can replace it with "N.A." by using the NVL() function. If object_id is a numeric, you'll need to use the DECODE() instead, as explained earlier.

Don't know if that makes your dream come true. I was in northern Italy last summer. Had some very good food while there. Here's hoping you find a dream lady to have that dinner with. :)

-- 
/Karsten
DBA > retired > DBA
Received on Fri Feb 21 2003 - 11:24:29 CST

Original text of this message

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