Friday, November 27, 2015

Fill null values with previous values, until next come in the row

want to fill the null values with previous name ('Orange' and 'mangoes') after spending few time i have build a query for this same.


Scenario - 1
Mean while i also found one function which can ease your work with the help of "last_value".
feasibility version - 10g, 11g 12c




Scenario - 2
feasibility version - all versions of Oracle


select 
  id, 
  substr(max(name_copy) over (order by id),8) name_copy ,
  value,
  city
from (
select 
  id, 
  value, 
  city,
  case when name is not null then to_char(row_number() over (order by id),'fm0000000') || name end name_copy  from test
)




Thursday, November 19, 2015

Change orientation of table, (Rotate table) rows to column

Some time we also use the terms to "denormalize rows" / "row flattening", here is sample query which can gave the fare idea to accomplish this task with the help of query.

Actual result:
select row_number() over(partition by deptno  order by 1) as rn, ename,deptno
from emp
where deptno = 10
/

   RN ENAME                    DEPTNO
----- -------------------- ----------
    1 CLARK                        10
    2 KING                         10
    3 MILLER                       10


Required result:
1  with t as
  2  (
  3     select
  4             row_number() over(partition by deptno  order by 1) as rn,
  5             ename,
  6             deptno
  7     from emp
  8     where deptno = 10)
  9  select ename1,ename2,ename3 from
 10  (
 11  (select ename  as ename1 ,deptno  from t where rn = 1) e1
 12  left outer join
 13  (select ename  as ename2 ,deptno from t where rn = 2) e2 on e2.deptno= e1.deptno
 14  left outer join
 15* (select ename  as ename3 ,deptno from t where rn = 3) e3 on e3.deptno= e1.deptno)
SQL> /

ENAME1               ENAME2               ENAME3
-------------------- -------------------- --------------------
CLARK                KING                 MILLER


LISTAGG function giving extra spaces in result

Lets reproduce the error:

create table emp1 as select * from emp;

alter table emp modify ename nvarchar2(20);


SQL> SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
  2   FROM emp GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------------------------------
        10  C L A R K, K I N G, M I L L E R
        20  A D A M S, F O R D, J O N E S, S C O T T, S M I T H
        30  A L L E N, B L A K E, J A M E S, M A R T I N, T U R N E R, W A R D

CAUSE
LISTAGG function returns varchar2, if we execute query on column of type nvarchar2 then value will startimplicit conversion to varchar2.

SOLUTION
use LISTAGG function always on column of type VARCHAR.


Wednesday, November 4, 2015

Share files with Virtual machine

Some case arise when some body is working on Virtual Machine like we need to copy large data onto the virtaul machine if machine specially is Linux.

How to share folder with linux/unix?, if yiu are using VMWare then it is pretty simple.

Some persons are using SAMBA server to access share folder on virtual machines installed with UNIX/LINUX server.

What is required:-
Before proceeding further you should have linux OS, installed with GCC/GCC+ and dependent libraries rpm's. While fresh installation of OS these RPM will be available in "Development kit".

After this install "Install VMWare Tools".
How to install:
  • Move to VM >Install VMWare Tools.
  • you can see *.tar file will open
  • Move and extract it on desktop
  • Double click on *.pl file and follow the steps and accept all the paths.

1. Virtaul machine should be in Power Off mode.
2. Open setting for virtual machine.



3. Enable the "Shared Folder" option under "option" tab.
4. Add any folder from local drive. as below in image



5. Share directory you can find under "\mnt\hgfs\".

Thanks for follow up!!!!!!!! like if it works
web stats