2 Replies Latest reply on Jul 11, 2015 3:32 AM by MariusPirv

    Execute SQL problem ( Fetch First with sum and arguments )

    MariusPirv

      Title

      Execute SQL problem ( Fetch First with sum and arguments )

      Post

      Hello guys,

      It appears I have bumped up into a problem with Execute SQL.

      This is what I'm trying to do:

      Let(
      [
      nrows = Invoices::page;
      SQL=ExecuteSQL    (
                          "
                          SELECT Sum ( a.\"Amount_nodiscount_faratva\" )
                          FROM \"Invoice Data\" a
                          WHERE a.\"INVOICE ID MATCH FIELD\" = ?  
                          FETCH FIRST ? ROWS ONLY
       
                          "
                          ; "|" ; ¶ ;
                           Invoice ID; nrows
                          )
      ];

      SQL
      )

       

      Everything works fine, except the query :(.

      Firstly it appears that even if I have Fetch First the Sum ( ) doesn't care and does it for all of the records that meet Where condition.

      Secondly I can't manage the have that second argument in the Fetch First, every time I put it there the result is  ? (Even with a working query).

       

      Please help or try to give some pointers on how I could do this otherwise.

      Thank you,

      Marius PIRV

        • 1. Re: Execute SQL problem ( Fetch First with sum and arguments )
          philmodjunk

          I believe that your Sum function is working correctly, but not producing the value you want. It looks like you need to include the Page number as part of your WHERE criteria and not use Fetch First.

          And I suspect that if you were to use Fetch first in a query like you have attempted here, you'd need to use this syntax instead of the optional parameter syntax:

          FETCH FIRST " & nrows & " ROWS ONLY

          • 2. Re: Execute SQL problem ( Fetch First with sum and arguments )
            MariusPirv

            So what I'm trying to do here is have a Subtotal per page of the products.

            Invoices::Page is Get(PageNumber) * 30 (30 records/page). This is filled at print preview.

            How can I query the database so that I get this subtotal for the first 30 records, 60 records and so on..

            I tried like this

            Let(
            [
            nrows = Invoices::page;
            $$SQL = ExecuteSQL    (
                                "
                                SELECT SUM ( a.\"Amount_nodiscount_faratva\" )
                                FROM \"Invoice Data\" a
                                WHERE a.\"INVOICE ID MATCH FIELD\" = ?  
                                FETCH FIRST " & nrows & " ROWS ONLY
                                "
                                ; "|" ; ¶ ;
                                 Invoices::Invoice ID
                                )
            ];

            $$SQL
            )

            but it still does the SUM for all records that meet Where condition not only for the First nrows  :(

             

            Thank you for your support so far.

             

            Edit:

            Found a way with having a position on each record and include that in the where clause. 

            Thank your help and pointers.

            Marius