how to use decode?case function in where clause

[vinayakkale2]

Hi I am using a select query from front end to generate report in such a way that I should be able to get records of Table W which are present in Table Y
The query which i am using is as follows:

Select Y.ntktno,Y.cpassengername,Y.cfromroute,Y.cuptoroute 
from t_indi_infiledtl Y,t_indi_cancfiledtl W where Y.ntktno in 
(select ntktno from t_indi_cancfiledtl) and Y.cfromroute=W.cfromroute and
Y.cuptoroute=W.cuptoroute and 
soundex(substr(Y.cpassengername,1,5))=soundex(substr(W.cpassengername,1,5)) 

By the above Query I am getting the desired output .
But I wants to check the minimum length of the passengername between (Y.cpassengername and W.cpassengername) and using that minimum length i wants to fire the query for the soundex function since i cant assume that soundex (Y.cpassengername)=soundex(W.cpassengername) for the first 5 characters Instead of 1st five characters I wants to take the minimum length
How can i solve the above problem.
Should i use Decode function or Case function and how in where clause????
Can anyone help me out of the above problem

[amitpatel66]

Hi I am using a select query from front end to generate report in such a way that I should be able to get records of Table W which are present in Table Y
The query which i am using is as follows:

Select Y.ntktno,Y.cpassengername,Y.cfromroute,Y.cuptoroute 
from t_indi_infiledtl Y,t_indi_cancfiledtl W where Y.ntktno in 
(select ntktno from t_indi_cancfiledtl) and Y.cfromroute=W.cfromroute and
Y.cuptoroute=W.cuptoroute and 
soundex(substr(Y.cpassengername,1,5))=soundex(substr(W.cpassengername,1,5)) 

By the above Query I am getting the desired output .
But I wants to check the minimum length of the passengername between (Y.cpassengername and W.cpassengername) and using that minimum length i wants to fire the query for the soundex function since i cant assume that soundex (Y.cpassengername)=soundex(W.cpassengername) for the first 5 characters Instead of 1st five characters I wants to take the minimum length
How can i solve the above problem.
Should i use Decode function or Case function and how in where clause????
Can anyone help me out of the above problem

Do you mean you want to take the passengername of smaller length either from the table Y or table W. Then try this:

 
Select Y.ntktno,Y.cpassengername,Y.cfromroute,Y.cuptoroute 
from t_indi_infiledtl Y,t_indi_cancfiledtl W where Y.ntktno in 
(select ntktno from t_indi_cancfiledtl) and Y.cfromroute=W.cfromroute and
Y.cuptoroute=W.cuptoroute and soundex(substr(Y.cpassengername,1,LEAST(LENGTH(Y.cpassengername),LENGTH(W.cpassengername))))=soundex(substr(W.cpassengername,1,LEAST(LENGTH(Y.cpassengername),LENGTH(W.cpassengername))))
 
 

[subashsavji]

Hi I am using a select query from front end to generate report in such a way that I should be able to get records of Table W which are present in Table Y
The query which i am using is as follows:

Select Y.ntktno,Y.cpassengername,Y.cfromroute,Y.cuptoroute 
from t_indi_infiledtl Y,t_indi_cancfiledtl W where Y.ntktno in 
(select ntktno from t_indi_cancfiledtl) and Y.cfromroute=W.cfromroute and
Y.cuptoroute=W.cuptoroute and 
soundex(substr(Y.cpassengername,1,5))=soundex(substr(W.cpassengername,1,5)) 

By the above Query I am getting the desired output .
But I wants to check the minimum length of the passengername between (Y.cpassengername and W.cpassengername) and using that minimum length i wants to fire the query for the soundex function since i cant assume that soundex (Y.cpassengername)=soundex(W.cpassengername) for the first 5 characters Instead of 1st five characters I wants to take the minimum length
How can i solve the above problem.
Should i use Decode function or Case function and how in where clause????
Can anyone help me out of the above problem

 
select a.dly_ref_no,a.cns_no,a.cns_date,a.cnee_code,a.branch_branch_code,a.ranban
where
a.ac_year_code=b.ac_year_code and
a.branch_branch_code=b.branch_branch_code and
 a.branch_branch_code=decode('column_value','ALL',a.branch_branch_code,'column_value') and
group by a.dly_ref_no,a.cns_no,a.cns_date,a.cnee_code,a.branch_branch_code,a.ranban