Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2017
    Posts
    8

    Answered: Showing all results including duplicates..?

    Hi All,


    I have a table called Allocation which has columns (Group_ID, StaffID, Role) of which Group_ID is PK of another table Tutorial_Grp, StaffID is PK of table Staff so they are FK in Allocation table, but Role column is a column of Allocation table.


    Code:
    CREATE TABLE TUTORIAL_GRP(
    GROUP_ID NUMBER(1),
    SESSION_DAY VARCHAR2(10),
    VENUE VARCHAR2(10),
    CONSTRAINT PK_TUTORIALGRP_GROUPID PRIMARY KEY (GROUP_ID)
    );
    
    Create Table Staff(StaffID number(4) Primary Key,
    Staff_Name varchar2(50),
    Gender varchar2(6) constraint gendercheck not null
    );
    
    Create table Allocation(StaffID,
    Group_ID,
    Role varchar2(8),
    Primary Key(StaffID, Group_ID),
    Foreign Key (StaffID) references Staff,
    Foreign Key (Group_ID) references Tutorial_Grp
    );
    Now I add data into Allocation table :

    Code:
    Insert into Allocation (Group_ID, StaffID, Role) values (1, 2001, 'Tutor'); 
    Insert into Allocation (Group_ID, StaffID, Role) values (3, 2001, 'Tutor'); 
    Insert into Allocation (Group_ID, StaffID, Role) values (2, 2002, 'Assistant');
    Insert into Allocation (Group_ID, StaffID, Role) values (4, 2002, 'Assistant');
    Insert into Allocation (Group_ID, StaffID, Role) values (1, 2003, 'Assistant'); 
    Insert into Allocation (Group_ID, StaffID, Role) values (2, 2003, 'Assistant'); 
    Insert into Allocation (Group_ID, StaffID, Role) values (4, 2003, 'Tutor'); 
    Insert into Allocation (Group_ID, StaffID, Role) values (2, 2004, 'Tutor'); 
    Insert into Allocation (Group_ID, StaffID, Role) values (, 2005, '');
    Now I want to view the results :

    Code:
    Select * from allocation;
    I see results only once, not the duplicates.

    Code:
    StaffID | Group_ID | Role
    2001		1	Tutor
    2001		3	Tutor
    2003		4	Tutor
    2004		2	Tutor
    What I want to do is to view duplicates as well and what I don't understand is why when I have added Group_ID as the first column during insert statement, in the result the first column is StaffID and also why are the results from the Tutor only showing and none from the assistant are shown.
    Last edited by TryllZ; 04-22-17 at 10:25.

  2. Best Answer
    Posted by outrider

    "Role 'Assistant' is too large!

    Quote Originally Posted by TryllZ View Post
    Create table Allocation(StaffID,
    Group_ID,
    Role varchar2(8),
    Primary Key(StaffID, Group_ID),
    Foreign Key (StaffID) references Staff,
    Foreign Key (Group_ID) references Tutorial_Grp
    );
    "


  3. #2
    Join Date
    Feb 2005
    Posts
    59
    Provided Answers: 1
    Role 'Assistant' is too large!

    Quote Originally Posted by TryllZ View Post
    Create table Allocation(StaffID,
    Group_ID,
    Role varchar2(8),
    Primary Key(StaffID, Group_ID),
    Foreign Key (StaffID) references Staff,
    Foreign Key (Group_ID) references Tutorial_Grp
    );

  4. #3
    Join Date
    Apr 2017
    Posts
    8
    Quote Originally Posted by outrider View Post
    Role 'Assistant' is too large!
    Thanks outrider, problem solved, thanks once again..

    However, I did not understand as to why Oracle did not issue any error of maximum character when the data was being input using insert statement..

    Oracle 11g SQL Developer...

    Thank You

  5. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,113
    Provided Answers: 5
    First of all, you should post the whole SQL*Plus session which would show exactly what you are doing. Partial information leads to confusion and it is impossible to reproduce what you claim. For example, because of foreign key constraints, INSERT INTO ALLOCATION fails because we don't have parent key values so I had to improvize.

    Check it carefully, line by line:

    Code:
    SQL> CREATE TABLE TUTORIAL_GRP
      2  (
      3     GROUP_ID      NUMBER (1),
      4     SESSION_DAY   VARCHAR2 (10),
      5     VENUE         VARCHAR2 (10),
      6     CONSTRAINT PK_TUTORIALGRP_GROUPID PRIMARY KEY (GROUP_ID)
      7  );
    
    Table created.
    
    SQL> CREATE TABLE Staff
      2  (
      3     StaffID      NUMBER (4) PRIMARY KEY,
      4     Staff_Name   VARCHAR2 (50),
      5     Gender       VARCHAR2 (6) CONSTRAINT gendercheck NOT NULL
      6  );
    
    Table created.
    
    SQL> CREATE TABLE Allocation
      2  (
      3     StaffID    NUMBER (4),
      4     GROUP_ID   NUMBER (1),
      5     Role       VARCHAR2 (8),
      6     PRIMARY KEY (StaffID, GROUP_ID),
      7     FOREIGN KEY (StaffID) REFERENCES Staff,
      8     FOREIGN KEY (GROUP_ID) REFERENCES Tutorial_Grp
      9  );
    
    Table created.
    
    SQL> -- inserting parent keys
    SQL> INSERT INTO tutorial_grp
      2       VALUES (2, NULL, NULL);
    
    1 row created.
    
    SQL> INSERT INTO staff
      2       VALUES (2002, null, 'x');
    
    1 row created.
    
    SQL> -- inserting into the ALLOCATION table
    SQL> -- "Tutor" will succeed
    SQL> INSERT INTO Allocation (GROUP_ID, StaffID, Role)
      2       VALUES (2, 2002, 'Tutor');
    
    1 row created.
    
    SQL> -- "Assisstant" will fail
    SQL> INSERT INTO Allocation (GROUP_ID, StaffID, Role)
      2       VALUES (2, 2002, 'Assistant');
         VALUES (2, 2002, 'Assistant')
                          *
    ERROR at line 2:
    ORA-12899: value too large for column "SJODS_ERV"."ALLOCATION"."ROLE" (actual: 9, maximum: 8)
    
    
    SQL> -- Select from the ALLOCATION will return only rows that weren't discarded
    SQL> select * From allocation;
    
       STAFFID   GROUP_ID ROLE
    ---------- ---------- --------
          2002          2 Tutor
    
    SQL>
    As a newbie (regarding problems you have), here's one for you: those who live by the GUI, die by the GUI. SQL Developer (or TOAD or any other GUI tool) is great if you know what you are doing. If you don't, well, you have problems.

    You should review GUI output pane; otherwise, you have no idea what exactly happened after running certain statements. "Assistant" obviously wasn't inserted into the ALLOCATION table; Oracle is too stupid to lie, so I believe it raised an error - it was you who failed to notice it. That's why SELECT displays only "Tutors" because they were correctly inserted.

    As of another question you have: INSERT INTO you used specified columns in certain order (Group_ID first, StaffID next, ...) so Oracle inserted those values into appropriate columns. If you omitted column names, INSERT would have failed (because of non-existent parent key or data and column size mismatch). "SELECT *" displays columns ordered by their place in a table, and in there you said that StaffID is the first, followed by Group_ID, ... You could have specified different column order by explicitly naming columns:
    Code:
    SQL> select role, staffid, group_id from allocation;
    
    ROLE        STAFFID   GROUP_ID
    -------- ---------- ----------
    Tutor          2002          2
    So - pay attention to what you do. Don't hurry, be careful and you'll be fine.

  6. #5
    Join Date
    Apr 2017
    Posts
    8
    Quote Originally Posted by Littlefoot View Post
    First of all, you should post the whole SQL*Plus session which would show exactly what you are doing. Partial information leads to confusion and it is impossible to reproduce what you claim. For example, because of foreign key constraints, INSERT INTO ALLOCATION fails because we don't have parent key values so I had to improvize.

    Check it carefully, line by line:

    Code:
    SQL> CREATE TABLE TUTORIAL_GRP
      2  (
      3     GROUP_ID      NUMBER (1),
      4     SESSION_DAY   VARCHAR2 (10),
      5     VENUE         VARCHAR2 (10),
      6     CONSTRAINT PK_TUTORIALGRP_GROUPID PRIMARY KEY (GROUP_ID)
      7  );
    
    Table created.
    
    SQL> CREATE TABLE Staff
      2  (
      3     StaffID      NUMBER (4) PRIMARY KEY,
      4     Staff_Name   VARCHAR2 (50),
      5     Gender       VARCHAR2 (6) CONSTRAINT gendercheck NOT NULL
      6  );
    
    Table created.
    
    SQL> CREATE TABLE Allocation
      2  (
      3     StaffID    NUMBER (4),
      4     GROUP_ID   NUMBER (1),
      5     Role       VARCHAR2 (8),
      6     PRIMARY KEY (StaffID, GROUP_ID),
      7     FOREIGN KEY (StaffID) REFERENCES Staff,
      8     FOREIGN KEY (GROUP_ID) REFERENCES Tutorial_Grp
      9  );
    
    Table created.
    
    SQL> -- inserting parent keys
    SQL> INSERT INTO tutorial_grp
      2       VALUES (2, NULL, NULL);
    
    1 row created.
    
    SQL> INSERT INTO staff
      2       VALUES (2002, null, 'x');
    
    1 row created.
    
    SQL> -- inserting into the ALLOCATION table
    SQL> -- "Tutor" will succeed
    SQL> INSERT INTO Allocation (GROUP_ID, StaffID, Role)
      2       VALUES (2, 2002, 'Tutor');
    
    1 row created.
    
    SQL> -- "Assisstant" will fail
    SQL> INSERT INTO Allocation (GROUP_ID, StaffID, Role)
      2       VALUES (2, 2002, 'Assistant');
         VALUES (2, 2002, 'Assistant')
                          *
    ERROR at line 2:
    ORA-12899: value too large for column "SJODS_ERV"."ALLOCATION"."ROLE" (actual: 9, maximum: 8)
    
    
    SQL> -- Select from the ALLOCATION will return only rows that weren't discarded
    SQL> select * From allocation;
    
       STAFFID   GROUP_ID ROLE
    ---------- ---------- --------
          2002          2 Tutor
    
    SQL>
    As a newbie (regarding problems you have), here's one for you: those who live by the GUI, die by the GUI. SQL Developer (or TOAD or any other GUI tool) is great if you know what you are doing. If you don't, well, you have problems.

    You should review GUI output pane; otherwise, you have no idea what exactly happened after running certain statements. "Assistant" obviously wasn't inserted into the ALLOCATION table; Oracle is too stupid to lie, so I believe it raised an error - it was you who failed to notice it. That's why SELECT displays only "Tutors" because they were correctly inserted.

    As of another question you have: INSERT INTO you used specified columns in certain order (Group_ID first, StaffID next, ...) so Oracle inserted those values into appropriate columns. If you omitted column names, INSERT would have failed (because of non-existent parent key or data and column size mismatch). "SELECT *" displays columns ordered by their place in a table, and in there you said that StaffID is the first, followed by Group_ID, ... You could have specified different column order by explicitly naming columns:
    Code:
    SQL> select role, staffid, group_id from allocation;
    
    ROLE        STAFFID   GROUP_ID
    -------- ---------- ----------
    Tutor          2002          2
    So - pay attention to what you do. Don't hurry, be careful and you'll be fine.
    I am not a newbie and I said Oracle did not issue an error because it did not, I know how Oracle works and have been using for a long time, I am aware of the GUI and what and where the error shows.

    I was well aware that it is going to be so minor a mistake I wont even know it.

    Thanks for your clarification though. Appreciate it.

  7. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,113
    Provided Answers: 5
    Quote Originally Posted by TryllZ
    I am not a newbie ... I know how Oracle works and have been using for a long time
    You have every right to claim so.

    I, on the other hand, have every right not to believe what you claim.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •