SQL & PL/SQL :: Way When Doing Rank Function In PL/SQL To Pass Field
Jul 13, 2011
there is a way when doing the Rank Function in PL/SQL to pass the field that will be ranked as an override.
SELECT rank(p_ColumnAmt) within group (order by p_ColumnNm desc) rank
INTO v_RnkNoAmt
FROM Table_name
WHERE ??????;
p_Column is the amount I am ranking
p_ColumnNm is the actual field name to Rank.
When I pass the field name with an override I do not get the correct rank back. If I run the Select with the actual field name curr_1_mth_amt, I get the correct rank.
I have about 70 different field to Rank and do not want to make a procedure for each field.
View 7 Replies
ADVERTISEMENT
Mar 24, 2013
I created a sample table named as "Student" with following data. table contains two columns only - stdid & marks.
stdid marks
10 75
20 60
30 60
40 45
50 30
I have to find the rank of students based on their marks in descending order.Is there a way to get rank without using RANK function?
View 11 Replies
View Related
Aug 30, 2012
I have the following table :
CREATE TABLE A_TEST (A INTEGER, B INTEGER, C INTEGER, D INTEGER, FLAG CHAR(11));
INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
[code].......
I would like to perform an update on the FLAG column by setting to "D" if it is a duplicate record.1,2,3,4);
I would like to use the rank function.
Desired update:
A B C D FLAG
1 2 3 4
2 4 5 8
1 2 3 4 D
2 4 5 8 D
7 2 3 4
9 2 3 4
7 2 3 4 D
1 2 3 4 D
5 4 5 8
2 2 3 9
2 4 5 8
6 2 3 4
1 3 3 4
8 2 8 4
View 5 Replies
View Related
Mar 17, 2013
can we create rank on a particular column without using rownum and rank function.
View 9 Replies
View Related
May 1, 2013
Here table - tac has
row1
-----
X
X
X
B
[code]...
I want ouptut like using sql query with out using dense_rank function,
row1 row2
X 1
X 1
X 1
B 2
[code]...
View 7 Replies
View Related
Oct 24, 2010
I wrote many functions before, but NEVER a function to pass through authentication...Here are some of the specs ...In this example, an institution will be accepting tuition and fee payments against bills that are stored in the our system. In addition to the minimum bill authentication parameters, the institution would like to pass us a full name to display when welcoming the user, control the users primary e-mail address, and restrict the payment method on a per user basis.
URL url This will be issued to you by your project manager. It should be of the form URL....
User Id user_id The unique identifier for the user within the institution. This is typically a student ID. (in our database)
Full Name full_name Users full name. Example "John Smith". (in our database) Email Address email Primary email address. in (our database) Payment Method paymentMethod Payment methods allowed for this user.
Allowed values:
"none" :no payments accepted
"ach" :ach is the only accepted
"cc" :credit cards only accepted
"ach_cc" :credit card and ach 16
Key key The shared key that is issued to you by your project manager.Time Zone tz The time zone you are located in. Valid values are:
"E" :Eastern time zone
"C" :Central time zone
"M" :Mountain time zone
"P" :Pacific time zone 1
[code]....
I know I need to have cursor to check on the user_id, full name, email (those values are store in our DATABASE. The URL and the key are giving to me. Do I have to hard code the url in the cursor and stored in a variable so I can check that the url coming in (url in varchar2) is equal to the url in my cursor? The same with the key?
View 2 Replies
View Related
Jun 17, 2013
i've following code
declare
i INTEGER:=0;
flag boolean:= FALSE;
vcInsert:='INSERT INTO CRM_DELT_IMPORT (';
if length(i_vcColumn1) > 0 then
flag:=TRUE;
[code]....
i am fetching data from excel all data is of general type, here i am passing value is VTDATE varchar2(100) but actual database column filed is DATE , How can i pass '13-06-13 05:54:33' to database table.
if i put this condition
vcInsert:=vcInsert|| ','||''''||i_ldata(i).to_char(VTDATE,'DD-MM-RR HH12:MI:SS')
i get the error
to_char must be declare.
if i remove this line
i am getting this error ORA-01843: not a valid month
View 8 Replies
View Related
Nov 8, 2012
I have written as function :
create FUNCTION Cov(p_str1_x IN VARCHAR2,p_str2_x IN VARCHAR2, p_str1_y IN VARCHAR2,p_str2_y IN VARCHAR2) RETURN NUMBER is
TYPE ty_varchar_assoc IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
v_x ty_varchar_assoc;
v_y ty_varchar_assoc;
v_inter ty_varchar_assoc;
[Code]....
Now i need to pass the values to the parameter from a table? How can i do that?
View 2 Replies
View Related
Oct 1, 2011
I have to create a function which count the max id in the provided table.
For example: I have two table customer and book
and
create or replace function Row_Count(tab_nam) return varchar2 is
CONTR_NO varchar2;
begin
select NVL(MAX(t.contract_num), 0) + 1
INTO CONTR_NO
FROM tab_nam t;
return(CONTR_NO);
end Row_Count;
when I call
Function( customer) ;
or
Function( book) ;
It should tell the max number with addition of one.
View 4 Replies
View Related
Dec 23, 2012
My need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":
with data as (
select 1 as t from dual union all
select 2 as t from dual union all
select 3 as t from dual union all
select 4 as t from dual union all
select 5 as t from dual
)
select * from data where t in (2,3)
View 2 Replies
View Related
Jul 20, 2010
I certainly know this is possibly but I am trying to do this on the fly and can't seem to work it out:
I have a table A:
ID Name Priority
-------------------
1 Smith 1
1 SSmith 2
1 ASmith 3
1 BSmith 3
2 John 2
3 Ed 1
and I am looking to create the following table from this:
ID Name Sum(Top3Priority)
--------------------------------
1 Smith,SSmith,etc 8
Now, I've got listagg working and everything appears to be going swimmingly but: for every listagg grouping on name I need to only sum the highest top 3 priorities. So in the example above there are four Smiths but I need to only sum the top 3 priorities which are 3,3,2 and ignore the 1 even though I do want all the listagg Smith's (SSmiht, ASmith, etc) in there.
Now I can sum the priority, but don't really know how to sum only the top 3 in any ID ? There can be 1 to n ID's so if there are only 2 ID's I want to sum those 2, if there are 3 all 3 and 4 upwards only the top 3.Here is a snippet of the SQL I am using
SLECT id,
listagg(MN_CR_LOOKUP.f_name, ',') within group (order by Priority)) roadname,
**** sum top 3 here ?****
count(*) "NumI", Sum("Elevation") "CombinedElevation"
FROM jc,
[code]...
View 3 Replies
View Related
Mar 10, 2013
I am trying to find out the difference in time between peoples memberships and also the order that these memberships are taken out in. So far I have added in a rank statement to work out the order the memberships were created in, but now want to look at the difference between the dates returned. The SQL I used is:
SELECT owner_party_id,
mem_number,
support_id,
mem_start_date,
RANK() OVER (PARTITION BY owner_party_id ORDER BY mem_start_date ASC) MEMBERSHIP_SEQUENCE
FROM membership_all
WHERE version_type = 'CUR'
AND owner_party_id IN ('65051', '65051', '65348', '65348', '65607', '65607', '65607')
to get:
"OWNER_PARTY_ID"|"MEM_NUMBER"|"SUPPORT_ID"|"MEM_START_DATE"|"MEMBERSHIP_SEQUENCE"
65051|318874751|8014747|01-MAR-10|1
65051|412311060|21502883|15-AUG-12|2
65348|308672459|3526913|01-MAY-10|1
65348|409951130|20950524|18-JUN-12|2
65607|315830192|7510133|17-MAY-10|1
65607|406448110|20024246|16-MAR-12|2
65607|409738130|20903556|14-JUN-12|3
Now I would like to calculate the difference between the start dates of each of the owner_party_id groups, so to get something like this:
OWNER_PARTY_ID|MEM_NUMBER |SUPPORT_ID|MEM_START_DATE |MEMBERSHIP_SEQUENCE|Diff
65051|318874751|8014747|01-Mar-10|1|
65051|412311060|21502883|15-Aug-12|2|898
65348|308672459|3526913|01-May-10|1
65348|409951130|20950524|18-Jun-12|2|779
65607|315830192|7510133|17-May-10|1
65607|406448110|20024246|16-Mar-12|2|669
65607|409738130|20903556|14-Jun-12|3|90
I think that I need to use the Lag function in, but I am not too sure if it can be linked to look at the data within a grouping of owner party id, as it would make no sense to calculate the difference in dates for two different owner party ids.
View 4 Replies
View Related
Mar 27, 2013
I need to return which hour for a given date range had the most calls. I have a query that works but it is inelegant and I'm pretty sure I could do better. I'm pretty new to analytic queries so go easy...
select hour,
calls
from
(
select hour,
calls,
rank() over (ORDER BY calls desc) as ranking
from
(
[Code]....
View 1 Replies
View Related
Dec 2, 2010
how do i retrieve only last rank row from table ?
View 7 Replies
View Related
Oct 4, 2013
I've below table.
with tmp_tbl as
(select 'H1' as hh_id, 'C1' as cust_id, 2 as f_rnk, 'F' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100203','YYYYMMDD') first_dt from dual
union
select 'H1' as hh_id, 'C2' as cust_id, 1 as f_rnk, 'M' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100303','YYYYMMDD') first_dt from dual
union
select 'H1' as hh_id, 'C3' as cust_id, cast(null as number) as f_rnk, 'U' as gender, to_date('20130103','YYYYMMDD') as purch_dt,
[code].....
Now i need to rank each cust_id in each hh_id based on below conditions.
1) If atleaset one cust_id in hh_id has f_rnk then gender 'F' with highest f_rnk (more then one F with same f_rnk then the one with oldest first_dt), if no 'F' then gender 'U' with highest f_rnk ((more then one F with same f_rnk then the one with oldest first_dt)), if no 'F' and 'U' then consider 'M' (more then one M with same f_rnk then the one with oldest first_dt).
2) If the above is not met (no cust_id in hh_id has f_rnk populated) then i've to rank based on purch_dt. Gender 'F' with recent purch_dt (if more than one F in household with same purch_dt then the one with oldest first_dt), if no 'F' then gender 'U' with recent purch_dt (if more than one U in household with same purch_dt then one with oldest first_dt), if no 'F' and 'U' then consider 'M' (more than one M in household with same purch_dt then the one with oldest first_dt).
3) If the above criteria is also not met, then rank based on gender_cd. Gender 'F' will have first preference then 'U' and then 'M'.
My output :
HH_ID CUST_ID F_RNK GENDER PURCH_DT FIRST_DT F_RNK_RANK PURCH_RANK GENDER_ONLY_RANK
----- ------- ---------- ------ ----------- ----------- ------------ ------------ -----------------
H1 C1 2 F 1/2/2013 2/3/2010 1
H1 C2 1 M 1/2/2013 3/3/2010 2
H1 C3 U 1/3/2013 4/3/2010 3
H2 C4 F 4/3/2013 10/2/2009 2
H2 C5 M 5/5/2013 8/8/2010 1
H3 C6 F 5/6/2008 1
H3 C6 M 7/8/2010 2
I've tried below query with one condition, but it's giving f_rnk_rank for all records. How can i include multiple conditions in the rank function.
with tmp_tbl as
(select 'H1' as hh_id, 'C1' as cust_id, 2 as f_rnk, 'F' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100203','YYYYMMDD') first_dt from dual
union
select 'H1' as hh_id, 'C2' as cust_id, 1 as f_rnk, 'M' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100303','YYYYMMDD') first_dt from dual
union
select 'H1' as hh_id, 'C3' as cust_id, cast(null as number) as f_rnk, 'U' as gender, to_date('20130103','YYYYMMDD') as purch_dt, s hh_id, 'C5' as cust_id,
[code]....
View 3 Replies
View Related
Dec 2, 2010
the following statement gives each customer owns how many promotions.
Q:) how do i retrieve a customer who has max. promotions?
SELECT C.CUSTOMER_ID,COUNT(P.PROMOTION_ID)
FROM PROMOTIONS P,CUSTOMERS C
WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
=====================================================
CUSTOMER_ID COUNT(P.PROMOTION_ID)
-------------------------------------
001 | 5
002 | 8
003 | 4
004 | 6
005 | 5
006 | 3
View 7 Replies
View Related
Feb 1, 2012
I have a SQL statement that return the following result.
col1
----
a
a
b
b
a
c
c
c
c
What i need is to eliminate the duplication and return the result as the following.
col1
----
a
b
a
c
Using group by or distinct will eliminate the third row "a" and will not return it ordered.My idea is to generate another column that rank the result as following.
col1rank
------------
a1
a1
b2
b2
a3
c4
c4
c4
c4
so using the following SQL query will return the result as needed
select col1, rank
from T
group by col1, rank
order by rank;
col1rank
------------
a1
b2
a3
c4
View 8 Replies
View Related
Mar 26, 2012
I have this code which simply gives me the count of term.
SELECT
OUA_ID
,COALESCE (MAX( DECODE( TERM, 0603, TERM_COUNT, NULL ) ), 0 ) "TERM_0603_CNT"
,COALESCE (MAX( DECODE( TERM, 0702, TERM_COUNT, NULL) ), 0 ) "TERM_0702_CNT"
,COALESCE (MAX( DECODE( TERM, 0705, TERM_COUNT, NULL ) ), 0 ) "TERM_0705_CNT"
,COALESCE (MAX( DECODE( TERM, 0707, TERM_COUNT, NULL ) ), 0 ) "TERM_0707_CNT"
FROM(
SELECT DISTINCT
OUA_ID, TERM_COUNT, TERM
FROM(
SELECT OUA_ID, TERM ,PROVIDER_CDE, COUNT(*) TERM_COUNT
FROM TABLE
WHERE PROVIDER_CDE = 'BILL'
GROUP BY OUA_ID, TERM, PROVIDER_CDE)) GROUP BY OUA_ID
Gives:
OUA_IDTERM_0603_CNTTERM_0702_CNTTERM_0705_CNTTERM_0707_CNT
32710 0 3 7 0
45726 2 0 1 0
Is there a way i could used the rank function (with decode) to only display the 1ST (First) and 2nd(Second) most used term per OUA_ID?
So i get this:
OUA_ID1st MOST USED2nd MOST USED
3271 705 702
45726 603 705
View 4 Replies
View Related
Jan 5, 2012
I am trying to use RANK() clause with a window clause...is that possible to use both together?
like
select col1, col2, col3,
RANK() OVER (ORDER BY col3 desc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RK
from table t
but getting error in SQL Developer
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
Error at Line: 2 Column: 33
The reason why i need to rank in window clause because i have data like this
Name Marks Quiz
Ali 10 1
John 20 1
Sara 30 1
John 40 2
Sara 50 2
Ali 20 2
...
...
and so on
I want to rank them based on their cumulative sum marks after every test..ranking should be in such a way that it should look current row and preceding rows
like this
Name Marks Quiz cumulative_marks rk
Ali 10 1 10 4
John 20 1 20 3
Sara 30 1 30 2
Peter 100 1 100 1
John 40 2 60 3 ==> becuase John has now third most overall cumulative marks (60) after quiz 2.
Sara 50 2 90 2 ==> becuase Sara has now 2nd most overall cumulative marks (90) after quiz 2.
Ali 20 2 30 4 ==> becuase Ali has now fouth most overall cumulative marks (30) after quiz 2.
View 11 Replies
View Related
Oct 22, 2012
I am attempting to user PIVOT and DENSE RANK in a query the following is the query and the record set it returns (condensed and de-identified)
"select * from(
select * from (select dense_rank() over (partition by 1 order by cal.weeksort desc) WEEK_nbr,
u.user_title Manager_Title, replace(hier.manager, '<br>',' - ') Manager,
replace(hier.user_hin, '<br>',' - ') user_name,
to_char(cal.calendar_date_week - 6, 'MM/DD/YYYY') ||' - '|| to_char(cal.calendar_date_week, 'MM/DD/YYYY') Week_of,
upper(substr(cal.day_of_week, 1,3)) DOW, count(distinct Pers_gen_key) cnt from apexim.hrw_member_action act
[code]....
View 2 Replies
View Related
Sep 1, 2010
Am working on a workbook to count the number of enrolments and withdrawals in the program. My data looks like this
name semester status year
A 1 enrol 2010
A 2 withdraw 2010
A 3 enrol 2010
B 1 enrol 2010
B 2 withdraw 2010
I want to count their latest status only. It should come up with
Total Enrol - 2
Total Withdrawn - 1
For total Withdrawn, I tried 'rank' and filter to equals 1 but it does not allow me. Is there any way to have this work?
Here's my calculation:(decode((FIRST_VALUE(status) OVER(PARTITION BY year, name ORDER BY semester DESC)),'withdraw', name)) It tells me that 'Aggregation of Analytic function not allowed'
View 3 Replies
View Related
Apr 23, 2010
Even though i am using COL1 CHAR(500) NULLIF COL1=BLANKS, then also i am getting same error for those columns.
View 13 Replies
View Related
May 3, 2013
I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.
I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.
--tables with insert statement
create table item_trans (trans_item varchar2(12),trans_qty number,trans_act_qty number)
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL);
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);
--i want to set the value of trans_Act_qty as trans_qty
--i create one dummy or test block to keep the select all check box. for that table test script is
CREATE TABLE TEST
(
C VARCHAR2(2000 BYTE),
B NUMBER,
A NUMBER
);
insert into test (C,B,A) values ('A',1,1);
--code written in select all check box which is created on test.block.
BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
:M_END_YN := :M_END_ALL;
[code].......
--code written in M_END_YN ( actual check boxes where i will uncheck).
IF :M_END_YN = 'N' THEN
:M_END_ALL := 'N';
END IF;
--code written on button to update those values which are checked.
BEGIN
GO_BLOCK('item_trans');
FIRST_RECORD;
LOOP
IF :M_END_YN = 'Y' THEN
[code]......
View 5 Replies
View Related
May 9, 2013
I am New to Apex. Im using Apex Version 4.2.
I have a tab named APPROPRIATIONS it has 10 fields. Among which is PROJECT NUMBER, AUTHORIZAION DATE (CALENDER TYPE POPUP), And FUNDS CHECK FLAG.
The Scenario is depending upon PROJECT NUMBER the Fields are Populated including FUNDS CHECK FLAG .
But the AUTHORIZAION DATE is a MANDATORY field has to be filled by User
The Requirement is to make AUTHORIZAION DATE as MANDATORY only when the FUNDS CHECK FLAG is *"BLANK "* or *“Y”*
If the FUNDS CHECK FLAG is *“N”* then the AUTHORIZAION DATE to be made OPTIONAL.
View 2 Replies
View Related
Apr 23, 2009
I need to update a field with the 1st 9 characters of another field in the same record.
View 1 Replies
View Related
Nov 14, 2011
I have a table called Customer_Type with following fields
Customer_type ,Active_date, Inactive_date
regular,11/01/2011
daily,11/04/2011
monthly,11/05/2011/11/11/2011
Tbale 2:Customer
Customer_name,Customer_type,Customer_Inactive_date
John,regular,
James,monthly,
Jake,daily,
Jill,monthly
What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql
I have teh code using merge function..I want something in traditional old fashion..
The sql statements are below
CREATE TABLE CUSTOMER_TYPE
(
type_code VARCHAR2(10),
[Code]....
View 5 Replies
View Related
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
Feb 4, 2013
I have the following C code:
class Factorial {
public:
int getVal (int a);
};
[code]....
/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.
Just in case, listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....
View 6 Replies
View Related
Mar 11, 2010
What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies
View Related
Oct 31, 2012
I want to run multiple sql select command after loggin to sqlplus and need to pass the value to variable to check the condition.
When I am running the below script I am getting the below error
----------------------
SP2-0734: unknown command beginning "return :va..." - rest of line ignored.
SP2-0734: unknown command beginning "return :va..." - rest of line ignored.
a2.sh: test: argument expected
------------------------
Example:
sqlplus -s system/SYSTEM_PASS << EOF
set pages 0
set head off
[Code].....
View 5 Replies
View Related