Home » SQL & PL/SQL » SQL & PL/SQL » I'm making progress on mid-term.
I'm making progress on mid-term. [message #109589] Sat, 26 February 2005 13:00 Go to next message
craigier
Messages: 42
Registered: February 2005
Member
I've created a table 'cards' with two columns NBR NUMBER(22) and CARD VARCHAR2(2). There are 52 rows represent one row for every card in a deck. I'm using this to generate random numbers

select r
from (select r
from (select rownum r
from all_objects
where rownum < 52)
order by dbms_random.value)
where rownum <= 52;

I need to figure how to assign my NBR to the corresponding random number so I can "shuffle" the deck. Then show all the numbers <20 as the card value as for each hand. 4 players 5 cards each. Thanks I hope this worded correctly.
  • Attachment: MIDTERM.doc
    (Size: 55.50KB, Downloaded 1288 times)

Report message to a moderator

Re: I'm making progress on mid-term. [message #109592 is a reply to message #109589] Sat, 26 February 2005 18:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
If you added another rownum column to the outer query, you would have two columns, one in numerical order and one in random order. Then you could match one of the columns to the nbr column in the cards table and update a shuffle column with the other number.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109593 is a reply to message #109589] Sat, 26 February 2005 18:57 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
Thanks. Here is what I've come up with.

select r,ROWNUM "NBR"
from (select r, ROWNUM
from (select rownum r
from all_objects
where rownum <= 52)
order by dbms_random.value)
where rownum <= 52;

I can't get this cursor to work to insert the values into the shuffle1 column.

DECLARE
CURSOR c_deal IS select C.nbr, c.card
from cards c

v_DEAL c_DEAL%rowtype;
V_SHUFFLE NUMBER(22);

BEGIN
FOR V_DEAL IN C_DEAL LOOP
select
case r
when 1 then '2D'
WHEN 2 THEN '3D'
WHEN 3 THEN '4D'
WHEN 4 THEN '5D'
WHEN 5 THEN '6D'
WHEN 6 THEN '7D'
WHEN 7 THEN '8D'
WHEN 8 THEN '9D'
WHEN 9 THEN '10D'
WHEN 10 THEN 'JD'
WHEN 11 THEN 'QD'
WHEN 12 THEN 'KD'

ELSE 'AD'
END into v_SHUFFLE1 from (select r
from (select rownum r
from all_objects
where rownum <= 13)
order by dbms_random.value)
where rownum <= 1;

INSERT INTO FACES VALUES (V_ST.NBR, V_ST.FACES, V_SHUFFLE1);
END LOOP;
END;

I know there is only 13 random's just started small and work up. Thanks for any help in advance. I need it.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109594 is a reply to message #109593] Sat, 26 February 2005 19:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Which problem are you working on? You should only be inserting into the faces table in problem 3 without any need for randomness. The randomness is needed in problems 4 and 5 for updating the cards table. You need to do things in the proper order. Please see the demonstration below, that shows the order that you should be doing things and the sample results that you should have after each problem.

-- after you have finished problems 1 and 2, you should have
scott@ORA92> SELECT * FROM faces
  2  /

       NBR FACE
---------- ----------
         1 2
         2 3
         3 4
         4 5
         5 6
         6 7
         7 8
         8 9
         9 10
        10 JACK
        11 QUEEN
        12 KING
        13 ACE

13 rows selected.

scott@ORA92> SELECT * FROM players
  2  /

       NBR PLAYER
---------- ----------
         1 SMITTY
         2 JONES
         3 CRAZY
         4 TINY

scott@ORA92> SELECT * FROM suits
  2  /

       NBR SUIT
---------- ----------
         1 DIAMONDS
         2 HEARTS
         3 CLUBS
         4 SPACES


-- then after problem 3, you shoud have:
scott@ORA92> SELECT * FROM cards
  2  /

       NBR CA   SHUFFLE1   SHUFFLE2   SHUFFLE3      DEALT
