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:

[CODE=oracle]Select Y.ntktno,Y.cpas sengername,Y.cf romroute,Y.cupt oroute
from t_indi_infiledt l Y,t_indi_cancfi ledtl W where Y.ntktno in
(select ntktno from t_indi_cancfile dtl) and Y.cfromroute=W. cfromroute and
Y.cuptoroute=W. cuptoroute and
soundex(substr( Y.cpassengernam e,1,5))=soundex (substr(W.cpass engername,1,5)) [/CODE]

By the above Query I am getting the desired output .
But I wants to check the minimum length of the passengername between (Y.cpassengerna me and W.cpassengernam e) and using that minimum length i wants to fire the query for the soundex function since i cant assume that soundex (Y.cpassengerna me)=soundex(W.c passengername) 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:

[CODE=oracle]Select Y.ntktno,Y.cpas sengername,Y.cf romroute,Y.cupt oroute
from t_indi_infiledt l Y,t_indi_cancfi ledtl W where Y.ntktno in
(select ntktno from t_indi_cancfile dtl) and Y.cfromroute=W. cfromroute and
Y.cuptoroute=W. cuptoroute and
soundex(substr( Y.cpassengernam e,1,5))=soundex (substr(W.cpass engername,1,5)) [/CODE]

By the above Query I am getting the desired output .
But I wants to check the minimum length of the passengername between (Y.cpassengerna me and W.cpassengernam e) and using that minimum length i wants to fire the query for the soundex function since i cant assume that soundex (Y.cpassengerna me)=soundex(W.c passengername) 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:

[code=oracle]

Select Y.ntktno,Y.cpas sengername,Y.cf romroute,Y.cupt oroute
from t_indi_infiledt l Y,t_indi_cancfi ledtl W where Y.ntktno in
(select ntktno from t_indi_cancfile dtl) and Y.cfromroute=W. cfromroute and
Y.cuptoroute=W. cuptoroute and soundex(substr( Y.cpassengernam e,1,LEAST(LENGT H(Y.cpassengern ame),LENGTH(W.c passengername)) ))=soundex(subs tr(W.cpassenger name,1,LEAST(LE NGTH(Y.cpasseng ername),LENGTH( W.cpassengernam e))))

[/code]

[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:

[CODE=oracle]Select Y.ntktno,Y.cpas sengername,Y.cf romroute,Y.cupt oroute
from t_indi_infiledt l Y,t_indi_cancfi ledtl W where Y.ntktno in
(select ntktno from t_indi_cancfile dtl) and Y.cfromroute=W. cfromroute and
Y.cuptoroute=W. cuptoroute and
soundex(substr( Y.cpassengernam e,1,5))=soundex (substr(W.cpass engername,1,5)) [/CODE]

By the above Query I am getting the desired output .
But I wants to check the minimum length of the passengername between (Y.cpassengerna me and W.cpassengernam e) and using that minimum length i wants to fire the query for the soundex function since i cant assume that soundex (Y.cpassengerna me)=soundex(W.c passengername) 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

[code=oracle]

select a.dly_ref_no,a. cns_no,a.cns_da te,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('c olumn_value','A LL',a.branch_br anch_code,'colu mn_value') and
group by a.dly_ref_no,a. cns_no,a.cns_da te,a.cnee_code, a.branch_branch _code,a.ranban

[/code]