Home » SQL & PL/SQL » SQL & PL/SQL » How to retrieve data only those which are in decimals (oracle 12c)
How to retrieve data only those which are in decimals [message #681942] Tue, 15 September 2020 01:23 Go to next message
lacchhii
Messages: 145
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

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 Go to previous messageGo to next message
Michel Cadot
Messages: 67560
Registered: March 2007
Location: Nanterre, France, http://...
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.

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 Go to previous messageGo to next message
lacchhii
Messages: 145
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
Re: How to retrieve data only those which are in decimals [message #681945 is a reply to message #681944] Tue, 15 September 2020 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 67560
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
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.

This is what ROUND does.

Re: How to retrieve data only those which are in decimals [message #681946 is a reply to message #681945] Tue, 15 September 2020 06:06 Go to previous message
lacchhii
Messages: 145
Registered: May 2009
Location: bangalore
Senior Member

thank you sir.

i got it.
Previous Topic: Sending email MS Word attachment containing Arabic letters
Next Topic: Invisible double quotes
Goto Forum:
  


Current Time: Wed Dec 02 07:47:26 CST 2020