Writing cursor based on other cursor

prashant.gadekar · #1 (**permalink**) 06-03-11, 09:29

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.

dav1mo · #2 (**permalink**) 06-03-11, 10:57

try union. you have 4 separate selects all unioned together.

tonkuma · #3 (**permalink**) 06-03-11, 12:23

Many vague or unclear descriptions...

For example:

Quote:

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

tonkuma · #4 (**permalink**) 06-03-11, 14:04

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech