Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    8

    Unanswered: Writing cursor based on other cursor

    Hi,

    I have to write a stored procedure that will generate a summary result.
    In this stored procedure I have a requirement that I have to group the
    result based on the input field.

    Here is my table structure:
    Code:
    	USERID VARCHAR(50),
    	WEBSESSION_ID VARCHAR(50),
    	WEBHITAT_DATE DATE,
    	FROMHOSTIP VARCHAR(39),
    	FROMCOUNTRY VARCHAR(100),
    	FROMCITY VARCHAR(100),
    	START_TIME TIME,
    	END_TIME TIME,
    	DEPT_ID VARCHAR(20),
    	PROJECT_ID VARCHAR(20),
    	MODULE_ID VARCHAR(20),
    	APP_ID VARCHAR(30),
    	ISAUTHENTICATED CHARACTER(1),
    	FROMANONYMISER CHARACTER(1)
    I have to get the rows that are having USERS that are having different FROMHOSTIP for same day. But the the FROMHOSTIP can be related to

    PROJECT_ID or MODULE_ID or APP_ID or DEPT_ID depending on the
    user requirement. I have to write a single stored procedure that do
    the above job.

    Till now I have learned that we can write a cursor that do job but group by
    will only for one field at a time.

    How I write a cursor that we give me result based on the user requirement.

    Please suggest. Any help is highly appreciated.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    try union. you have 4 separate selects all unioned together.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Many vague or unclear descriptions...

    For example:
    ... the FROMHOSTIP can be related to PROJECT_ID or MODULE_ID or APP_ID or DEPT_ID depending on the user requirement.
    1) What means related to?
    2) How to relate FROMHOSTIP to PROJECT_ID or MODULE_ID or APP_ID or DEPT_ID?
    3) After making the relations, what to do next?
    4) How to get user requirement?
    5) How to make the relations from user requirement?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although, the example might not meet your requirements,
    here is an example which returns summary data grouped by input column name for an input department number.

    Create a table function:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE OR REPLACE FUNCTION group_salary_by_input
    ( in_deptno       VARCHAR(3)
    , in_group_column VARCHAR(8)
    )
      RETURNS TABLE
    ( group_value VARCHAR(25)
    , count_mbr   SMALLINT
    , sum_salary  DEC(9 , 2)
    , avg_salary  DEC(9 , 2)
    , max_salary  DEC(9 , 2)
    , min_salary  DEC(9 , 2)
    )
      NO EXTERNAL ACTION
    RETURN
    SELECT COALESCE(
              group_value
            , ' ' || MAX(workdept) || ': ' || UPPER(in_group_column)
            , '***Invalid deptno: ' || in_deptno
           ) out_group_value
         , COUNT(group_value)
         , SUM(salary)
         , AVG(salary)
         , MAX(salary)
         , MIN(salary)
     FROM  (SELECT workdept
                 , CASE UPPER(in_group_column)
                   WHEN 'EDLEVEL'  THEN VARCHAR( CHAR(edlevel) , 20)
                   WHEN 'SEX'      THEN sex
                   WHEN 'HIREYEAR' THEN CHAR( YEAR(hiredate) )
                   ELSE            '***Invalid group column'
                   END  AS group_value
                 , salary
             FROM  employee
             WHERE workdept = in_deptno
          ) s
     GROUP BY
           ROLLUP( group_value )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    Query example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  TABLE( group_salary_by_input('D11' , 'edlevel') )
    ;
    ------------------------------------------------------------------------------
    
    GROUP_VALUE               COUNT_MBR SUM_SALARY  AVG_SALARY  MAX_SALARY  MIN_SALARY 
    ------------------------- --------- ----------- ----------- ----------- -----------
     D11: EDLEVEL                     9   512100.00    56900.00    72250.00    44680.00
    16                                5   280400.00    56080.00    72250.00    44680.00
    17                                3   181860.00    60620.00    68270.00    51340.00
    18                                1    49840.00    49840.00    49840.00    49840.00
    
      4 record(s) selected.

    Query example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  TABLE( group_salary_by_input('D11' , 'sex') )
    ;
    ------------------------------------------------------------------------------
    
    GROUP_VALUE               COUNT_MBR SUM_SALARY  AVG_SALARY  MAX_SALARY  MIN_SALARY 
    ------------------------- --------- ----------- ----------- ----------- -----------
     D11: SEX                         9   512100.00    56900.00    72250.00    44680.00
    F                                 3   163430.00    54476.66    62250.00    49840.00
    M                                 6   348670.00    58111.66    72250.00    44680.00
    
      3 record(s) selected.

    Query example 3:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  TABLE( group_salary_by_input('D11' , 'hireyear') )
    ;
    ------------------------------------------------------------------------------
    
    GROUP_VALUE               COUNT_MBR SUM_SALARY  AVG_SALARY  MAX_SALARY  MIN_SALARY 
    ------------------------- --------- ----------- ----------- ----------- -----------
     D11: HIREYEAR                    9   512100.00    56900.00    72250.00    44680.00
    1998                              2   118110.00    59055.00    68270.00    49840.00
    1999                              1    44680.00    44680.00    44680.00    44680.00
    2002                              2   113020.00    56510.00    57740.00    55280.00
    2003                              2   123590.00    61795.00    72250.00    51340.00
    2004                              1    50450.00    50450.00    50450.00    50450.00
    2006                              1    62250.00    62250.00    62250.00    62250.00
    
      7 record(s) selected.

    Query example 4(exceptional message):
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  TABLE( group_salary_by_input('D11' , 'hiredate') )
    ;
    ------------------------------------------------------------------------------
    
    GROUP_VALUE               COUNT_MBR SUM_SALARY  AVG_SALARY  MAX_SALARY  MIN_SALARY 
    ------------------------- --------- ----------- ----------- ----------- -----------
     D11: HIREDATE                    9   512100.00    56900.00    72250.00    44680.00
    ***Invalid group column           9   512100.00    56900.00    72250.00    44680.00
    
      2 record(s) selected.

    Query example 5(exceptional message):
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  TABLE( group_salary_by_input('D13' , 'hireyear') )
    ;
    ------------------------------------------------------------------------------
    
    GROUP_VALUE               COUNT_MBR SUM_SALARY  AVG_SALARY  MAX_SALARY  MIN_SALARY 
    ------------------------- --------- ----------- ----------- ----------- -----------
    ***Invalid deptno: D13            0           -           -           -           -
    
      1 record(s) selected.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •