An easier way to do this and just as fast is to do two SQL queries:
one to get the IDs for the first date, one to get the IDs for the 2nd date and then use the FM function FilterValues to compare both lists.
That is going to be much easier to troubleshoot down the road.
The filtervalue returns the same deals I had the previous day. I need a to know the new deals.
A couple of things:
- you query should just ask for the PRIMARYKEY field to compare the list. You'll need a 3rd query at the end to ask for the data of the added records
- you are specifying "¶" as the delimiter for both fields and records, that does not work. The default is commas between fields and returns between records, if that is what you want you can just leave those entries blank:
ExecuteSQL ( "some sql query" ; "" ; "" ; someParameter )
- you are using aliases (ID, GL ) but you don't use them in the SELECT field list or for the DATEINPUT field, that is confusing; it is not clear what table those fields come from
- I'm not entirely sure you need the join, but I don't know enough about your data structure to know for sure.
- FilterValues will give you the IDs that both lists have in common. There is a custom function available at www.briandunning.com named AntiFilterValues that gives you the reverse. That's probably better for you to avoid any post processing of the lists.
- unrelated to the issue: don't use global variables ($$) where a local variable is enough ($). It is good practice to limit the life-time of a variable to the scope where it is used. If you need them in a script they should not survive the end of the script
Thanks for all the comments. Yes, I agree with all of them.