---------- -- ---------- ---------- ---------- ----------
         1 2D
         2 3D
         3 4D
         4 5D
         5 6D
         6 7D
         7 8D
         8 9D
         9 1D
        10 JD
        11 QD
        12 KD
        13 AD
        14 2H
        15 3H
        16 4H
        17 5H
        18 6H
        19 7H
        20 8H
        21 9H
        22 1H
        23 JH
        24 QH
        25 KH
        26 AH
        27 2C
        28 3C
        29 4C
        30 5C
        31 6C
        32 7C
        33 8C
        34 9C
        35 1C
        36 JC
        37 QC
        38 KC
        39 AC
        40 2S
        41 3S
        42 4S
        43 5S
        44 6S
        45 7S
        46 8S
        47 9S
        48 1S
        49 JS
        50 QS
        51 KS
        52 AS

52 rows selected.


-- then after problem 4, you should have:
scott@ORA92> SELECT * FROM cards
  2  /

       NBR CA   SHUFFLE1   SHUFFLE2   SHUFFLE3      DEALT
---------- -- ---------- ---------- ---------- ----------
         1 2D         18         50         50
         2 3D         14          8         21
         3 4D         33          7         26
         4 5D          8         47         31
         5 6D         52         21          3
         6 7D         43         28         10
         7 8D         45         52         49
         8 9D         41         12         25
         9 1D         22         44         35
        10 JD         31         16         13
        11 QD          3         33         34
        12 KD         40         15         33
        13 AD          7          4         51
        14 2H         21         38         22
        15 3H          1         26         28
        16 4H         50         43         16
        17 5H          9         25         36
        18 6H         32         45         48
        19 7H         16          1         12
        20 8H         49         36         47
        21 9H         50         22         19
        22 1H         18         51         15
        23 JH         51         20         38
        24 QH         35          9          9
        25 KH         31         29          2
        26 AH          1         39          7
        27 2C         27         24         37
        28 3C          5         27          4
        29 4C         15         32         30
        30 5C         38         46         27
        31 6C         26          6         41
        32 7C         50         41          6
        33 8C         17         30          1
        34 9C         43         49         40
        35 1C         45         48         46
        36 JC         41          3         14
        37 QC         20         10         42
        38 KC         52         23         24
        39 AC         37         34         23
        40 2S         13         31         43
        41 3S          9         35         45
        42 4S         45         14          8
        43 5S         43         13         39
        44 6S         35          2         44
        45 7S         23         18          5
        46 8S          6         11         52
        47 9S         24         40         32
        48 1S         46         17         18
        49 JS         26         42         11
        50 QS          7         19         20
        51 KS         40          5         17
        52 AS          7         37         29

52 rows selected.


-- then after problem 5, you should have:
scott@ORA92> SELECT * FROM cards
  2  /

       NBR CA   SHUFFLE1   SHUFFLE2   SHUFFLE3      DEALT
---------- -- ---------- ---------- ---------- ----------
         1 2D         18         50         50          1
         2 3D         14          8         21
         3 4D         33          7         26
         4 5D          8         47         31
         5 6D         52         21          3
         6 7D         43         28         10
         7 8D         45         52         49
         8 9D         41         12         25
         9 1D         22         44         35          4
        10 JD         31         16         13          3
        11 QD          3         33         34
        12 KD         40         15         33
        13 AD          7          4         51
        14 2H         21         38         22
        15 3H          1         26         28          2
        16 4H         50         43         16
        17 5H          9         25         36
        18 6H         32         45         48          2
        19 7H         16          1         12          1
        20 8H         49         36         47
        21 9H         50         22         19
        22 1H         18         51         15          4
        23 JH         51         20         38          3
        24 QH         35          9          9
        25 KH         31         29          2
        26 AH          1         39          7
        27 2C         27         24         37
        28 3C          5         27          4
        29 4C         15         32         30          3
        30 5C         38         46         27          3
        31 6C         26          6         41
        32 7C         50         41          6
        33 8C         17         30          1          1
        34 9C         43         49         40
        35 1C         45         48         46          3
        36 JC         41          3         14          4
        37 QC         20         10         42
        38 KC         52         23         24          4
        39 AC         37         34         23
        40 2S         13         31         43
        41 3S          9         35         45
        42 4S         45         14          8          2
        43 5S         43         13         39          1
        44 6S         35          2         44          2
        45 7S         23         18          5
        46 8S          6         11         52
        47 9S         24         40         32          1
        48 1S         46         17         18          4
        49 JS         26         42         11
        50 QS          7         19         20          2
        51 KS         40          5         17
        52 AS          7         37         29

