FETCH FIRST and OFFSET should work in Sub Queries

Idea created by Vincent_L on Apr 1, 2016
    Active
    Score7
    • belg4mit
    • Benjamin Fehr
    • Ben
    • brianc
    • jbante
    • robertnaud
    • Vincent_L

    Hi,

     

    To my dismay, I was once again bitten by that Filemaker ExecuteSQL limitation, you can't use  FETCH FIRST 1 ROWS ONLY in a sub query.

    That's documented in Filemaker's documentation. But that's a terrible limitation.

    Therefore I'd like those non supported clauses to be supported

     

    I've a product table MyProducts, and I've ProductImages table. Of course each product can have several images. The images have an order.

     

    MyProducts table :

    SKU     ProductName

    PA         iPhone
    PB        iPad

    PC        iPod

     

    ProductImages

    SKU     imageName     MyOrder

    PA        img1.jpg          10

    PA        img3.jpg          20         

    PB        img5.jpg          10

    PB        img6.jpg          10

    PB        img7.jpg          20   

    PC        img8.jpg          20                                 

     

    See, user made a mistake and put 10 for the second B image, and 20 for PC's only image

     

    Now I need to get the main image (the first one) of each product, which there SKU and ProductName

     

    So I should be able to write

     

    SELECT P.SKU,P.ProductName,I.ImageName FROM MyProducts P

    JOIN  ProductImages I ON I.SKU=P.SKU

    WHERE I.MyOrder=

    (SELECT D.MyOrder FROM ProductImages D WHERE D.SKU=I.SKU  ORDER BY D.MyOrder ASC FETCH FIRST 1 ROWS ONLY)

     

    This should output

     

    PA         iPhone      img1.jpg
    PB        iPad          img5.jpg

    PC        iPod          img8.jpg    

     

     

    But unfortunately this doesn't work because FETCH FIRST clause is not supported in sub queries.

     

    While we may think about writing this

     

    SELECT P.SKU,P.ProductName,I.ImageName FROM MyProducts P

    JOIN  ProductImages I ON I.SKU=P.SKU

    WHERE I.MyOrder=

    (SELECT Min( D.MyOrder) FROM ProductImages D WHERE D.SKU=P.SKU)

     

    This wouldn't get a result because due to user input errors (please don't tell me I should prevent user error that's not the point, that's an example), the sub query would return several results !

    Moreover it's awfully slow

     

    As sub query needs to only output one result, and as FETCH FIRST 1 ROWS ONLY is the only way to guarantee this, this is a very big limitation that renders sub queries useless in many situations.

     

    I think this situation is very very common, user can stumble on this with any one to many relationship.

    If somebody know a reliable, and fast work around I'm all hears (thought that workaround shouldn't be an excuse to not fix this issue)