AnsweredAssumed Answered

FM 11 - Problems with correlated subquery in HAVING clause! No results

Question asked by electric_soul on Jan 4, 2013

Title

FM 11 - Problems with correlated subquery in HAVING clause! No results

Post


     Hello

     I use FM Pro 11.0v3 ADV

     So here is my query and  use the baselements plugin. The following code is correct, but I do not get any results. I think the red marked part is a problem.

     

          BE_FileMakerSQL ( "
                  SELECT oli.ID_Product, sum(oli.\"Quantity ordered\")
                  FROM \"ORD_Order_Line Items\" oli

                  WHERE oli.Kind='Material'
                  AND oli.b_selected=1
                  AND oli.ID_Order='" & ORD_Order::ID & "'

                  GROUP BY oli.ID_Product
                  HAVING sum(oli.\"Quantity ordered\")=(SELECT sum(ap2.Quantity) FROM Artikelposten ap2 WHERE ap2.\"Item Number\" = oli.ID_Product)
          ")
           

     Now lets remove the having clause and check if the assumption is correct.

     

           

     

          BE_FileMakerSQL ( "
                  SELECT oli.ID_Product, sum(oli.\"Quantity ordered\")
                  FROM \"ORD_Order_Line Items\" oli

                  WHERE oli.Kind='Material'
                  AND oli.b_selected=1
                  AND oli.ID_Order='" & ORD_Order::ID & "'

                  GROUP BY oli.ID_Product
          ")

      

Result

     

          APS-KIT0    230
          B-FC-2000P    52

      

     Looks good. So far so good.

     Now lets change the subquery in the HAVING clause into a non correlated sql statement. Let's test it with 'APS-KIT0'

      

     

          BE_FileMakerSQL ( "
                  SELECT oli.ID_Product, sum(oli.\"Quantity ordered\")
                  FROM \"ORD_Order_Line Items\" oli

                  WHERE oli.Kind='Material'
                  AND oli.b_selected=1
                  AND oli.ID_Order='" & ORD_Order::ID & "'

                  GROUP BY oli.ID_Product
                  HAVING sum(oli.\"Quantity ordered\")=(SELECT sum(ap2.Quantity) FROM Artikelposten ap2 WHERE ap2.\"Item Number\" = 'APS-KIT0')
          ")

Result: APS-KIT0    230

     Now I get a result. Nice. But why do I not get a result with the sql statement at the top. What's wromng with oli.ID_Product ?

      

     I hope somebody knows :)

Outcomes