SQL & PL/SQL :: Calculate Difference Between Multiple Numbers

Feb 28, 2013

I need to subtract multiple numbers to get the difference between each numbers, the amount of numbers to subtract between each others can vary between 2 and 10.

create table table_1 (
col1 varchar2(5),
col2 number(6,0),
col3 number(12,0)
)

insert into table_1 values ('AAA', 34379, 11111);
insert into table_1 values ('AAA', 39032, 11111);
insert into table_1 values ('AAA', 54337, 11111);
insert into table_1 values ('AAA', 78005, 11111);

insert into table_1 values ('AAA', 66793, 22222);
insert into table_1 values ('AAA', 74323, 22222);
insert into table_1 values ('AAA', 81426, 22222);

Expected Output:

col1 col2 col3
AAA -4653 11111
AAA -15305 11111
AAA -23668 11111
AAA -7530 22222
AAA -7103 22222

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Calculate Difference Between Multiple Dates At Same Time?

Jan 10, 2012

how to calculate the difference between multiple dates at the same time..

Select to_date('10/10/2011','mm/dd/yyyy')
- to_date('09/10/2011','mm/dd/yyyy')
- to_date('08/10/2011','mm/dd/yyyy') from dual;

Giving me an error...

ORA 00932 : inconsisten data types:expected DATE Julkian got DATE..

View 8 Replies View Related

PL/SQL :: Calculate Difference By Which Day Count Of Difference Is Going?

Oct 17, 2012

Detail table will look like below:

Product_id issue_date action_date Force_date
1 10/10/2012 10/10/2012 10/10/2012
2 10/10/2012 10/10/2012 10/10/2012
3 10/10/2012 13/10/2012 15/10/2012
[code]....

Need the data like

