SQL & PL/SQL :: Decimal Places In A Value

Dec 12, 2011

I have a table with column amount as decimal(11,2). I want to store the values as shown below

15.30

but it stores as

15.3

Is it possible to store the value in 15.30?

View 4 Replies


ADVERTISEMENT

PL/SQL :: How To Generate Large Random Number Without Decimal Places

Nov 5, 2012

I need to generate random numbers from the range 71 million to 90 million. While generatind the same iam getting the value with decimal places.

How to generate with out decimal places ?

SQL> select ABS(dbms_random.value(71000000,90000000)) from dual;
ABS(DBMS_RANDOM.VALUE(71000000,90000000))
-----------------------------------------
87283730.7

View 6 Replies View Related

Semantic Technologies :: MapViewer And Google Places API

Apr 17, 2013

I'm new in the group and no have much experience in Spatial area.

I'm looking for information about the posibility of use google places apis to get point of interest from Oracle Spatial / MapBuilder / MapViewer in order to use point of interest from google with own spatial information.

View 1 Replies View Related

SQL & PL/SQL :: Store Data From Multiple Places In Materialized View?

Apr 14, 2011

I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.So, I created an object type (I've shortened the definitions):

CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));

Then created the table type of the object:

CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;

Then in the stored procedure defined a variable of the table type:

v_mf_record mf_table_type := mf_table_type();

Then I loop and populate the record type:

v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);

When all that is done I try and create the materialized view:

EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');

ORA-00904: "V_MF_RECORD": invalid identifier

Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?

View 1 Replies View Related

Reports & Discoverer :: Field Value To Be Printed Partially In 3 Places?

Jul 21, 2011

I need to print a value of a report partially at three places.

Example: If Field Value is 12-24-15 then I need to print 12 at One Place then 24 at second Place and 15 at third Place on the same page of a report and omitting -(Hyphen).

View 11 Replies View Related

SQL & PL/SQL :: Integer As Decimal

Feb 18, 2010

I have a field in a table that is declared in the CREATE statement as an INT datatype. However, when I query that table using vb.net, the value comes back as a decimal.

How do you declare a field in Oracle as a true integer data type?

View 5 Replies View Related

PL/SQL :: Two Decimal Points

Aug 10, 2012

What would be the syntax if I want to use use '999G999G990D00' to show only two decimal points for outstanding_receipt_value in the following

((declared_quantity - delivered_qty) * po_unit_price) outstanding_receipt_value

View 3 Replies View Related

How To Convert Hex To Decimal For Each 2 Digits

Dec 6, 2008

Actually I got a data in hex values and i want to convert it to datetime format. But before i convert the hex value into datetime format, i must convert the hex values into decimal val first for each 2 digits of hex values. This is an example of the input data:-

This is input data in hex value:-
STARTTIME : 080b1317021a

This is decimal value after convert from hex to decimal:-
STARTTIME : 081119230226

My problem is how could i convert hex to decimal in oracle? Below are my coding:-

case
when substr(LOAD_NGNSM.STARTTIME, 1, 2) not between '00' and '99'
or substr(LOAD_NGNSM.STARTTIME, 3, 2) not between '01' and '12'
or substr(LOAD_NGNSM.STARTTIME, 5, 2) not between '01' and '31'
or substr(LOAD_NGNSM.STARTTIME, 7, 2) not between '00' and '59'
or substr(LOAD_NGNSM.STARTTIME, 9, 2) not between '00' and '59'
[code]....

View 5 Replies View Related

SQL & PL/SQL :: 2nd Decimal Place Not Appearing?

Jul 19, 2012

I am using Oracle Forms 6i. In my program unit i write into a .CSV and .TXT File. Here if the amount is 100.00 then it prints as 100 only. That is if the 2nd decimal digit is 0 it doesnt print it.

For example
10.00 -->10
10.01--> 10.01
10.10-->10.1
10.12-->10.12

I used to_char(nvl(&amount,0),'9999999.99') so if query select to_char(nvl(10.10,0),'9999999.99') from dual, then i get the output as 10.10.

Do you think this is a problem with Oracle forms or SQL or the formatting of .TXT & .CSV.

View 12 Replies View Related

SQL & PL/SQL :: Format Column Value In Decimal

Jul 26, 2013

I have an amount field and I want the o/p in the format of $9,999,999.99

