This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Dec 8, 2012 4:23 AM by EdStevens RSS

select lookup statement

960529 Newbie
Currently Being Moderated
Hi,

I'm looking for an oracle sql query statement that will return the manager's userid value from a unique attribute value in a different person's account...all accounts are in a single table.

For example, userA has an attribute called 'mymanager', I need to lookup which account in the table corresponds to the mymanager value, then lookup that account's userid value, then update userA's setmanager attribute with the mymanager's userid value.

Thoughts?
  • 1. Re: select lookup statement
    Aman.... Oracle ACE
    Currently Being Moderated
    fortgolf wrote:
    Hi,

    I'm looking for an oracle sql query statement that will return the manager's userid value from a unique attribute value in a different person's account...all accounts are in a single table.

    For example, userA has an attribute called 'mymanager', I need to lookup which account in the table corresponds to the mymanager value, then lookup that account's userid value, then update userA's setmanager attribute with the mymanager's userid value.
    Any sample data for us to work through and what query you wrote so far for the same?

    Aman....
  • 2. Re: select lookup statement
    sb92075 Guru
    Currently Being Moderated
    fortgolf wrote:
    Hi,

    I'm looking for an oracle sql query statement that will return the manager's userid value from a unique attribute value in a different person's account...all accounts are in a single table.

    For example, userA has an attribute called 'mymanager', I need to lookup which account in the table corresponds to the mymanager value, then lookup that account's userid value, then update userA's setmanager attribute with the mymanager's userid value.

    Thoughts?
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ


    why do not include ANY SQL in your post?

    can you compose SQL when you don't know table or column names?
  • 3. Re: select lookup statement
    rp0428 Guru
    Currently Being Moderated
    >
    For example, userA has an attribute called 'mymanager', I need to lookup which account in the table corresponds to the mymanager value, then lookup that account's userid value, then update userA's setmanager attribute with the mymanager's userid value.
    >
    That's pretty easy to do.

    First lookup the account that corresponds to the mymanager value, get that accounts userid and update the attribute with the userid.

    You can do that all with just one query and if you add a hint it will be faster.

    Post the results of executing the query I provided so we can see how it all worked out.
  • 4. Re: select lookup statement
    960529 Newbie
    Currently Being Moderated
    Hi,

    Sorry for the lack of context...
    The table name is HR
    I want to update the manager attribute value in every account in the HR table
    The manager attribute value needs to be the manager's userid value
    Every account has an attribute called mymanager, but the value is NOT the manager's userid, it is a different unique value associated with each account
    As a result, I need to query and find the account with the userA's mymanager value where mymanager=mymid, then lookup the userid attribute of the account, and then take that value and update the source user account's manager attribute.

    Note the two attributes will have the same value: mymanager=mymid

    I didn't provide any sql query because it wouldn't be accurate...

    select mymanager from HR
    ...and then another select to find that account
    where mymanager=mymid
    ...and then another select to find that account's mymid value
    update manager
    ...and then update the manager attribute with the mymid value
  • 5. Re: select lookup statement
    960529 Newbie
    Currently Being Moderated
    to rp0428...didn't see a query to run...
  • 6. Re: select lookup statement
    rp0428 Guru
    Currently Being Moderated
    >
    to rp0428...didn't see a query to run...
    >
    Really? I based the query on the tables and other information that you didn't provide. I thought it might help you get the concept of not being able to work with things that people don't post.

    If you want detailed help you have to provide the details. You can't just say the equivalent of "my car won't go. why won't my car go."
  • 7. Re: select lookup statement
    960529 Newbie
    Currently Being Moderated
    Okay..I'll try again...this is not working as I'm not sure how to declare m2.mymid...

    update manager m1
    set m1.manager = (select mymanager
              from HR m2
              where m2.mymanager = m1.mymid)
    where exists (select NULL
              from HR m2
              where m2.mymanager = m1.mymid)


    The idea is to update the manager attribute with the m1.mymid value found from a query of mymanager.
  • 8. Re: select lookup statement
    sb92075 Guru
    Currently Being Moderated
    CREATE TABLE MANAGER is valid SQL statement which you should provide.


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 9. Re: select lookup statement
    960529 Newbie
    Currently Being Moderated
    Here's another go at the code...though does not work...not sure how the m1.mymanager is resolved in the equation:
              
    update HR m1
    set m1.manager=(case
         when
         (select mymanager from HR m2
         where m2.mymid=m1.mymanager)
         end);
    select * from v$version is:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
  • 10. Re: select lookup statement
    sb92075 Guru
    Currently Being Moderated
    fortgolf wrote:
    Here's another go at the code...though does not work...not sure how the m1.mymanager is resolved in the equation:
    my car does not work.
    how do I make my car go?

    I still do not have any tables to compose SQL against/for.
  • 11. Re: select lookup statement
    960529 Newbie
    Currently Being Moderated
    This is how each user's account looks from a table perspective in SQL Developer...the goal is stated below. Thanks for looking....

    Connections
         oracleOIMdb
              Other Users
                   HR
                        Tables (Filtered)
                             IS
                                  IS_OID
                                  IS_POID
                                  IS_MGR_POID
                                  IS_MGR_OID
                                  IS_SPVR_OID
                                  IS_FIRST
                                  IS_LAST
                                  IS_MIDDLE
                                  IS_ADDRESS1
                                  IS_ADDRESS2
                                  IS_ADDRESS3
                                  IS_ADDRESS4
                                  IS_CITY
                                  IS_STATE
                                  IS_COUNTRY
                                  IS_ZIPCODE
                                  IS_CLASS
                                  IS_TERM
                                  IS_TELEPHONE
                                  IS_EMAIL
                                  
         Where the IS_OID and IS_MGR_OID are known, and where IS_MGR_OID's IS_OID is known to exist in the IS table,
         and where we want to use a SQL query to lookup each IS_OID record's IS_MGR_OID value (if exists, otherwise lookup IS_SPVR_OID value
         (if does not exist, exit with message "IS_MGR_OID nor IS_SPVR_OID values exist") and discovery its corresponding
         IS_OID record's IS_POID attribute value, taking this IS_POID value and update the IS_OID record's ID_MGR_POID value.
  • 12. Re: select lookup statement
    EdStevens Guru
    Currently Being Moderated
    fortgolf wrote:
    Here's another go at the code...though does not work...not sure how the m1.mymanager is resolved in the equation:
              
    update HR m1
    set m1.manager=(case
         when
         (select mymanager from HR m2
         where m2.mymid=m1.mymanager)
         end);
    select * from v$version is:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
    better but still not what is needed.
    - go to a command prompt
    - use sqlplus to connect to the database
    - issue this command:
    desc hr
    Then copy the entire session and paste the results back to your next post.

    We need to see the table structure. The above will provide it.
  • 13. Re: select lookup statement
    960529 Newbie
    Currently Being Moderated
    SQL> desc HR;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     IS_OID                                    NOT NULL VARCHAR2(11)
     IS_POID                                            VARCHAR2(7)
     IS_LAST                                            VARCHAR2(50)
     IS_FIRST                                           VARCHAR2(50)
     IS_MIDDLE                                          VARCHAR2(50)
     IS_ADDRESS1                                        VARCHAR2(100)
     IS_ADDRESS2                                        VARCHAR2(100)
     IS_ADDRESS3                                        VARCHAR2(55)
     IS_ADDRESS4                                        VARCHAR2(55)
     IS_CITY                                            VARCHAR2(50)
     IS_STATE                                           VARCHAR2(20)
     IS_ZIPCODE                                         VARCHAR2(12)
     IS_COUNTRY                                         VARCHAR2(50)
     IS_CLASS                                           VARCHAR2(1)
     IS_STATUS                                          VARCHAR2(1)
     IS_MGR_OID                                         VARCHAR2(11)
     IS_SPVR_OID                                        VARCHAR2(11)
     IS_MGR_POID                                        VARCHAR2(7)
     IS_TERM                                            DATE
     IS_EMAIL                                           VARCHAR2(100)
     IS_TELEPHONE                                       VARCHAR2(50)
     IS_DEPTID                                          VARCHAR2(10)
     IS_DEPT                                            VARCHAR2(50)
     IS_LOCATION                                        VARCHAR2(10)
     IS_JOB                                             VARCHAR2(20)
     IS_EXPENSE                                         VARCHAR2(50)
     IS_JOBTITLE                                        VARCHAR2(50)
    
    SQL>
    Edited by: fortgolf on Dec 6, 2012 2:24 PM

    Edited by: fortgolf on Dec 6, 2012 2:26 PM

    Edited by: fortgolf on Dec 6, 2012 2:27 PM

    Edited by: fortgolf on Dec 6, 2012 2:28 PM
  • 14. Re: select lookup statement
    960529 Newbie
    Currently Being Moderated
    Here's another attempt at the script...improvements to fill in ???
    UPDATE HR
       set IS_MGR_POID =
           (
                CASE
                    WHEN ( IS_MGR_OID = 
                CASE
                    SELECT IS_OID from HR WHERE IS_MGR_OID = ???)
                    THEN 0
                    ELSE
               CASE
                    WHEN (IS_SPVR_OID =
                CASE
                    SELECT IS_OID from HR WHERE IS_SPRV_OID = ???)
                    THEN 0
                    ELSE EXIT
                END
          )
1 2 Previous Next

Legend

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