SQL & PL/SQL :: Perform SUM On Two Columns Based On Level?

Oct 21, 2011

Below is the code that i tried to perform the sum operation.

CREATE TABLE TEST11
(
FISCAL_TIME_ID NUMBER,
data_id number,
M_VALUE NUMBER,
Y_VALUE NUMBER
);

[code].....

The result I got is

SQL> SELECT FISCAL_TIME_ID, DATA_ID, M_VALUE,
2 SUM(m_value) OVER (PARTITION BY fiscal_time_id, data_id
3 ORDER BY FISCAL_TIME_ID) AS YTD_VALUE
4 from test11;

[code].....

But what I am actually want to get is.

FISCAL_TIME_ID DATA_ID M_VALUE YTD_VALUE
-------------- ---------- ---------- ----------
20110500 3 2 2
20110700 3 50 52
20110800 3 52
20111000 3 250 352
20111100 3 300 652

That is, the YTD_Value column is nothing but sum of M_VALUE column + previous fiscal_month_id's ytd_value column.

Test case:

The YTD_VALUE for fiscal_time_id 20110700 is obtained as current M_VALUE + previous fiscal_time_id's ytd_value => 50 + 2

I tried with the SQL but i could not get the result.

View 5 Replies


ADVERTISEMENT

Server Utilities :: What Privileges Are Required To Perform Only Schema Level Export And Import

Feb 20, 2012

Quote:The EXP_FULL_DATABASE and IMP_FULL_DATABASE, respectively, are needed to perform a full export and import.

what privileges are required to perform only schema level export and import?

View 3 Replies View Related

Forms :: Populate Default Values In Line Level Based On Header

May 29, 2011

I have two blocks.In header 2 columns are there same columns in line how to populate default values in line level based on header?

View 1 Replies View Related

SQL & PL/SQL :: Top Row Based On Multiple Columns?

Dec 1, 2011

I have a table with Column A, B, C. I want to write a query to retrieve the top row of A, B combination. i.e, for every unique value of A,B combination I want the row having highest value for C. I tried using rank() function but am not able to get the top row with combination of A,B.

View 8 Replies View Related

SQL & PL/SQL :: Aggregation Two Different Columns Based On Same Condition?

Apr 3, 2011

I have a result-set which has 4 columns like (Region, PaymentDate, DebitAmt, CreditAmt). This result-set will always have a maximum of one month's records in it.

Suppose, imagine i have data for a month (ex, Mar 1 to Mar 31)...

Now... to aggregate (sum) the amount columns (DebitAmt, CreditAmt) in my resultset based on different date ranges, i wrote a sql like below...

Quote:
SELECT
REGION,
SUM(CASE WHEN PAYMENTDATE BETWEEN MAR 1 AND MAR 15 THEN DebitAmt ELSE 0 END) AS Debit_H1,
SUM(CASE WHEN PAYMENTDATE BETWEEN MAR 1 AND MAR 15 THEN CreditAmt ELSE 0 END) AS Credit_H1,

[code]...

My doubt is, in the above query, to aggregate two different columns based on same condition, i am checking the same condition twice...

View 2 Replies View Related

SQL & PL/SQL :: Compare 2 Tables Based On 2 Columns

Oct 19, 2010

My tables looks like this:

Desc Table A (account)
Account1_id
Account2_id
name,
empid

Table B (Bill )
BillNo
Advertiserid
agencyid
total vvalue

I need to pick up total value from table B where the unique combination of advertiser-Agency id is the same as the given account1_id -Account2_id combination in table A for each employee id.

In other words my output should be like

Empid | Account_id (should be same as advertiserid)| Account2_id (same as agencyid) | sum(total_value) for this adv-agency combination.....

objective: Get the total value from table B for each unique account1-account2 combination (advertiser-agency in other words) .

I am not sure if I should use a correlated subquery or how to handle the situation....Right now I am just checking the two columns separately like this:

select.......from a,b
where b.advertiser_id = a.account1_id and b.agencyid = b.account2id

Is it correct to do so? I have a feeling that I am missing something if I join them separately like this.

View 3 Replies View Related

SQL & PL/SQL :: Return Results Based On 2 Columns?

Jul 15, 2013

I've been having an issue and cannot figure it out for the life. First, here's an example set of the data I'm using so you can see exactly what I'm asking.

Emplid Effdt Effseq

10001 '01-JAN-99' 0

10001 '01-JUL-11' 0

10001 '01-JUL-11' 1

10001 '01-JUL-11' 2

10001 '01-JUL-12' 3

