Re: QP9 Database Query Question

NigelH wrote:
> 
> I have a spreadsheet sheet set up as a database with four key columns 
> and four numerical data columns. I have a macro set up to take a set of 
> input data (loaded as a csv file), and use it to generate queries 
> against the database sheet. My problem is that occasionally the input 
> data results in keys for which there isn't a match in the database sheet.
> 
> What I'd like to happen is that the lookup returns data fields of 0 0 0 
> 0. What currently happens is that the database query reports no match 
> and operator intervention is required.
> 
> Here is the relevant code fragment:
> {Query.Database_Block MDB:A1..H11861}
> {Query.Criteria_Table Queries:A1..H2}
> {Query.Output_Block Queries:J1..Q2}
> {Query.Extract}
> 
> 
> Can I somehow trap the interrupt so that operator intervention is
disabled?

Not directly, but indirectly.  In general terms, change your strategy from 
controlling QUERY execution to controlling MACRO execution; i.e., trap the error

by identifying it BEFORE your macro reaches the query code and circumvent query

execution altogether.

Cheers,
Uli

I have a spreadsheet sheet set up as a database with four key columns 
and four numerical data columns. I have a macro set up to take a set of 
input data (loaded as a csv file), and use it to generate queries 
against the database sheet. My problem is that occasionally the input 
data results in keys for which there isn't a match in the database sheet.

What I'd like to happen is that the lookup returns data fields of 0 0 0 
0. What currently happens is that the database query reports no match 
and operator intervention is required.

Here is the relevant code fragment:
{Query.Database_Block MDB:A1..H11861}
{Query.Criteria_Table Queries:A1..H2}
{Query.Output_Block Queries:J1..Q2}
{Query.Extract}


Can I somehow trap the interrupt so that operator intervention is disabled?

Thanks for your help,

NigelH

Uli wrote:

> 
> NigelH wrote:
> 
>>
>> I have a spreadsheet sheet set up as a database with four key columns 
>> and four numerical data columns. I have a macro set up to take a set 
>> of input data (loaded as a csv file), and use it to generate queries 
>> against the database sheet. My problem is that occasionally the input 
>> data results in keys for which there isn't a match in the database
sheet.
>>
>> What I'd like to happen is that the lookup returns data fields of 0 0 
>> 0 0. What currently happens is that the database query reports no 
>> match and operator intervention is required.
>>
>> Here is the relevant code fragment:
>> {Query.Database_Block MDB:A1..H11861}
>> {Query.Criteria_Table Queries:A1..H2}
>> {Query.Output_Block Queries:J1..Q2}
>> {Query.Extract}
>> 
>>
>> Can I somehow trap the interrupt so that operator intervention is 
>> disabled?
> 
> 
> Not directly, but indirectly.  In general terms, change your strategy 
> from controlling QUERY execution to controlling MACRO execution; i.e., 
> trap the error by identifying it BEFORE your macro reaches the query 
> code and circumvent query execution altogether.
> 
> Cheers,
> Uli

Thanks for your reply, but I'm not sure it's practical. I don't know 
what key values will be in the csv file.

I'm playing with another approach - take each set of key values, add 
them to the end of the database sheet with data 0 0 0 0. Amend the query 
to return up to two rows and add the corresponding sets of returned 
values. Afterwards delete the 'fake' data from the database sheet. 
(Although if only one row was returned I might as well leave it there).

NigelH

NigelH wrote:
> 
> Uli wrote:
> 
>>
>> NigelH wrote:
>>
>>>
>>> I have a spreadsheet sheet set up as a database with four key
columns 
>>> and four numerical data columns. I have a macro set up to take a
set 
>>> of input data (loaded as a csv file), and use it to generate
queries 
>>> against the database sheet. My problem is that occasionally the
input 
>>> data results in keys for which there isn't a match in the database

>>> sheet.
>>>
>>> What I'd like to happen is that the lookup returns data fields of 0
0 
>>> 0 0. What currently happens is that the database query reports no 
>>> match and operator intervention is required.
>>>
>>> Here is the relevant code fragment:
>>> {Query.Database_Block MDB:A1..H11861}
>>> {Query.Criteria_Table Queries:A1..H2}
>>> {Query.Output_Block Queries:J1..Q2}
>>> {Query.Extract}
>>> 
>>>
>>> Can I somehow trap the interrupt so that operator intervention is 
>>> disabled?
>>
>>
>> Not directly, but indirectly.  In general terms, change your strategy 
>> from controlling QUERY execution to controlling MACRO execution; i.e.,

>> trap the error by identifying it BEFORE your macro reaches the query 
>> code and circumvent query execution altogether.
>>
>> Cheers,
>> Uli
> 
> Thanks for your reply, but I'm not sure it's practical. I don't know 
> what key values will be in the csv file.

Not knowing what key values will be in the csv file isn't a problem -- you 
simply make query execution dependent on the absence of an error condition.  For

example:

Z1:  @MATCH(Key1FromCsv,KeyColumn1,0)
Z2:  @MATCH(Key2FromCsv,KeyColumn2,0)
Z3:  @MATCH(Key3FromCsv,KeyColumn3,0)
Z4:  @MATCH(Key4FromCsv,KeyColumn4,0)

Macro:  {IF @ISERR(@SUM(Z1..Z4))}{Branch Gimme4zeroes}
         {Branch DoQuery}

Cheers,
Uli