Alphabets
WITH AlphaBet AS
( SELECT CHR((ROWNUM + 64)) AS Character
FROM DUAL CONNECT BY level <= 157)
SELECT * FROM AlphaBet;
------------------------------------------------------------------------
Some Analytic Functions
SELECT G.*,ROW_NUMBER() OVER(PARTITION BY G.DEPTNO ORDER BY G.EMPNO)
FROM EMP G;
SELECT G.DEPTNO,G.ENAME,SUM(G.SAL) OVER (PARTITION BY G.DEPTNO ORDER BY G.DEPTNO) FROM EMP G ;
SELECT FIRST_VALUE(G.EMPNO) OVER() FROM EMP G;
---------------------------------------------------------------------------
Level Query
select level,lpad(' ',3*(level)) || VC_designation s,nn_desgcode dsg,nn_repdesgcode rcd,
SYS_CONNECT_BY_path (nn_repdesgcode,'/') op,
substr(SYS_CONNECT_BY_path (nn_repdesgcode,'/'),3,
length((SYS_CONNECT_BY_path (nn_repdesgcode,'/')))) hjdd
from hr_designation_mast where nn_repdesgcode is not null
start with nn_repdesgcode is not null
connect by prior nn_desgcode=nn_repdesgcode
No comments:
Post a Comment