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

[Electric_Soul]

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 oli.ID_Product in the having clause 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