Need a push in the right direction with SQL queries and arguments

[Ciaran]

Hi all,

As a relatively new user to version 9 (I've worked a lot in v7) the SQL features are causing a bit of a head scratching moment. I understand the concept of active and passive linked tables but what I cannot get my head around is how to run a query based on an argument supplied by the user.

My query looks like this ;

select w.work_order_no,to_char(w.date_opened,'DD-MM-YY HH24:MI:SS') from work_order w
where w.work_order_no = '1165556'
UNION
select w.work_order_no,to_char(o.login_time,'DD-MM-YY HH24:MI:SS'),w1.workplace,h.parent_serial
from work_order w,assembly_header h,unit_in_process u, operation_result o,workplace w1
where w.work_order_no = '1165556'
and w.work_order_key = h.work_order_key
and h.parent_serial = u.serial_no
and u.unit_key = o.unit_key
and w1.wp_key = o.wp_KEY
order by login_time

This works fine for the supplied order number, but the concept I'm having difficulty with is how to define the table (either active or passive) without specifying the order number and without A5 trying to suck in all the data from the remote table.

I guess if I explained what I'm trying to achieve, that might help. At a high level, I'd like to be able to have a dialog box pop-up, the user enter an order number and the above query is executed on the remote database using the order number as a filter. Has anyone done something similar and wouldn't mind pushing me in the right direction ?

Thanks

[TimG]

Re: Need a push in the right direction with SQL queries and arguments

During the creation of the Active-Link table, when defining the WHERE clause, when you Add a new condition, instead of specifying a fixed value, you can Insert Argument instead. This will generate the user-popup you are looking for at run-time.

[dordije]

Re: Need a push in the right direction with SQL queries and arguments

> This will generate the user-popup you are looking for at run-time.

Tim, what if I in run-time know the value for inserted argument ? Say that I inserted argument :mFIELD in WHERE clause of SELECT statement of active table. How can I set this argument in run-time on some value I have in for example global variable, without user popup ?

May be it must be done through OnInit event of the form based on active table ?

I tried with SQL::Arguments, and its .Set function, but till now without success.

Thanks.