Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Answers Viewqa SQL dynamic query

Have Programming Question? Ask it here!
 
 
 


kish
dynamic query
1 Answer(s)      2 years and 2 months ago
Posted in : SQL


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;

Advertisement
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
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
Advertisements
query
checks the program to find out the possible problems, later dynamic (runtime
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
DYNAMIC BINDING
DYNAMIC BINDING  WHAT IS DYNAMIC BINDING
dynamic report
dynamic report  i need complete code for generating dynamic report in jsp
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 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 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 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 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 calender
dynamic calender  hi i need the code to "insert date using GUI"   Hi Friend, Try the following code: import java.awt.*; import java.awt.event.*; import javax.swing.*; class DatePicker{ int month
dynamic jquery statement
dynamic jquery statement  dynamic jquery statement
PHP Dynamic CheckBox
PHP Dynamic CheckBox  Help with Dynamic Checkbox in PHP
php dynamic array checking
php dynamic array checking  php dynamic array checking
php dynamic array checking
php dynamic array checking  php dynamic array checking
create dynamic array in javascript
create dynamic array in javascript  How to create dynamic array in javascript
how to add dynamic data
how to add dynamic data  how to add dynamic data to an existing web application
dynamic web pages in html
dynamic web pages in html  How to create Dynamic web pages in HTML
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?
static page and dynamic pages?  what is the static page and dynamic pages adv and disadv
Dynamic include jsp
Dynamic include jsp  I need dynamic include jsp page with an example
dynamic image change javascript
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 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 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 - 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 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 html examples
Dynamic html examples  Hi, What is Dynamic HTML? Explain with dynamic html examples. Thanks   Hi, DHTML stands for Dynamic HTML and is uses the HTML, JavaScript, DOM, & CSS in a program. Following is very simple
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 - 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
Creating Dynamic Tree
Creating Dynamic Tree   Creating Dynamic Tree: I have to build a tree structure with the following data lets say that with this data childId parentId 1 - 0 (lets say this is root) 2 - 1 3 - 1 4 - 3 5 - 3 6 - 5 7 - 6 all I need
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
dynamic select box
dynamic select box  hello friends i created a form and details of city & locations have to be come from database. if city hyderabad chosen then locations of hyderabad only appear in location selectbox.plz send code it is more
need dynamic image example
need dynamic image example  struts 2 img example for a dynamic image.. one that the user of the website uploads. To be more clear on what I am doing. I am creating a profile for a user and the user is uploading his image which I
problem with dynamic url
problem with dynamic url  Hello! I was asked to implement a dynamic url code below to a html template I designed. I am a web designer, and although I understand the code, there is something wrong with it, or maybe I didn't do
Hibernate : Dynamic-insert
This tutorial contains description of Hibernate dynamic-insert
STRUTS2 Dynamic data is not updated
STRUTS2 Dynamic data is not updated  Hi All, I am new in struts and try to make an application in struts2. Need help on dynamic data content. In application i am tried to fetch data and save in variable and use set & get
STRUTS2 Dynamic data is not updated
STRUTS2 Dynamic data is not updated  Hi All, I am new in struts and try to make an application in struts2. Need help on dynamic data content. In application i am tried to fetch data and save in variable and use set & get
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
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 Line draw in JSp
Dynamic Line draw in JSp  In my application. I have one source selected from listbox and multiple targets selected from checkboxes. After submitting "next" button it should give me multiple lines drw from one source to respective
Dynamic web pages
Dynamic web pages  hi, i am just creating a web page using html and javascript. i want to give it a eye catching look using javascript. I just want to know how could i make it happen using javascript functions,like zoom in &
dynamic drop down list
dynamic drop down list  I want to create 2 drop down list, where it takes value from database and the two list are dependent..means if I select a particular value from one the other list is developed accordingly..depending
Dynamic table in jsp
Dynamic table in jsp  Hi..... I have a java file to read excel file. I have stored all the elements of excel file in a variable. Now i want to show all values of excel file on jsp page in a table format. All elements
query in JDBC
query in JDBC  What are the steps required to execute a query in JDBC
dynamic display - JSP-Servlet
dynamic display  hi, i want to display dynamic values in drop drown box in a jsp page.these values are in the form of arraylist's object which came form another servlet or bussiness logic after being validated
Dynamic link in JSP
Dynamic link in JSP  Hii everyone...I have stuck from last 2 days with this problem. I am creating one application in JSP.In that application , i am... stuck with this LINK ...How to generate this dynamic link n all? I have tried
 

 

 

DMCA.com