Mur
User
Platinum Boarder
| Posts: 116 |   |
Karma: 0
|
|
RE: array typed parameter
|
|
Posted: 2003/06/26 09:17 |
|
|
|
|
If your list has constant number of items, use this SQL:
ciquery1.SQL.Text:='Select * from invoices i where i.company_id in ( 1, 2, 3)';
ciquery1.parambyname('p1').Value:=101;
ciquery1.parambyname('p2').Value:=102;
ciquery1.parambyname('p3').Value:=105;
In most cases this approach is unacceptable because of variable number of items in list.
In that case use macro
ciquery1.SQL.Text:='Select * from invoices i where i.company_id in (&List)';
ciquery1.macrobyname('List').AsString:='101,102,105';
Bad thing is that your operator wil be parsed every time you run it with different value of &List. &List must never be empty, or you will get OCI error.
This best apporoach (but not always usable) is to use 'where i.company_id in (select ...)' construction.
For example: companies 101,102,105 are all the companies located in city with city_id=33. In that case your SQL will be:
ciquery1.SQL.Text:='Select * from invoices i where i.company_id in (select company_id from company where city_id=:city_id)';
ciquery1.parambyname('city_id').Value:=33;
|
|
