Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 7 Help (Oracle 7)
Oracle 7 Help [message #359144] Thu, 13 November 2008 19:46 Go to next message
king-kong
Messages: 9
Registered: October 2008
Junior Member
I have a table as follows (ignore the hyphen signs as the page won't take multiple spaces and the columns will appear crooked if each field is not of the same width):


+-------+-------+-----+
| Type- | Value | ID- |
+-------+-------+-----+
| Start | 11500 | A01 |
| End-- | 11580 | A02 |
| End-- | 12600 | B04 |
| Start | 12500 | B04 |
| ..... | ..... | ... |
| Start | 11000 | X55 |
| End-- | 11700 | X55 |
+-------+-------+-----+

The table contains the start and end numbers of blocks of numbers (not sorted). The ID column identifies a block.

My query takes a number and have to return the ID(s) of the block(s) the number is in. For example, given 11555 it should return A01 and X55. Given 12599 it should return B04, and so on.

How do I do it in one nice Oracle 7 query?

Thank you
Re: Oracle 7 Help [message #359160 is a reply to message #359144] Thu, 13 November 2008 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
have a table as follows (ignore the hyphen signs as the page won't take multiple spaces and the columns will appear crooked if each field is not of the same width):

Please read OraFAQ Forum Guide, especially "How to format your post?" section.

Quote:
How do I do it in one nice Oracle 7 query?

Why did you try so far?

It is a good practice when asking for a query to post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Oracle 7 Help [message #359167 is a reply to message #359144] Fri, 14 November 2008 00:15 Go to previous messageGo to next message
king-kong
Messages: 9
Registered: October 2008
Junior Member
I can't provide INSERT statements as I have no write access to this particular database, and my working database is Microsoft SQL Server.

I have a workaround which consists of three separate database calls from an ASP.Net page, interspersed with C# code. My current code is, where the number to search is X and my table is T1:

a. SELECT ID FROM T1 WHERE Value<=X and Type='Start' and RowNum=1 ORDER BY Value DESC

I get the value of ID through an OracleDataReader. This is the highest Start Value that is smaller than X.

b. SELECT ID FROM T1 WHERE Value>=X and Type='End' and RowNum=1 ORDER BY Value

I get the value of ID through an OracleDataReader. This is the lowest End value that is greater than X.

If the two IDs are equal, then I consider that a successful hit, and I make another query to get the other columns I need.

Now, this was before I discovered that X could appear in more than one number block as in my sample table given. When X is in more than one number block, the above will not always work, and it will return only one correct block at best.

[Updated on: Fri, 14 November 2008 00:19]

Report message to a moderator

Re: Oracle 7 Help [message #359170 is a reply to message #359144] Fri, 14 November 2008 00:23 Go to previous messageGo to next message
king-kong
Messages: 9
Registered: October 2008
Junior Member
There is a mistake in my sample table!! There's no A02. It should be A01.

+-------+-------+-----+
| Type- | Value | ID- |
+-------+-------+-----+
| Start | 11500 | A01 |
| End-- | 11580 | A01 |
| End-- | 12600 | B04 |
| Start | 12500 | B04 |
| ..... | ..... | ... |
| Start | 11000 | X55 |
| End-- | 11700 | X55 |
+-------+-------+-----+
Re: Oracle 7 Help [message #359173 is a reply to message #359144] Fri, 14 November 2008 00:28 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

My query takes a number and have to return the ID(s) of the block(s) the number is in. For example, given 11555 it should return A01 and X55. Given 12599 it should return B04, and so on.

In that case How 11555 will return A01 and X55 ?

Smile
Rajuvan.

[Updated on: Fri, 14 November 2008 00:44] by Moderator

Report message to a moderator

Re: Oracle 7 Help [message #359179 is a reply to message #359167] Fri, 14 November 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I can't provide INSERT statements as I have no write access to this particular database

You don't need to access to a database to write here: "create table ..." and "insert into ...".

I think you have to study SQL and espcially Joins.

Regards
Michel
Re: Oracle 7 Help [message #359180 is a reply to message #359144] Fri, 14 November 2008 00:49 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

By the way ,

Did you try the basic Connect By .
Connect By queries are supported form Oracle 2. ( if I am not wrong )

Smile
Rajuvan.
Re: Oracle 7 Help [message #359185 is a reply to message #359173] Fri, 14 November 2008 00:59 Go to previous messageGo to next message
king-kong
Messages: 9
Registered: October 2008
Junior Member
rajavu1 wrote on Fri, 14 November 2008 14:28
My query takes a number and have to return the ID(s) of the block(s) the number is in. For example, given 11555 it should return A01 and X55. Given 12599 it should return B04, and so on.

In that case How 11555 will return A01 and X55 ?

Smile
Rajuvan.


Sorry I didn't provide more explanation on the examples.

Block A01 starts from 11500 and ends at 11580.
Block B04 starts from 12500 and ends at 12600.
Block X55 starts from 11000 and ends at 11700.

The number 11555 falls between 11500 and 11580, hence A01. It is also between 11000 and 11700, hence X55.
Re: Oracle 7 Help [message #359186 is a reply to message #359173] Fri, 14 November 2008 01:11 Go to previous messageGo to next message
king-kong
Messages: 9
Registered: October 2008
Junior Member
rajavu1 wrote on Fri, 14 November 2008 14:28
In that case How 11555 will return A01 and X55 ?

Smile
Rajuvan.


Sorry I didn't provide more explanation on the examples.

Block A01 starts from 11500 and ends at 11580.
Block B04 starts from 12500 and ends at 12600.
Block X55 starts from 11000 and ends at 11700.

The number 11555 falls between 11500 and 11580, hence A01. It is also between 11000 and 11700, hence X55.
Re: Oracle 7 Help [message #359187 is a reply to message #359185] Fri, 14 November 2008 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answers are still the same:
- post a test case
- use a (self-)join

Regards
Michel
Re: Oracle 7 Help [message #359193 is a reply to message #359144] Fri, 14 November 2008 01:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Still I am sure CONNECT BY Will work for OP .

Smile
Rajuvan.
Re: Oracle 7 Help [message #359196 is a reply to message #359193] Fri, 14 November 2008 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Given OP SQL level (infered from his solution), I think a classic self-join (and dbms independent) is a good starting point before going to more complex and tricky solution. Smile

Regards
Michel
Re: Oracle 7 Help [message #359205 is a reply to message #359196] Fri, 14 November 2008 02:29 Go to previous messageGo to next message
king-kong
Messages: 9
Registered: October 2008
Junior Member
Michel Cadot wrote on Fri, 14 November 2008 15:36
Given OP SQL level (infered from his solution), I think a classic self-join (and dbms independent) is a good starting point before going to more complex and tricky solution. Smile

Regards
Michel



Michel Cadot, can you please stop posting this thread if you are not going to contribute to an answer to my problem. What is OP? The FAQ says not to use IM slang.

I have already elaborated on the test cases three times. If it is still ambiguous to you, state your interpretations so that I can clarify.

I read the FAQ before posting. The best way of presenting my test case is to use a HTML table to illustrate my table contents. However, I am banned (only this web site does it) from creating a HTML table.
Re: Oracle 7 Help [message #359207 is a reply to message #359205] Fri, 14 November 2008 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What is OP? The FAQ says not to use IM slang.

OP is Original Poster. It is the only acronym tolerated here as it is known of every forum.

Quote:
Michel Cadot, can you please stop posting this thread if you are not going to contribute to an answer to my problem.

I have to say that you can't tell me to stop posting anywhere, if you want to see my answer then don't post yourself.
In addition, I gave you a solution and links to finalize it yourself but you have to make a little effort and work and learn a little bit. Note that this site does NOT provide full answer to beginners unless they proved they worked on the solution.

Quote:
I have already elaborated on the test cases three times

You have NOT posted a test case, read what I posted twice. The problem is perfectly clear and I already wrote a (not 2) solution for myself.

Quote:
The best way of presenting my test case is to use a HTML table to illustrate my table contents.

You did read neither what I posted nor the guide section I mentioned. It is explained you can do it.

Regards
Michel



[Updated on: Fri, 14 November 2008 02:56]

Report message to a moderator

Re: Oracle 7 Help [message #359246 is a reply to message #359207] Fri, 14 November 2008 05:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I can think of three approaches to this that will probably work on Oracle 7.
Out of curiosity, where did you find such an archaic version? It nwas supersceded a decade ago.
Would you still use Windows 95?
No.

So why use Oracle 7?

Approach 1: Set based.
Write a query to get the list of Ids where your value is greater than the Start value
Write a query to get the list of Ids where your value is lower than the End value
Use Intersect to get the ids present in both queries

Approach 2: Self join
Take 2 instances of the table, join them by the Id, specift that your value must be larger than the start value for one instance, and smaller than the end value for the other.

Approach 3: Aggregation
Write a query that gets the max and min values for an id. Use the Having clause to specift that your value must be in this range.

Here's a test case for you:
 create table test_019 ( rowtype varchar2(30), rowvalue number, ID varchar2(3));

insert into test_019 values ('Start' , 11500 , 'A01');
insert into test_019 values ('End'   , 11580 , 'A01');
insert into test_019 values ('End'   , 12600 , 'B04');
insert into test_019 values ('Start' , 12500 , 'B04');
insert into test_019 values ('Start' , 11000 , 'X55');
insert into test_019 values ('End'   , 11700 , 'X55');


Have a try at implementing one of these solutions. If you hit a problem, more help is available.

Re: Oracle 7 Help [message #359268 is a reply to message #359246] Fri, 14 November 2008 08:17 Go to previous messageGo to next message
king-kong
Messages: 9
Registered: October 2008
Junior Member
Thanks. This information will allow me to explore further. I was hoping that someone might have done this before and have a neat query for it.

It's an old PeopleSoft application that could not be upgraded. Hence Oracle 7.

Thanks for the test case too. I do not have write access to any Oracle database and so any CREATE/INSERT statements would best be theoretical. I thought my old style ASCII table would suffice.
Re: Oracle 7 Help [message #359270 is a reply to message #359268] Fri, 14 November 2008 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I do not have write access to any Oracle database and so any CREATE/INSERT statements would best be theoretical.

Do you think JRowbottom used a database to write this, not at all, he just write it in notepad (or any other editor) or even on the fly while typing his post.

Regards
Michel
Re: Oracle 7 Help [message #359283 is a reply to message #359268] Fri, 14 November 2008 09:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
It's an old PeopleSoft application that could not be upgraded. Hence Oracle 7.


As Oracel own Peoplesoft, does that mean you've still got support for an Oracle 7 db?

That's got to be on the punishment detail rota at Metalink

Even incorrect Create/insert statements are quicker to fix and show your intentions better than a text table.
Re: Oracle 7 Help [message #359457 is a reply to message #359246] Sun, 16 November 2008 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As a reference for future readers, the 4 mentioned ways are the following ones (queries can be written in other ways).

0/ Initialisation
drop table t;
create table t (t varchar2(5), v integer, id varchar2(3));
insert into t values ('Start', 11500, 'A01');
insert into t values ('End', 11580, 'A01');
insert into t values ('End', 12600, 'B04');
insert into t values ('Start', 12500, 'B04');
insert into t values ('Start', 11000, 'X55');
insert into t values ('End', 11700, 'X55');
commit;

SQL> select * from t;
T         V ID
----- ----- ---
Start 11500 A01
End   11580 A01
End   12600 B04
Start 12500 B04
Start 11000 X55
End   11700 X55

6 rows selected.

SQL> var inval number;
SQL> exec :inval := 11555 

PL/SQL procedure successfully completed.


1/ Set based.
SQL> select id from t where t = 'Start' and v <= :inval
  2  intersect
  3  select id from t where t = 'End' and v >= :inval
  4  /
ID
---
A01
X55

2 rows selected.


2/ Self join
SQL> select a.id
  2  from t a, t b
  3  where a.id = b.id
  4    and a.t = 'Start' and a.v <= :inval
  5    and b.t = 'End' and b.v >= :inval
  6  /
ID
---
A01
X55

2 rows selected.


3/ Aggregation
SQL> select id
  2  from ( select id, min(v) minv, max(v) maxv
  3         from t
  4         group by id )
  5  where :inval between minv and maxv
  6  /
ID
---
X55
A01

2 rows selected.


4/ hierarchical (connect by)
SQL> select id
  2  from t
  3  where prior v <= :inval
  4    and v >= :inval
  5  start with t = 'Start'
  6  connect by prior id = id and prior t = 'Start' and t = 'End'
  7  /
ID
---
A01
X55

2 rows selected.

Regards
Michel
Re: Oracle 7 Help [message #359549 is a reply to message #359457] Mon, 17 November 2008 03:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's a different version of 3 that you can use:
select id
from t
having :inval between min(v) and max(v)
group by id
Re: Oracle 7 Help [message #359551 is a reply to message #359549] Mon, 17 November 2008 03:59 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, a better one, moreover if you look at execution plan you see that Oracle rewrites my version to JRowbottom's one (at least in 10.2.0.4) merging the inline view into the outer query.

Regards
Michel

[Updated on: Mon, 17 November 2008 04:09]

Report message to a moderator

Previous Topic: strange trigger
Next Topic: Returning a ref cursor with updated contents of a cursor variable
Goto Forum:
  


Current Time: Fri Dec 09 21:41:05 CST 2016

Total time taken to generate the page: 0.22891 seconds