0

I need to select some records from a table according to my conditions. simply i need to do like this.

SELECT g.intds_intratechange 
case when g.intds_intratechange='0.5% Up to 1%' 
then g.intds_intratechange='ddd' 
end
FROM glinfo.ref_tblintratefordesignation g
WHERE g.intds_designation =(select s.STR_OFF_TYPE from dpg.inf_responsible_maildetails s where s.STR_USER_CODE='10020336')

in above table, there are 3 values. "0 t0 0.5" , "0.5 to 1" and "more than 1". I need to filter records from my select accoring to above values. like if value is "0 to 0.5" then select first 10 records. need a way to solve this. this code says "cannot founf from keyword where it expected"

Didu
  • 79
  • 2
  • 11
  • 1
    Didu, questions like this cry out for some data. Provide a sample of your data and we can assist faster with greater certainty (and way less questions back to you) Just a simple copy/pate of **some** data into the question formatted as a table is usually sufficient. – Paul Maxwell Nov 17 '17 at 05:57

1 Answers1

1

You can use BETWEEN to check those conditions.I am assuming your g.intds_intratechange column has a '%' at the end. If Not TRIM(g.intds_intratechange,'%') or TO_NUMBER might not be required.

SELECT 
        g.intds_intratechange,
        case    when TO_NUMBER(TRIM(g.intds_intratechange,'%'))  BETWEEN 0 AND 0.5 THEN 'ddd'
                when TO_NUMBER(TRIM(g.intds_intratechange,'%'))  BETWEEN 0.5 AND 1 THEN 'whatever_u_need'
                when TO_NUMBER(TRIM(g.intds_intratechange,'%'))  > 1  THEN 'whatever_u_need'
        end name_of_new_col
FROM 
        glinfo.ref_tblintratefordesignation g
WHERE   g.intds_designation IN 
        (select 
                s.STR_OFF_TYPE 
        from 
                dpg.inf_responsible_maildetails s 
        where   s.STR_USER_CODE='10020336' 
        );

like if value is "0 to 0.5" then select first 10 records. need a way to solve this.

Not completely clear what you meant by this. Please add more details in the question.

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • thanks kaushik. that's exactly what i need. but i didn't use % at the end. so i did like u said. but there is an error occurs saying "Invalid Number" – Didu Nov 17 '17 at 05:32
  • It means your `g.intds_intratechange` column has non-numeric characters. – Kaushik Nayak Nov 17 '17 at 05:36
  • this is what i need. I have a select statement that returns many records. now i need to filter this select to some conditions. there are 5 user types. "Director","Gen Manager" ... etc . there is a table that links with this user types. so i need to filter my select according to this user types. if "Director" then select first 10 records. if "Gen Manager" then select 10 to 25 records likewise. above code is my try. but don't have a solution yet – Didu Nov 17 '17 at 05:37
  • so do i need to change the data in table? i need to add 3 ranges. 0 to 0.5 , 0.5 to 1 and more than 1. how do i do that? – Didu Nov 17 '17 at 05:39
  • Use the query in the link https://stackoverflow.com/questions/7957423/finding-rows-that-dont-contain-numeric-data-in-oracle to find non-numeric characters . Then we could use proper TRIM , replace functions to eliminate them in select statement – Kaushik Nayak Nov 17 '17 at 05:42
  • Solved the problem. thanks kaushik. your answer showed me the way – Didu Nov 17 '17 at 06:33
  • @Didu : people say thanks in SO by upvoting and marking answers correct. – Kaushik Nayak Nov 17 '17 at 07:00