52 rows selected.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109599 is a reply to message #109589] Sat, 26 February 2005 23:26 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
How did you get it to run the shuffle in number 4? I've been fooling with it all day. I think I can get the deal part(#5) to work but i need to get 4 first. Did you use a cursor/loop or just a cursor? I have figured out #3 using a straight insert statement. Ala, insert into cards values (1, '2D',NULL,NULL,NULL,NULL); but I can get the shuffle. Thank you very much for all of the help you have offered. This is more than my 35 yr old mind can figure. Thanks again, I do appriecaite all your help.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109600 is a reply to message #109599] Sun, 27 February 2005 00:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
For problem 3, I believe it called for a procedure using cursors, not just insert statements. This can be accomplished by nesting one for loop with an implicit cursor within another. You can loop through the faces and suits tables and concatenate the first characters of two of the columns to create the required column for the cards table and insert it.

For problem 4, there are various ways to do things. You were very close with your select statement with one numericly ordered column of numbers and another randomly ordered column of numbers. You can use that in an update statement and do one update statement for each shuffle and put those update statements within a procedure. Or you can use it in a for loop and do three separate for loops, no nesting. I have tested both methods. You can create explicit cursors if you like and loop through them, but I prefer a for loop with an implicit cursor. It is less code and I find it easier to read and maintain.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109621 is a reply to message #109589] Sun, 27 February 2005 07:07 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
Would you be willing share the script? You don't have to give me the exact code. Do I put the for loop inside the implicit cursor and use the random number generator or the other way around? I'm just not sure of the structure. We have used loop and cursor but never together and inserting values into tables. We always used an dmbs_output for queries. I understand what i need to do but not how. Thank you again for responding. This is incredibly hard, at least I think so. Thanks again. This what we were told to reconfigure:

DECLARE
CURSOR c_st IS select s.s_id, cs.c_sec_id
from course_section cs, student s
minus
select s_id, c_sec_id
from enrollment;

v_st c_st%rowtype;
v_grade char(1);

BEGIN
FOR V_ST IN C_ST LOOP
select
case r
when 1 then 'A'
WHEN 2 THEN 'B'
WHEN 3 THEN 'C'
WHEN 4 THEN 'D'
ELSE 'F'
END into v_grade from (select r
from (select rownum r
from all_objects
where rownum <= 5)
order by dbms_random.value)
where rownum <= 1;

INSERT INTO ENROLLMENT VALUES (V_ST.S_ID, V_ST.C_SEC_ID, V_GRADE);
END LOOP;
END;


But i'm kind of stuck. Once i get the shuffle I think I will have it. Thank you again.

[Updated on: Sun, 27 February 2005 08:00]

Report message to a moderator

Re: I'm making progress on mid-term. [message #109627 is a reply to message #109589] Sun, 27 February 2005 11:02 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
Here's what I've been working on

DECLARE
CURSOR c_deal IS select c.nbr
from cards c;


v_DEAL c_DEAL%rowtype;
V_SHUFFLE NUMBER(22);

BEGIN
FOR V_DEAL IN C_DEAL LOOP
select
case r
when 1 then '2D'
WHEN 2 THEN '3D'
WHEN 3 THEN '4D'
WHEN 4 THEN '5D'
WHEN 5 THEN '6D'
WHEN 6 THEN '7D'
WHEN 7 THEN '8D'
WHEN 8 THEN '9D'
WHEN 9 THEN '10D'
WHEN 10 THEN 'JD'
WHEN 11 THEN 'QD'
WHEN 12 THEN 'KD'
ELSE 'AD'

END into v_SHUFFLE1 from select r,ROWNUM "NBR"
from (select r, ROWNUM
from (select rownum r
from all_objects
where rownum <= 52)
order by dbms_random.value)
where rownum <= 52;


INSERT INTO cards VALUES (V_ST.NBR,
V_SHUFFLE1);
END LOOP;
END;

Report message to a moderator

