Sorry,
It still doesn't give me the analytes without norm.
Paul
-----Original Message-----
From: Mahmoud Reza Zare [mailto:mrezair_at_0racledba.com]
Sent: 03 October 2000 08:45
To: Multiple recipients of list ORACLE-L
Subject: Re: SQL Query
You should break your SELECT in two parts , sth like this
select TES.TEST_ID, me.ANALYTE, me.value from
(select ANA.ANALYTE, nvl(TO_CHAR(NOR.Value),'NULL') Value, ana.test_id,
nor.ns_id
from NORMS NOR, ANALYTES ANA
WHERE NOR.ANALYTE(+)=ANA.ANALYTE) me, test tes
where tes.TEST_ID = me.TEST_ID
and me.NS_ID=TES.NS_ID
AND TES.test_id= 1
Further you may optimize it to
select TES.TEST_ID, me.ANALYTE, me.value from
(select ANA.ANALYTE, nvl(TO_CHAR(NOR.Value),'NULL') Value, ana.test_id,
nor.ns_id
from NORMS NOR, ANALYTES ANA
WHERE NOR.ANALYTE(+)=ANA.ANALYTE
and ana_tes_id (+) = 1 ) me, test tes
where tes.TES.TEST_ID = me.TEST_ID
and me.NS_ID=TES.NS_ID
- Paul van Dijken <paul.vandijken_at_sema.nl>
> wrote:
>Dear All,
>
>Oracle 7.3.4
>
>Maybe I did not have enough coffe, but I can't figure this out.
>I have 4 tables Tests, Analytes, Normsystems and Norms
>
>Tests Test_id, Name, NS_Id
>Analyte A_id, Analyte, Test_Id
>Normsystems NS_ID, Name
>Norms N_Id, NS_Id, Analyte, Value
>
>+--------------------+ +--------------------+
>| Test |--------------| Normsystems |
>+--------------------+ +--------------------+
> | |
>+--------------------+ +--------------------+
>| Analyte |-------------| Norms |
>+--------------------+ +--------------------+
>
>Each Test has 1 or more Analytes
>Each Normsystems has 1 or more Norms
>Each Test has 0 or 1 Normsystem (NS_Id may be NULL)
>Each Analyte has 0 or more Norms (An analyte may be in more then 1
>normsystem)
>
>Within a specific Test I want to get all analytes together with the
>Norms.Value for each Analyte, with the value "NULL" when no norm is found
>for the Analyte.
>
>When I issue :
>select TES.TEST_ID, ANA.ANALYTE, nvl(TO_CHAR(NOR.Value),'NULL') Value
>from
> NORMS NOR, ANALYTES ANA, TEST TES
>WHERE
> TES.TEST_ID=ANA.TEST_ID
> AND NOR.NS_ID=TES.NS_ID
> AND NOR.ANALYTE(+)=ANA.ANALYTE
> AND TES.test_id=1
>;
>
>I only receive the analytes for which a norm exists, NOT the ones for which
>a norm does not exist. I want both.
>
>When I try to add an extra outer join to AND NOR.NS_ID(+)=TES.NS_ID, I get
>the error
>ORA-01417: a table may be outer joined to at most one other table
>
>How do I solve this ??
>
>Paul
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Paul van Dijken
> INET: paul.vandijken_at_sema.nl
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
Be someone_at_0racledba.com - get your free mail from Oriole Corporation
http://www.oriolecorp.com - Performance tools for Oracle DBAs
Get email for your site ---> http://www.everyone.net
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mahmoud Reza Zare
INET: mrezair_at_0racledba.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
Received on Tue Oct 03 2000 - 02:16:39 CDT
