[Gambas-user] Subst$ with a Variant array of values

[Bruce]

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:
	sQry=Subst$(sPattern, vaCriteria)
Is this feature possible?

tia
Bruce

(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 
notifying us.

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?
b