mjt@acd4.UUCP ( Mark Teegarden ) (06/14/90)
Here's an interesting situation that I just ran into while doing
program maintenance in ORACLE. A certain select using a union will
pull up the same record twice. I am puzzled about how to fix it
without seriously degrading performance. I have been working with a
table that looks about like this. (Field names have been changed to
protect the innocent...B-):
Table: GAMES
State PlayerA handicapA PlayerB handicapB
----- ------- --------- ------- ---------
IN 1 1 7 1
IN 1 2 7 3
IN 2 1 8 1
IN 2 4 8 2
IL 3 1 9 3
The view is built to retrieve a player and his opponent no matter if
the player is playerA or playerB. If playerB is the selected player,
the record will be selected with player B displayed with his
respective handicap as the first player (Player) and player A
displayed with his respective handicap as the second player
(Opponent). The actual view that I was working with was more
complicated and used several tables instead of just one. The view can
be approximated as this for our sample table:
Create view Match as
select State, PlayerA Player, handicapA hcap1, PlayerB Opponent,
handicapB hcap2
from games
union
select State, PlayerB Player, handicapB hcap1, PlayerA Opponent,
handicapA hcap2
from games;
If I used the view to select all the players and their opponents from
Illinois, the results would look like this:
select * from match where state = 'IL';
State Player hcap1 Opponent hcap2
----- ------ ----- -------- -----
IL 3 1 9 3
IL 9 3 3 1
How should I fix the view or the select to display this record only
once and still be able to select by state alone? Anyone have any
suggestions?
--
-- --
Mark Teegarden 2 wire (812)232-6051
Applied Computing Devices 4 wire uunet!acd4!mjt
100 N. Campus Dr. Aleph Pk. mjt@acd4kaf9345@evtprp0b.UUCP (Kim A. Faedtke) (06/15/90)
In article <1990Jun13.174337.12111@acd4.UUCP> mjt@DEV2.UUCP ( Mark Teegarden ) writes: >Here's an interesting situation that I just ran into while doing >program maintenance in ORACLE. A certain select using a union will >pull up the same record twice. I am puzzled about how to fix it >without seriously degrading performance. I have been working with a >table that looks about like this. (Field names have been changed to >protect the innocent...B-): > >Table: GAMES >State PlayerA handicapA PlayerB handicapB >----- ------- --------- ------- --------- >IN 1 1 7 1 >IN 1 2 7 3 >IN 2 1 8 1 >IN 2 4 8 2 >IL 3 1 9 3 > > >How should I fix the view or the select to display this record only >once and still be able to select by state alone? Anyone have any >suggestions? > >-- I would put just one record in the table listing a player and his opponent. In other words, not all of the players would be listed in the 'Player' column, some would be listed in the 'Opponent' column. I would also change the columns in the table to be 'Player' and 'Opponent' instead of 'PlayerA' and 'PlayerB'. -- |Kim Faedtke (206) 266-8653 (wk) | |Boeing Commercial Airplanes ....uunet!bcstec!evtprp0b!kaf9345 | |M/S 03-87, P.O. Box 3707, Seattle, WA 98124 | |These are not the opinions of The Boeing Co.! | |----------------------------------------------------------------|