Re: Question

From: Ed Prochak <edprochak_at_adelphia.net>
Date: Fri, 21 Feb 2003 17:07:21 GMT
Message-ID: <3E56605B.2050703@adelphia.net>

Francesco S. Rinaldi wrote:
> 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

Have you considered an outer join?
SELECT tableA.dataA, NVL(tableB.dataB, 'N.A.') .... FROM tableA, tableB ...

  WHERE tableA.object_id = 666
    AND tableB.object_id(+) = tableA.object_id ...
    AND tableA.property = 'boh'
    AND tableB.property(+) = 'pippo' ....

NOTE: the key is every place the outer joined table appears, it must have the outer join operator (+). Otherwise it defaults to (effectively becomes) a normal join.

HTH (PS FIDE.org? is that the Chess Federation? Looks like the website is down. Need help?)

-- 
Ed Prochak
running: http://www.faqs.org/faqs/running-faq/
family:  http://web.magicinterface.com/~collins
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Received on Fri Feb 21 2003 - 11:07:21 CST