OraFAQ Forum: SQL & PL/SQL » How to retrieve data only those which are in decimals

Home » SQL & PL/SQL » SQL & PL/SQL » How to retrieve data only those which are in decimals (oracle 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to retrieve data only those which are in decimals [message #681942]

Tue, 15 September 2020 01:23

lacchhii
Messages: 151
Registered: May 2009
Location: bangalore

Senior Member

Hi,

I have a huge data of around 4 lakhs. There are some values of a particular field in decimals. I need to filter only those values which are in decimals so that i can update those records using floor or ceil.

I did try using this query, it did not give the entire record and still there are many records with decimal values.

SELECT pnsr_file_no, 
       pnsr_full_name, 
       pnsr_secn_id, 
       pnsr_dcrg_amt,  
       Sum(auth_amt) auth 
FROM   m_pensioner, 
       m_pen_authority 
WHERE  ceil (pnsr_dcrg_amt * 10) != ( pnsr_dcrg_amt * 10 ) 
       AND pnsr_pk = auth_pnsr_pk 
       AND pnsr_file_no LIKE '25%' 
       AND auth_type='761' 
GROUP  BY pnsr_file_no, 
          pnsr_full_name, 
          pnsr_secn_id, 
          pnsr_dcrg_amt

Could anyone please help me in a proper query to retrieve all the required data

Report message to a moderator

Re: How to retrieve data only those which are in decimals [message #681943 is a reply to message #681942]

Tue, 15 September 2020 02:27

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

4 lakhs is not huge, I have tables with 20 times this on my laptop.
Why do you want to filter them? just update all rows with the ceil, floor or round function as you want.

Note for your next topic, you didn't specify on what and how you want to filter.

Also, as I told you in a previous topic:

Michel Cadot wrote on Thu, 15 October 2015 08:25

...
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Report message to a moderator

Re: How to retrieve data only those which are in decimals [message #681944 is a reply to message #681943]

Tue, 15 September 2020 04:33

lacchhii
Messages: 151
Registered: May 2009
Location: bangalore

Senior Member

Hi sir,

Thanks for the reply.

My oracle version is Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production.

I need to filter the data with decimal because i need to use floor for cases with decimal value less than 0.50 and ceil for cases with decimal value more than 0.50.

I don't know if there is any method to do it in one single update

Report message to a moderator