Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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