[Gambas-user] Subst$ with a Variant array of values
adamnt42 at gmail.com
Sun May 19 07:51:41 CEST 2019
I don't think I have seen this before. (in fact I've never tried it before.)
Basically, I have a Variant that contains the values that I want to
substitute into a string. That variant could be any length but (in this
case) is specific to the substitution pattern.
The pattern is in fact an SQL query pattern that looks like
"SELECT * FROM &1 WHERE blah=$$&2$$ AND blahblah=&3".
The value array actually comes from another query that returns a single
row as an array, e.g. ["sales","D KB","27-Mar-2019"]. In other words, I
am trying to build a query based on the results of a prior query.
So, I would "like" to be able to:
Is this feature possible?
(As usual, the long explanation...)
We have a whole bunch of queries that verify that our database is
correct and a project that runs these queries and returns a "list" of
invalid, suspect or otherwise data. An example could be:
Auct Date Error'
==== =========== ==============================================
D KB 27-Mar-2019 Catalogue vs sold mismatch (sold=102,list=101)
i.e. there appeared to be more lots sold than were advertised.
So out of those 102 sales I need to find the one that is not in the
catalogue list, i.e.
Select * from sales s left outer join catalogue where s.auction = $$D
KB$$ AND s.saledate = $$27-Mar-2019$$ AND c.lotnum is null;
(There are lots and lots of validation queries and even more "find the
error" queries. I have put them all into a database with the idea that
we don't have to keep typing vast numbers of queries every day to
isolate the errors.
The idea is to enhance the validation program to allow us to select one
line from the list and have it generate the appropriate query into the
clipboard to be pasted into the db query utility.
The line in the list comes from the array ["sales","D
KB","27-Mar-2019",12437,102,101] which is returned by the validation
query where "sales" is the table being verified,"D KB","27-Mar-2019" are
the partial key for the table and depend on the validation performed,
12437 is our internal error code and 102,101 are the values returned by
the query that are not correct. (12437 is made up of the validation
query that was executed (124) and the actual error message format code(37).
On any day there may be a hundred or so validation errors. There are
currently 289 validations run and this number grows week by week as we
find more ways that &#$@$^#@ users give is bad data. On Friday for
instance there was a dozen errors returned from the 124 validation, most
of them data entry errors/omissions at the client end - 11 cases where
there was a missing sale and 1 auction that took an extra lot without
Each error takes significant time to resolve, so any shortcuts we can
take are important.
Sheeeesh, Benoit, are you sure you want to be a private entrepeneur?
More information about the User