like for 1500 it should display $1,500.00
for 25000 it should display $25,000.00
for 25000000 it should display $25,000,000.00

Test case

add comma from last 3rd position ,and append a $ in the start with two decimal places

View 15 Replies View Related

SQL & PL/SQL :: Comparing Two Decimal Values

Nov 5, 2011

Recently i have started working on PLSQL coding. I have a requirement. Either error or un-processed record count is 90% of to be processed records then the script has to fail. Currently I am having a situation where error count is 1 and total to be processed is also 1.

in the below
V_ERR is error count
V_UPS is un processed count
V_PROCESSED_COUNT is total to be processed.

I am expecting PASS result but it is giving FAIL.

DECLARE
V_ERR NUMBER:=0;
V_UPS NUMBER:=0;
V_PROCESSED_COUNT NUMBER:=0;
NIN NUMBER;
BEGIN
V_PROCESSED_COUNT:=1;
[Code] .......

View 1 Replies View Related

SQL & PL/SQL :: IDs Decimal Values Are Truncating

Jul 10, 2012

If I perform a Query as below

SELECT SUM(total_amt) FROM Amont_dtls WHERE id=10;

It's giving me the value as

2.08166817117217E-19

The values in total_amount column for ID=10 is 0.01 and -0.01..Actually it should return 0.If I use the query like

SELECT SUM(0.01-0.01) FROm Amont_dtls WHERE id=10;

Then I am getting 0.Why it's a variation.And also the data type of the column is NUMBER.If I use

SELECT TRUNC(SUM(total_amt)) FROm Amont_dtls WHERE id=10;

Then I am getting 0.But some other ID's decimal values are truncating.

Ex: If i Use TRUNC
Instead of 28781947.48 it's showing 28781947

Instead of 590874.5 it's showing 590874

View 6 Replies View Related

SQL & PL/SQL :: How To Get Integer Portion From Decimal

Feb 21, 2012

Using Oracle SQL Developer 2.1.1.64 to run the queries & Oracle 11g.

I have two numbers in two colomns of an oracle table(colomn a & colomn b). I am trying to divide colomn a/colomn b and putting the results in colomn c & also in colomn d (all in the same table) using update commands

Eg: UPDATE MTOTABLE_PWELD pw SET pw.WELDO=(pw.pipe_length/12000);

But here is the real issue. In colomn d I only need the integer portion of the division value.

For example , when I divide colomn a/colomn b , let us assume that we are getting a value of 2.56. Then I want the value of 2 to go to colomn d.

I tried round((colomn a/colomn b),0). But it rounds off 2.56 to 3. I dont want that. I need the exact integer portion of the value to be seperated.

View 2 Replies View Related

SQL & PL/SQL :: No Rounded Decimal Numbers

Aug 5, 2011

how can show this query in this picture

select round(15.555,2) from dual appear final result 15.55 only

and this query don't be this result 15.56

because i have filed and always Decimal numbers be (15.555,2)

i want only result appear after two numbers and no rounded the numbers

View 2 Replies View Related

PL/SQL :: Number Not Showing Zero After Decimal

Feb 21, 2013

i have a column

NUMBER(13,4)

when i insert 234.000

in show only 234

where as when i insert 234.12

i shows 234.12

is there any setting by which i can show 234.0000

View 13 Replies View Related

SQL & PL/SQL :: Decimal Values Validation

Jun 5, 2012

CREATE TABLE TEST ( CREDIT_BALANCE VARCHAR2(11 BYTE), AWARDS_BALANCE VARCHAR2(11 BYTE) )

Insert into TEST(CREDIT_BALANCE, AWARDS_BALANCE)
Values ('1678.09', '1678.9');
Insert into TEST(CREDIT_BALANCE, AWARDS_BALANCE)

[Code]....

From valid records from test table those two columns,need to be insert into test1 table. We should validate the decimal values while selecting from test table.

using regexp.

View 26 Replies View Related

DECIMAL EXTERNAL In Control File?

Sep 13, 2010

In a control file, the code is as follows:

load data
infile 'C:\Documents and Settings\xxxxx\Desktop\abc.txt'
APPEND
PRESERVE BLANKS
INTO TABLE table1

[code]...

When I run the above control file in sqlldr, I'm getting the error as
Record 1: Rejected - Error on table table1, column column3.
ORA-01481: invalid number format model

