Hello All,

Please let me know how to use informix directives on the following sql
I want the directives should hit the ORDER BY CLAUSE in-order to avoid creation of temporary Tables. With out Informix Directives Temporary table is created causing the query very slow.

QUERY:
------
SELECT
device_requests.trace_audit_no,
device_requests.trans_date,
device_requests.trans_time,
device_requests.device_type,
dt.device_type_desc,
device_requests.device_id,
device_requests.request_no,
device_requests.card_acceptor_code,
m.merchant_name,
device_requests.comp_code
,fcc.comp_desc,
device_requests.trans_local_dtime,
device_requests.trans_status,
device_requests.amount,
device_requests.currency_code,
cc.currency_desc,
device_requests.contra_account,
device_requests.description,
device_requests.consumer_entry,
device_requests.business_date,
device_requests.commission_amount,
device_requests.retrieval_ref_no,
device_requests.auth_code,
fac.auth_desc,
device_requests.discounted_price,
products.product_name,
device_requests.clerk_id,
device_requests.description,
device_requests.shift_no,
device_requests.iso_reconcil_date
FROM
device_requests,
merchants m,
currency_codes cc,
fc_auth_codes fac,
device_types dt,
fc_comp_codes fcc ,
products ,
trans_charges t
WHERE
device_requests.card_acceptor_code=m.merchant_id
AND device_requests.currency_code=cc.currency_code
AND device_requests.device_type=dt.device_type
AND device_requests.auth_code=fac.fc_auth_code
AND device_requests.comp_code=fcc.fc_comp_code
AND t.trace_audit_no=device_requests.trace_audit_no
And device_requests.consumer_entry=products.product_id
AND t.receiver_code=='2090701001 '
And t.level >0
order by 1 ASC

Thaks in advance.
Hope to hear from you very soon.

What are the indexes available on the tables you have used in where clause? Indexes play a very important role in reading records. Make the query according to the index available & then check. Let me know.

KKS

Hello,
I appricate your answere but here is what I actually want.

I want to avoid the creation of Temporary tables which are created when ever I use Order by clause. Although the columns being used in order by clause are indexed, but the informix optimizer does't use indexes on order by clause and start creating temporary tables for processing(sorting) data.

Then the things come in mind is to force the Informix optimizer to use indexes of order by cluase by embeding the Informix directives in db query(SQL) . How to use those directive is the problem. I am getting syntax errors in using informix optimizer directives.

I think it would clear the situation well.

Please help me out of this problem.
Thanks in advance.