Re: I'm making progress on mid-term. [message #109628 is a reply to message #109627] Sun, 27 February 2005 12:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
I believe it would be unethical for me to share the script that I have written, as it would amount to doing your midterm for you. I don't mind helping you learn though, so that you can do your midterm yourself. It is better if you post what you have and I make suggestions to point you in the right direction. I am repeating myself here, but which problem are you working on, 3 or 4? It would help if you would answer that question. You seem to be attempting to combine the two problems or something. In problem 3 you should be inserting values into the nbr and card columns of the cards table. All inserts into the other three tables should have been done in problems 1 and 2, so there should be no more inserts at all for all of the rest of the problems. Problem 3 also does not require any use of random ordering. When you get to problem 4, you should already have rows in your cards table with values in the nbr and card columns that were inserted in problem 3. In problems 4, you should therefore be updating, not inserting values into the shuffle1 , shuffle2, and shuffle3 columns only, and leaving the rest of the columns alone, neither inserting nor updating them. It would help if you post the results of:

SELECT * FROM faces
/
SELECT * FROM players
/
SELECT * FROM suits
/
SELECT * FROM cards
/

so that I can see that you have completed problems 1 and 2 properly, since if they are not done correctly, you will not be able to get the rest to work. I will wait for you to provide the results of that and tell me which problem you are working on, 3 or 4, before offering further suggestions, since I can't really tell you what to change in the code if I do not know what you are trying to make it do.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109629 is a reply to message #109589] Sun, 27 February 2005 12:13 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
I am on problem 4 updating the shuffles. All of my tables look as they are supposed to. I am having a problem getting the update to work with the shuffle:

UPDATE CARDS
SET shuffle1 = (select r,ROWNUM "NBR"
from (select r, ROWNUM
from (select rownum r
from all_objects
where rownum <= 52)
order by dbms_random.value)
where rownum <= 52)
where NBR <= 52;

But as you can tell this is not working. I figure I would start small and see what happens. I'm getting an invalid character. I hope this is what you are looking for. Thanks again.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109630 is a reply to message #109629] Sun, 27 February 2005 12:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
You cannot update one column with two values.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109631 is a reply to message #109630] Sun, 27 February 2005 12:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
You need to update the shuffle1 column with one of the values where the other value matches the nbr column.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109632 is a reply to message #109589] Sun, 27 February 2005 12:26 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
do you mean from one of the other tables or in the random number generator? I am 100% stumped. I've tired 20 diffrent updates and nothing.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109633 is a reply to message #109631] Sun, 27 February 2005 12:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Here are some more hints. In order to work up to it one piece at a time, you might try creating a table with the two columns of numbers, one ordered and one random, using your select statement, that you already know works. Then try to update your cards table from that newly created table. Once you have gotten that to work, then subsitutte your select statement that you used to created the table in place of your table in your update statement and get that to work. Then put that update statement in a procedure.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109634 is a reply to message #109633] Sun, 27 February 2005 12:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
It looks like we were typing at the same time. I meant to update the shuffle1 column of the cards table from the randomly generated numbers in the select statement that you have written to generate numbers in random order by selecting the rownum from the all_tables table and using dbms_random to put them in random order. You are very close, but are missing some basic issues with how to do an update statement. You cannot:

