PL/SQL :: Multiply With Previous Row Value?

Aug 21, 2012

i need to multiply with previous row value?

my table have about to 100 columns and lakhs of rows

Ex:

date adj
------ -----
8/21/2012 1
1/1/2012 1
12/1/2011 0.5
8/1/2011 0.5

[code]....

My requirement is multiply the existing adj by adj

adj value coming as 0.5
present year always 1

Ex:

date adj
------ -----
8/21/2012 1
1/1/2012 1
.
.
12/1/2011 0.5
8/1/2011 0.5

[code]....

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: Multiply Rows From Different Tables?

Oct 27, 2011

I have the task that I have to determine the number of parts that need to be produced based on the number of products sold for the day (each product consists of many parts).

I am using SQL 11g Express.

The report would look something like this:

{OrderDate PartID PartDesc NumOfParts(Total for that day)
10-24-2011 2001 12" X 12" Solid Shelf 108
10-24-2011 2003 12" X 24" Solid Shelf 32
10-24-2011 3001 96" Side Panel 50

[code].......

My issue is, I can't get the equation right to produce the total number of parts. I think I need to multiply ProductPart.NumOfParts by SUM(CustOrder.Qty) Group by CustOrder.SKU.

Below I have what the calculations should look like

SKU PartID Total Parts Needed by SKU
DVCK1212 1001 42 (7 orders * 6 shelves)
DVCK1812 1002 0 (NONE)
DVCK2412 1003 42 (7 orders * 6 shelves)
DVCK3012 1004 78 (13 orders * 6 shelves)

[code].......

Here's my script:

/**PRODUCT**/
CREATE table Product
(
SKU VARCHAR2(10) NOT NULL,
ProdDesc VARCHAR2(50) NOT NULL,
Price NUMBER(5,2),
PRIMARY KEY(SKU)
);

[code].......

Query 1

SELECT ProductPart.NumOfParts
FROM ProductPart,
( SELECT CustOrder.SKU,
sum(CustOrder.qty) cust_qty_sum
FROM CustOrder

[code].......

Result: I am only getting a total of 2 parts

Query 2

SELECT ProductPart.Qty,
ProductPart.PartID,
ProductPart.PartDesc
(SELECT COUNT(CustOrder.SKU)
FROM CustOrder
GROUP BY CustomerOrder.SKU)TotalProducts,

[code].......

ORA-00936: missing expression

Query 3

SELECT Part.PartDesc.
ProductPart.NumOfParts
FROM ProductPart, Part
( SELECT CustOrder.SKU,
sum(CustOrder.qty) cust_qty_sum
FROM CustOrder

[code].......

ORA-00933: SQL command not properly ended

Query 4

SELECT o.OrderDate AS "Date Ordered",
o.OrderDate + 5 AS "Date Due",
pp.PartID AS "Part No.",
pp.NumOfParts * COUNT(o.SKU) AS "Qty"
FROM CustOrder o

[code].......

ORA-00934: group function is not allowed here

View 2 Replies View Related

SQL & PL/SQL :: Writing A Query To Multiply Columns

Nov 26, 2012

I am writing a query and I am trying to multiply some of the columns with *1.50 and 0.75.

The columns I am trying to multiply are coke_rebate.volumecsd and coke_rebate.volumencb. I am getting an error in oracle sql command from web "ORA-00937: not a single-group group function"

Here is the code I am writing:

SELECT CUSTOMERS.CUSTID,
CUSTOMERS.MEMBERID,
CUSTOMERS.BNAME,
COKE_REBATE.COKEID,
SUM(COKE_REBATE.VOLUMECSD*1.50) "TOTALCSD",
[code]........

View 7 Replies View Related

SQL & PL/SQL :: Extract Numbers From Text / Multiply / Get Result

Feb 27, 2013

Have 2 small tasks for regexp..where we need to extract numbers from the text & multiply & get the result.

Input field varchar2 : 5x a day x 10 days
Output : 5 * 10 = 50
Select regexp_replace('5x a day x 10 days', '[^[:digit:]]' from dual;

The code extracts numbers ..but doesn't multiply & give the result.Have one more scenario as well.

Input field varchar2 : take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days
Output : 2 * 800 * 4 * 6 * 10 = 384000
Select regexp_replace('take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days', '[^[:digit:]]' from dual;

For the above code, if it's hours ..we need to convert into day by multiplying with the required factor to make it a day.

View 9 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

Manually Find Difference In Days And Multiply By 24

Apr 25, 2011

1) First Problem:

Difference in hours is supposed to be 35 but it comes as 34.When i manually find the difference in days and multiply by 24 it comes as 34.999999999 and floor converts it as 34.. But when i execute the difference between dates alone and convert to 24 it shows as 35. So when i apply floor to this, it should be 35 instead of 34 right?

SQL> select (to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
2 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss'))
3 from dual
4 ;
[code].....

1) Second Problem:

For the following query, difference of the dates in minutes is 2100.. so mod(2100,60) should give 0 but it's giving 60.. Separately if if put mod(2100,60) it is giving as 0 only.

SQL> SELECT MOD(((to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
2 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 1440),
3 60) AS duration,
4 (to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
5 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 1440 as diffinmin
[code]....

View 2 Replies View Related

SQL & PL/SQL :: Query For Previous Day And Day Before

Jan 15, 2013

The main condition in SQL is like this.

SELECT TO_DATE (TO_CHAR (doc_date, 'MON-YY'), 'MON-YY') "INV_MTH",
SUM (inv_amt) INV_TOTAL
FROM table_x
WHERE doc_date BETWEEN TRUNC (SYSDATE, 'YYYY')
AND LAST_DAY (
ADD_MONTHS (TRUNC (SYSDATE, 'YYYY'), 11)
);

My Output from if run in JAN as of now 16 Jan.

INV_MTHINV_TOTAL
Jan-136260830.42

I want an sql until previous day of that month for example 15 Jan and another sql until day before previous day of that month for example 14 Jan.

View 15 Replies View Related

SQL & PL/SQL :: Get Previous Value Of Rows?

Dec 29, 2010

How to get the previous value of row with calling function to add value in SELECT statement for the row value.

Consider the example Table A1 having column a with values 1,NULL,NULL,NULL

SELECT CASE WHEN a IS NULL THEN (prev_row_value+function_return_Value) ELSE a END as A from A1

And my result-set should be like

a
----------------------
1
1+(Return Value Of Function)
Prev_Row_Value+(Return Value Of Function)
Prev_Row_Value+(Return Value Of Function)
Below is sample code but doesn't fulfill my criteria

[code]....

Output is

A A2
---------------------------
1 1
3
3
3

View 8 Replies View Related

SQL & PL/SQL :: How To Know Previous Scn Number

Jun 4, 2012

i want to know the all scn number's that are generated yesterday or in any previous day? how can i achieve it?

View 5 Replies View Related

Client IP Of Previous Transaction

Mar 11, 2013

Is it possible for me as a DBA to find IP address of the client who ran a specific transaction or query in past?

Oracle server version I'm using is 11g.

View 4 Replies View Related

SQL & PL/SQL :: Executing Rows With Previous Row

Nov 3, 2010

i have tablestructure like this

empno ename sal
1 sam 1000
2 tom 2000
3 ric 3000
4 mac 4000
5 doy 5000

i want TO WRITE SELECT QRY WHICH WILL GO like this

empno ename sal prevemp prevename presale
1 sam 1000 0 0 0
2 tom 2000 1 sam 1000
3 ric 3000 2 tom 2000
4 mac 4000 3 ric 3000
5 doy 5000 4 mac 4000

means when each current row executes it shld show details from previous row also means when details of tom is executing it also shows sam details

View 4 Replies View Related

SQL & PL/SQL :: Cumulative Sum Of Previous Row In Same Column?

Jun 28, 2013

Is it possible to get cumulative sum of the same column? I am trying to get a value for COL6... it is dependent on the values of previous row

COL6 Formula:

IF COL2 = 'A' THEN
IF 100 - [SUM_COL6] > COL5 THEN
COL5
ELSE
(TRUNC(100 - [SUM_COL6] / COL4) )* COL4
END IF

[code]....

View 6 Replies View Related

SQL & PL/SQL :: Getting Data From Previous Month

Mar 8, 2011

I need to get data from a table in which dates is equal from previous month. The dates in this table has a formula DD-MMM-YY (CRE_DTTM is the name for date column).

I've already achieve getting data from the previous month by using this formula:

(to_char(CRE_DTTM,'MON')) = UPPER(to_char(add_months(trunc(sysdate,'MONTH'),-1), 'Mon'))

My problem now is what if the current month is for example JAN 2011.. I need to get the data from DEC 2010. How can I query the previous year in this case?

View 10 Replies View Related

PL/SQL :: Previous Record Find?

Jun 25, 2013

CREATE TABLE F_TIME(  PERIOD_ID      NUMBER,  PERIOD_NAME    VARCHAR2(30 CHAR),  PERIOD_YEAR    NUMBER,  PERIOD_TYPE    VARCHAR2(30 CHAR),  CREATION_DATE  DATE,  UPDATE_DATE    DATE,  UPDATE_BY      NUMBER); SET DEFINE OFF;Insert into F_TIME   (PERIOD_ID, PERIOD_NAME, PERIOD_YEAR, PERIOD_TYPE, CREATION_DATE,     UPDATE_DATE) Values   (16, 'Q4', 2012, 'q', TO_DATE('04/20/2013 17:41:28', 'MM/DD/YYYY HH24:MI:SS'),     TO_DATE('04/20/2013 17:41:28', 'MM/DD/YYYY HH24:MI:SS'));Insert into F_TIME  

[code]...

if i pass 30 then it will return period id=16 data

View 3 Replies View Related

Compare Current With Previous Row Until Mismatch

Nov 2, 2011

We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:

CODEemp_salary
----------
emp_id NUMBER(15) NOT NULL
effective_date DATE NOT NULL
salary NUMBER(15) NOT NULL

Primary key - emp_id, effective_date..This table is yearly partitioned...I have to find out how long the salary is not changed for an employee from given date, and last salary. I am using below query to do this:

CODEWITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary

[code]....

For emp_id 1, if we ran this query for 10/31/2011, then it has to compare the 10/31 salary with 10/29 and do the same until the salary mismatches. In this case, salary salary mismatch occurs on 10/20, so the stale salary period is from 10/31 to 10/21 which is 7 days.Below query will give that result:

CODE
WITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Find Previous Cursor Loop Value

Dec 15, 2011

I take a select into a cursor and process it record by record.I have to do sum based on a column and display row by row by using dbms_output.put_line .... So the sum has to happen based on a column. Based on the column value i need to display the cumulative sum as well.

Example:-

col1 col2 amount
DL AADD 25
DL BBCC 10
DL BBRR 15

Sum value for DL ----- 50

TX ADED 20
TX EDWW 60

Sum value for TX ----- 80

All the above data should be displayed using DBMS_OUTPUT.PUT_LINE in a pl/sql code. I use cursor to take the values from the table but the problem i face is .... I am not able to display the sum based in the col1 values.

Since i use the cursor .. i took the col1 values in to a variable and checked every time
old_variable = new_variable
if yes then continue the sum
else
display the sum value.

once i get the above check satisfied i am loosing a new col1 row in the check. The next loop only run for the new col1 values -1( which is used in the check loop).So is there any better way to get the solution or is there a facility to store the previous loop values in a cursor ? so that i dont have to loose that one row of data.

I am not able to come up with proper loop so which can identify that the col1 has changed and you have to display the sum value.

View 17 Replies View Related

SQL & PL/SQL :: Previous Record For Multiple Combination

Aug 17, 2012

How to achieve "Prev_Value" column as shown below without using ORACLE analytic functions

I have records stored in table for various categories and based on ID / Name / Office / Product / Category combination I want to achieve previous value column through efficient SQL query

Test Scripts as below

CREATE TABLE TEST_Prev
(
ID1 NUMBER(3),
Name1 VARCHAR2(10),
OFFICE VARCHAR2(20),
PRODUCT VARCHAR2(20),
Overall VARCHAR2(20),
DATE1 DATE,
VALUE1 NUMBER(1)
);
commit;
[code]......

Expected output as in attached sheet.

View 11 Replies View Related

SQL & PL/SQL :: Command To Take Previous Day Upto Midnight

Mar 19, 2013

What command can be used to take the previous day upto mid night?

For example

TO_DATE('18-MAR-13 23:59:59', 'dd-mon-yy hh24:mi:ss')

Instead of me entering the date any way to take the previous day till mid-night.

I dont think sysdate-1 will work for me because if I enter sysdate-1 it will take from now -1 that means 18-mar-13 15.45.45 but I want till the previous date until mid-night.

View 3 Replies View Related

SQL & PL/SQL :: Compare Previous Records With Current?

Aug 29, 2013

I am trying to set the min date if there is no gap between dates.compare previous date2 value with current date1,if they are same then my new date will be min(date1).

source data

date1 iddate2 new_date
1/2/20111234/2/2011
4/2/20111237/2/2011
7/2/201112310/2/2011
10/2/20111231/2/2012
1/2/20121234/2/2012
4/2/20121237/2/2012
12/17/20121233/17/2013
3/17/20131236/17/2013

and I am expecting the out put like this

date1 id date2 new_date
1/2/20111234/2/20111/2/2011
4/2/20111237/2/20111/2/2011
7/2/201112310/2/20111/2/2011
10/2/20111231/2/20121/2/2011
1/2/20121234/2/20121/2/2011
4/2/20121237/2/20121/2/2011
12/17/20121233/17/201312/17/2012
3/17/20131236/17/201312/17/2012

how to achieve this with SQL

View 3 Replies View Related

SQL & PL/SQL :: How To Get Data For 25th Of Previous Month

Dec 12, 2010

i am looking for a SQL query by which i can extract data between 25th of previous month till today .

i tried the below code but no luck

SELECT TO_CHAR(SYSDATE, 'YYMM'), TO_CHAR(SYSDATE, 'YYMM') -1, TO_CHAR(SYSDATE, 'YYMM') -2, TO_CHAR(SYSDATE, 'YYMM') -3
FROM DUAL

View 4 Replies View Related

Forms :: How To Go Back To Previous Block

Feb 21, 2012

I'm making a menu in my form, wherein it has FILE, TRANSACTION and REPORT. Under FILE it has BACK, and LOGOUT. In my back menu item, i want to go back to the previous block or previous module. I used previous_block but it's not working in some of my blocks.

View 8 Replies View Related

Tar - Error Exit Delayed From Previous?

May 2, 2013

I have a tar.gz file when i issue command like this to untar,

mysql@test ~]$ tar xfvz mysql-advanced-5.5.17-linux2.6-x86_64.tar.gz

output:

mysql-advanced-5.5.17-linux2.6-x86_64/bin/mysql2mysql
mysql-advanced-5.5.17-linux2.6-x86_64/bin/mysqlslap
tar: skipping to next header
tar: Archive contains obsolescent base-64 headers incomplete literal tree
gzip: stdin: invalid compressed data--format violated
tar: Child returned status 1
tar: Error exit delayed from previous errors

But when i check i found the file like below,

mysql@test ~]$ ls
mysql-advanced-5.5.17-linux2.6-x86_64

This means its completly untar the file?Why that error is showing?

View 2 Replies View Related

PL/SQL :: Find Previous Value From Oracle Table

Jun 4, 2013

I have a stat table that got info like login_date,user_id etc.For a specific user, i have a requirement based on the no of days difference between the current login date and last login date.

For example, Tom logged in on June 4th 2013. His previous login was May 31. So no_of_days_difference is 5 days.How to programmatically get this for each user inside a pl-sql sub block.

View 2 Replies View Related

PL/SQL :: Function To Round To Previous Quarter

Feb 28, 2013

I need to have a function which will round to previous quarter

For ex :

if the value is 1.95 it should give 1.75
if the value is 1.60 it should give 1.50

View 2 Replies View Related

SQL & PL/SQL :: Function Recovery Of Previous Version

Jun 2, 2011

I've overwritten my PL/SQL function and it's not working now. Is there a way to recover the previous version of this function?

View 6 Replies View Related

SQL & PL/SQL :: Updating Current Column With Previous Columns?

Dec 22, 2011

I need to update the current column with sum of the previous column values. Following are the creation scripts

DROP TABLE TEST_LOG;
CREATE TABLE TEST_LOG
(

[Code]....

Above query is working fine to retrieve the previous column values.But when we are updating the SUM_PRE_COLS column with those values it's not working fine.

I tried by using the following query

UPDATE TEST_LOG T SET SUM_PRE_COLS =
( SELECT LAG(T2.KEY0, 1, 0) OVER(ORDER BY T2.KEY0) + LAG(T2.KEY1, 1, 0) OVER(ORDER BY T2.KEY0)
FROM TEST_LOG T2 WHERE T2.ROWID= T.ROWID);

View 5 Replies View Related

SQL & PL/SQL :: Selecting Value Of Previous Column In Case Statement?

Feb 20, 2012

Is there a way of selecting the value of a previous column in a case statement, without having to duplicate. E.g.

SELECT prod_id as Product,
Some complex calculation... as Total,
CASE

[Code]....

I do not want to re-write the same calculation in the else statement but just want it to be be the value of Total column.

View 1 Replies View Related

SQL & PL/SQL :: Revert Back Package Changes To Previous Version?

Mar 29, 2012

I overwritten the package and want to get the previous version.

Is there a way I can get it using FLASHBACK or any other feature?

My user_recyclebin is showing only tables.

SELECT object_name, original_name, TYPE
FROM user_recyclebin;

View 10 Replies View Related

Forms :: Repeat Same Values To Next Record From Previous?

Jun 16, 2011

How do you repeat the same values to the next record from the previous record to reduce the user entry in d2k ??

View 1 Replies View Related

PL/SQL :: Compare A Row Of A Table With Its Previous Row / Rows In A Query

Oct 1, 2013

create table a(sourcerow number(2),  test_level number(2),  dpn varchar2(1),  qty number(5)); T

he insert scripts are as follows:  

insert into a  values(1,3,'Y',5); insert into a values(2,2,'Y',4);  insert into a values(3,3,'N',3); insert into a  values(4,4,'Y',3);  insert into a  values(5,1,'N',6);  insert into a values(6,2,'N',5);  insert into a  values(7,2,'Y',4);  insert into a  values(8,3,'N',2);  insert into a values(9,4,'Y',2);  insert into a  values(10,1,'Y',3); .  SQL>select * from v$version;  Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production 

My logic should have the output as follows without 5th column:  sourcerowleveldpnqtyremark13Y522Y4This row in main table shouldn't be displayed in the query result as it has

dpn='N'44Y3*3=9In this row qty=9 will bedisplayed.

It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.this row will not be displayed as dpn='N'this row will not be displayed as dpn='N'72Y4*6=24in its previous row level value is same so it will check the previous to previous row  where level is 1(parent of current row) and dpn='N' ,then it will multiply the qty of that row with current row and display the value in qty column.this row will not be displayed as dpn='N'94Y2*2=4In this row qty=4 will be displayed.It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.101Y3It will not check for the previous rows as level 1 doesn't have any parent. 

View 5 Replies View Related







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