PL/SQL : query numeric data type by scale

oracleplsql

Wish to select rows with number fields having a certain amount of data after the decimal

select number from table where number.scale > 4;

Is this possible to do directly, or must I cast to string and parse character wise

Best Answer

If I understand the question correctly you want to find numbers that have, for example, more than 4 digits of precision after the decimal? E.g., you want 3.0671, 3.06713, etc. but not 3.067 or 3.06 or 3. If that is the case then you could do something like:

select number from table where number - trunc(number,4) <> 0;

trunc here will simply remove everything ater the fourth decimal digi

(I'm not taking into consideration what Nick said regarding 2 and 2.00.)

Related Solutions

Corrupt date fields causing query failure in Oracle

The date looks fine:

SQL> CREATE TABLE testdate (d date);
Table created
SQL> INSERT INTO testdate
  2     VALUES (to_date(to_char((120-100)*100+107-100)||'-12-31','yyyy-mm-dd'));
1 row inserted
SQL> SELECT DUMP(d), d FROM testdate;
DUMP(D)                                                                          D
-------------------------------------------------------------------------------- -----------
Typ=12 Len=7: 120,107,12,31,1,1,1                                                31/12/2007

Are you able to use the specific conversion function TO_CHAR on those lines? For instance SELECT to_char(confidential_until, 'yyyy-mm-dd') FROM mytable WHERE id = :x

Oracle Indexes – SQL Query to Identify Missing Hinted Indexes

Personally, I would start with DBA_HIST_ACTIVE_SESS_HISTORY, and look at all statments that contain that type of hint.

From there, you can pull the index name coming from that hint, and then do a lookup on dba_indexes to see if the index exists, is valid, etc.

You should be able to do this via PL/SQL if you want to make it really fancy and do it all in one step, otherwise a few pieces of SQL and a spreadsheet application can be your friend.

Best Answer

Related Solutions

Corrupt date fields causing query failure in Oracle

Oracle Indexes – SQL Query to Identify Missing Hinted Indexes

Related Question