08-24-2012 12:31 PM
I am using below sql query to extract the results for my requirement. I am able to get the reslts, but its taking 2 mins time for 10 input records (table: Ext_cus) also.
Could you please some one help me extract the same results in any efficient way.
Req: if macro variable '®_cnt is ne 0 then i have to extarct all the records from t1. else i have to extract the records by join with reg_list
create table work.ext_final as
select * from Ext_cus t1
%if ®_cnt ne 0 %then
(t1.region_id ne ' ');
%else (t1.customer_group_cd in (select region_nm1 from reg_list where region_nm1 ne ' ')) or
(t1.customer_id in (select region_nm2 from reg_list where region_nm2 ne ' ')) or
(t1.region_id in (select region_nm3 from reg_list where region_nm3 ne ' ')) ;
08-24-2012 03:09 PM
Do you have missing data in T1? The NE operator is very inefficient, so avoid it if you can. Even if you have some missing data, it might be more efficient to do the selects without the WHERE clauses and then delete the missing data later.