Varying elements in IN list

varying elements in IN list of query

Create or replace type myTableType as table of varchar2(5);  /or Number/

Create or replace function str2tbl( p_str in varchar2 )
 Return myTableType
as
l_str long default p_str || ',';
l_n number;
l_data myTableType := myTabletype();
Begin
Loop
 l_n := instr( l_str, ',' );
 exit when (nvl(l_n,0) = 0);
 l_data.extend;
 l_data( l_data.count ) := ltrim(rtrim(substr(l_str, 1, l_n-1)));
 l_str := substr( l_str, l_n+1 );
End loop;

 Return l_data;

End str2tbl;
　
select *from THE ( select cast( str2tbl('1, S, 3, 5, 6') as mytableType ) from dual) ;

Column_value
1
S
3
4
5

Ref. http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:110612348061

Pivoting - Multi column. in One

Ref. http://www.dba-oracle.com/t_sql_pivoting_rows_several_columns.htm

Using the SYS_CONNECT_BY_PATH operator

select

deptno,
substr(sys_connect_by_path(lname, ','),2) name_list from
( select lname,
           deptno,
           count(*) Over (partition by deptno ) cnt,
           Row_Number() Over(partition by deptno order by lname) seq
  from emp
   where deptno is not null
)
where seq=cnt
start with seq=1
connect by prior seq+1=seq and prior deptno=deptno; 　

Select pole_code, genPact_le_code,coe_code, sdu_code,
        substr(sys_connect_by_path(user_id, ','), 2) name_list
From (select user_id, pole_code, genPact_le_code,coe_code, sdu_code,
                count(1) over(partition by role_id, pole_code, genPact_le_code,coe_code, sdu_code) Cnt,
                Row_Number()over(partition by role_id, pole_code, genPact_le_code,coe_code,sdu_code 
                 order by user_id) seq
           from gbs_userroleactivity_mst where role_id = 1
       --and pole_code='10' and genpact_le_code='D84' and sdu_code='BD'
) where seq= cnt
start with seq=1
connect by prior seq+1=seq and prior sdu_code = sdu_code and prior pole_code=pole_code and prior coe_code=coe_code and prior genpact_le_code=genpact_le_code;


Using the Listagg  function [Oracle 11]

Ref. http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions087.htm

• As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.
• As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.
• As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause.

select pole_code, genPact_le_code,coe_code, sdu_code ,
(select listagg(user_id, ',') within group (order by created_date desc ) from gbs_user_mst
where user_id in (select distinct user_id from gbs_userroleactivity_mst where role_id = 1 and pole_code =a.pole_code and genpact_le_code = a.genpact_le_code
and coe_code = a.coe_code and sdu_code = a.sdu_code)) billing_master_maker
From gbs_billingbase_mst

Oracle Analytic Function

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. 

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

In some cases an analytic function can run a hundred times faster than regular SQL that does the analytic with a subquery :-- 

 http://www.dba-oracle.com/t_OracleAnalyticFunctions.htm

 ********************************  
 Using Analytic Function

  ********************************

select    id,
    last_name,
    salary,
    sum(salary) over (order by id) cume_salary
from
    emp
    order by id;   Using AF: 0.84 seconds
Statistics
----------------------------
           1  recursive calls           0  db block gets
         290  consistent gets           0  physical reads
           0  redo size

 ********************************
-- Using regular SQL
********************************

select
    e1.id,
    e1.last_name,
    e1.salary,
   (select   sum(salary)   from   emp   where  id <= e1.id)    AS cume_salary
from
    emp e1
    order by id;

-- ********************************
-- Using regular SQL: over 8 minutes
-- ********************************

Statistics
----------------------------
       1  recursive calls
       0  db block gets
11200335  consistent gets –over 38,000 times as many
     255  physical reads
       0  redo size

Analytic Example

The  example makes the same calculation as the previous Aggregate example but returns the result for each employee within the department:

SELECT last_name, department_id, salary,
   MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
      OVER (PARTITION BY department_id) "Worst",
   MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
      OVER (PARTITION BY department_id) "Best"
    FROM employees
    ORDER BY department_id, salary;

LAST_NAME           DEPARTMENT_ID     SALARY      Worst       Best
------------------- ------------- ---------- ---------- ----------
Whalen                         10       4400       4400       4400
Fay                            20       6000       6000      13000
Hartstein                      20      13000       6000      13000
. . .
Gietz                         110       8300       8300      12000
Higgins                       110      12000       8300      12000
Grant                                   7000       7000       7000

Aggregate Functions

Aggregate functions return a single result row based on group by of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses.

All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls.
COUNT and REGR_COUNT never return null, but return either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.
The aggregate functions are:

AVG
COLLECT
CORR
CORR_*
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
GROUP_ID
GROUPING
GROUPING_ID
LAST
MAX
MEDIAN
MIN
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
RANK
REGR_ (Linear Regression) Functions
STATS_BINOMIAL_TEST
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MODE
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST_*
STATS_WSR_TEST
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
SYS_XMLAGG
VAR_POP
VAR_SAMP
VARIANCE
XMLAGG

Aggregate Example

The following example returns, within each department of the sample table hr.employees, the minimum salary among the employees who make the lowest commission and the maximum salary among the employees who make the highest commission:

SELECT department_id,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
   FROM employees
   GROUP BY department_id
   ORDER BY department_id, "Worst", "Best";

DEPARTMENT_ID      Worst       Best
------------- ---------- ----------
           10       4400       4400
           20       6000      13000
           30       2500      11000
           40       6500       6500
           50       2100       8200
           60       4200       9000
           70      10000      10000
           80       6100      14000
           90      17000      24000
          100       6900      12000
          110       8300      12000
                    7000       7000