In the table the column3 data type is NUMBER(6,2).: The column size in table is 6 and position of column3 in control file is only 4. Also if possible let me know how the same data (send me 2 dummy records) which exactly works for the above control file especially for column3 where decimal number comes in the flat file.

For generating the flat file, for column3, i'm using LPAD(:value,4,0) in the select query column list.

View 1 Replies View Related

Fix Number Of Digit After Decimal Point?

Mar 21, 2007

I have a small prob. I want an amount to be always 2 decimal places. I've used the Round function - Round(amount,2). the problem is that if the amount is only to 1 dp like 1.4. the above function will return 1.4. I want it to appear like 1.40

View 2 Replies View Related

SQL & PL/SQL :: How To Retrieve 6 Decimal Place Of Data

Oct 14, 2011

IF I have Salary Table,In that Salary Table,How to retrieve 6 decimal place of data .

View 16 Replies View Related

SQL & PL/SQL :: How To Convert Decimal Number To A Character

Aug 14, 2013

I want to convert decimal number 44 to character(" ' " i.e single quotation);

I have already tried to_char function but it's not working.

View 2 Replies View Related

Oracle 11g Different Decimal Char While Using Sproc

Jul 6, 2011

I am using an portuguese(brazil) Oracle 11g db,which should have ',' as decimal char. it seems to be working fine when i m trying to get numerical data via select query,but when i am trying to retrive the same data using out parameter of an sproc.I am getting data with '.' as decimal char.

View 2 Replies View Related

SQL & PL/SQL :: Selecting Numbers That Has Only 3 Digits After Decimal Point?

Mar 10, 2011

How to write SQL query for selecting all the values (numbers) in the table which has 3 digit after decimal point.

For example say A is the table it has following values

200.24
300.456
123.22
1234.344

From this I need only

300.456
1234.344

View 14 Replies View Related

SQL & PL/SQL :: Function That Multiply Number When There Is More Than 3 Decimal Point?

Mar 20, 2010

how to create a function that will multiply the number when the number has more than 3 decimal point.

Example.
123.012 - multiply by 10
123.0123 - multiply by 100
123.01234 - miltiply by 1000

Column A is the result of the number being multiplied according to decimal point.
Column B contains the multiplier used in column A.

View 10 Replies View Related

SQL & PL/SQL :: Data Type To Return Decimal Values In Function

Feb 26, 2013

I have the following database function.

GetRegionDetails(id in varchar2, o_lat out number, o_lon out number);

The problem is, the output values are returning as whole numbers ie. 38.108766567 is being returned as 38 and -78.16423574566 is returned as 78

what data type I should use so that my output is returns all the decimal values?

View 5 Replies View Related

Spatial :: Decimal Points - Geometry - Sql Developer Geographer?

Aug 13, 2013

I have loaded shape files with SQL developer Georaper and limited number of decimals to 8. There is option to limit number of decimals in georapter.But there are some X, Y with many decimals. This may create problem in spatial functions in which geometries are compared and filtered

how to control the decimal points of X,Y to be loaded in geometry column.  -0.4695623500000000172249770002963487058878                    51.47160360000000167701728059910237789154-0.4702653600000000211345252409955719485879                    51.47174944999999723904693382792174816132-0.4713475400000000092859409051015973091125                    51.47330664999999783049133839085698127747-0.4717886499999999760923685698799090459943  
                 
[code]...

View 2 Replies View Related

PL/SQL :: Regular Expression To Check Number With At Least One Decimal Point?

Nov 18, 2013

I would like to use the REGEX_LIKE to check a number with up to two digits and at least one decimal point: Ex.10.11.1112 This is what I have so far.

if regexp_like(v_expr, '^(d{0,2})+(.[0-9]{1})?$') t

View 3 Replies View Related

PL/SQL :: Round Function Output Depend On 2nd Digit After Decimal Point

Oct 19, 2012

I have a question on Round Function

Round(###.###,1)

Will the round function output depend on 2nd digit after decimal point also or not?

View 6 Replies View Related

How To Change Decimal Symbol And Digit Grouping Symbol

Jun 19, 2011

Currently , my system using , as digit grouping symbol and . as decimal symbol in Oracle forms and report. Is there any way to reverse that to the whole system?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved