dynamic query

Home Answers Viewqa SQL dynamic query

kish

dynamic query
1 Answer(s) a year and 3 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;

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;

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

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 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 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 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

second selectbox of dynamic
"); if(!$select) die("select failed"); echo "selected vtigercrm530"; $query="SELECT DISTINCT address_city FROM vtiger_users"; $result=mysql_query($query..."; $result1=mysql_query($query1); if(!$result1) die("database access failed:" . mysql

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

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 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

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

dynamic drop down list box - Java Beginners
dynamic drop down list box hi all , I want to dynamically populate a drop down box from an sql query in a servlet program, using only html... Rajanikant Hi friend, dynamic bombobox in servlet

Create dynamic page through JSP
Create dynamic page through JSP... how to create dynamic page. This code shows one by one record of student from... running this jsp code, first create a database named 'student' through the sql query

DYNAMIC BINDING
DYNAMIC BINDING WHAT IS DYNAMIC BINDING

query

Query

query

Query

query

Query

query

JPA Named Query
executing the query. Types of query: Static query (Named query) Dynamic... JPA Named Query In this section, you will read about the JPA Query, its types

Hibernate Criteria Query
Hibernate Criteria Query In this tutorial you will learn about the Hibernate Criteria Query. Hibernate provides an API for writing the dynamic query in an elegant way to execute . Except the use of HQL in Hibernate, Criteria query

dynamic report
dynamic report i need complete code for generating dynamic report in jsp

Hibernate Query Language
Hibernate Query Language Hibernate Query Language or HQL for short is extremely powerful query language. HQL is much like SQL and are case-insensitive, except

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 retrieval od data from database and display it in the table at jsp
Dynamic retrieval od data from database and display it in the table at jsp ...:3306/test", "root", "root"); String query = "select * from employee"; Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query); while

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 retrival of data from mysql database to dropdownlist in jsp
dynamic retrival of data from mysql database to dropdownlist in jsp ...","root","root"); String query = "select * from dept"; Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query); while(rs.next()) { %> <

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 jquery statement
dynamic jquery statement dynamic jquery statement

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

i want to store dynamic number of value on one jsp and retrieve them on other.
i want to store dynamic number of value on one jsp and retrieve them on other. in my problem, i want to run a query from database.then while in loop of result processing i have dynamic number of drop downs on a single page which

PHP Dynamic CheckBox
PHP Dynamic CheckBox Help with Dynamic Checkbox in PHP

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

static page and dynamic pages?
static page and dynamic pages? what is the static page and dynamic pages adv and disadv

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

Ask Questions?

If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.

Ask your questions, our development team will try to give answers to your questions.

Tutorial Topics