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
ADVERTISEMENT
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
Aug 30, 2012
I am trying to run a query that will count the number of each occurrence of a combined month and day. Birthdays are formatted as: 1947-07-26 00:00:00
I need to query everyone in the system who share the same month & day, but not year & time. For example:
1927-07-26 00:00:00
1927-07-26 00:00:00
1987-07-26 00:00:00
1965-07-26 00:00:00
1981-03-14 00:00:00
would give me a Count of 4 for 7/26 and a Count of 1 for 3/14
View 5 Replies
View Related
May 19, 2011
I have a timestamp field in an oracle table. The data in that field looks like this.
19-MAY-2011 10.55.21.628206000 AM
I want to query the data in this field by only date portion (not the time portion).
Something like this.
Select * from mytable where archivedate = to_date('19-may-2011','dd-mon-yyyy')
this query doesn't return any data. But actually there is data for 19-may-2011 (along with time portion) in that field.
how to query based on only date portion?
View 4 Replies
View Related
Oct 9, 2012
In an Application Procedure, I'd like to reference some items that are local to the current page. Obviously, hard-coding like the following won't work unless it happens to be on Page 25.
:f105_blah := :p25_blah;
So how can I write this line of PL/SQL code so that the "25" is automatically set to the number of the current page?
View 13 Replies
View Related
Sep 10, 2013
Is there any function which return the next multiples of 10 value directly ?
Ex : input - 11 then it should return 20
156 - 160
299 - 300
43 - 50
View 18 Replies
View Related
Aug 14, 2012
I have times stored as an integer in a table.
e.g.
102506 = 10:25:06
130455
How can I convert this to time format HH24:MI:SS?
View 6 Replies
View Related
May 24, 2012
I want to find nearest integer value where mod returns 0 in sql statement. I've tried following but it doesn't fulfill my requirement.
My Try
SQL> select
2 ((1200*1000)+45-mod((1200*1000),45)) f1,
3 mod( ((1200*1000)+45-mod((1200*1000),45)),45 ) f2,
4 ((1200*1000)+45-mod((1200*1000),45))/1000 f3
5 from dual;
F1 F2 F3
---------- ---------- ----------
1200015 0 1200.015
In above result F3 represent the actual result, which is nearest value where mod returns the 0, but i want nearest integer value which is 1206. how it is possible. In above case consider 1200 as Kgs and 45 as Grams.
View 20 Replies
View Related
Feb 5, 2012
I have the following function that I am using as a template for any function that executes a select statement and return a single value as an output.
The function is working but I wanted to take an expert opinion if it can be optimized.
CREATE OR REPLACE FUNCTION AFESD.F_AGR_GET_AGREEMENT_SERIAL
(I_NUMBER0 IN NUMBER, S_SUB_NUMBER VARCHAR2 DEFAULT NULL, I_TYPE_ID NUMBER)
[Code]....
In addition I want to use the parameter S_SUB_NUMBER that can be NULL and add it to the select statement of the cursor, but I dont know how to do that in one statement.
CURSOR C_AGREEMENT
IS
SELECT AGREEMENT_SERIAL
FROM VW_AGR_AGREEMENT
WHERE NUMBER0 = I_NUMBER0
AND TYPE_ID = I_TYPE_ID
-->and sub_number is null;
-->and sumb_number = s_sub_number
View 11 Replies
View Related
Nov 28, 2012
Recent events at work are forcing me to take a much closer look at hash joins in an attempt to understand them much deeper than just on the surface. But my question today is maybe simple. I have done lots of reading and can't for the life of me figure out how to get more memory to my HASH JOINS.
is there are way to get around this limit of 2GB on a box that has 64GB with some 20gb not in use?
1) my databases are all using workarea_size_policy=AUTO
2) I am not afraid to go back to =MANUAL and set my own work area sizes.
3) It seems I cannot set HASH_AREA_SIZE to more that about 2GB.
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
18:40:31 SQL> alter session set hash_area_size = 6000000000 ;
alter session set hash_area_size = 6000000000
*
ERROR at line 1:
ORA-02017: integer value required
I know there is a limit of about 2GB on my box for HASH_AREA_SIZE and setting it to 2GB works fine. But it is still not enough.
18:50:22 SQL> alter session set hash_area_size = 2147483647;
Session altered.
Elapsed: 00:00:00.23
is there are way to get around this limit of 2GB on a box that has 64GB with some 20gb not in use? Using hash_area_size and 2GB, I get better performance than with my current PGA_AGGREGATE_TARGET doing the allocation for me.
I think I'd like to get as much as 20GB to specific sessions for hash joins but maybe I am pipe dreaming?
NAME TYPE VALUE
------------------------------------ ----------- -------------------
_pga_max_size big integer 1258280K
pga_aggregate_target big integer 6G
View 5 Replies
View Related
Oct 18, 2010
im creating sequences for an already created db and need to set MINVALUE to sequences.
create sequence tb_1
minvalue X
start with X;
so, X == select Max(id_tb1) from tb_1.
I need that select returns an integer.
How can I do this??
View 11 Replies
View Related
Aug 15, 2012
what could be effective data type to store large integer values like, 50,000; 10,000,000 etc.?
View 3 Replies
View Related
Jun 14, 2012
How to get Time Difference between two DateTime Columns in Oracle 10g ?
View 10 Replies
View Related
Jul 13, 2011
I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :
TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209
My expected results should look like :
ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209
View 7 Replies
View Related
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Mar 7, 2013
I'm currently doing migration from Oracle 10gR2 RDF to Oracle 11gR2 Semantic Technology.I followed the steps on the documentation and successfully created the network using the following:
-----
EXECUTE SEM_APIS.CREATE_SEM_NETWORK('rdf_tblspace');
CREATE TABLE rdf_network_trace (id NUMBER, triple SDO_RDF_TRIPLE_S);
--Created SEQUENCE andTRIGGER FOR rdf_network_trace id
[code]....
when I looked at my Node Ids, they were like +635762253807433724+, +6118969225776891730+. The problem is, I am not the one who is assigning Node Ids, They were automatically generated when inserting TRIPLE data to the rdf table.
Did I miss something when I created my network?
View 11 Replies
View Related