Issue_date count_action_date count_Force_date (diff(action_date,force_date) 1 2 3 4 5 6(days since over)

10/10/2012 3 4 1 4 2 1 0 0

How to get the data like this? automatically how to get 123.... and how to calculate the difference by which day the count of difference is going?

View 3 Replies View Related

SQL & PL/SQL :: How To Calculate Time Difference

Aug 10, 2003

I want to calculate time difference b/w two time clocks, just like we calculate the date difference and answer is in days, In the same way i like to have answer in hrs,min and ss.

View 4 Replies View Related

SQL & PL/SQL :: Calculate Time Difference Between 2 Date

Nov 29, 2011

during application migration, i got one table from MS Access, and have situation where two events are splited into 4 columns (start: date1 time1 and stop: dat2 and time2). How to properly calculate duration between these two events, and show it in format: hh:mi ?

CREATE TABLE ACCBTP_DCZASTOJ
(ID NUMBER(11,0),
NASTANAK_KVARA DATE,
VRIJ_NASTKVARA DATE,
VRIJEME_OPRAVKE DATE,
DATUM_OPRAVKE DATE);

[Code]....

View 6 Replies View Related

Client Tools :: Toad / Export And Import - Value Difference For Big Numbers?

Oct 13, 2010

I wanted to load some data(selected rows) from one system to other.

from source(production) system Toad: selected * from employee table where registration date is not today.

from data grid I exported the rows using save as (in insert statement format) to employee.sql.

the sql file(employee.sql) has the below values for emaplyee 2,60716197237375E17 in the source(production) system this value is 260716197237375278, Later I loaded the sql file to dev system at linux prompt with sql file

@emplaoyee.sql

I retrieved the data after loading using to_char

select toc_char(emp_code) from employee_t;

the value I got is :260716197237375000

where as my expectation is : 260716197237375000

Is there any configuration required on linux system to convert the data correctly while loading to database using @employee.sql?

To conclude:

Exported value : 260716197237375278 (but exported as 2,60716197237375E17)

After import the value is : 260716197237375000 instead 260716197237375000

View 3 Replies View Related

Syntax To Calculate Number Of Days Difference

Sep 27, 2010

setting up the query/correcting the syntax below so that it calculates the 'number of days difference' between whatever the 'Biggest Date' field value is and whatever the 'current date' is using the 'sysdate'. So far, I've only managed to get the query to calculate the number of days difference (days past due) between the 'need date' and 'estimated delivery date'.

CODESELECT
To_Date(need_date, 'YYYYMMDD') Need_Dt,
To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Biggest_Date,  
To_Date(need_date, 'YYYYMMDD') - To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Date_Diff
        
FROM tableT

WHERE
need_date <=  (Case when estimated_delivery > ' ' THEN  estimated_delivery ELSE need_date END)

ORDER BY Date_Diff ASC

View 6 Replies View Related

SQL & PL/SQL :: To Calculate Minutes From Difference Of To Number Fields

Apr 9, 2013

I need the query to calculate minutes from difference of to number fields. Test case is as below.

DROP TABLE tmp;

CREATE TABLE tmp
(
code NUMBER(4),
stime NUMBER(4,2),
otime NUMBER(4,2)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
[code]......

CODE STIME OTIME
---------- ---------- ----------
1065 20 19.49
1082 20 18.57
1279 19.3 18.59
2075 19.3 15.32

Required output is

CODE STIME OTIME HR_MIN MINUTES
---------- ---------- ---------- ------------- --------
1065 20 19.49 00 HR 21 MIN 21
1082 20 18.57 01 HR 03 MIN 63
1279 19.3 18.59 00 HR 31 MIN 31
2075 19.3 15.32 03 HR 58 MIN 238

View 8 Replies View Related

Forms :: Calculate Difference Time Between Two Fields

Mar 24, 2011

I have two different date in my payroll software,

1-Shift_date shift date *used to contain shift timings
2-Attendace_datedate *used to contain employee IN timings

As you all know that shift is a setup form, where user input data once in the starting of software so the shift_date can be "01/jan/2011 16:00 pm" but attendance loads daily and attendance field data can be in this form "24/mar/2011 16:15 pm"..Now I want to calculate difference time between these two fields therefore I used this statement

SQL> Select to_char(attendance_date,'HH24:MI') to_char(shift_date,'HH24:MI') from dual;

but it is showing error: ORA-01722: invalid number...I used hours/minutes format mask in my query because you can see there is a difference of dates between these fields and it will be increase in the coming future and I need late hours and minutes.

View 2 Replies View Related

SQL & PL/SQL :: Write A Function To Calculate Difference Between Two Dates?

Sep 22, 2011

create or replace function getDate(p_joing_date Date,p_sysdate)
Return Date;
IS
v_compltd_mnths;
BEGIN
SELECT into v_compltd_mnths MONTHS_BETWEEN(TO_DATE('sysdate','MM-DD-YYYY'), TO_DATE('joing_date','MM-DD-YYYY') ) "Months"FROM DUAL;
return v_compltd_mnths;
END;

that i have worte..

View 3 Replies View Related

How To Calculate Individual Sums Of Multiple Columns In Single Query

May 13, 2013

Using Oracle 11gR2 on windows 7 client. I have a question on calculating sum() on multiple columns on different columns and store the results in a view. Unfortunately I could not post the problem here as it keeps on giving error "Sorry, this content is not allowed", without telling where or what it is! So I had to post it in the stack-overflow forum, here is the link: [URL] .........

View 6 Replies View Related

Select Multiple Images Using Department Field In Spriden Table To Pull Needed Id Numbers

Jan 29, 2009

I have an Oracle 10g database, on the App Serv I have an image file that has 20,000 .jpg files that has an id number as each image name.I have successfully queryed the image file and posted one image to my web page matching the image id number.
sample:

select substr(spriden_last_name,1,20)||', '||
substr(spriden_first_name,1,20)||' '||
substr(spriden_mi,1,1) stname,
'<img src = "/images/&1..JPG" width="400" height="400"/>' pic
from spriden
where spriden_id = '&1'
/

the &1 is the matching id number that is input from the user.My task now is to select multiple images using a department field in the spriden table to pull the needed id numbers.I have not been successful in the proper format to pass the id number to the <img src field.

View 4 Replies View Related

SQL & PL/SQL :: Extracting Numbers And Few Portion Of Text From A String Containing Dates / Characters And Numbers

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

String Of Numbers

Oct 12, 2007

I have the following set of numbers that i am passing in as one input into a stored procedure.

234,456,234,456,567

Now i want to take this list of numbers and use it in an IN statement:

select * from table where column_a in (P_INPUT);

however, when i try this, it give me an invalid error. I have tried inserting single quote around each value and get the same invalid error. I tried a To_char around my column, which solved the error, but it never finds a match!

View 6 Replies View Related

SQL & PL/SQL :: How To Get Random Numbers

Jul 1, 2010

I want to get 10 random numbers from existing 100 numbers. How can we get/generate random numbers ?

for example I have a table with customer ID, customer Name, having 100 record. We want 10 customers ID randomly from that 100 record not repeated any number. Have any command or procedure for that ?

View 3 Replies View Related

SQL & PL/SQL :: How To Get Column Containing Numbers Only

Jul 25, 2010

I have a column of datatype varchar2

this column may contain char or may contain number or may contain both

some values in this column are only number is there any function or any other way to get those column that are only number

View 3 Replies View Related

SQL & PL/SQL :: Combinations Of Numbers

Nov 1, 2010

Earlier, we had a discussion how to generate the permutations. generating the code for combinations

For eg: if I have input as [1,2,3,4,5] then 5c2 = 20 combinations should be generated as pairs

1,2
1,3
1,4
1,5
2,3
2,4
2,5
3,4
3,5
4,5

Total of 10 combinations

View 8 Replies View Related

SQL & PL/SQL :: Function To Sum Five Numbers

Apr 13, 2013

I am creating a function to sum five numbers (less 1). Is it possible to have an array of numbers in an SQL function, and how would this be implemented?

Here is the screenshot of my output (I cannot embed links until 5 posts!): flic.kr/p/eaSHBP

CREATE OR REPLACE FUNCTION sumfivenumbers ( n1 NUMBER, n2 NUMBER, n3 NUMBER, n4 NUMBER, n5 NUMBER)
RETURN NUMBER
IS
Sumnums NUMBER;
BEGIN
SELECT SUM(n1+n2+n3+n4+n5-1) INTO Sumnums FROM DUAL;
DBMS_OUTPUT.PUT_LINE(Sumnums);
RETURN 1;
END sumfivenumbers;
/
SELECT sumfivenumbers(5,5,5,5,5) AS "Five Numbers less 1" FROM DUAL;

View 7 Replies View Related

SQL & PL/SQL :: Selecting 1st And 2nd TOP Numbers (from Same Row)

Jul 6, 2012

I need to find the top two values value for each ID Number:

CREATE TABLE TABLE_1
(ID number (8),
NUMBER_1 number (2),
NUMBER_2 number (2),
NUMBER_3 number (2),
NUMBER_4 number (2));

INSERT INTO TABLE_1
VALUES
('12345679','30','25','30','05');
INSERT INTO TABLE_1
VALUES
('99999999','30','25','15','05');

Desired Result:

ID Number 1st 2nd
12345679 30 30
99999999 30 25

View 7 Replies View Related

SQL & PL/SQL :: Numbers To Alphabets

Jul 6, 2013

I have a requirement to display numbers as equivalent alphabets , like if the stored value is 1 then it should display as 'A' ,2 means 'B' ,is there a way to find out.

CREATE TABLE APS ( ITEM_NO NUMBER, ITEM_NAME VARCHAR2(12))
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (1,'TEST1');
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (2,'TEST2');
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (3,'TEST3');
INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (4,'TEST4');

-- The output to be is.

item_no , item_name
A TEST1
B TEST2
C TEST3
D TEST4

View 23 Replies View Related

SQL & PL/SQL :: Numbers To Words

Jul 6, 2012

I tried to convert numbers to words, it shows the below error.

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 11:00:29 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;

TO_CHAR(TO_DATE(2447834,'J'),'JSP')
---------------------------------------------------------------------------
two million four hundred forty-seven thousand eight hundred thirty-four

SQL> SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL;
SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL
*
ERROR at line 1: ORA-01830: date format picture ends before converting entire input string

SQL>

View 4 Replies View Related

PL/SQL :: Extract Numbers Only

Sep 18, 2012

Here i face probelm that he numbers must be follw by DOT "." , this is not correct if the statment only conatines numbers without DOT that not extract. As the

SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;

but mean i have to add . after numbers . as follow

SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;

but this is not right

i want to extract numbers without DOT also.

View 6 Replies View Related

SQL & PL/SQL :: How To Print Even Numbers Without Using MOD Function

Mar 1, 2012

How to print even numbers 1 to 10.without using the MOD Function

View 17 Replies View Related

Insert All Client Numbers

May 12, 2010

I am trying to run this script:

Update ACT.CUSTOMER_CLIENT_REFERENCE
Set ORIGINAL_SOURCE_FG = 'N',
CANADIAN_ULTIMATE_REFERENCE_FG = 'N',
LUDT = SYSDATE,
CLT_NO = (Select clt_no from client.client where clt_no between 701885 and 705287)
Where Exists (Select 1 from client.client where clt_ofc_no = 19 and clt_no between 701885 and 705287)

But the CLT_NO = .... line is giving me this error: ORA-01427: single-row subquery returns more than one row

what i am trying to do is insert all the client numbers between 701885 and 705287 into ACT.CUSTOMER_CLIENT_REFERENCE

View 6 Replies View Related

SQL & PL/SQL :: Checking Consecutive Numbers

May 11, 2012

I have a table with three columns: terminal, place and batch. How can I check for missing batches?

select * from transactions;

terminal place batch
84812
84813
84814
84816
84821
84823
84824
84825

View 5 Replies View Related

SQL & PL/SQL :: Spell Out Numbers To Words

Mar 14, 2002

I found a message which explain how to spell out numbers to words. I'm french and the purpose of my question is how to convert numbers to word (to print cheque) but in french the function found in the newsgroup was :

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual
/

I'm afraid but i don't undaunted how pass my number to this function (&num ???)

View 12 Replies View Related

SQL & PL/SQL :: Missing Numbers In A Sequence

Feb 16, 2010

create table t (
number_from number,
number_to number);
insert into t values(1,3);
insert into t values(5,9);
insert into t values(10,15);
commit;

I need to create a stored function that could find/return missing numbers between number_from and number_to for each record in table t.

For example:

Number_fromNumber_to missing

13
594 from previous record, this record is supposed to start from 4

View 24 Replies View Related

Forms :: Calculation When Add Numbers

Feb 5, 2011

I am developing a form where I need to add Numbers.In fact we have a bag of Cones that contain 24 cones.In normal calculation when I add numbers for example

5.24 Plus 5.24 it will give the result 10.48

I Need the appropriate method to calculate if I add these two numbers it should give the result 5.24 Plus 5.24 the result should be 12

View 7 Replies View Related

SQL & PL/SQL :: Find Numbers Between More Than 1 Condition

Sep 21, 2011

I have a table A with 10000 records with values as 1 to 10000. Table B has columns as range,number1,number2. Totally there are three rows as

ROW1=range1,1,100 & ROW2=range2,300,500 & ROW3=range3,800,900

I need to select all records from table A when it is between any one of the three rows from table B.

View 13 Replies View Related

SQL & PL/SQL :: Assigning Numbers To The List?

Apr 10, 2012

acheive the below result.

Select * from result;

res
===
3
3
3
3
3
0
3
3
3
0
3
0

output:

I would like to get res1 using SQL query.

resres1

31
32
33
34
35
00
31
32
00
31
32
33
34
00
.. ....

View 9 Replies View Related







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