1 2 Previous Next 20 Replies Latest reply: Dec 8, 2012 6:23 AM by EdStevens RSS

    select lookup statement

    960529
      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....
          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
            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
              >
              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
                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
                  to rp0428...didn't see a query to run...
                  • 6. Re: select lookup statement
                    rp0428
                    >
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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