How to find a SQL query results with different column?-CodePudding

I used the query select * from account where transaction_ID = 18256 and I was able to get the result that I wanted. It gave me the column account_number = 5.48129651984531E18 and transaction_ID = 18526.

I tried searching up the data by using the query: select * from account where account_number = '5.48129651984531E18' and did not get any results.

I tried where account_number = 5481296519845310000 and it still did not work. I also used single quotes for account_number = '5481296519845310000' and it still didin't give me any results but i should be getting the same results when i used the first query where transaction_ID = 18256. Not sure what is wrong.

CodePudding user response：

select * from account where account_number = CAST(CAST('5.48129651984531E18' AS FLOAT) AS bigint)

CodePudding user response：

You get such output just because of wrong formatting settings. For example in sqlplus:

SQL> show numformat
numformat ""
SQL> select 12345678901234567890   1 x from dual;

         X
----------
1.2346E 19

SQL> set numformat tm9
SQL> select 12345678901234567890   1 x from dual;

                    X
--------------------
12345678901234567891

SQL> select to_char(12345678901234567890   1, 'tm9') x from dual;

X
--------------------
12345678901234567891

So just use to_char(account_number, 'tm9') to output it correctly without round up