dynamic query
DECLARE
QUERY VARCHAR2(32767);
FIN_QUERY VARCHAR2(32767);
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 ';
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 ';
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);
EXECUTE IMMEDIATE FIN_QUERY;
END;
View Answers
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 query
dynamic 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
dynamic 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
ModuleNotFoundError: No module named 'redash-dynamic-query'
ModuleNotFoundError: No module named 'redash-dynamic-query' Hi...: No module named 'redash-dynamic-query' How to remove the ModuleNotFoundError: No module named 'redash-dynamic-query' error? Thanks Hi
ModuleNotFoundError: No module named 'redash-dynamic-query' Hi...: No module named 'redash-dynamic-query' How to remove the ModuleNotFoundError: No module named 'redash-dynamic-query' error? Thanks Hi
Advertisements
Hibernate Criteria Dynamic Association Fetching
Hibernate 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
Hibernate 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
Query //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
Query //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 polymorphism
dynamic 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 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 form
dynamic 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 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 keyword
Dynamic 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 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 polymorphism
dynamic 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 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
create dynamic array in javascript
create dynamic array in javascript How to create dynamic array in javascript
create dynamic array in javascript How to create dynamic array in javascript
dynamic textfields
dynamic 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 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 form
Dynamic 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 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 calender
dynamic 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
dynamic 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
how to add dynamic data
how to add dynamic data how to add dynamic data to an existing web application
how to add dynamic data how to add dynamic data to an existing web application
static page and dynamic pages?
static page and dynamic pages? what is the static page and dynamic pages
static page and dynamic pages? what is the static page and dynamic pages
static page and dynamic pages?
static page and dynamic pages? what is the diff between static page and dynamic pages
static page and dynamic pages? what is the diff between static page and dynamic pages
static page and dynamic pages?
static page and dynamic pages? what is the static page and dynamic pages adv and disadv
static page and dynamic pages? what is the static page and dynamic pages adv and disadv
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' 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
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 image change javascript
dynamic image change javascript How to display images in JavaScript dynamically
dynamic image change javascript How to display images in JavaScript dynamically
Dynamic Array iPhone
Dynamic Array iPhone Dynamic Array iPhone How can i add NSMutable array dynamically into my iPhone App
Dynamic Array iPhone Dynamic Array iPhone How can i add NSMutable array dynamically into my iPhone App
Dynamic check box problem
Dynamic 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
Dynamic 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 loading
Bulk 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
Bulk 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 Beginners
Dynamic 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 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 Beginners
Dynamic 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 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 box
dynamic 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 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 Designing
Dynamic 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
Dynamic 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
Dynamic google maps
Dynamic 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 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 maps
Dynamic 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 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 maps
Dynamic 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 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 maps
Dynamic 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 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 maps
Dynamic 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 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 examples
Dynamic 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
Dynamic 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
How to create dynamic array in C?
How to create dynamic array in C? How to create dynamic array in c programming language? Dynamic Array in C Tutorial
How to create dynamic array in C? How to create dynamic array in c programming language? Dynamic Array in C Tutorial
query on java servlet
query 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
query 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-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 - Struts
Dynamic 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
Dynamic 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
pagination+dynamic pagesize+hibernate - Struts
pagination+dynamic pagesize+hibernate pagination using hibernate with dynamic page size plzz heilp me
pagination+dynamic pagesize+hibernate pagination using hibernate with dynamic page size plzz heilp me
making of dynamic textfields using swings
making of dynamic textfields using swings how to crate dynamic texfields by clicking on button
making of dynamic textfields using swings how to crate dynamic texfields by clicking on button
making of dynamic textfields using swings
making of dynamic textfields using swings How to make dynamic textfields using java swings
making of dynamic textfields using swings How to make dynamic textfields using java swings
Ads
