How to retrieve data only those which are in decimals [message #681942] |
Tue, 15 September 2020 01:23 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
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
|
|
|
|
|