What I need to do here, is obtain 3 rows. The 3 rows I need are rows 1, 4, and 5. I need row 1 because its a completely different date. I need row 5 for the same reason: it's a different date. The issue arises with how I can obtain row 4. The problem is that because rows 2, 3, and 4 all have the same effective date(effdt), SQL Developer just returns one of those rows. Because those 3 rows all have the same effective date(effdt), the tie breaker becomes the effective sequence(effseq) number. When the effective date(effdt) is the same, you need to grab the maximum effective sequence(effseq) number and return that whole row's results such as the emplid, effdt, and effseq. It seems so straight forward and something you can use a subquery for, but its not that simple. Note, that you can specifically use the emplid = 10001 in any specific form because there's many employee id's. Also, the rows will not be in a specific order so you cannot just always grab rows 1, 4, and 5. Some employees may only have a single row in the database, and some may have 50 rows. Everything solely depends on the combination of employee id(emplid), effective date(effdt), and effective sequence(effseq) as the tie breaker.

View 7 Replies View Related

SQL & PL/SQL :: Compute Based On Columns As Variables

Jul 1, 2012

My issue is with a calculation using columns as variables. below is the code to create and attached is the code (INSERT_data.txt) to fill-in the required tables....

what's required here?

I need to update (through update or simply create new table statement)

BPF(i), BPC(i), BPY(i) of table root_tbl (A) based on InF_IDX, InC_IDX & InY_IDX and OutF_IDX, OutC_IDX & OutY_IDX using BPF(i), BPC(i), BPY(i) from table pvt_vectors_tbl(B).

Join Clause: A.P_NODE=B..P_NODE and
A.C_NODE=B.C_NODE and
A.P_NODE_Date=B.P_NODE_Date and
A.C_NODE_Date=B.C_NODE_Date

the formula are:

BPF(i):
if A.InF_IDX<=A.OutF_IDX then
FOR i=1 to A.OutF_IDX+1
if i<InF_IDX then A.BPF(i)= A.BPF(i)+ B.BPF(OutF_IDX + i - InF_IDX); else A.BPF(i)=0;
i=i+1;
else
FOR i=1 to A.InF_IDX+1
if i<OutF_IDX then A.BPF(i)= A.BPF(i)+ B.BPF(InF_IDX + i - OutF_IDX); else A.BPF(i)=0;
i=i+1;
idem for BPc and BPY.

in real word:

* root_tbl table has ~2 million records, 50 BPF(i) columns, 50 BPC(i) columns and ~475 BPY(i) columns
* pvt_vectors_tbl has ~50.000 records

/*create and fill-in pvt_vectors_tbl table*/

CREATE TABLE root_tbl
(
P_NODE VARCHAR2(3),
C_NODE VARCHAR2(3),
P_NODE_Date date,
C_NODE_Date date,
InF_IDX number,
InC_IDX number,
InY_IDX number,
[code].........

View 2 Replies View Related

PL/SQL :: Delete Dupes Based On Multiple Columns

Dec 28, 2012

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

I want to delete dups from a table based on 3 columns

with sample_table as (
select '101' as ID1, '201' as ID2, '4' as weight  from dual union all
select '101' as ID1, '201' as ID2, '5' as weight  from dual union all
select '105' as ID1, '205' as ID2, '6' as weight  from dual union all
select '105' as ID1, '205' as ID2, '6' as weight  from dual union all
[code].........                     

Desired Output

with sample_table as (
select '101' as ID1, '201' as ID2, '5' as weight  from dual union all
select '105' as ID1, '205' as ID2, '6' as weight  from dual union all
select '110' as ID1, '215' as ID2, '9' as weight  from dual
)
select * from sample_table

View 5 Replies View Related

Reports & Discoverer :: How To Skip Columns With Space Based On Parameters

Jan 10, 2011

I have Two Parameters like 'YES' & 'NO'

when parameter is YES, column having values
when Parameter is NO, column having no values

In this scenario, how can i skip the column with space when parameter is NO because the column exist in middle of columns in report and Present with space when parameter is YES.

View 9 Replies View Related

Application Express :: Disable Report Columns Based On Item Value

Jul 25, 2013

I've got a report with two lov's, where the user is able to change the lov value and submit it. After submit the status of the item P100_status will be changed in Disable. Based on this value the lov's must be disables with apex_disabled.  How can I disable these columns based on the value of P100_status?

View 11 Replies View Related

SQL & PL/SQL :: Dynamically Concatenating Column Values Based Upon Number Of Grouped Columns

Feb 17, 2011

My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.

SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......

What will be the case when I need to concatenate for more number of values.

like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.

View 10 Replies View Related

SQL & PL/SQL :: Row Level To Column Level Data Transposition?

May 14, 2010

I have a data like,

1) manual_temp_master

auto_idbatch_id sec_idsec_id_type crrncy_cdcreate_Dt price_dt
------------------------------------------------------------------
11234ABC1CUSIPUSD14/05/201014/05/2010
23456XYZ1SEDOLGBP13/05/201013/05/2010

2)manual_temp_detl

