By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,740 Members | 844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,740 IT Pros & Developers. It's quick & easy.

how to use decode?case function in where clause

P: 2
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:

Expand|Select|Wrap|Line Numbers
  1. Select Y.ntktno,Y.cpassengername,Y.cfromroute,Y.cuptoroute 
  2. from t_indi_infiledtl Y,t_indi_cancfiledtl W where Y.ntktno in 
  3. (select ntktno from t_indi_cancfiledtl) and Y.cfromroute=W.cfromroute and
  4. Y.cuptoroute=W.cuptoroute and 
  5. 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
Dec 21 '07 #1
Share this Question
Share on Google+
2 Replies


amitpatel66
Expert 100+
P: 2,367
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:

Expand|Select|Wrap|Line Numbers
  1. Select Y.ntktno,Y.cpassengername,Y.cfromroute,Y.cuptoroute 
  2. from t_indi_infiledtl Y,t_indi_cancfiledtl W where Y.ntktno in 
  3. (select ntktno from t_indi_cancfiledtl) and Y.cfromroute=W.cfromroute and
  4. Y.cuptoroute=W.cuptoroute and 
  5. 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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Select Y.ntktno,Y.cpassengername,Y.cfromroute,Y.cuptoroute 
  3. from t_indi_infiledtl Y,t_indi_cancfiledtl W where Y.ntktno in 
  4. (select ntktno from t_indi_cancfiledtl) and Y.cfromroute=W.cfromroute and
  5. 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))))
  6.  
  7.  
Mar 3 '08 #2

P: 93
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:

Expand|Select|Wrap|Line Numbers
  1. Select Y.ntktno,Y.cpassengername,Y.cfromroute,Y.cuptoroute 
  2. from t_indi_infiledtl Y,t_indi_cancfiledtl W where Y.ntktno in 
  3. (select ntktno from t_indi_cancfiledtl) and Y.cfromroute=W.cfromroute and
  4. Y.cuptoroute=W.cuptoroute and 
  5. 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



Expand|Select|Wrap|Line Numbers
  1.  
  2. select a.dly_ref_no,a.cns_no,a.cns_date,a.cnee_code,a.branch_branch_code,a.ranban
  3. where
  4. a.ac_year_code=b.ac_year_code and
  5. a.branch_branch_code=b.branch_branch_code and
  6. a.branch_branch_code=decode('column_value','ALL',a.branch_branch_code,'column_value') and
  7. group by a.dly_ref_no,a.cns_no,a.cns_date,a.cnee_code,a.branch_branch_code,a.ranban
  8.  
  9.  
Mar 4 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.