Archive

Archive for the ‘Technical’ Category

Transform from vertical rows to horizontal columns in oracle sql

Syntax:

————

select
Primary column,
max((case vertical_column_name when  ‘val1’
THEN  ‘display value’
else  null
end )) col1,

max((case vertical_column_name when  ‘val2’
THEN  ‘display value’
else  null
end )) col2,

max((case vertical_column_name when  ‘val3’
THEN  ‘display value’
else  null
end )) col3
from

table1 t1

where  conditions
group by primary_column;

Example:

—————

SELECT

st.roll_no,

max((case Phone_number_type when  ‘1’
THEN  Phone_number
else  null
end )) Land_Line_Number,

max((case Phone_number_type when  ‘2’
THEN  Phone_number
else  null
end )) Hand_Phone_Number

FROM

STUDENTS st

WHERE

st.rollno between 100 and 200

GROUP BY st.rollno;

 

 

 

 

Advertisements
Categories: SQL, Technical

add indent or tab to the beginning of the line in VI editor

open the vi editor

press Esc, :> — for one indentation

press Esc, :5> — for one indentation at line 5

press Esc, :5,10 > — for one indentation from line 5 to 10

Categories: Technical, VI editor

set Print area for excel using xml /Print certain columns in excel

Use the following XML tags to print only certain area in Excel (Note: excel should have only one table otherwise it wont work)

<x:ExcelName>

<x:Name>Print_Area</x:Name>

<x:SheetIndex>1</x:SheetIndex>

<x:Formula>=Sheet1!$A:$C</x:Formula>

</x:ExcelName>

Categories: Technical

Grep Before and after lines in unix

Syntax: Before lines (3- is the number of lines before your search text here)

grep -B3 “Your search text” your file name

Syntax: After Lines (3- is the number of lines after your search text here)

grep -A3 “Your search text” your file name

 

Categories: Technical, unix commands

How to remove cap in the barcode generated in unix from postscript

printf(“0 setlinecap\n”);

Print the above line to the output postscript file before generating the barcode….

 

 

Table not listed under connections in SQLDeveloper

When I created a connection with an existing user name, i was able to connect to the sql server but in the Tables tree i cannot see any tables list.

Solution: Use another user who has access to the table list(i.e. user sa)the user which i tried previously is not having access to the table listing, thats why the table tree was empty

Categories: PL/SQL, SQL

Hierarchical Query in ORACLE SQL

Hierarchical queries are suitable for tables which is having self foreign key references.

SQL: create table hierarchy(Person_id number primary key, name varchar2(20),ancestor number references hierarchy(person_id));

INSERT SQL:

insert into hierarchy values(1,’Great Grand Father’,”);

insert into hierarchy values(2,’Grand Father’,1);

insert into hierarchy values(3,’Father’,2);
insert into hierarchy values(4,’Son1′,3);
insert into hierarchy values(5,’Son2′,3);
insert into hierarchy values(6,’Son3′,3);
insert into hierarchy values(7,’Grand Son1′,4);
insert into hierarchy values(8,’Grand Son1′,5);
SELECT * from hierarchy;

PERSON_ID NAME ANCESTOR
2 Grand Father 1
3 Father 2
4 Son1 3
5 Son2 3
6 Son3 3
7 Grand Son1 4
8 Grand Son1 5
1 Great Grand Father

Hierarchical Query:-

SELECT LEVEL,PERSON_ID,NAME
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY ANCESTOR=PRIOR PERSON_ID;

LEVEL PERSON_ID NAME
1 1 Great Grand Father
2 2 Grand Father
3 3 Father
4 4 Son1
5 7 Grand Son1
4 5 Son2
5 8 Grand Son1
4 6 Son3

For better understanding, hierarchical query in tree structure

SELECT LEVEL,PERSON_ID,LPAD(‘ ‘,LEVEL*2,’.’) || NAME
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY ANCESTOR=PRIOR PERSON_ID;