UPDATE table1
SET one_table1_column =
(SELECT one_value, another_value
FROM
...

you need to:

UPDATE table1
SET one_table1_column =
(SELECT one_value
FROM
...
WHERE another_table1_column = another_values;

Report message to a moderator

Re: I'm making progress on mid-term. [message #109637 is a reply to message #109589] Sun, 27 February 2005 12:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
It looks like the select statement that your instructor provided can be used as is within your code and the rest generally demonstrates how to create a procedure, how to create a function, an explicit cursor, an implicit cursor, and usage of the select statement within a procedure, however it does not do exactly what you need to do, and you should not expect it to do so, otherwise he would have written your code for you, and you would just be substituting the table names and column names, and not having to think or learn anything. Why don't you try doing as I suggested, one step at a time, and create a table using the select statement that you have been given, as demonstrated below, then try to update the cards table from that new table. I am curious. Where do you go to school and who is your instructor?

  1  create table test_tab as
  2  select r,ROWNUM "NBR"
  3  from (select r, ROWNUM
  4  from (select rownum r
  5  from all_objects
  6  where rownum <= 52)
  7  order by dbms_random.value)
  8* where rownum <= 52
scott@ORA92> /

Table created.

scott@ORA92> select * from test_tab;

         R        NBR
---------- ----------
         5          1
        12          2
        44          3
         7          4
         8          5
        42          6
        40          7
        38          8
        31          9
        41         10
        46         11
        17         12
        16         13
        14         14
        37         15
         2         16
        48         17
        21         18
        43         19
        20         20
        26         21
        22         22
        10         23
         1         24
        30         25
         9         26
        32         27
        51         28
        24         29
        33         30
        28         31
        13         32
        34         33
        50         34
         4         35
        19         36
        35         37
        25         38
        52         39
        18         40
        23         41
        47         42
         3         43
         6         44
        29         45
        11         46
        49         47
        39         48
        36         49
        27         50
        45         51
        15         52

52 rows selected.

scott@ORA92>

Report message to a moderator

Re: I'm making progress on mid-term. [message #109639 is a reply to message #109634] Sun, 27 February 2005 13:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
There are many ways to do things. If you can get the update to run in SQL, then put the update statements into a procedure your procedure can be as simple as:

CREATE OR REPLACE PROCEDURE shuffle_cards
AS 
BEGIN
  UPDATE cards 
  SET    shuffle1 =
  ...
  UPDATE cards
  SET    shuffle2 =
  ...
  UPDATE cards
  SET    shuffle3 = 
  ...
END;
/


where each update statement updates all the rows in one column or you can do something like:

CREATE OR REPLACE PROCEDURE shuffle_cards
AS
BEGIN
  FOR rec IN (SELECT ...) LOOP
     UPDATE cards
     SET    shuffle1 = 
     ...
  END LOOP;
  FOR rec IN (SELECT ...) LOOP
     UPDATE cards
     SET    shuffle2 = 
     ...
  END LOOP;
  FOR rec IN (SELECT ...) LOOP
     UPDATE cards
     SET    shuffle3 = 
     ...
  END LOOP;
END;
/


where each time through each loop it updates one row in each column.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109640 is a reply to message #109589] Sun, 27 February 2005 13:11 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
I'm working on the update statement now. I'm not 100% yet on how to do it exactly but I will narrow it down. Just one more quick question. I should use the test_tab in the update statement?

Report message to a moderator

Re: I'm making progress on mid-term. [message #109641 is a reply to message #109640] Sun, 27 February 2005 13:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Yes, use the test_tab initially as part of the process of developing your code:

UPDATE cards
SET    shuffle1 = 
       (SELECT ...
        FROM   test_tab
        ...


then once you have gotten that to work, then just substitute the select statement that was used to create the test_tab for the test_tab table, enclosing that statement within parentheses:

UPDATE cards
SET    shuffle1 = 
       (SELECT ...
        FROM   (SELECT ...) -- instead of the test_tab table
        ...



You will not use the test_tab table in your final code. It is only a stepping stone to developing your code. You will find, once you get the update to work with the test_tab that when you try to use the select statement in its place there may be other issues. If you were just to skip ahead to that stage, you would not know if you had the update statement correct and whether the errors were a result of that or something else.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109643 is a reply to message #109640] Sun, 27 February 2005 13:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Here is a link that no Oracle developer or DBA should be without. It is a menu that can be used to search the Oracle online documentation in various ways. It may require that you register on Oracle's website, with OTN (Oracle Technology Network) to view it, but it is free.

http://otn.oracle.com/pls/db92/db92.homepage

If, for example, you search for UPDATE, you will find various sections that include syntax, explanation, and examples. You will proably find most of what you need in the SQL reference manual. This should be the first place that you search. Here is another link to the section on UPDATE in the SQL reference manual.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_108a.htm#SQLRF01708

Hopefully, this will help you figure out how to create your update statement.

Are you switching careers or are you a programmer learning a new language? Sometimes it helps if we know what other languages you have programmed in, so we can just tell you what to expect to be the same or similar and what is different.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109644 is a reply to message #109589] Sun, 27 February 2005 13:26 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
here's my update

update cards
set shuffle1 =
(select r
from test_tab)
where nbr <=52;

but getting single-row subquery returns more than one row.

I thought that I was updating shuffle1 with R from the test_tab
where nbr <= 52 in the cards table. Please tell me I'm getting warmer.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109645 is a reply to message #109644] Sun, 27 February 2005 13:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
If you use the link that I provided, there is an option within that link to search for error messages:

http://otn.oracle.com/pls/db92/db92.error_search?remark=homepage&prefill=ORA-

Any time that you receive an error, this is where you should go to find out more about that error. It helps if you provide the actual error number as shown below.

scott@ORA92> update cards
2 set shuffle1 =
3 (select r
4 from test_tab)
5 where nbr <=52;
(select r
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

So, if you use that menu to search for ORA-01427, you get:

"ORA-01427 single-row subquery returns more than one row

Cause: The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row.

Action: Use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is retrieved."


I am tryng to teach you how to diagnose things. The text of this one may be somewhat confusing to you here. The problem is that you are trying to update each value of shuffle1 in each row with 52 values from the test_tab table. You can only update one value with one value. As I said before, you need to compare another column in the cards table to another column in the test_tab table to narrow those 52 values down to one. Otherwise, it is like saying to set the row of this column = to 1 and 2 and 3 and 4 and ... and 51 and 52, when you can only set it to one of those values. The key part of the message text above that applies is, "reword the query so only one row is retrieved." The rest of the text applies to other situations where you might want to compare a value to multiple values. Oracle has no way of knowing which you are trying to do, when it provides the error message.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109646 is a reply to message #109589] Sun, 27 February 2005 13:43 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
I've managed to set all of shuffle1 = test_tab_nbr random number(22) for me.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109647 is a reply to message #109646] Sun, 27 February 2005 13:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Are you saying that all rows in the shuffle1 column ended up with the value 22? Please post the update statement that you used.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109648 is a reply to message #109589] Sun, 27 February 2005 13:52 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
I was trying everything and this is what I used.



update cards
set shuffle1 =
(select R
from test_tab
where nbr >= 52)
where nbr <= 52;

Report message to a moderator

Re: I'm making progress on mid-term. [message #109649 is a reply to message #109648] Sun, 27 February 2005 14:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
As I said previously, you cannot update one value with 52 values, you need to add a condition to narrow it down to one value. You need to do as I said previously:

UPDATE table1
SET one_table1_column =
(SELECT one_value
FROM
...
WHERE another_table1_column = another_values);

Remember the test_tab tables has two columns for a reason, so that we can update using the value from one of the columns and compare the tables using the other column, in order to limit it to one value instead of 52.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109650 is a reply to message #109589] Sun, 27 February 2005 14:06 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member


update cards
set shuffle1 =
(select R
from test_tab
where test_tab.nbr = cards.nbr);


I did it!!

Report message to a moderator

Re: I'm making progress on mid-term. [message #109651 is a reply to message #109650] Sun, 27 February 2005 14:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Congratulations. Now, try substituting the select statement that you used to create the test_tab table in place of your test_tab table in the upate statement.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109652 is a reply to message #109589] Sun, 27 February 2005 14:12 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
Now that I did that what do I compare when using my random generator? I understand what I did but not sure of what to do next. BTW you did in one afternoon which hadn't been able to done in six weeks of class. Thanks a lot. It make oracle more enjoyable when you understand what you are doing.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109653 is a reply to message #109652] Sun, 27 February 2005 14:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Are you saying that you successfully substituted the select statement for the test_tab, executed the update, and it worked properly? Please post the update statement that you used.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109654 is a reply to message #109589] Sun, 27 February 2005 14:22 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
No I'm still trying to figure out where to substitute the random number generator in the insert and if I need the where test_tab.nbr = cards.nbr or not. I just realized I have to deal the buggers yet.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109655 is a reply to message #109652] Sun, 27 February 2005 14:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
I think I understand what you are saying and asking now. The comparison should not change. Just put the select statement in place of the test_tab and see what happens.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109656 is a reply to message #109589] Sun, 27 February 2005 14:28 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
from selesctr,ROWMUN "NBR"
ERRORat line 4
ORA-00903 Invalid table name



update cards
set shuffle1 =
(select R
from select r,ROWNUM "NBR"
from (select r, ROWNUM
from (select rownum r
from all_objects
where rownum <= 52)
order by dbms_random.value)
where rownum <= 52
where test_tab.nbr = cards.nbr)

Report message to a moderator

Re: I'm making progress on mid-term. [message #109657 is a reply to message #109655] Sun, 27 February 2005 14:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
You can use test_tab as an alias:

UPDATE cards
SET    shuffle1 =
       (SELECT r
        FROM   (SELECT ...) test_tab
        WHERE  test_tab.nbr = cards.nbr);

Report message to a moderator

Re: I'm making progress on mid-term. [message #109658 is a reply to message #109656] Sun, 27 February 2005 14:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
You also need to put parenthese () around your seleect statement.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109659 is a reply to message #109657] Sun, 27 February 2005 14:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Just to make sure that your code is updating from the alias and not the test_tab table, get rid of the test_tab table:

DROP TABLE test_tab;

You might also want to use a different alias:

UPDATE cards
SET    shuffle1 =
       (SELECT r
        FROM   (SELECT ...) random_numbers_table
        WHERE  random_numbers_table.nbr = cards.nbr);

Report message to a moderator

Re: I'm making progress on mid-term. [message #109660 is a reply to message #109589] Sun, 27 February 2005 14:36 Go to previous messageGo to next message
craigier
Messages: 42
Registered: February 2005
Member
It is working. The shuffles are full. Interested in going for dealing problem? Thank you very much. I'm sure you hear that a lot but I truly mean it. Without you I'm done for.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109661 is a reply to message #109660] Sun, 27 February 2005 14:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Before you move on to the dealing problem, you need to finish the shuffle problem, so take that update statement and put it in a procedure, make two additional copies of the update statment, one for shuffle2, and one for shuffle3, and add them to the procedure, then execute the procedure. When you are done with that, then post what you have tried on the dealing problem, complete with any errors received, but please try looking up the error messages and figuring it out for yourself first.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109662 is a reply to message #109660] Sun, 27 February 2005 14:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
By the way, did you ever get problem 3 done correctly, using a procedure, as per the instructions? I believe you resorted to a simple insert at one point and I doubt that your instructor will accept that. If not, then you should go back and do problem 3 correctly, before moving on to problem 5. Problem 3 uses some simple looping that will also be used in problem 5, but with many more complications. It would be better to get the hang of a simple loop in problem 3 before progressing to problem 5.

Report message to a moderator

Re: I'm making progress on mid-term. [message #109663 is a reply to message #109589] Sun, 27 February 2005 14:49 Go to previous messageGo to previous message
craigier
Messages: 42
Registered: February 2005
Member
Here's my procedure but it was created with errors;

CREATE OR REPLACE PROCEDURE shuffle_cards
AS
BEGIN
UPDATE CARDS
SET shuffle1 =
(select R
from select r,ROWNUM "NBR"
from (select r, ROWNUM
from (select rownum r
from all_objects

where rownum <= 52)
order by dbms_random.value)
where rownum <= 52
where test_tab.nbr = cards.nbr);

UPDATE CARDS
SET shuffle2 =
(select R
from (select r,ROWNUM "NBR"
from (select r, ROWNUM
from (select rownum r
from all_objects
where rownum <= 52)
order by dbms_random.value)
where rownum <= 52) test_tab
where test_tab.nbr = cards.nbr);

UPDATE CARDS
SET shufle3 =
(select R
from (select r,ROWNUM "NBR"
from (select r, ROWNUM
from (select rownum r
from all_objects
where rownum <= 52)
order by dbms_random.value)
where rownum <= 52) test_tab
where test_tab.nbr = cards.nbr);

END;


I haven't even begun to look at the dealing problem. Are you going to be around in an hour or so? I should be good and confused by then plus have done some work on it. Thanks again.

Report message to a moderator

Previous Topic: Create .xls
Next Topic: need help with insert
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Feb 08 17:58:14 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.