using the max() varible with ODBC

[#1]

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

[#2]

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

- Sridhar