3 Replies Latest reply: Dec 10, 2012 7:57 AM by 603257 RSS

    VLOOKUPS IN SQL COMMANDS?

    Ewaver
      Hi,

      I am new to SQL and would appreciate any help you can provide with this question.

      For my project I have a two tables called PRICE_MODEL_BLANK and PRICE_ROLE

      Here are the columns of interest for this problem:

      PRICE_MODEL_BLANK

      MONTH COUNTRY ROLETITLE SALARY
      1/1/2012 USA MANAGER

      PRICE_ROLE

      COUNTRY ROLETITLE SALARY
      USA MANAGER 1000
      AUSTRALIA ASSOCIATE MANAGER 500

      The goal here is ultimately to get the Salary from the PRICE_ROLE table into the PRICE_MODEL_BLANK table by matching up the COUNTRY AND ROLETITLE for all of the rows in the PRICE_MODEL_BLANK table.
      I would like to do this in a view and also in an interactive report. If you could help with the SQL code I would greatly appreciate it, but if you also know if I can use the code to create a view from the PRICE_MODEL_ROLETITLE with the values inserted from the PRICE_TABLE and where I would use a query in an interactive report. I've tried editing the report and choosing "Interactive Report" attributes and placing the query in the conditions area, but I could not get it to work, probably b/c my code was wrong.

      Any help with any of these would be greatly appreaciated.

      Thanks,
        • 1. Re: VLOOKUPS IN SQL COMMANDS?
          Sc0tt
          A vlookup is called a "join" in SQL.
          select table.col1, table2.col1
            from table, table2
           where table.id = table2.id
          Before you jump too far into Apex, you really should learn the basics of SQL. With the technology we have today, there is no reason you can't find an adequate tutorial on the web.
          • 2. Re: VLOOKUPS IN SQL COMMANDS?
            Ewaver
            Thanks for the advice, point well taken. I have played with joins in the query builder alot, but I was never getting the results that I needed, so I started typing out queries in SQL commands. I am on my 3rd app in Apex and could use a little further advice if you don't mind. There are two columns COUNTRY and ROLETITLE that need to match to pull over the SALARY data in the join. The SALARY data exists in the PRICE_ROLE table, and I have a SALARY column in the PRICE_MODEL_BLANK table.

            From what I understand about joins, aren't you actually moving columns or joining columns from one table to another? But what I am trying to do is to get the data from the SALARY column in PRICE_ROLE into the PRICE_MODEL_BLANK SALARY column whenever the country and roletitle matchup. Is this a different type of join then, like an inner join or not a join at all and something else?

            Thank You,
            • 3. Re: VLOOKUPS IN SQL COMMANDS?
              603257
              With all due respect, and please don't take this the wrong way, you are not going to get very far at all in APEX without knowing at least some basic SQL. True, you can use the query builder, single table data manipulation forms etc, however you'll find very quickly that you will hit a brick wall. Plus without knowing what is actually going on, you will find that your applications are likely to be inefficient, don't extend and scale very well, and are extremely difficult to debug.

              The best thing you could do is go online and find a few Oracle SQL tutorials to work through.