Results 1 to 7 of 7
  1. #1
    Join Date
    May 2008
    Posts
    4

    Unanswered: Problem with Insert

    I am currently trying to insert data into a table and can not get the code correct.

    What I am wanting to do is select all users from IP_USER table with a ROLE_CODE of 466 and insert the USER_NAME along with a few other values into a table called GRANTED_CODE and I cant seem to figure out how to do it.

    GRANTED_CODE has 11 columns with the 2nd column needing to be the USER_NAME from IP_USER.

    The biggest problem I believe I am running into is that there is no direct relationship between the 2 tables. Actually GRANTED_CODE is not directly related to any table within the database.

    Any help on how to approach this would be appreciated and I am using Oracle SQL+ to code this.
    Last edited by Cornelious; 05-05-08 at 16:55.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In order to INSERT a record, you don't need a relationship. Syntax is quite simple:
    Code:
    INSERT INTO granted_code (column1, user_name, column3, etc.)
    SELECT some_column1, USER_NAME, another_Column, ...
    FROM ip_user
    WHERE role_code = 466;
    If that's not what you are looking for, too bad (because we have no idea how your tables look like, what are values of other 10 columns in the 'granted_code' table, where to find them etc.

  3. #3
    Join Date
    May 2008
    Posts
    4
    The only thing I am trying to pull from the IP_USER table is USER_NAME and ROLE_CODE and the other columns are all the exact same data that has to be associated with the 68 people with a ROLE_CODE of 466 but is not available in any table and will have to be inserted manually.

    The ROLE_CODE is for all managers in the organization and I am trying to restrict all of their access from viewing a particular object. I can insert them one at a time no problem but I am trying to find a way to do all of them at once with the fact that other then the USER_NAME/ROLE_CODE the data can not be found anywhere else.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK, so you'll have to insert this values manually. If these values are the same for all of them, where's the problem? Do it as
    Code:
    INSERT INTO granted_code (column1, user_name, column3, etc.)
    SELECT 'value_for_the_first_column', USER_NAME, 'value_for_another_Column' , 'x', 123, 'y', 'm', 332211 ...
    FROM ip_user
    WHERE role_code = 466;

  5. #5
    Join Date
    May 2008
    Posts
    4
    This is what the problem is, I have tried that and copied what you told me to put in but actually put in my variables

    SQL> INSERT INTO GRANTED_CODE (FROM_USER, USER_NAME, TYPE, CODE, USAGE, REPORT_TIME, APPROVE_TIME, APPROVE_EXPENSES, PRIM_RES, APPROVE_REQUEST, RESTR_FORWARD_IND)
    2 SELECT 'BG592CM', USER_NAME, 'WBS1', '33131', 'EX', '', '', '', '', '', '')
    3 FROM IP_USER
    4 WHERE ROLE_CODE = '466'
    and I receive this in response to that code

    ERROR at line 2:
    ORA-00923: FROM keyword not found where expected

  6. #6
    Join Date
    May 2008
    Posts
    4
    Sorry that worked I just had horrible syntax and typing errors I didnt catch.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    In case it matters, '' (zero-length text string) is the same as NULL in Oracle.

    Code:
    SQL> create table temp (f varchar2(1));
    
    Table created.
    
    SQL> insert into temp values ('');
    
    1 row created.
    
    SQL> select count(*) from temp where f is null;
    
      COUNT(*)
    ----------
             1

Posting Permissions

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