auto_idbatch_id Price_bkt_cdscreate_Dtprice_date
---------------------------------------------------------
11234PS114/05/201014/05/2010
11234PS214/05/201014/05/2010
11234PS314/05/201014/05/2010
11234PS414/05/201014/05/2010

[code]....

I want to write a sql query which will fetch the data from manual_temp_master and manual_temp_detl.But from manual_temp_detl table, Price_bkt_cds columns should be displayed as columns. Like the should look like as below:

sec_idsec_id_type crrncy_cd COL_PS1 COL_PS2 COL_PS3 COL_PS4 COL_PS5COL_PS6price_date
--------------------------------------------------------------------------------------
ABC1CUSIPUSDPS1PS2PS3PS4PS5PS614/05/2010
XYZ1SEDOLGBPPS1PS2PS3PS4PS5PS613/05/2010

View 8 Replies View Related

SQL & PL/SQL :: DBMS_STATS For Table Level Vs Partition Level

Nov 17, 2010

what is the difference on DBMS_STATS for table level and partition level , which will provide the best optimizer . If the table xxxx is partitioned from 1 to 10 ,then running gather stats on table xxxx as whole table level or partition level which will provide best result on the performance.

View 1 Replies View Related

PL/SQL :: To Create Function Based Index For Group Function Columns

Jun 15, 2012

Is anyway to create function based index for group function columns.

For example

select max(timestamp),min(age),averge(sal).... ... .. from tab;

View 5 Replies View Related

Backup & Recovery :: Incremental Backup Level 1 Or Level 0 Without Archivelogs?

Oct 22, 2012

Can you take an incremental backup level 1 or level 0 without archivelogs?

syntax would bebackup as compressed backupset cummulative level 1 database.

The reason I ask is because when I run backup as compressed backupset cummulative level 1 database plus archivelogs # it runs fine, but when I run backup as compressed backupset cummulative level 1 database it just hangs.

View 20 Replies View Related

SQL & PL/SQL :: Row Level And Statement Level

Apr 11, 2013

between statement level or row level trigger, which trigger will execute first.We have BEFORE_UPDATE_ROWLEVEL_TRIGGER and BEFORE_ UPDATE_ STATEMENT LEVEL_TRIGGER triggers on table product.

which will execute first on update DML event ?

View 2 Replies View Related

Perform SELECT INTO In SQLPlus Script

Apr 11, 2007

I'm trying to perform a SELECT INTO in a SQL*Plus script.the script i'm working towards to, looks something like this;

set term on
set echo off
set serveroutput off
set verify off
ACCEPT ReportName PROMPT 'Please enter a report name; '
PROMPT
PROMPT retrieving current settings for &ReportName:
select STARTDATE, ENDDATE, SUFFIX
into pStart, pEnd, pSuffix
from MyTable
where NAME = '&ReportName';
[code]....

I'm basically trying to perform a select into based in user input and then put the retrieved data back to the console with a prompt. Is this possible within a sql script running on SQL*Plus?

View 3 Replies View Related

SQL & PL/SQL :: Perform Math Calculation Upon Dates?

Feb 9, 2011

I got 4 fields:

event_date (01/01/1900 hh:mm:ss)
team_count (number)
interview_count (number)
duration (01/01/1900 hh:mm:ss) - duration of each interview

The calculation is:

(interview_count * team_count * duration(hh:mm)) + event_date
i.e.:
(3 * 2 * 02:30) + 01/01/1900 08:00:00
= 15:00:00 + 01/01/1900 08:00:00
= 01/01/1900 23:00:00.

all I care about is the hh:mm in the result.How can I preform this calculation in pl/sql?

View 7 Replies View Related

PL/SQL :: Perform DML Or DDL When Select Query Is In Progress?

Aug 27, 2012

Is it possible to perform a DML or DDL when a select query is in progress?

View 8 Replies View Related

To Perform A Reorganization Of The Partition Table

May 6, 2010

I would like to perform a reorganization of the partition table. The table contains 144 partitions and 2038 indexes partitions with that. I would like to ask you to make me understand the best possible way to perform the reorg with cascade options in one attempt.

Also, am not sure how to get the information of the index type is Globally / Local index partition. Is there any document for reference that details about the reorg of partition tables?

View 7 Replies View Related

SQL & PL/SQL :: ORA-14551 - Cannot Perform DML Operation Inside Query

Dec 9, 2010

Attempted to execute the Procs below with

Select OTMP_TCIS_RS.Get_UserInfo('EN') from dual; but i get the following error:
ORA-14551: cannot perform a DML operation inside a query.

The intention of the code is to perform an insert into my table based on passing in values via an object into Stored Procedure Apply_Users_Update

