4 Replies Latest reply on Sep 20, 2012 8:09 AM by davidhamannmedia

    Subselects with ExecuteSQL?

    davidhamannmedia

      Hey everybody,

       

      doesn't ExecuteSQL() support subselects or am I blind?

       

      I can't figure out why this query doesn't work:

       

      ExecuteSQL (
      
      "SELECT " &
      "SUM(" & _fieldNameQuoted ( _Salaries_::d_pay ) & ") " &
      "FROM " & 
      "(SELECT " &
      _fieldNameQuoted ( _Salaries_::_fk_person ) & ", " &
      "MAX(" & _fieldNameQuoted ( _Salaries_::d_dateStart ) & ") as maxstartdate " & 
      "FROM " &
      _fieldTableName ( _Salaries_::__pk ) & " " &
      "WHERE " &
      _fieldNameQuoted ( _Salaries_::_fk_person ) & " IN (123,456) AND " & 
      _fieldNameQuoted ( _Salaries_::d_dateStart ) & " <= ?" & " " &
      "GROUP BY " &
      _fieldNameQuoted ( _Salaries_::_fk_person ) & " " &
      ") p " &
      "JOIN " &
      _fieldTableName ( _Salaries_::__pk ) & " s " &
      "ON " &
      "s." & _fieldNameQuoted ( _Salaries_::_fk_person ) & "=" & "p." & _fieldNameQuoted ( _Salaries_::_fk_person ) & " AND " &
      "s.maxstartdate=" & "p." & _fieldNameQuoted ( _Salaries_::d_dateStart )
      ;"";"";Date(1;1;2012)
      
      )
      

       

       

       

      FYI: _fieldNameQuoted is just a CF that returns the quoted field name - nothing special.

       

      Do I need to find a nasty bug in my code or is ExecuteSQL() not able to understand subselects?

       

      Any hint is highly appreciated.

        • 1. Re: Subselects with ExecuteSQL?
          beverly

          d, you are returning "quoted field names" with the custom function right?

          does it return "table.fieldName" or "table::fieldName" ?

          remember this is SQL you use the "." between the table and field name, not "::".

           

          Beverly

          • 2. Re: Subselects with ExecuteSQL?
            davidhamannmedia

            Hi Beverly,

             

            thanks for your answer.

             

            However that's not the problem. My CF returns just the field name and I use it in the entire solution. I can even execute the second select as a single query without any problem. The subselect seems to be the "breaker".

             

            I'm wondering if my code is bad or the executeSQL() function doesn't support it. Can't find any documentation about this :-(

            • 3. Re: Subselects with ExecuteSQL?

              I had posted this in another thread so easy copy and paste. :-)

               

               

               

              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: Subselects with ExecuteSQL?
                davidhamannmedia

                mr_vodka, thanks for your reply.

                 

                Yes, this seems unfortunately to be true. I also tested some other queries and – as you say – if you use another select in e.g. an IN clause it works. Directly referencing does not. :-(

                 

                Nevertheless thank you for your confirmation.

                 

                Best,

                David