Re: Adding can-find to a query-prepare?

[Tim Kuehn]

I've never done it that way - if you're doing a join-ish lookup, I'd
add another buffer to the query and do a FOR FIRST on it for the
filter condition.

If you're trying to do a NOT CAN-FIND().... I don't think that's
possible. - you'd need to use a pro-dataset to get that kind of
functionality.

Tim

On Tue, Nov 5, 2013 at 2:04 PM, Richard Uchytil
<Richard...@hamptonaffiliates.com> wrote:
> Progress 10.2B
>
> I'm new to using query-prepare. I want to do a "for each table where not can-find" in my query but I'm getting the message, "can-find is invalid within an open query. (3541)". So, how do I do it? Or am I out of luck and have to find a different way to handle it? Thanks!
>
>
> Rich Uchytil
> Portland, OR
>
>
>



--
Tim Kuehn: Senior Consultant - TDK Consulting Services
Ontario PUG President
PUG Challenge Americas Executive, Program Committee Chair

Skype: timothy.kuehn
Ph: 519-576-8100
Cell: 519-781-0081

[Richard Uchytil]

[Richard Uchytil]

I should know better and provide more info.

I've got a program that can run for all unprocessed invoices or a specific invoice. It has an input param of iInvoiceNumber. If iInvoiceNumber is 0 then run it for all unprocessed invoices, otherwise run it for that specific invoice.

I was thinking I'd do a query like this:

If iInvoiceNumber ne 0 then
qQuery:query-prepare("for each table1 no-lock where table1.InvNum eq " + quoter(iInvoiceNumber)).
Else
qQuery:query-prepare("for each table1 no-lock where NOT can-find(first table2 where table2.keyfield eq table1.keyfield)").

When an invoice is processed it is added to table2. So when it runs for unprocessed invoices, it's looking for all invoices where the invoice number isn't in table2.

I wondered about using pro-datasets, I'll have to go read up on them. Thanks!

[Brynjar Hasle]

I learned this trick right here on the PEG a few years ago.
F.ex looking for items in the Sports database without sale last year
(most of them as it turns out..):

FOR EACH Item NO-LOCK WHERE true,EACH orderline NO-LOCK OF Item,FIRST
Order NO-LOCK OF Orderline WHERE order.OrderDate > DATE(TODAY) - 365
OUTER-JOIN,FIRST ttNotExist1 WHERE NOT AVAIL Order

(ttNotExist1 has one record)

Brynjar

--
Brynjar Hasle
Chemistry as
www.chemistry.no
Mob: +47 9092 3875

[Richard Uchytil]

Cool, thanks!