Package Definition
create or replace
PACKAGE OTMP_TCIS_RS AS
--1 PROCEDURE Get_UserInfo
PROCEDURE Get_UserInfo(
o_OutCode OUT INT,
i_language IN VARCHAR2);
FUNCTION Get_UserInfo(
i_language IN VARCHAR2)
RETURN NUMBER;
[code]....

View 5 Replies View Related

Server Administration :: How To Perform Database Refresh

Mar 3, 2012

How to perform db refresh.

View 1 Replies View Related

Forms :: WEBUTIL_C_API Perform Functions Of A DLL Coupon

Aug 16, 2011

I'm using WEBUTIL_C_API, to perform functions of a DLL coupon issuer and I try to tax problems.

problems:
1) performs the function and the application closes. The code that does this is below:
FUNCTION FUN_REDUCAO_Z (impressora varchar2) RETURN pls_integer IS
DLL_FUNCAO webutil_c_api.FunctionHandle;
lv_plist webutil_c_api.ParameterList;
param1 webutil_c_api.ParameterHandle;
ret PLS_INTEGER:=0;
BEGIN
[code]....

I am running these functions in the web client.

View 4 Replies View Related

Forms :: How To Perform Search Detail Block

Dec 26, 2011

I have detail block in my form and i need to give option to user to search item in that block.

For example I display 100 items from item master and user need to search particular item by either code or name.

View 6 Replies View Related

PL/SQL :: How To Perform A Pattern Check - Regular Expression

Oct 16, 2012

Is there a way to perform a pattern check on a value ?

For example: 654321HD9

The pattern is 6 numbers, followed by 2 letters, followed by 1 number. The data type for the attribute is a string.

Examples of right or wrong
654321HD9 - correct value
654321HD - wrong value
654321111 - wrong value
HD1111111 - wong value

The pattern has to be as i mentioned above (6 number, 2 letters, 1 number) otherwise its wrong. My pattern does not cover all cases.

select REGEXP_SUBSTR('654321HD9', '[0-9]+[A-Za-z]+[0-9]+') from dual;

View 6 Replies View Related

Perform Regular Updates On Several Oracle Tables

Sep 26, 2012

I am attempting to perform regular updates on several Oracle tables. The scripts performing the updates are scheduled to run every two minutes, get a value and update the table with that value.

The value doesn't always change but the scripts will still attempt to perform an update.

The same script is part of 7 objects, all of them are scheduled to run at the same time. They update the same table but never the same row.Even though the script is mostly the same on the 7 objects, they run completely independently of each other. The first object will usually perform the update without any problems but when it comes to the second object the script will time out.

View 9 Replies View Related

Data Guard :: Unable To Perform A Switchover

Nov 2, 2013

I have a dataguard configuration (physical standby).Database A is the primary database and database B is the standby database (I do not use the broker).Both are Oracle RDBMS 12cR1 EE. The Apply process uses the current log (standby redo logs).On database B, when querying v$managed_ standby;, I get:           

ClientProcess    process    Status  ---------- ARCH       ARCH       CONNECTED          ARCH       ARCH       CONNECTED          ARCH       ARCH       CONNECTED          ARCH       ARCH       CONNECTED          RFS        ARCH       IDLE               MRP0       N/A        APPLYING_LOG       RFS        LGWR       IDLE                

It works fine.   Before to perform a switchover,- from database A (current physical database), I get switchover_status = 'TO STANDBY'- from database B (current standby database). I get switchover_status = 'NOT ALLOWED'. What switchover_status = 'NOT ALLOWED' means exactly and how to get further with the switchover? 

View 2 Replies View Related

Perform Operation With Case Function - Getting Error?

Apr 3, 2012

I want to perform some operation with case statement. But I am confusing with ora 00932 error. My question is what data type should I use while performing case function?

SQL> select * from samp;

NAME EMPID SALARY DEPT
---
sony 10680 8200 sap
bala 10708 4300 .net
sam 10600 9000 oracle
chris 10655 5500 java
rose 10487 8700 oracle

[code]....

My big question is

different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields.

// just I am trying to perform basic operation. why oracle didn't support?

View 1 Replies View Related

RMAN - Perform TSPITR On Newly Created Tablespace?

Mar 7, 2011

I am attempting to perform a TSPITR on a newly created tablespace. The error indicates that the tablespace is not in the recovery catalog in which case it is. The database is Oracle 11g on Win2k8 64bit enterprise edition.

List of Datafiles in backup set 52
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 3959509 06-MAR-11 +DATA/orcl/datafile/system.257.742678049
2 Full 3959509 06-MAR-11 +DATA/orcl/datafile/sysaux.258.742678049

[code]...

View 4 Replies View Related







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