Re: Oracle Problem from a beginner

From: Michael Friedman <mfriedma_at_asiansources.com>
Date: 1997/11/18
Message-ID: <347131CE.B0933703@asiansources.com>

This is a multi-part message in MIME format. --------------8E4421485C52FDB3D87E0759
Content-Type: multipart/alternative; boundary="------------8D60779B3103DF756A2761E2"

--------------8D60779B3103DF756A2761E2
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Two issues:

As you say, you can't use a subquery for this
When you do use a subquery in this way you need to make sure that it only brings back one row.

I suggest that you do this in a pre-insert and pre-update trigger.

Execute the query

select ... from BOOKING B where
B.DATE_FROM between new.DATE_FROM and new.DATE_TO or B.DATE_TO between new.DATE_FROM and new.DATE_TO or (B.DATE_FROM < new.DATE_FROM and B.DATE_TO > new.DATE_FROM)

If you get back any rows, raise an exception.

Chung Wing Tat Áéºa¹F wrote:

> Hello every body!
>
> I am a beginner of Oracle in Hong Kong, I have a table which structures
> are listed as follows:
>
> ref_no varchar(8)
> date_from date
> date_to date
>
> I need to setup a constraint to this table to ensure that there is no
> overlapping date range in all rows so I want to setup following constraint
> just example ) :
>
> alter table booking
> add constraint CHECK_DOBULE_BOOKING check ( not (
> ( date_to < ( select date_from from booking ) ) and
> ( date_from > ( select date_to from booking ) ) ) )
>
> I was prompted that Oracle doesn't support sub-query in this case. how can
> I do it by table constraint? and how can I distinguish the field between the
> newly added one and the one already inside the table when setup a
> constraint?
>
> Chung Wing Tat
> wtchung_at_writeme.com
>
> 16-Nov-1997

--
-------------------------------------------------------------------------------
Building the future of business to business electronic commerce...
-------------------------------------------------------------------------------
ASM eTrade - Software from the Asian Sources Media Group
-------------------------------------------------------------------------------
24/F Vita Tower, Block B                  Tel: +852 2814 5678
29 Wong Chuk Hang Road                 Direct: +852 2814 5671
Aberdeen, Hong Kong                       Fax: +852 2311 3893
-------------------------------------------------------------------------------


--------------8D60779B3103DF756A2761E2
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<HTML>
Two issues:
<OL>
<LI>
As you say, you can't use a subquery for this</LI>

<LI>
When you do use a subquery in this way you need to make sure that it only
brings back one row.</LI>
</OL>
I suggest that you do this in a pre-insert and pre-update trigger.

<P>Execute the query

<P>select ... from BOOKING B where
<BR>B.DATE_FROM between new.DATE_FROM and new.DATE_TO
<BR>or B.DATE_TO between new.DATE_FROM and new.DATE_TO
<BR>or (B.DATE_FROM &lt; new.DATE_FROM and B.DATE_TO > new.DATE_FROM)

<P>If you get back any rows, raise an exception.

<P>Chung Wing Tat &Aacute;&eacute;&ordm;a&sup1;F wrote:
<BLOCKQUOTE TYPE=CITE>Hello every body!

<P>&nbsp; I am a beginner of Oracle in Hong Kong, I have a table which
structures
<BR>are listed as follows:

<P>&nbsp;&nbsp; ref_no&nbsp;&nbsp;&nbsp;&nbsp; varchar(8)
<BR>&nbsp;&nbsp; date_from&nbsp; date
<BR>&nbsp;&nbsp; date_to&nbsp;&nbsp;&nbsp; date

<P>&nbsp; I need to setup a constraint to this table to ensure that there
is no
<BR>overlapping date range in all rows so I want to setup following constraint
<BR>just example ) :

<P>&nbsp;&nbsp; alter table booking
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; add constraint CHECK_DOBULE_BOOKING
check ( not (
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ( date_to&nbsp;&nbsp; &lt; ( select
date_from from booking ) ) and
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ( date_from > ( select date_to&nbsp;&nbsp;
from booking ) ) ) )

<P>&nbsp; I was prompted that Oracle doesn't support sub-query in this
case. how can
<BR>I do it by table constraint? and how can I distinguish the field between
the
<BR>newly added one and the one already inside the table when setup a
<BR>constraint?

<P>Chung Wing Tat
<BR>wtchung_at_writeme.com

<P>16-Nov-1997</BLOCKQUOTE>
&nbsp;

<P>--
<BR>-------------------------------------------------------------------------------
<BR>Building the future of business to business electronic commerce...
<BR>-------------------------------------------------------------------------------
<BR>ASM eTrade - Software from the Asian Sources Media Group
<BR>-------------------------------------------------------------------------------
<BR>24/F Vita Tower, Block B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Tel: +852 2814 5678
<BR>29 Wong Chuk Hang Road&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Direct: +852 2814 5671
<BR>Aberdeen, Hong Kong&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Fax: +852 2311 3893
<BR>-------------------------------------------------------------------------------
<BR>&nbsp;</HTML>

--------------8D60779B3103DF756A2761E2--

--------------8E4421485C52FDB3D87E0759
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Michael Friedman
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Michael Friedman
n:              Friedman;Michael
org:            Asian Sources Media Group (eTrade)
email;internet: mfriedma_at_asiansources.com
x-mozilla-cpt:  ;0
x-mozilla-html: FALSE
version:        2.1
end:            vcard


--------------8E4421485C52FDB3D87E0759--

Received on Tue Nov 18 1997 - 00:00:00 CST