LEVEL PERSON_ID LPAD(”,LEVEL..
1 1 . Great Grand Father
2 2 … Grand Father
3 3 ….. Father
4 4 ……. Son1
5 7 ……… Grand Son1
5 9 ……… Grand Son2
5 10 ……… Gra Son3
4 5 ……. Son2
5 8 ……… Grand Son1
4 6 ……. Son3
5 12 ……… grand son2
5 11 ……… grand son1

ORDER SIBLINGS BY:-

SELECT LEVEL,PERSON_ID,LPAD(‘ ‘,LEVEL*2,’.’) || NAME
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID LPAD(”,LEVEL..
1 1 . Great Grand Father
2 2 … Grand Father
3 3 ….. Father
4 4 ……. Son1
5 10 ……… Gra Son3
5 7 ……… Grand Son1
5 9 ……… Grand Son2
4 5 ……. Son2
5 8 ……… Grand Son1
4 6 ……. Son3
5 11 ……… grand son1
5 12 ……… grand son2

SYS_CONNECT_BY_PATH in Hierarchical Query:-

SQL:SELECT LEVEL,PERSON_ID,SYS_CONNECT_BY_PATH(NAME,’/’)
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID SYS_CONNECT_B..
1 1 /Great Grand Father
2 2 /Great Grand Father/Grand Father
3 3 /Great Grand Father/Grand Father/Father
4 4 /Great Grand Father/Grand Father/Father/Son1
5 10 /Great Grand Father/Grand Father/Father/Son1/Gra Son3
5 7 /Great Grand Father/Grand Father/Father/Son1/Grand Son1
5 9 /Great Grand Father/Grand Father/Father/Son1/Grand Son2
4 5 /Great Grand Father/Grand Father/Father/Son2
5 8 /Great Grand Father/Grand Father/Father/Son2/Grand Son1
4 6 /Great Grand Father/Grand Father/Father/Son3
5 11 /Great Grand Father/Grand Father/Father/Son3/grand son1
5 12 /Great Grand Father/Grand Father/Father/Son3/grand son2

CONNECT_BY_ROOT in Hierarchical Query:-

SELECT LEVEL,PERSON_ID,NAME,CONNECT_BY_ROOT NAME AS ANCE
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID NAME ANCE
1 1 Great Grand Father Great Grand Father
2 2 Grand Father Great Grand Father
3 3 Father Great Grand Father
4 4 Son1 Great Grand Father
5 10 Gra Son3 Great Grand Father
5 7 Grand Son1 Great Grand Father
5 9 Grand Son2 Great Grand Father
4 5 Son2 Great Grand Father
5 8 Grand Son1 Great Grand Father
4 6 Son3 Great Grand Father
5 11 grand son1 Great Grand Father
5 12 grand son2 Great Grand Father

SQL:SELECT LEVEL,PERSON_ID,NAME,CONNECT_BY_ROOT NAME AS ANCE
FROM
HIERARCHY
START WITH PERSON_ID=3
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID NAME ANCE
1 3 Father Father
2 4 Son1 Father
3 10 Gra Son3 Father
3 7 Grand Son1 Father
3 9 Grand Son2 Father
2 5 Son2 Father
3 8 Grand Son1 Father
2 6 Son3 Father
3 11 grand son1 Father
3 12 grand son2 Father

Exclude some branches from the tree structure:-

SELECT LEVEL,PERSON_ID,NAME
FROM
HIERARCHY
START WITH PERSON_ID=1
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
AND PERSON_ID != 5
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID NAME
1 1 Great Grand Father
2 2 Grand Father
3 3 Father
4 4 Son1
5 10 Gra Son3
5 7 Grand Son1
5 9 Grand Son2
4 6 Son3
5 11 grand son1
5 12 grand son2

WHERE CLAUSE IN Hierarchical Query:-

SELECT LEVEL,PERSON_ID,NAME
FROM
HIERARCHY
WHERE PERSON_ID != 5
START WITH PERSON_ID=1
CONNECT BY  ANCESTOR=PRIOR PERSON_ID
ORDER SIBLINGS BY NAME;

LEVEL PERSON_ID NAME
1 1 Great Grand Father
2 2 Grand Father
3 3 Father
4 4 Son1
5 10 Gra Son3
5 7 Grand Son1
5 9 Grand Son2
5 8 Grand Son1
4 6 Son3
5 11 grand son1
5 12 grand son2

 

Categories: SQL, Technical