February 17, 2012 at 6:41 PM
DECLARE
QUERY VARCHAR2(32767);
QUERY2 CLOB;
FIN_QUERY CLOB;
CURSOR C IS
(select distinct PORTFOLIO from MPE_TEST1);
CURSOR C2 IS
SELECT DISTINCT PORTFOLIO_LEVEL, PORTFOLIO FROM MPE_TEST1 ORDER BY PORTFOLIO_LEVEL;
TYPE ARRAY IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
PORTFOLIO_LEVEL ARRAY;
IDX NUMBER :=1;
BEGIN
QUERY := 'SELECT DISTINCT ACCOUNT_ID,';
for R IN C
LOOP
IDX := 1;
FOR R2 IN C2
LOOP
IF(R2.PORTFOLIO = R.PORTFOLIO)
THEN
PORTFOLIO_LEVEL(IDX) := R2.PORTFOLIO_LEVEL;
IDX := IDX + 1;
END IF;
END LOOP;
QUERY := QUERY || 'CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(1) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_SALE_ASS_C,
CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(1) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_SALES_ASS_EXP_DT,
CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(2) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_SALE_MST_C,
CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(2) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_SALE_MST_EXP_DT,
CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(3) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_SALE_PROF_C,
CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(3) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_SALE_PROF_EXP_DT,
CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(4) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_TECH_ASS_C,
CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(4) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_TECH_ASS_EXP_DT,
CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(5) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_TECH_MST_C,
CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(5) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_TECH_MST_EXP_DT,
CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(6) ||''' THEN COUNT ELSE 0 END as '||R.PORTFOLIO||'_TECH_PROF_C,
CASE WHEN f.PORTFOLIO_LEVEL ='''||PORTFOLIO_LEVEL(6) ||''' THEN END_DATE END as '||R.PORTFOLIO||'_TECH_PROF_EXP_DT,';
END LOOP;
QUERY := SUBSTRING(QUERY,0,LENGTH(QUERY)-1);
QUERY := QUERY || ' FROM MPE_TEST1 F ';
--DBMS_OUTPUT.PUT_LINE('QUERY--'||QUERY);
FIN_QUERY := 'select i.ACCOUNT_ID, ';
FOR R IN C
LOOP
FIN_QUERY := FIN_QUERY ||
'(case when '||R.PORTFOLIO||'_TECH_ASS_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL ASSOCIATE'')
then ''Elite Specialist, Specialist, Authorized''
when '||R.PORTFOLIO||'_TECH_ASS_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL ASSOCIATE'')
then ''Specialist, Authorized''
when '||R.PORTFOLIO||'_TECH_ASS_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL ASSOCIATE'')
then ''Authorized''
else ''Authorized''
end) '||R.PORTFOLIO||'_TA_LEVEL,
(case when '||R.PORTFOLIO||'_SALE_ASS_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES ASSOCIATE'')
then ''Elite Specialist, Specialist, Authorized''
when '||R.PORTFOLIO||'_SALE_ASS_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES ASSOCIATE'')
then ''Specialist, Authorized''
when '||R.PORTFOLIO||'_SALE_ASS_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES ASSOCIATE'')
then ''Authorized''
else ''Authorized''
end) '||R.PORTFOLIO||'_SA_LEVEL,
(case when '||R.PORTFOLIO||'_TECH_MST_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL MASTER'')
then ''Elite Specialist, Specialist, Authorized''
when '||R.PORTFOLIO||'_TECH_MST_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL MASTER'')
then ''Specialist, Authorized''
when '||R.PORTFOLIO||'_TECH_MST_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL MASTER'')
then ''Authorized''
else ''Authorized''
end) '||R.PORTFOLIO||'_TM_LEVEL,
(case when '||R.PORTFOLIO||'_SALE_MST_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES MASTER'')
then ''Elite Specialist, Specialist, Authorized''
when '||R.PORTFOLIO||'_SALE_MST_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES MASTER'')
then ''Specialist, Authorized''
when '||R.PORTFOLIO||'_SALE_MST_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES MASTER'')
then ''Authorized''
else ''Authorized''
end) '||R.PORTFOLIO||'_SM_LEVEL,
(case when '||R.PORTFOLIO||'_TECH_PROF_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL P'')
then ''Elite Specialist, Specialist, Authorized''
when '||R.PORTFOLIO||'_TECH_PROF_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL P'')
then ''Specialist, Authorized''
when '||R.PORTFOLIO||'_TECH_PROF_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''TECHNICAL P'')
then ''Authorized''
else ''Authorized''
end) '||R.PORTFOLIO||'_TP_LEVEL,
(case when '||R.PORTFOLIO||'_SALE_PROF_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''ELITE SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES P'')
then ''Elite Specialist, Specialist, Authorized''
when '||R.PORTFOLIO||'_SALE_PROF_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''SPECIALIST'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES P'')
then ''Specialist, Authorized''
when '||R.PORTFOLIO||'_SALE_PROF_C >=
(select PGM_RQMT_DET_TARGET from DIM_MPE_SF_PGM_RQMT_DET where PGM_LVL_RQMT_SPEC_LEVEL =''AUTHORIZED'' and PGM_LVL_RQMT_TECH='''||R.PORTFOLIO||''' and PGM_RQMT_DET_CRITERIA=''SALES P'')
then ''Authorized''
else ''Authorized''
end) '||R.PORTFOLIO||'_SP_LEVEL, ';
END LOOP;
FIN_QUERY := FIN_QUERY || ' i.* from (' || QUERY;
FIN_QUERY := FIN_QUERY || ') I ';
--DBMS_OUTPUT.PUT_LINE(FIN_QUERY);
QUERY2 := 'SELECT ';
FOR R IN C
LOOP
QUERY2 := QUERY2 || 'MAX('||R.PORTFOLIO||'_TA_LEVEL) '||R.PORTFOLIO||'_TA_LEVEL,
MAX('||R.PORTFOLIO||'_SA_LEVEL) '||R.PORTFOLIO||'_SA_LEVEL,
MAX('||R.PORTFOLIO||'_TM_LEVEL) '||R.PORTFOLIO||'_TM_LEVEL,
MAX('||R.PORTFOLIO||'_SM_LEVEL) '||R.PORTFOLIO||'_SM_LEVEL,
MAX('||R.PORTFOLIO||'_TP_LEVEL) '||R.PORTFOLIO||'_TP_LEVEL,
MAX('||R.PORTFOLIO||'_SP_LEVEL) '||R.PORTFOLIO||'_SP_LEVEL,
SUM('||R.PORTFOLIO||'_SALE_ASS_C ) '||R.PORTFOLIO||'_SALE_ASS,
MIN('||R.PORTFOLIO||'_SALES_ASS_EXP_DT) '||R.PORTFOLIO||'_SALES_ASS_EXP_D,
SUM('||R.PORTFOLIO||'_TECH_ASS_C) '||R.PORTFOLIO||'_TECH_ASS,
MIN('||R.PORTFOLIO||'_TECH_ASS_EXP_DT) '||R.PORTFOLIO||'_TECH_ASS_EXP_D,
SUM('||R.PORTFOLIO||'_SALE_PROF_C) '||R.PORTFOLIO||'_SALE_PROF,
MIN('||R.PORTFOLIO||'_SALE_PROF_EXP_DT) '||R.PORTFOLIO||'_SALE_PROF_EXP_D,
SUM('||R.PORTFOLIO||'_TECH_PROF_C) '||R.PORTFOLIO||'_TECH_PROF,
MIN('||R.PORTFOLIO||'_TECH_PROF_EXP_DT) '||R.PORTFOLIO||'_TECH_PROF_EXP_D,
SUM('||R.PORTFOLIO||'_SALE_MST_C) '||R.PORTFOLIO||'_SALE_MST,
MIN('||R.PORTFOLIO||'_SALE_MST_EXP_DT) '||R.PORTFOLIO||'_SALE_MST_EXP_D,
SUM('||R.PORTFOLIO||'_TECH_MST_C) '||R.PORTFOLIO||'_TECH_MST,
MIN('||R.PORTFOLIO||'_TECH_MST_EXP_DT) '||R.PORTFOLIO||'_TECH_MST_EXP_D,';
END LOOP;
QUERY2 := SUBSTR(QUERY2,0,LENGTH(QUERY2)-1);
QUERY2 := QUERY2 || ' FROM ( ' || FIN_QUERY || ') ';
DBMS_OUTPUT.PUT_LINE('FINAL--'||QUERY2);
END;
Related Tutorials/Questions & Answers:
dynamic querydynamic query DECLARE
QUERY VARCHAR2(32767);
FIN_
QUERY VARCHAR2...;
PORTFOLIO_LEVEL ARRAY;
IDX NUMBER :=1;
BEGIN
QUERY..._LEVEL;
IDX := IDX + 1;
END IF;
END LOOP;
QUERY :=
QUERY Advertisements
Hibernate Criteria Dynamic Association FetchingHibernate Criteria
Dynamic Association Fetching
In this tutorial you will learn about the
dynamic association fetching in Criteria
Query.
In a Criteria
Query a
dynamic fetching operation can be done using the
fields, JOIN, SELECT
query complexity. Java checks the program to find out the possible problems, later
dynamic QueryQuery //A.java
public class A
{
static int a=regd;
public A()
{
}
}
//B.java
public class B
{
static int b=A.a;
public A()
{
}
}
I created two classes separately.Here regd is
dynamic variable means it's varying with the given
dynamic reportdynamic report i need complete code for generating
dynamic report in jsp
dynamic polymorphismdynamic polymorphism Develop with suitable hierarchy, classes for Point, Shape, Rectangle, Square, Circle,
Ellipse, Triangle, Polygon, etc. Design a simple test application to demonstrate
dynamic polymorphism
dynamic formdynamic form I need to make a
dynamic form using php, for example, i... not connect: ' . mysql_error());
}
mysql_select_db('db_ajax');
$
query="SELECT id,statename FROM state WHERE countryid='$country'";
$result=mysql_
query($
query);
?>
Dynamic keywordDynamic keyword hi.......
What is the
dynamic keyword used for in flex?
give me the answer ASAP
Thanks Ans:
Dynamic keyword-> Specifies that instances of a class may possess
dynamic properties added
dynamic polymorphismdynamic polymorphism give an example for
dynamic polymorphism?
Dynamic polymorphism is where a class overrides a superclass method... seen at runtime, so they are considered
dynamic.
Here is an example
dynamic textfieldsdynamic textfields Hi,
I am a fresher and joined recently in one company. they gave me a desktop application project using swings.here is my detailed spec Screen 1 - The user enters the ingredients from a food product, saves
Dynamic formDynamic form I need to make a
dynamic form using jsp for example, i will need a list of items which when we select one option another list is modified ( like 2 lists of countries and citys ), and also a radio button wich once
dynamic calenderdynamic calender hi
i need the code to "insert date using GUI"
Hi Friend,
Try the following code:ADS_TO_REPLACE_1
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
class DatePicker{
int month
ModuleNotFoundError: No module named 'dynamic'ModuleNotFoundError: No module named '
dynamic' Hi,
My Python... '
dynamic'
How to remove the ModuleNotFoundError: No module named '
dynamic... to install padas library.
You can install
dynamic python with following command
ModuleNotFoundError: No module named 'dynamic'ModuleNotFoundError: No module named '
dynamic' Hi,
My Python... '
dynamic'
How to remove the ModuleNotFoundError: No module named '
dynamic... to install padas library.
You can install
dynamic python with following command
Dynamic Array iPhoneDynamic Array iPhone
Dynamic Array iPhone
How can i add NSMutable array dynamically into my iPhone App
Dynamic check box problemDynamic check box problem In my project i have used a
dynamic table... dynamically]. Now my problem is that i can't access those values from that
dynamic...,password);
String
query = "select * from data";
st = con.createStatement();
ResultSet
Bulk insert and dynamic schema loadingBulk insert and
dynamic schema loading Hi All,
I am new to H2... assistance regarding H2 database.
Overview of my work :
I get the
query from reporting engine.
Query is split and only the select with where is run
Dynamic polymorphism - Java BeginnersDynamic polymorphism Develop with suitable hierarchy, classes for point, shape, rectangele, square, circle,ellipse, triangle, polygon, etc. Design a simple test application to demonstrate
dynamic polymorphism..
Thanks
Dynamic Polymorphism - Java BeginnersDynamic Polymorphism Develop with suitable hierarchy, classes for Point, Shape, Rectangle, Square, Circle,
Ellipse, Triangle, Polygon, etc. Design a simple test application to demonstrate
dynamic polymorphism.?
Thanks
dynamic select boxdynamic select box thank u my dear friend.but i have a code like...) die("select failed");
echo "selected vtigercrm530";
$
query="SELECT DISTINCT address_city FROM vtiger_users";
$result=mysql_
query($
query);
if(!$result) die
Dynamic Website DesigningDynamic Website Designing
The
Dynamic sites differ from the static in terms of content and design. In
dynamic sites, the content live in the database and appears when it is asked to access.
Dynamic website is quicker in uploading
query in JDBCquery in JDBC What are the steps required to execute a
query in JDBC
Dynamic google mapsDynamic google maps I have to read data from a database and show...;
Timestamp event_date;
%>
<%
String
query="SELECT EVENTSIZE...(
query);
} catch (SQLException e) {
request.getSession().invalidate
Dynamic google mapsDynamic google maps I have to read data from a database and show...;
Timestamp event_date;
%>
<%
String
query="SELECT EVENTSIZE...(
query);
} catch (SQLException e) {
request.getSession().invalidate
Dynamic google mapsDynamic google maps I have to read data from a database and show...;
Timestamp event_date;
%>
<%
String
query="SELECT EVENTSIZE...(
query);
} catch (SQLException e) {
request.getSession().invalidate
Dynamic google mapsDynamic google maps I have to read data from a database and show...;
Timestamp event_date;
%>
<%
String
query="SELECT EVENTSIZE...(
query);
} catch (SQLException e) {
request.getSession().invalidate
Dynamic google mapsDynamic google maps I have to read data from a database and show...;
Timestamp event_date;
%>
<%
String
query="SELECT EVENTSIZE...(
query);
} catch (SQLException e) {
request.getSession().invalidate
Dynamic html examplesDynamic html examples Hi,
What is
Dynamic HTML? Explain with
dynamic html examples.
Thanks
(adsbygoogle = window.adsbygoogle || []).push({});
Hi,
DHTML stands for
Dynamic HTML and is uses the HTML
query on java servletquery on java servlet hello sir,i am doing online exam using j2ee (i.e) with servlet and back end ms access just like statically after that i will use
dynamic connection sir.what queries to be used for data base validation
Dynamic-update not working in Hibernate.Dynamic-update not working in Hibernate. Why is
dynamic update not working in hibernate?
Dynamic-update is not working. It means when you are running your update, new data is added in your table in place
Dynamic pages - StrutsDynamic pages I want to recreate a page on click and then enter data in my database via struts1.1 please help me what can i use or what help i can get from