5 Replies Latest reply on Dec 20, 2012 3:59 AM by electric_soul

    ExecuteSQL() subquery limitations?

    itd

      Hi all

       

      I have been using SQL queries in FM for quite some time. I'm currently moving gradually from using a plugin for purely reading queries to using the ExecuteSQL script step of FM12.

       

      I have been working on a problem with a subquery (nested JOIN) for a number of hours now. I've tried so many ways of performing the query and I just can't work it out. Whenever I reduce the complexity by one level, the queries work just fine, but as soon as I try to do the nested JOIN described below, I can't get it to work. I even wrote my own SQL query testing FM12 database with a few simple tables, which are interconnected in various ways and a layout where I can write the queries and instantaneously get the result. Many things work (e.g. a sequence of JOIN statements which are not nested), but not the one below.

       

      Now the question is whether the problem is me or whether I hit on a FM SQL limitation .

       

      Here goes...

       

      Imagine three tables myA, myB and myC.

       

      I'm trying to carry out a nested subquery of the following kind:

       

      SELECT

      A.aa1,

      A.aa2,

      D.bbb,

      D.ccc

      FROM myA A

      LEFT JOIN (

      SELECT B.id, B.myAid, B.bbb, C.ccc

      FROM myB B

      LEFT JOIN myC C

      ON B.myCid = C.id

      ) D

      ON A.id = D.myAid

       

      As mentioned above, I've tried different approaches and all failed. So, what I'm posting here is just one of dozens of versions.

      Has anyone carried out similar queries successfully?

      Can you either spot the mistake in my query or confirm that there are certain limitations in the FM12 SQL engine?

       

      I would be grateful for your hints.

        • 1. Re: ExecuteSQL() subquery limitations?
          BruceHerbach

          Hi

          My guess is there is an issue with the syntax and not a limitation.  I have been using Seedcode's SQLexplorer to work out the basic query and the using the copy as a calculation to put the SQL code to work.  It requires some tweaks, but this has saved me a ton of time.

           

          SQLexplorer is free and will develop multi level querys.  It also let's you add tables from your Db. Part of what makes it do helpful.

          HTH

          1 of 1 people found this helpful
          • 2. Re: ExecuteSQL() subquery limitations?
            itd

            Hi Bruce

             

            Thanks, for your reply.

            I know SQLexplorer and had already used it briefly to address my problem.

             

            However, I accepted your post as an incentive to give SQLexplorer another try and invested some more time.

             

            And I can say now, that your guess was right.

            My problem was a case of PEPKAC (problem exists between keyboard and chair) .

             

            The query I'm trying to perform can be done without nesting, even though dozens of websites indicated to me that nesting would be right way to do it.

             

            Here's a viable solution for my query:

             

              SELECT

                A.aa1,

                A.aa2,

                B.bbb,

                C.ccc

              FROM myA A

              LEFT JOIN myB B ON B.myAid = A.id

              LEFT JOIN myC C ON B.myCid = C.id

             

            It's that easy as soon as you know how to do it...

             

            Thanks for giving me a helpful pointer.

            • 3. Re: ExecuteSQL() subquery limitations?

              AFAIK, and from what I have tested, nested subqueries referencing derived tables do not work. You can reference it when using for example an IN but directly referencing it seems to not work.

               

               

              For example,

               

                SELECT * FROM (

                SELECT

                  Category,

                  Sum(amt)

                FROM Table

                GROUP BY Category)qry_sub1

               


              1 of 1 people found this helpful
              • 4. Re: ExecuteSQL() subquery limitations?
                itd

                Thanks, mr vodka

                 

                Now, we're at least two people who think so.

                 

                I have written a whole series of tests including textbook examples of derived tables generated in subqueries and referenced from the main query.

                It didn't work for me either.

                 

                What a pity and what a limitation.

                • 5. Re: ExecuteSQL() subquery limitations?
                  electric_soul

                  This sucks....  it's a request