This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Dec 8, 2012 4:23 AM by EdStevens Go to original post RSS
  • 15. Re: select lookup statement
    960529 Newbie
    Currently Being Moderated
    Okay, here is another idea...thoughts on getting it to work...any logic/syntax pointers...

    CREATE TABLE #M1
    (
    COL1 INT,
    COL2 INT
    )

    INSERT INTO M1(COL1)
    SELECT DISTINCT IS_MGR_OID FROM HR.IS;

    INSERT INTO M1(COL2)
    SELECT IS_OID FROM HR.IS where IS_MGR_OID=M1(COL1);

    UPDATE HR.IS set IS_MGR_POID=M1(COL2) where IS_MGR_OID=M1(COL1);

    DROP TABLE #M1

    END
  • 16. Re: select lookup statement
    sb92075 Guru
    Currently Being Moderated
    How will you, I, or anyone recognize that a correct solution has been posted?
  • 17. Re: select lookup statement
    960529 Newbie
    Currently Being Moderated
    Logically proofing the commands I should say...then I can execute any recommendations since what is provided doesn't work yet.

    Thanks for the insight.
  • 18. Re: select lookup statement
    sb92075 Guru
    Currently Being Moderated
    fortgolf wrote:
    Logically proofing the commands I should say...then I can execute any recommendations since what is provided doesn't work yet.

    Thanks for the insight.
    you need to post CREATE TABLE statements for all tables involved.
    You need to post INSERT statements contain sample data.
    You need to post expect/desired results based upon sample data above & "rules" why specific data is produced.


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 19. Re: select lookup statement
    960529 Newbie
    Currently Being Moderated
    Did all that...the request is asking to verify if the statements are correct...if they are not correct...kindly correct them...otherwise I get distracted.
  • 20. Re: select lookup statement
    EdStevens Guru
    Currently Being Moderated
    fortgolf wrote:
    Okay, here is another idea...thoughts on getting it to work...any logic/syntax pointers...

    CREATE TABLE #M1
    (
    COL1 INT,
    COL2 INT
    )

    INSERT INTO M1(COL1)
    SELECT DISTINCT IS_MGR_OID FROM HR.IS;

    INSERT INTO M1(COL2)
    SELECT IS_OID FROM HR.IS where IS_MGR_OID=M1(COL1);

    UPDATE HR.IS set IS_MGR_POID=M1(COL2) where IS_MGR_OID=M1(COL1);

    DROP TABLE #M1

    END
    Lot's of them. Why didn't you do this for yourself?
    SQL> CREATE TABLE #M1
    (
      2    3  COL1 INT,
      4  COL2 INT
      5  )
      6
    SQL> INSERT INTO M1(COL1)
      2  SELECT DISTINCT IS_MGR_OID FROM HR.IS;
    SELECT DISTINCT IS_MGR_OID FROM HR.IS
                                       *
    ERROR at line 2:
    ORA-00903: invalid table name
    
    
    SQL>
    SQL> INSERT INTO M1(COL2)
      2  SELECT IS_OID FROM HR.IS where IS_MGR_OID=M1(COL1);
    SELECT IS_OID FROM HR.IS where IS_MGR_OID=M1(COL1)
                          *
    ERROR at line 2:
    ORA-00903: invalid table name
    
    
    SQL>
    SQL> UPDATE HR.IS set IS_MGR_POID=M1(COL2) where IS_MGR_OID=M1(COL1);
    UPDATE HR.IS set IS_MGR_POID=M1(COL2) where IS_MGR_OID=M1(COL1)
              *
    ERROR at line 1:
    ORA-00903: invalid table name
    
    
    SQL>
    SQL> DROP TABLE #M1
      2  ;
    DROP TABLE #M1
               *
    ERROR at line 1:
    ORA-00911: invalid character
    Where to begin?

    - Use of '#' as part of a table name is not allowed, but oracle couldn't flag it at this point because it couldn't find the end of the CREATE TABLE statement because you left out the semi-colon.

    - you try to create table "#M1", but then try to insert into table "M1".

    - you didn't terminate your INSERT statement with a semi-colon

    - lots of errors stem from lack of semi-colons where they are needed.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points