Tag Archives: sql

one more stragg

select   to_char(     sum(       power(100,rownum-1)*       deptno     ),     ‘FM99G99G99G99G99′,     ‘NLS_NUMERIC_CHARACTERS=,;’   ) deptlist from dept DEPTLIST       ————— 40;30;20;10 I also wrote about distinct listagg. The same applies for sum distinct. select   to_char(     sum(power(1e3,d-1)*deptno),     ‘FM999G999G999′,     ‘NLS_NUMERIC_CHARACTERS=,;’   ) deptsum,   to_char(     sum(distinct power(1e2,d-1)*deptno),     ‘FM99G99G99′,     ‘NLS_NUMERIC_CHARACTERS=,;’   ) deptsumdist,   to_char(     sum(power(1e1,d-1)),     ‘FM9G9G9′,     ‘NLS_NUMERIC_CHARACTERS=,;’   ) deptcount,   to_char(     sum(power(1e4,c-1)*comm),     ‘FM9999G9999G9999G9999G9999′,     ‘NLS_NUMERIC_CHARACTERS=,;’   ) commlist from ( […]

rowid of the last insert

If you look for the last insert result, check returning into. Is it identity column, the rowid, any expression, get it back SQL> var r varchar2(24) SQL> var x number SQL> var d varchar2(30) SQL> insert into t values (default)   returning rowid,x,sysdate into :r,:x,:d; 1 row created. SQL> print R ——————– AAAaFTAAIAAAAILAAD          X ———- […]