SQL & PL/SQL :: Cumulative Total - Passing Parameter Value

Mar 27, 2012

i have a table empl which have three column (name,period,attendance)if we pass parameter which value is based on period column

like :January

then out should come sum of all attendence of january group by name like that

name attendance
a60.00
b20.00
c20.00
w40.00

if we pass parameter value :February then attendance should come sum of (January and February) and if we pass parameter March then attendance should come sum of(January,February and march)

create table empl (name varchar2(10),period varchar2(10),attendance number)
insert into empl values('a','January',20)
insert into empl values('a','January',10)
insert into empl values('a','January',30)
insert into empl values('a','February',20)
insert into empl values('a','March',60)
insert into empl values('b','January',20)
insert into empl values('c','January',20)
insert into empl values('w','January',40)

View 22 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Find Cumulative Total

Jul 3, 2010

how to find cumulative total means i need total amount between april to sysdate.

View 1 Replies View Related

Passing Parameter To In Statement?

Oct 9, 2008

I have a scenario where I have to pass a paramter to "in statement". When I run the query from SQL plus its working fine.......but when I run the query at run time it doesnot come back with the results. I am sure its formating issue.

This is how I create a paramter

_sbInStatement.Append("(");
foreach (ListItem item in _listBox.Items)
{

[Code]....

oCmd.Parameters.Add(":InStatement", OracleDbType.Varchar2).Value = InStatement;

View 2 Replies View Related

SQL & PL/SQL :: Passing Required Parameter

Jun 14, 2011

I have a procedure with 20 parameters, acutely it is to update a table and each param represents respective columns in a table. I want to update only few selected columns(random), as of now am passing Null as param values for remaining.

Is there any way to ignore the unnecessary parameters instead of passing NULL value.My Proc call looks like...

Exec MyProce(IN_ID, NULL, NULL, NULL,NULL,NULL,'SOME_VALUE', NULL,NULL,NULL,NULL,NULL,NULL,...);

Or

Exec MyProce(IN_ID, NULL, NULL, NULL,'SOME_VALUE' ,NULL,'SOME_VALUE', NULL,NULL,NULL,NULL,NULL,NULL,...);

View 4 Replies View Related

SQL & PL/SQL :: Output After Passing Parameter

Mar 23, 2012

we have a table which name is empl have two column (comapnyname,jobdate) i want output after passing parameter which name is month which value is like(feb 2011,or mar 2011 or apr 2011 or jun 2011 etc) output should come the nameof company with jobdate whose jobdate is equal to 3 or greate than three

name of company,jobdate whose job date in particular month is greater than three or equal to three the purpose of this we want to find only the name of company where we visit morethan three times in particular month table structure is

create table empl(companyname varchar2(10),jobdate date)

insert into empl values('ABC','1-feb-2011')
insert into empl values('ABC','10-feb-2011')
insert into empl values('ABC','21-feb-2011')
insert into empl values('xyz','18-feb-2011')
insert into empl values('xyz','1-feb-2012')
insert into empl values('xyz','1-apr-2011')
insert into empl values('ABC','28-feb-2012')

output:

after passing parameter month=feb 2011

companyname jobdate
abc 1-feb-2011
abc 10-feb-2011
abc 21-feb-2011

View 3 Replies View Related

SQL & PL/SQL :: Passing Multiple Value In Parameter

Oct 3, 2011

How can I pass multiple value in one parameter

example

select * from table

where table_cd in ('01','02','03','04')

here i want to put multiple value like above query by select 1 value in list

like when user select 'A'
THE VALUE PASS IN WHERE CLAUSE ('01','02','03')
FOR 'B' ('03','045','07')
FOR 'C'('044','046','078')

View 6 Replies View Related

SQL & PL/SQL :: Parameter Passing In Oracle

Dec 13, 2011

i have a SQL query . In the where clause of the query , there is function called dimension_intersect which takes 2 parameters.Now , when the 2 dimensions passed intersect , the function returns "Y" and the query works as expected

Function in where clause is as below

Dimension_intersect(Dimension1,select dimension2 from product where product_sys_id=1)='Y'

The above function works fine till only 1 record is returned by the inner subquery used in above function. But when "select dimension2 from product where product_sys_id=1" return 2 dimensions then the function fails as it can accept only one dimension at a time . I am not allowed to edit this function. I need to find a way to pass both the dimensions one at a time.

Query
-----
SELECT DISTINCT PROD_LONG_NAME,
P.PROD_ID_USER,
MRS.RESTRICTION_SEVERITY,
MRT.RESTRICTION_TYPE,
RESTRICTION_COMMENT RESTRICTION_DETAIL,
[code].......

View 15 Replies View Related

SQL & PL/SQL :: Passing Parameter In Procedure

Jan 4, 2011

I have got this procedure:

CREATE OR REPLACE PROCEDURE GET_NUM_ROWS(TABLE_NAME VARCHAR2) AS
NUM_ROWS NUMBER;
BEGIN
SELECT COUNT(*) INTO NUM_ROWS FROM TABLE_NAME;
DBMS_OUTPUT.PUT_LINE(NUM_ROWS);
END;

When I try to compile it, the compiler says:

ERROR at line 4: PL/SQL: ORA-00942: table or view does not exist.

View 3 Replies View Related

SQL & PL/SQL :: Run Procedure Without Passing Value For IN Parameter

Mar 10, 2012

I have a pl/sql procedure having IN, OUT and IN-OUT parameters, this procedure in called from front end application. Now I need create a script to run this procedure from back end (sql prompt) and the result must be same as the front end application call to this procedure.

For the procedure I don't want to pass IN parameter instead to pick the value from the package where the derivation is defined. how to run this procedure from sql prompt without passing value for IN parameter.

View 6 Replies View Related

SQL & PL/SQL :: Passing Parameter To Cursor?

Apr 13, 2004

I have a table Student with two columns Rno and Name and i write following PL-Sql, it is working fine, my question is that how can i pass the parameter to cursor in the following query, e.g. if i pass the roll no. 501 then it should display only the particular Name.

declare
��� cursor st_name is
����������� select rno,name from student;
����������� studentnm st_name%ROWTYPE;
begin
�� open st_name;

[code]....

View 6 Replies View Related

SQL & PL/SQL :: Passing IN Parameter Of Datatype Varchar2

Feb 16, 2010

I have a stored proc which takes IN parameter of datatype varchar2.When I am trying to run the proc it is throwing error that "input buffer too small".The datatype assigned to IN parameter is of varchar2(200) but actually the length of the parameter passed is around 500 characters.the way to increase the length of Input parameter to 500 characters??

View 16 Replies View Related

SQL & PL/SQL :: Passing Character As Parameter To Search

Apr 30, 2010

i AM PASSING CHARATER AS PARAMETER TO SEARCH

create or replace procedure procedure_name(p_like VARCHAR2)
is
cursor cursor_name
is select last_name from employees
where UPPER(last_name) like '%p_like%';
emp_rec cursor_name%rowtype;
begin
open cursor_name;
[code]....

WHAT SHOULD NEED TO MODIFY::::::

View 1 Replies View Related

ODP.NET :: Passing Value As A Parameter In Select Statement

Jun 22, 2012

how do I pass the values that i get in the cursor to a select statement. If table1 values are 1,2,3,4 etc , each time the cursor goes through , I will get one value in the variable - OfferSo I want to pass that value to the select statement...the one below does not work.

drop table L1;
create table L1
(col1 varchar(300) null) ;
insert into L1 (col1)
select filter_name from table1 ;
[code]....

View 4 Replies View Related

SQL & PL/SQL :: Passing Input Parameter To Like Variable In Procedure

Dec 27, 2012

I have a procedure named 'GetShipperinfo' which takes i_name as input and needs to build a cursor taking i_name as input

i.e.

The following sql when executed at sqlplus prompt gives correct results.

select dept, supplier, shipper_id
from shippers
where upper(shipper_name) like upper('Frank Robert%');

How can I transform this inside a cursor within a procedure passing 'Frak Robert' value as i_name input.

i.e I should be able to call the procedure as follows

sql> variable v1 varchar2;
sql> exec pkg_shipment.GetShipperinfo('Frank Robert',:v1);
sql> print :v1;

Should the cursor inside the procedure be built as follows

cursor c1 is
select dept, supplier, shipper_id
from shippers
where shipper_name like ''||upper(i_name'%''||)'';

Iam unable to build the sql for the cursor.

View 3 Replies View Related

Forms :: Passing Values Without Using Parameter Or Global

Jan 24, 2012

How to pass a values from one to another, but without using parameters or globals. I've used parameters and every time i have to list the values of the form that values have been passed to, it ask do you want to save... even if I didn't passed any values. It's because my code in where-new-form instance, where i give values to a parameters.

View 17 Replies View Related

Forms :: Passing Parameter With Form Link

Jan 5, 2011

can we pass parameter in a link. i.e when we open a new form clicking on the link, some values to be passed to the form so that new form get populated with that value.

View 2 Replies View Related

SQL & PL/SQL :: Passing Ref Cursor To PROEDURE As INOUT Parameter

Dec 8, 2010

I need to know how to pass the ref cursor as INOUT parameter to a procedure. I have the following procedure and I need to execute it.

PROCEDURE get_site_setup_detail (
p_study_id IN SITES.study_id%TYPE,
p_proj_act_date IN VARCHAR2,

[code]....

View 2 Replies View Related

Reports & Discoverer :: Parameter Passing Through Report

Feb 2, 2012

I had created drill down reports. rep_1 dispalying various Location_cd. by clicking location_cd The parameter form of rep_2 is displayed in which location_cd is displayed which is already selected and accept dept_cd from user. While running the rep_2 the parameter location_cd is passing null value.

View 1 Replies View Related

SQL & PL/SQL :: Passing CLOB Data As Input Parameter?

May 24, 2010

I have a requirement where in I have to store large data in one of the database columns using stored procedure.

I have declared the column as CLOB as it can store upto 4GB and also the input parameter for the procedure as CLOB. But when I am trying to pass large data it is not allowing to store as it is throwing literal string too large error.

Is there any restriction in the data size to be passed to the stored procedure?

View 9 Replies View Related

SQL & PL/SQL :: Function Invoked By Passing Date Parameter

Oct 18, 2011

get rid of the below error

CREATE OR REPLACE FUNCTION fn (
p_salesrep_id IN jtf_rs_salesreps.salesrep_id%TYPE,
p_org_id IN jtf_rs_salesreps.org_id%TYPE,
p_cnf_date IN emcint_ord_headers_all.creation_date%TYPE

[Code]....

Invoking Functions

select fn(-3,293,'1/1/1952'), resource_id from jtf_rs_salesreps
where rownum < 5
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

View 8 Replies View Related

Stored Procedure For Searching Data From A Table By Passing Tablename As A Parameter

Feb 6, 2012

This procedure is not working properly.

create or replace procedure bank_search_sp
(
p_tablename in varchar2,
p_searchname in varchar2,
p_bankcode out varchar2,
p_bankname out varchar2,
p_dist_code out number
)
as
v_tem varchar2(5000);
begin
v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || '
where bankname like '''|| p_searchname||'';
execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ;
commit;
end bank_search_sp;

the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown..ORA-01756: quoted string not properly terminated

ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14
ORA-06512: at line 1

View 1 Replies View Related

Reports & Discoverer :: Error Message When Passing The Report Parameter From Command Line?

Jan 31, 2012

I prepared a report in Oracle reports and trying to run the report through command line by creating a batch file. but i am getting the following error message when i run the batch file

REP-0069: Internal error
REP-57054: In-process job terminated:Executed successfully but there were some errors when distribute the output
REP-50159: Executed successfully but there were some errors when distribute the output

the batch file is as follows

D:DevSuiteHome_1BINRWRUN p:spannawazeitspanna_wa_zeit.Rep userid=alrayatec/alrayatecprod@alraya.world
DESFORMAT=HTMLCSS DESTYPE='mail' DESNAME='sarfraz_it@hnd.com'

View 1 Replies View Related

Application Express :: Dynamic Reports - Runtime Parameter Passing To Report Query

Jul 27, 2013

I have developed a report using apex shared components (report query and report layout) along with BI Publisher for report printing in pdf format. the Report template is built using Template Builder for Word. I have configured my Apex4.1 env with BI Publisher 11. This report is working fine for hardecode ID ( like REQUEST_ID=10 ).  to make report dynamic i have made following additions in the report 

I have added where clause in report query as WHERE REQUEST_ID = :REQUEST_ID (:REQUEST_ID is the text field in my page).

Added a text field names :REQUEST_ID to get user input at run time.

Create a button and add Repot Query URL in its properties. When i run the report with some valid value in :REQUEST_ID text field  i did not get expected result infect there is no record getting displayed in the PDF file. i think this is not the write way to do this but interestingly im not getting any error. how can i get this functionality in apex this way or the other ?

View 0 Replies View Related

SQL & PL/SQL :: Cumulative Some Last 5 Rows?

Jun 25, 2013

I need to get the sum of next 5 rows that are order by one specific column .

Ex :

DROP TABLE TEST_5;

CREATE TABLE TEST_5 AS
SELECT TO_NUMBER(200 || ROWNUM) YEAR, ROWNUM TOT
FROM ALL_OBJECTS
WHERE ROWNUM < = 15 ;

SQL> select * from test_5 order by year;

YEAR TOT
---------- ----------
2001 1
2002 2
2003 3
2004 4
2005 5
2006 6
2007 7
2008 8
2009 9
20010 10
20011 11
20012 12
20013 13
20014 14
20015 15

15 rows selected

SQL>

Now Expected Results :

SQL> select * from test_5;

YEAR TOT Exp_res
---------- -----------------------
2001 1 1+2+3+4+5 = 15
2002 2 2+3+4+5+6 = 20
2003 3 3+4+5+6+7 = 25
2004 4
2005 5
2006 6
2007 7
2008 8
2009 9
20010 10 10+11+12+13+14
20011 11 11+12+13+14+15
20012 12 12+13+14+15
20013 13 13+14+15
20014 14 14+15
20015 15 15

15 rows selected

SQL>

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 :: Cumulative Calculation In Oracle?

Feb 24, 2011

Is it possible to calculate cumulatively in oracle sql queries, i.e. using the results of the last row for calculating the values in the current row?For example I want my query to return the following:

Month col1 _ col2 _ col3
jan 1 _ 100 _ 100 * 1
feb 2 _ 200 _ 100 * 2 + 200
mar 3 _ 300 _ 400 * 3 + 300
apr 4 _ 400 _ 1500 * 4 + 400
may 5 _ 500 _ 6400 * 5 + 500

In col3 above, for feb I want to use the result returned for jan ((100 * 1)*2+200), for mar I am using the result returned for feb((100 * 2 + 200) * 3 + 300) and so on.

i.e. I want to use the previous value of column3 to derive the current value of column3. Like using the LAG function but on the analytically derived column itself.

View 16 Replies View Related

SQL & PL/SQL :: Last 3 Receipts Based On Cumulative Balance?

Mar 19, 2011

I have one table where the item stock balance can be taken.What i want is like a ageing report based on the ending balance.If the ending balance is 10,000 for item a.It should bring all the Receipts which have qty lesser than 10,000.

I managed to write one function like below to get the receipts numbers but the format is 3778578-3778612-3790428 and i want to convert them into rows like

3778578
3778612
3790428

The function used is as below.

CREATE OR REPLACE FUNCTION l_get_rcpt (
l_item IN VARCHAR2,
l_date DATE,
l_bal IN NUMBER
)

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Cumulative Running Balance By Condition?

Mar 24, 2013

Is there a way to find running balance for accounts, suppose i have two tables.one for transaction and one summary of balance.

i want to have the closing balance based on transactions subtracted or added based on credit and debit for example.it will add to the running total if its debit and subtract if its credit and final output is the balance.

create table acnt_trans ( acnt_code varchar2(12),trans_date date,debit_amt number,credit_amt number);
insert into acnt_trans values ('1001','02-FEB-2013',200,0);
insert into acnt_trans values ('1001','03-FEB-2013',0,100);
insert into acnt_trans values ('1001','04-FEB-2013',50,0);

[code]....

--I want the output as below.if i run the report as of '04-feb-2013'

trans_date , acnt_code , debit_amt,credit_amt , balance
02-feb-2013 1001 200 0 3200
03-feb-2013 1001 0 100 3100
04-feb-2013 1001 50 0 3150 -- this is the closing balance.

View 4 Replies View Related

PL/SQL :: Total And Grand Total

Mar 26, 2013

I'm running a query like the below but now i would like to make the last line actually say Grand Total. Instead of just total.

SELECT   decode (grouping (farinva_invh_Code),0,null,'Total') farinva_invh_Code,
         --decode ( grouping (amt),0,null,'GrantTotal')Grant_Total,
         farinva_invh_Code,
         spriden_id,
         --spriden_last_name "last Name"

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Running Total SUM (Amt) Over?

Apr 21, 2012

My example: I'm given an Allowance throughout the week. It happens to be 10 dollars but it can vary from day to day.I can create a running total with SUM(Amt) Over etc...This is the CUMUL column in the example below.

On certain days I've spent different percentages of the allowance. (The SPENT Column which is a field in the database)I can't manage to create the AMTLEFT column in the example below.The AmtLeft column seems to be a kind of running total that 'refers to itself' so this is where I'm stumped.

Week,Day,Amt,Cumul,Spent,AmtLeft
1,Mon,10,10,0%,10
1,Tue,10,20,50%,10
1,Wed,10,30,0%,20
1,Thu,10,40,0%,30

[code]...

My imaginary SQL would look something like at this point (if I have it right):

SELECT Week, Day, Amt, Sum(Amt) Over (Partition By Week, Order By Day) AS Cumul, Spent FROM AllowancesTable

How to get the last column AmtLeft?

View 14 Replies View Related







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