0 Replies Latest reply on Jan 4, 2013 3:35 AM by electric_soul

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

    electric_soul

      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 :)