7 Replies Latest reply on Dec 3, 2013 4:37 PM by Daniel Seisun

    How to join results of two custom SQL queries?

    Kevin MacDonell

      Searched around quite a bit but couldn't find what I was looking for. I am querying an Oracle database (11g) and want to do a simple join between the results of two queries, in Custom SQL. The following is a simplified example; I realize that I can just join the tables using Tableau's 'multiple tables', but my real task is more complex than this example. If I can get the example to work, I can move on to the more complex job.

       

      I have tried dozens of variations with this, but I keep getting the same uninformative Oracle error: "Oracle database error 907: ORA-00907: missing right parenthesis". The parentheses are matched -- there's something else going on.

       

       

      SELECT AL1.PIDM, AL1.NAME

      FROM BANINST1.AA_ENTITY

      WHERE ((AL1.CHANGE_IND IS NULL AND AL1.DEAD_IND IS NULL)) AS query1

       

      INNER JOIN

      (SELECT AL1.PIDM, SUM ( AL1.GIFT_AMT_TOT ) AS AMOUNT FROM BANINST1.AA_GIFT GROUP BY AL1.PIDM) AS query2

      ON query1.PIDM = query2.PIDM

        • 1. Re: How to join results of two custom SQL queries?
          Matt Lutton

          Just curious--what is keeping you from using the multiple tables option, or joining them in your database?

          • 2. Re: How to join results of two custom SQL queries?
            Daniel Seisun

            Hey Kevin,

             

            It doesn't look like your query is structured right. Your query should look more like

             

            select

                 a.col1

                 a.col2

                 b.col1

                 b.col2

            from

                 (select * from table1

                 where table1.col1 is null) as a

            inner join

                 (select * from table2) as b on a.joinkey = b.joinkey

             

            Does this make sense?

            1 of 1 people found this helpful
            • 3. Re: How to join results of two custom SQL queries?
              Kevin MacDonell

              Daniel,

               

              No doubt you're right that my SQL is not up to snuff! I've tried to follow your example (see below), but I get the same uninformative error message. Am I getting any closer?

               

              SELECT a.PIDM, a.ID, b.SUM(AL1.GIFT_AMT_TOT) AS AMOUNT

              FROM (SELECT * FROM BANINST1.AA_ENTITY

              WHERE (AL1.CHANGE_IND IS NULL AND AL1.DEAD_IND IS NULL)) AS a

               

              INNER JOIN

              (SELECT * FROM BANINST1.AA_GIFT GROUP) AS b

              on a.PIDM = b.PIDM

              • 4. Re: How to join results of two custom SQL queries?
                Kevin MacDonell

                Hi Matthew,

                 

                Let's just say that my full project requires a lot of different aggregations on subsets of data, meeting various conditions, before bringing all together in a tidy TDE. I have been using Python to do all my complex data prep and saving as TDEs, but I need a method that can be set up to refresh automatically on Server, and so I am back to custom SQL unless I can have my Python scripts run automatically on the server.

                 

                Perhaps I shouldn't rule out multiple tables, but I find it a bit clumsy for complex data modeling.

                • 5. Re: How to join results of two custom SQL queries?
                  Daniel Seisun

                  Hi Kevin,

                   

                  So if I reformat your query a bit it looks like this:

                   

                  SELECT a.PIDM, a.ID, b.SUM(AL1.GIFT_AMT_TOT) AS AMOUNT

                  FROM

                    (

                    SELECT * FROM BANINST1.AA_ENTITY

                    WHERE (AL1.CHANGE_IND IS NULL AND AL1.DEAD_IND IS NULL)

                    ) AS a

                   

                  INNER JOIN

                    (

                    SELECT * FROM BANINST1.AA_GIFT GROUP

                    ) AS b on a.PIDM = b.PIDM

                   

                  Theres a couple of issues.

                  First:   WHERE (AL1.CHANGE_IND IS NULL AND AL1.DEAD_IND IS NULL)

                       - What is AL1? If you mean to reference the AA_ENTITY table you should either alias the table of explicitly use the table name

                  Second:   SELECT * FROM BANINST1.AA_GIFT GROUP

                       - You aren't referencing what to group your subquery by. Do you want to aggregate your data in the subquery and then join it to your original table? If so I would guess your query should look something like this

                   

                  SELECT a.PIDM, a.ID, b.AMOUNT

                  FROM

                    (

                    SELECT * FROM BANINST1.AA_ENTITY

                    WHERE (AA_ENTITY.CHANGE_IND IS NULL AND AA_ENTITY.DEAD_IND IS NULL)

                    ) AS a

                   

                  INNER JOIN

                    (

                    SELECT AA_GIFT.PIDM, SUM(AA_GIFT.GIFT_AMT_TOT) AS AMOUNT FROM BANINST1.AA_GIFT

                  GROUP BY AA_GIFT.PIDM

                    ) AS b on a.PIDM = b.PIDM


                  Let me know if this ends up working for you,

                   

                  Daniel

                  • 6. Re: How to join results of two custom SQL queries?
                    Kevin MacDonell

                    Daniel,

                     

                    Thanks for the re-write -- that makes sense to me. I usually just copy the SQL statement out of our query software (Hyperion Brio), and that's where AL1 is coming from. I never really knew what it meant, only that it worked when pasted into Tableau -- now I understand it's an alias for the table.

                     

                    Your revised query does now work -- that is, after I removed the two AS's before each alias ... I did read somewhere that Oracle doesn't like AS for that use.

                     

                    So, much thanks for this!

                    • 7. Re: How to join results of two custom SQL queries?
                      Daniel Seisun

                      Ah, thanks for the reminder on the Oracle AS statement. Glad it worked for you!