how to compare value of two varchar2 fields ?

[moorthyvisu]

Hi,

I have a below scenario:

Table name : ClientCode.
Fields are : startcode varchar2(20) & endcode varchar2(20);

The values in these two fields should not overlap.

For Ex:

#1. startcode = 50 & endcode = 110 - this is allowed
#2. startcode = 130 & endcode = 180 - this is allowed
#3. startcode = 90 & endcode = 100 - this should not allow, since the values are
overlapped.

I would highly appreciate if any one can post the query to resolve this issue.

Thnx in advance.

[pradeep kaltari]

Hi Moorthyvisu,
Could you please tell us what exactly do you mean by overlap?

In the 3rd case you have mentioned startcode=90 and endcode=100 are overlapped. Meaning??

Anyways, if this data should not be allowed in the table itself then you can create CHECK constraint on the table specifying your rule.

Hi,

I have a below scenario:

Table name : ClientCode.
Fields are : startcode varchar2(20) & endcode varchar2(20);

The values in these two fields should not overlap.

For Ex:

#1. startcode = 50 & endcode = 110 - this is allowed
#2. startcode = 130 & endcode = 180 - this is allowed
#3. startcode = 90 & endcode = 100 - this should not allow, since the values are
overlapped.

I would highly appreciate if any one can post the query to resolve this issue.

Thnx in advance.

[amitpatel66]

Hi,

I have a below scenario:

Table name : ClientCode.
Fields are : startcode varchar2(20) & endcode varchar2(20);

The values in these two fields should not overlap.

For Ex:

#1. startcode = 50 & endcode = 110 - this is allowed
#2. startcode = 130 & endcode = 180 - this is allowed
#3. startcode = 90 & endcode = 100 - this should not allow, since the values are
overlapped.

I would highly appreciate if any one can post the query to resolve this issue.

Thnx in advance.

Write a BEFORE INSERT trigger and check if the values are getting over lapped
then dont allow the user to insert a new record in to the table.

Eg:

If new.startcode => startcode AND new.endcode <= endcode THEN
<print error>

[mwasif]

To use amitpatel66's suggestion, you have to change the of startcode and endcode to INT.

[moorthyvisu]

Hi,

Actually what I mean is "Overlap"=> the new value of start & end code should not be in the range of the existing values.

For Ex:

#1. Start = 30 & End = 90

If I give start as 40 and end as 80 it should not return a row.

Its not only the number the column is alphanumeric. If the user is entering alphanumeric for these two fields then how can we change the query?

[amitpatel66]

Hi,

Actually what I mean is "Overlap"=> the new value of start & end code should not be in the range of the existing values.

For Ex:

#1. Start = 30 & End = 90

If I give start as 40 and end as 80 it should not return a row.

Its not only the number the column is alphanumeric. If the user is entering alphanumeric for these two fields then how can we change the query?

Your first point will satisfy with the logic tha I have given you to check for over lapping in case if it is just an integer.

But how about handling of alphamumeric?

Eg: sc = 100A and EC = 200B
So you want to restrict new data if it is between 100 and 200?