By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,505 Members | 1,034 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,505 IT Pros & Developers. It's quick & easy.

how to compare value of two varchar2 fields ?

P: 3
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.
Nov 5 '07 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 102
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.
Nov 6 '07 #2

amitpatel66
Expert 100+
P: 2,367
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>
Nov 6 '07 #3

mwasif
Expert 100+
P: 801
To use amitpatel66's suggestion, you have to change the of startcode and endcode to INT.
Nov 6 '07 #4

P: 3
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?
Nov 6 '07 #5

amitpatel66
Expert 100+
P: 2,367
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?
Nov 7 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.