1 Reply Latest reply: Jun 2, 2009 7:39 AM by Sridhar Ethiraj RSS

    using the max() varible with ODBC

    dynamicsupplies

      Hi Guys im hoping someone can help me

       

      im trying to write an ODBC query that will only extract out entry number with an EntryNo greater than the value of a varible

      i have created a varible called "LastEntryNo" which contains the formula "=max(EntryNo)" (entryno is a consecutive number incrementing by 1)

      my problem lies in that when i try to run the below code i get an sql error because the varible is not resolving to the numeric number it is actually passing through the text "=max(EntryNo)"

      SELECT "Item Ledger Entry"."Entry No_", "Item Ledger Entry"."Item No_"
      FROM "Item Ledger Entry" "Item Ledger Entry"
      WHERE ("Item Ledger Entry"."Entry No_"=$(LastEntryNo)

       

      is there a way i can validate this number before running the WHERE statement

       

      i was thinking along the lines of something like

      let LastEntryNo=max(EntryNo) however this returns an aggregation error

       

      Thanks for your help Guys

       

      Rhys

       

        • using the max() varible with ODBC

          Hi,

          I believe, you are going wrong while trying to take the maximum value in the variable.

          It is not possible to get the max() of a field in variable by the way you are trying to follow.

          1st try to load your Entryno table and order the same table by Entryno ascending order.

          then use peek function to take the maximum value, then use that variable in select query.

          Hope that should work.

          Check my attachment to take max value in to variable.

          Test1:

          Load Entry_Num,

          Sales

          from test order by Entry_Num ASC;

          LET var1 = peek('Entry_Num', -1, Test1);

           

          I hope this should help you.

          Cheers Beer

          - Sridhar