If you need to compare two text files in Unix, you're mostly likely to use the diff command.
Today I'll talk about the simplest scenario: you want to compare two files and understand if there are any differences.
Suppose you have two files in /tmp directory:
/tmp/1.txt:
aaa
bbb
ccc
ddd
eee
fff
ggg
and /tmp/2.txt:
bbb
c c
ddd
eee
fff
ggg
hhh
I have deliberately created them so short and simple - this way it's easier to explain how the comparison works. If there are no differences between the files, you will see no output, but if two text files are indeed different, all the text mismatches will be highlighted using the standard diff output:
$ diff /tmp/1.txt /tmp/2.txt
1d0
< aaa
3c2
< ccc
---
> c c
7a7
> hhh
Lines like "1d0" and "3c2" are the coordinates and types of the differences between the two compared files, while lines like "< aaa" and "> hhh" are the differences themselves.
Diff change notation includes 2 numbers and a character between them. Characters tell you what kind of change was discovered:
d - a line was deleted
c - a line was changed
a - a line was added
Number to the left of the character gives you the line number in the original (first) file, and the number to the right of the character tells you the line number in the second file used in comparison.
So, looking at the two text files and the diff output above, you can see what happened:
This means that 1 line was deleted. < aaa suggests that the aaa line is present only in the original file:
1d0
< aaa
And this means that the line number 3 has changed. You can see how this confirms that in the first file the line was "ccc", and in the second it now is "c c".
3c2
< ccc
---
> c c
Finally, this confirms that one new line appeared in the second file, it's "hhh" in the line number 7:
7a7
> hhh
That's all you need to know to start playing with text comparisons
Thursday, January 29, 2009
The dots in Gmail Id ??
Is this a bug or a feature???? So, basically it doesn’t reeeaaallly matter if I have dots in my userid or not??? Ok. Suppose my mail id really does contain dots, then what happens? (Stupid question???)
Gmail doesn’t recognize dots (.) as characters within a username. This way, you can add and remove dots to your username for desired address variations. messages sent to your.username@gmail.com and y.o.u.r.u.s.e.r.n.a.m.e@gmail.com are delivered to the same inbox, since the characters in the username are the same.
Keep in mind that hyphens (-) and underscores (_) can’t be used in a Gmail address. Also, usernames are case insensitive, so it doesn’t matter if you enter upper case or lower case letters.
If you created your account with a dot in your username and you wish you hadn’t, you can change your ‘Reply-to address.’ To change your reply-to address:
Click ‘Settings’ at the top of any Gmail page.
Enter your username@gmail.com without a dot in the ‘Reply-to address’ field.
Click ‘Save Changes.’
When you log in to Gmail, you need to enter any dots that were originally defined as part of your username. Note: Google Apps for Your Domain does recognize dots. If you’d like to receive mail with a dot (.) in your username, please ask your domain administrator to add the desired username as a nickname in your user account.
Gmail doesn’t recognize dots (.) as characters within a username. This way, you can add and remove dots to your username for desired address variations. messages sent to your.username@gmail.com and y.o.u.r.u.s.e.r.n.a.m.e@gmail.com are delivered to the same inbox, since the characters in the username are the same.
Keep in mind that hyphens (-) and underscores (_) can’t be used in a Gmail address. Also, usernames are case insensitive, so it doesn’t matter if you enter upper case or lower case letters.
If you created your account with a dot in your username and you wish you hadn’t, you can change your ‘Reply-to address.’ To change your reply-to address:
Click ‘Settings’ at the top of any Gmail page.
Enter your username@gmail.com without a dot in the ‘Reply-to address’ field.
Click ‘Save Changes.’
When you log in to Gmail, you need to enter any dots that were originally defined as part of your username. Note: Google Apps for Your Domain does recognize dots. If you’d like to receive mail with a dot (.) in your username, please ask your domain administrator to add the desired username as a nickname in your user account.
Tuesday, January 20, 2009
SQL - How does one implement IF-THEN-ELSE logic in a SELECT statement?
create table Emp (Empno Number,Ename varchar2(50),Job varchar2(30),Mgr varchar2(50),Hiredate date,Deptno number);
select * from emp
insert into emp values(1,'name1','job1','Manager1','17-Dec-2008',101 );
insert into emp values(2,'name2','job2','Manager2','14-Dec-2008',102 )
insert into emp values(3,'name3','job3','Manager3','1-Dec-2008',102 );
insert into emp values(4,'name4','job4','Manager4','2-Dec-2008',102 );
insert into emp values(5,'name5','job5','Manager5','3-Dec-2008',102 );
create table test (A number,B number, C number) ;
insert into test values(1,2,3);
insert into test values(3,2,3);
insert into test values(2,3,1);
insert into test values(2,1,3);
insert into test values(2,2,1);
How does one implement IF-THEN-ELSE logic in a SELECT statement?
CASE EXPRESSIONS
SELECT ename,deptno,
CASE WHEN deptno = 101 THEN 'Minimum wage'
WHEN deptno > 102 THEN 'Over paid'
ELSE 'Under paid'
END AS "Salary Status"
FROM emp;
Decode() function
select job,deptno,decode(deptno, 101, 'supervisor', 102, 'HR', 'Unknown')
from emp;
Greatest() or least() functions
select a,b,decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B',
'B is greater than A') from test
NVL and NVL2 can be used to test for NULL values.
NVL(a,b) == if 'a' is null then return 'b'.
SELECT ename,nvl(ename, 'No Name') FROM emp;
NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.
SELECT nvl2(ename, 'Do have a name', 'No Name') FROM emp;
COALESCE() Function
COALESCE() returns the first expression that is not null. Example:
SELECT 'Dear '|| COALESCE(ename, 'Sir or Madam') FROM emp;
NULLIF() Function
NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:
SELECT NULLIF(ename, ename) FROM emp;
--dont forget to create table Emp (Empno Number,Ename varchar2(50),Job varchar2(30),Mgr varchar2(50),Hiredate date,Deptno number);
select * from emp
insert into emp values(1,'name1','job1','Manager1','17-Dec-2008',101 );
insert into emp values(2,'name2','job2','Manager2','14-Dec-2008',102 )
insert into emp values(3,'name3','job3','Manager3','1-Dec-2008',102 );
insert into emp values(4,'name4','job4','Manager4','2-Dec-2008',102 );
insert into emp values(5,'name5','job5','Manager5','3-Dec-2008',102 );
create table test (A number,B number, C number) ;
insert into test values(1,2,3);
insert into test values(3,2,3);
insert into test values(2,3,1);
insert into test values(2,1,3);
insert into test values(2,2,1);
How does one implement IF-THEN-ELSE logic in a SELECT statement?
CASE EXPRESSIONS
SELECT ename,deptno,
CASE WHEN deptno = 101 THEN 'Minimum wage'
WHEN deptno > 102 THEN 'Over paid'
ELSE 'Under paid'
END AS "Salary Status"
FROM emp;
Decode() function
select job,deptno,decode(deptno, 101, 'supervisor', 102, 'HR', 'Unknown')
from emp;
Greatest() or least() functions
select a,b,decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B',
'B is greater than A') from test
NVL and NVL2 can be used to test for NULL values.
NVL(a,b) == if 'a' is null then return 'b'.
SELECT ename,nvl(ename, 'No Name') FROM emp;
NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.
SELECT nvl2(ename, 'Do have a name', 'No Name') FROM emp;
COALESCE() Function
COALESCE() returns the first expression that is not null. Example:
SELECT 'Dear '|| COALESCE(ename, 'Sir or Madam') FROM emp;
NULLIF() Function
NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:
SELECT NULLIF(ename, ename) FROM emp;
Dont forget to Drop Table YOUR_TABLE after you are done doing this example.
select * from emp
insert into emp values(1,'name1','job1','Manager1','17-Dec-2008',101 );
insert into emp values(2,'name2','job2','Manager2','14-Dec-2008',102 )
insert into emp values(3,'name3','job3','Manager3','1-Dec-2008',102 );
insert into emp values(4,'name4','job4','Manager4','2-Dec-2008',102 );
insert into emp values(5,'name5','job5','Manager5','3-Dec-2008',102 );
create table test (A number,B number, C number) ;
insert into test values(1,2,3);
insert into test values(3,2,3);
insert into test values(2,3,1);
insert into test values(2,1,3);
insert into test values(2,2,1);
How does one implement IF-THEN-ELSE logic in a SELECT statement?
CASE EXPRESSIONS
SELECT ename,deptno,
CASE WHEN deptno = 101 THEN 'Minimum wage'
WHEN deptno > 102 THEN 'Over paid'
ELSE 'Under paid'
END AS "Salary Status"
FROM emp;
Decode() function
select job,deptno,decode(deptno, 101, 'supervisor', 102, 'HR', 'Unknown')
from emp;
Greatest() or least() functions
select a,b,decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B',
'B is greater than A') from test
NVL and NVL2 can be used to test for NULL values.
NVL(a,b) == if 'a' is null then return 'b'.
SELECT ename,nvl(ename, 'No Name') FROM emp;
NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.
SELECT nvl2(ename, 'Do have a name', 'No Name') FROM emp;
COALESCE() Function
COALESCE() returns the first expression that is not null. Example:
SELECT 'Dear '|| COALESCE(ename, 'Sir or Madam') FROM emp;
NULLIF() Function
NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:
SELECT NULLIF(ename, ename) FROM emp;
--dont forget to create table Emp (Empno Number,Ename varchar2(50),Job varchar2(30),Mgr varchar2(50),Hiredate date,Deptno number);
select * from emp
insert into emp values(1,'name1','job1','Manager1','17-Dec-2008',101 );
insert into emp values(2,'name2','job2','Manager2','14-Dec-2008',102 )
insert into emp values(3,'name3','job3','Manager3','1-Dec-2008',102 );
insert into emp values(4,'name4','job4','Manager4','2-Dec-2008',102 );
insert into emp values(5,'name5','job5','Manager5','3-Dec-2008',102 );
create table test (A number,B number, C number) ;
insert into test values(1,2,3);
insert into test values(3,2,3);
insert into test values(2,3,1);
insert into test values(2,1,3);
insert into test values(2,2,1);
How does one implement IF-THEN-ELSE logic in a SELECT statement?
CASE EXPRESSIONS
SELECT ename,deptno,
CASE WHEN deptno = 101 THEN 'Minimum wage'
WHEN deptno > 102 THEN 'Over paid'
ELSE 'Under paid'
END AS "Salary Status"
FROM emp;
Decode() function
select job,deptno,decode(deptno, 101, 'supervisor', 102, 'HR', 'Unknown')
from emp;
Greatest() or least() functions
select a,b,decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B',
'B is greater than A') from test
NVL and NVL2 can be used to test for NULL values.
NVL(a,b) == if 'a' is null then return 'b'.
SELECT ename,nvl(ename, 'No Name') FROM emp;
NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.
SELECT nvl2(ename, 'Do have a name', 'No Name') FROM emp;
COALESCE() Function
COALESCE() returns the first expression that is not null. Example:
SELECT 'Dear '|| COALESCE(ename, 'Sir or Madam') FROM emp;
NULLIF() Function
NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:
SELECT NULLIF(ename, ename) FROM emp;
Dont forget to Drop Table YOUR_TABLE after you are done doing this example.
SQL Tips2
1. To find The Nth Maximum Salary.
SELECT DISTINCT SAL
FROM EMP A
WHERE &N=(SELECT COUNT (DISTINCT B.SAL) FROM EMP B WHERE A.SAL<=B.SAL);
2. To use Exists Clause. Correlated subquery
SELECT DNAME, DEPTNO
FROM DEPT
WHERE EXISTS (SELECT * FROM EMP WHERE
DEPT.DEPTNO = EMP.DEPTNO)
3. To Find The Not Null Column Alone In A Table.
SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE NULLABLE = 'N'
AND TABLE_NAME = 'COUNTRY'
4.To delete The Duplicate Rows Alone in A Table. (frequently asked question)
DELETE DEPT WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM DEPT GROUP BY DEPTNO HAVING COUNT (*) >=1)
5.To find The Max Salary without MAX Function.
1. SELECT DISTINCT SAL
FROM EMP1 WHERE SAL NOT IN
(SELECT SAL FROM EMP1 WHERE SAL < ANY (SELECT SAL FROM EMP1))
2. SELECT SAL FROM EMP WHERE SAL >= ALL (SELECT SAL FROM EMP)
6. To find the database name
select name from v$database;
7. To convert the given number to word
SELECT TO_CHAR (TO_DATE (&NUM,'J'),'JSP') FROM DUAL;
8. What is the difference between unique and distinct keyword in Oracle.
To summarise:
True:
* Only one primary key is allowed per table (any number of unique keys are allowed).
* Primary key columns automatically become NOT NULL (unique keys can contain nulls).
* UNIQUE and DISTINCT are synonymous in a SELECT list.
Not true:
* A unique key cannot be referenced in a foreign key definition (it can)
* A primary key will have a unique index (it might not)
* DISTINCT and UNIQUE handle LOBs differently (they do not)
* UNIQUE is Oracle syntax while DISTINCT is ANSI (both appear to be ANSI-compliant according to the SQL*Plus flagger option, although I couldn't find this standard documented. Oracle did not support SELECT UNIQUE until 8.1.5.)
9. What is the difference between union and union all
UNION
1. returns all distinct rows selected by either query
2. does the multi level sorting on data starting from first column
3. due to sorting query performance goes down dramatically.
UNION ALL
1. returns all the rows selected by either query including all duplicates.
2. No sorting is applied on the data.
3. No performance issue in this set operation. And it is advisable to use union all instead of union where ever applicable.
SELECT DISTINCT SAL
FROM EMP A
WHERE &N=(SELECT COUNT (DISTINCT B.SAL) FROM EMP B WHERE A.SAL<=B.SAL);
2. To use Exists Clause. Correlated subquery
SELECT DNAME, DEPTNO
FROM DEPT
WHERE EXISTS (SELECT * FROM EMP WHERE
DEPT.DEPTNO = EMP.DEPTNO)
3. To Find The Not Null Column Alone In A Table.
SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE NULLABLE = 'N'
AND TABLE_NAME = 'COUNTRY'
4.To delete The Duplicate Rows Alone in A Table. (frequently asked question)
DELETE DEPT WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM DEPT GROUP BY DEPTNO HAVING COUNT (*) >=1)
5.To find The Max Salary without MAX Function.
1. SELECT DISTINCT SAL
FROM EMP1 WHERE SAL NOT IN
(SELECT SAL FROM EMP1 WHERE SAL < ANY (SELECT SAL FROM EMP1))
2. SELECT SAL FROM EMP WHERE SAL >= ALL (SELECT SAL FROM EMP)
6. To find the database name
select name from v$database;
7. To convert the given number to word
SELECT TO_CHAR (TO_DATE (&NUM,'J'),'JSP') FROM DUAL;
8. What is the difference between unique and distinct keyword in Oracle.
To summarise:
True:
* Only one primary key is allowed per table (any number of unique keys are allowed).
* Primary key columns automatically become NOT NULL (unique keys can contain nulls).
* UNIQUE and DISTINCT are synonymous in a SELECT list.
Not true:
* A unique key cannot be referenced in a foreign key definition (it can)
* A primary key will have a unique index (it might not)
* DISTINCT and UNIQUE handle LOBs differently (they do not)
* UNIQUE is Oracle syntax while DISTINCT is ANSI (both appear to be ANSI-compliant according to the SQL*Plus flagger option, although I couldn't find this standard documented. Oracle did not support SELECT UNIQUE until 8.1.5.)
9. What is the difference between union and union all
UNION
1. returns all distinct rows selected by either query
2. does the multi level sorting on data starting from first column
3. due to sorting query performance goes down dramatically.
UNION ALL
1. returns all the rows selected by either query including all duplicates.
2. No sorting is applied on the data.
3. No performance issue in this set operation. And it is advisable to use union all instead of union where ever applicable.
SQL Tips
1. Multiple row generation from DUAL
select rownum i from dual connect by level <= 16
Above query works only in 10g
select rownum from dual a, dual b connect by level <>This query works on both 9i and 10g
2. Lock Query
SELECT s.username dbuser,
owner || '.' || object_name "Object",
s.osuser,
s.PROGRAM,
lk.sid,
lk.type lock_type,
decode(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.lmode)) lockmode,
decode(request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.request)) requestmode
FROM v$lock lk,
v$session s,
dba_objects ao
WHERE lk.lmode > 1
AND object_name LIKE 'JAI%'
AND s.username IS NOT NULL
AND lk.sid = s.sid
AND ao.object_id(+) = lk.id1
To find and kill the locking program..
select session_id from dba_dml_locks where name = 'JAI_RCV_HEADERS' ;
select sid, serial# from v$session where sid in ( );
alter system kill session ',';
repeat alter stmt for all sids obt. in 2nd query
3. Desc query
In oracle, you would have used desc to describe the table. Now let us see, how to generate DESC output through sql query.
SELECT column_name || ' ' || decode(nullable, 'N', 'not null', ' ') || ' ' || data_type ||
decode(data_type, 'NUMBER', decode('' || nvl(data_precision, -1), '-1', '', '(' || data_precision || ',' || data_scale || ')'),
'(' || data_length || ')') fields
FROM all_tab_columns
WHERE TABLE_NAME = 'PO_VENDORS';
4. How to disable a not null constraint.
In case you have created a database column as 'not null'. And you don't want it to be 'Not null' then how will you change it?
eg:
create table temp_temp ( a number not null, b number not null)
And your want to remove "Not null" constraint for columns A.
Ans:
sql> desc temp_temp:
Name Null? Type
------------------------------- -------- ----
A NOT NULL NUMBER
B NOT NULL NUMBER
Get the Not null Constraint name from the database.
SQL> select constraint_name, search_condition from all_constraints where table_name='TEMP_TEMP';
CONSTRAINT_NAME SEARCH_CONDITION
----------------- ---------------------
SYS_C00258673 "A" IS NOT NULL
SYS_C00258674 "B" IS NOT NULL
In the results look for the row with name of the column(like "A" is not null) in the search condition. Take the corresponding constraint_name
SQL> alter table temp_temp drop constraint SYS_C00258673;
Table altered.
SQL> desc temp_temp
Name Null? Type
------------------------------- -------- ----
A NUMBER
B NOT NULL NUMBER
Not null constraint is dropped from the Table.
5. How to convert row values into columns. or how to perform multi dimensional query?
Run the script for this example:
--drop table temp_temp ; --if there is any already present
create table temp_temp ( dept varchar2(100), year number ,amount number)
insert into temp_temp values('A',2005,100);
insert into temp_temp values('B',2005,500);
insert into temp_temp values('C',2005,344);
insert into temp_temp values('A',2006,400);
insert into temp_temp values('B',2006,122);
select * from temp_temp;
For Eg Dept Year Amount
A 2005 100
B 2005 500
C 2005 344
A 2006 400
B 2006 122
Here you need to display the report in following format. That is comparative sales analysis of each department between 2005 and 2006.
Dept 2005 2006
A 100 400
B 500 122
C 344 0
The challenge is to bring the row values into columns. And here is the solution.
SELECT dept, sum(decode(year,'2005',amount,0)) "2005",
sum(decode(year,'2006',amount,0)) "2006"
from temp_temp
group by dept;
6. How to give if condition within the column.
This is so simple problem, you can use Case when clause to do that.
select ename, eid,case when salary <=1000 then 'A'
when salary <=2000 then 'B'
when salary <=3000 then 'C'
when salary <=4000 then 'D'
when salary <=5000 then 'E'
else 'F'
end from emp;
But how will you do switch-case without use when clause?
Here is the query to do the same stuff.
select ename,
eid,
decode(greatest(salary,0),least(salary,1000),'A',decode(greatest(salary,1001)
,least(salary,2000),'B',decode(greatest(salary,2001),least(salary,3000),'C',decode(greatest (salary,3001),least(salary,4000),'D',decode(greatest(salary,4001),least(salary,5000),'E','F')))))
from emp order by 3;
7. How would you select the range of rows from the table?
Say for example, you wanted to select the rows from 2 to 5.
Solution:
select dept, no
from (select dept, rownum no from temp_temp)
where no between 2 and 5;
8. How can one dump/ examine the exact content of a database column?
SELECT DUMP (col1) FROM tab1 WHERE cond1 = val1;
DUMP (COL1)
----------------
Typ=96 Len=4: 65,66,67,32
For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.
select rownum i from dual connect by level <= 16
Above query works only in 10g
select rownum from dual a, dual b connect by level <>This query works on both 9i and 10g
2. Lock Query
SELECT s.username dbuser,
owner || '.' || object_name "Object",
s.osuser,
s.PROGRAM,
lk.sid,
lk.type lock_type,
decode(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.lmode)) lockmode,
decode(request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.request)) requestmode
FROM v$lock lk,
v$session s,
dba_objects ao
WHERE lk.lmode > 1
AND object_name LIKE 'JAI%'
AND s.username IS NOT NULL
AND lk.sid = s.sid
AND ao.object_id(+) = lk.id1
To find and kill the locking program..
select session_id from dba_dml_locks where name = 'JAI_RCV_HEADERS' ;
select sid, serial# from v$session where sid in ( );
alter system kill session ',';
repeat alter stmt for all sids obt. in 2nd query
3. Desc query
In oracle, you would have used desc to describe the table. Now let us see, how to generate DESC output through sql query.
SELECT column_name || ' ' || decode(nullable, 'N', 'not null', ' ') || ' ' || data_type ||
decode(data_type, 'NUMBER', decode('' || nvl(data_precision, -1), '-1', '', '(' || data_precision || ',' || data_scale || ')'),
'(' || data_length || ')') fields
FROM all_tab_columns
WHERE TABLE_NAME = 'PO_VENDORS';
4. How to disable a not null constraint.
In case you have created a database column as 'not null'. And you don't want it to be 'Not null' then how will you change it?
eg:
create table temp_temp ( a number not null, b number not null)
And your want to remove "Not null" constraint for columns A.
Ans:
sql> desc temp_temp:
Name Null? Type
------------------------------- -------- ----
A NOT NULL NUMBER
B NOT NULL NUMBER
Get the Not null Constraint name from the database.
SQL> select constraint_name, search_condition from all_constraints where table_name='TEMP_TEMP';
CONSTRAINT_NAME SEARCH_CONDITION
----------------- ---------------------
SYS_C00258673 "A" IS NOT NULL
SYS_C00258674 "B" IS NOT NULL
In the results look for the row with name of the column(like "A" is not null) in the search condition. Take the corresponding constraint_name
SQL> alter table temp_temp drop constraint SYS_C00258673;
Table altered.
SQL> desc temp_temp
Name Null? Type
------------------------------- -------- ----
A NUMBER
B NOT NULL NUMBER
Not null constraint is dropped from the Table.
5. How to convert row values into columns. or how to perform multi dimensional query?
Run the script for this example:
--drop table temp_temp ; --if there is any already present
create table temp_temp ( dept varchar2(100), year number ,amount number)
insert into temp_temp values('A',2005,100);
insert into temp_temp values('B',2005,500);
insert into temp_temp values('C',2005,344);
insert into temp_temp values('A',2006,400);
insert into temp_temp values('B',2006,122);
select * from temp_temp;
For Eg Dept Year Amount
A 2005 100
B 2005 500
C 2005 344
A 2006 400
B 2006 122
Here you need to display the report in following format. That is comparative sales analysis of each department between 2005 and 2006.
Dept 2005 2006
A 100 400
B 500 122
C 344 0
The challenge is to bring the row values into columns. And here is the solution.
SELECT dept, sum(decode(year,'2005',amount,0)) "2005",
sum(decode(year,'2006',amount,0)) "2006"
from temp_temp
group by dept;
6. How to give if condition within the column.
This is so simple problem, you can use Case when clause to do that.
select ename, eid,case when salary <=1000 then 'A'
when salary <=2000 then 'B'
when salary <=3000 then 'C'
when salary <=4000 then 'D'
when salary <=5000 then 'E'
else 'F'
end from emp;
But how will you do switch-case without use when clause?
Here is the query to do the same stuff.
select ename,
eid,
decode(greatest(salary,0),least(salary,1000),'A',decode(greatest(salary,1001)
,least(salary,2000),'B',decode(greatest(salary,2001),least(salary,3000),'C',decode(greatest (salary,3001),least(salary,4000),'D',decode(greatest(salary,4001),least(salary,5000),'E','F')))))
from emp order by 3;
7. How would you select the range of rows from the table?
Say for example, you wanted to select the rows from 2 to 5.
Solution:
select dept, no
from (select dept, rownum no from temp_temp)
where no between 2 and 5;
8. How can one dump/ examine the exact content of a database column?
SELECT DUMP (col1) FROM tab1 WHERE cond1 = val1;
DUMP (COL1)
----------------
Typ=96 Len=4: 65,66,67,32
For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.
Find the OA Application/IDs/ShortNames
FND_APPLICATION_VL is a view that joins the translated, language
specific values from FND_APPLICATION_TL with the non-displayed
data from FND_APPLICATION to form complete application
entities. Oracle Application Object Library uses this view
to display information in languages installed at your
site
select * from FND_APPLICATION_VL where application_id=702 (your app id)
specific values from FND_APPLICATION_TL with the non-displayed
data from FND_APPLICATION to form complete application
entities. Oracle Application Object Library uses this view
to display information in languages installed at your
site
select * from FND_APPLICATION_VL where application_id=702 (your app id)
Monday, January 19, 2009
User_source view for source code
How to view source code of a Function ?
How to view source code of a Prodcedure ?
How to view source code of a Package ?
How to view source code of a Trigger ?
you can view the source code of a function or a procedure by using data dictionary views. All the PL SQL objects like types, procedure, functions, Packages code can be viewed from a view called “USER_SOURCE“.
User_source data dictionary view provides the source code of all the PL SQL objects.
Each line of code represents a single row in this dictionary view.
If you want to view the source of PL SQL OBJECTS created by you then use dictionary view “USER_SOURCE“.
If you want to view the source of PL SQL OBJECTS created by others(other users) but have an execute permisssion on the OBJECTS, then use dictionary view “ALL_SOURCE“.
IF you have ‘SELECT ANY DICTIONARY’ Previliege, then you can view the source of all the pl/sql objects from the dictionary view DBA_SOURCE.
want to view the code of a FUNCTION in oracle ?
select * from user_source where type='FUNCTION' AND NAME='FUNCTION_NAME' ORDER BY LINE;
want to view the code of a PROCEDURE in oracle ?
select * from USER_SOURCE where type='PROCEDURE' AND NAME='PROCEDURE_NAME' ORDER BY LINE;
want to view the code of a PACKAGE SPECIFICATION in oracle ?
select * from USER_SOURCE where type='PACKAGE' AND NAME='PACKAGE_NAME' ORDER BY LINE;
want to view the code of a PACKAGE BODY in oracle ?
select * from USER_SOURCE where type='PACKAGE BODY' AND NAME='PACKAGE_NAME' ORDER BY LINE;
want to view the code of a TRIGGER in oracle ?
select * from USER_SOURCE where type='TRIGGER' AND NAME='TRIGGER_NAME' ORDER BY LINE;
want to view source code of a procedure created by another user?
select * from user_source where type='PROCEDURE' and NAME='PROCEDURE_NAME' and owner='USERS_NAME' order by line;
want to view the source code of a Function created by another user in oracle ?
select * from user_source where type='FUNCTION' AND OWNER='USERS_NAME' AND NAME='FUNCTION_NAME' ORDER BY LINE;
How to view source code of a Prodcedure ?
How to view source code of a Package ?
How to view source code of a Trigger ?
you can view the source code of a function or a procedure by using data dictionary views. All the PL SQL objects like types, procedure, functions, Packages code can be viewed from a view called “USER_SOURCE“.
User_source data dictionary view provides the source code of all the PL SQL objects.
Each line of code represents a single row in this dictionary view.
If you want to view the source of PL SQL OBJECTS created by you then use dictionary view “USER_SOURCE“.
If you want to view the source of PL SQL OBJECTS created by others(other users) but have an execute permisssion on the OBJECTS, then use dictionary view “ALL_SOURCE“.
IF you have ‘SELECT ANY DICTIONARY’ Previliege, then you can view the source of all the pl/sql objects from the dictionary view DBA_SOURCE.
want to view the code of a FUNCTION in oracle ?
select * from user_source where type='FUNCTION' AND NAME='FUNCTION_NAME' ORDER BY LINE;
want to view the code of a PROCEDURE in oracle ?
select * from USER_SOURCE where type='PROCEDURE' AND NAME='PROCEDURE_NAME' ORDER BY LINE;
want to view the code of a PACKAGE SPECIFICATION in oracle ?
select * from USER_SOURCE where type='PACKAGE' AND NAME='PACKAGE_NAME' ORDER BY LINE;
want to view the code of a PACKAGE BODY in oracle ?
select * from USER_SOURCE where type='PACKAGE BODY' AND NAME='PACKAGE_NAME' ORDER BY LINE;
want to view the code of a TRIGGER in oracle ?
select * from USER_SOURCE where type='TRIGGER' AND NAME='TRIGGER_NAME' ORDER BY LINE;
want to view source code of a procedure created by another user?
select * from user_source where type='PROCEDURE' and NAME='PROCEDURE_NAME' and owner='USERS_NAME' order by line;
want to view the source code of a Function created by another user in oracle ?
select * from user_source where type='FUNCTION' AND OWNER='USERS_NAME' AND NAME='FUNCTION_NAME' ORDER BY LINE;
Tuesday, January 13, 2009
CZ - --Query to find the difference b/w 2 configurations :
--Query to find the difference b/w 2 configurations :
select
CFG.ps_node_name Name,
TXT.TEXT_STR Description,
CFG.item_num_val Quantity
from
CZ_CONFIG_ITEMS CFG,
CZ_INTL_TEXTS TXT,
CZ_PS_NODES ND
where CFG.inventory_item_id is not null
and ND.intl_text_id=TXT.intl_text_id
and ND.ps_node_id=CFG.ps_node_id
and CFG.config_hdr_id = :1
and CFG.config_rev_nbr = :2
MINUS
select
CFG.ps_node_name Name,
TXT.TEXT_STR Description,
CFG.item_num_val Quantity
from
CZ_CONFIG_ITEMS CFG,
CZ_INTL_TEXTS TXT,
CZ_PS_NODES ND
where CFG.inventory_item_id is not null
and ND.intl_text_id=TXT.intl_text_id
and ND.ps_node_id=CFG.ps_node_id
and CFG.config_hdr_id = :3
and CFG.config_rev_nbr = :4
select
CFG.ps_node_name Name,
TXT.TEXT_STR Description,
CFG.item_num_val Quantity
from
CZ_CONFIG_ITEMS CFG,
CZ_INTL_TEXTS TXT,
CZ_PS_NODES ND
where CFG.inventory_item_id is not null
and ND.intl_text_id=TXT.intl_text_id
and ND.ps_node_id=CFG.ps_node_id
and CFG.config_hdr_id = :1
and CFG.config_rev_nbr = :2
MINUS
select
CFG.ps_node_name Name,
TXT.TEXT_STR Description,
CFG.item_num_val Quantity
from
CZ_CONFIG_ITEMS CFG,
CZ_INTL_TEXTS TXT,
CZ_PS_NODES ND
where CFG.inventory_item_id is not null
and ND.intl_text_id=TXT.intl_text_id
and ND.ps_node_id=CFG.ps_node_id
and CFG.config_hdr_id = :3
and CFG.config_rev_nbr = :4
CZ - List published models in Configurator Repository
select (Select vl.application_name
from fnd_application_vl vl
where vl.application_id = ma.fnd_application_id) app_name,
ma.fnd_application_id,
ma.publication_id,
mp.remote_publication_id,
p.Name Model_Name,
p.Desc_text,
ma.publication_mode,
ma.product_key,
(Select mtl.segment1
from mtl_system_items mtl where mtl.inventory_item_id =
ma.inventory_item_id and mtl.organization_id = ma.bom_explosion_org_id) ITEM,
ma.start_date,
ma.disable_date,
ma.server_id,
ma.ui_def_id,
you.Name Usage
--, ma.*
from
cz_model_applicabilities_v ma,
cz_model_publications mp,
cz_model_usages you,
cz_devl_projects p
where
you.model_usage_id = ma.usage_id
and ma.publication_id = mp.publication_id
and p.devl_project_id = ma.model_id
and mp.deleted_flag = 0
and mp.disabled_flag = 0
and ma.inventory_item_id is not null
and fnd_application_id = 880
and language = 'US'
and mp.source_target_flag = 'T'
and Decode (mp.UI_STYLE, 0, 'DHTML', 3, 'APPLET', 7, 'JRAD', 'NA') = 'JRAD'
and upper(mp.publication_mode) =
(
select upper(
COALESCE
(
(select v.profile_option_value
from
fnd_profile_option_values v,
fnd_user you
where
v.level_id = 10004
and you.user_name = FND_GLOBAL.user_name
and v.level_value = you.user_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_responsibility_vl are
where
v.level_id = 10003
and are.responsibility_name = fnd_global.resp_name
and v.level_value = are.responsibility_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_application_vl a
where
v.level_id = 10002
and a.application_name = fnd_global.application_short_name
and v.level_value = a.application_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v
where
v.level_id = 10001
and v.profile_option_id = o.profile_option_id)
)
) PUB_MODE
from
fnd_profile_options o
where
o.profile_option_name = 'CZ_PUBLICATION_MODE'
)
and you.name =
(
select
COALESCE
(
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_user you
where
v.level_id = 10004
and you.user_name = fnd_global.user_name
and v.level_value = you.user_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_responsibility_vl are
where
v.level_id = 10003
and are.responsibility_name = fnd_global.resp_name
and v.level_value = are.responsibility_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_application_vl a
where
v.level_id = 10002
and a.application_name = fnd_global.application_short_name
and v.level_value = a.application_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v
where
v.level_id = 10001
and v.profile_option_id = o.profile_option_id
)
) USAGE
from
fnd_profile_options o
where
o.profile_option_name = 'CZ_PUBLICATION_USAGE'
)
Order by model_name
-----------------------------------------
--debugging scripts :
SELECT * FROM cz.cz_db_logs WHERE message_id='353320' --publication id
SELECT model_id,publication_id from cz_model_publications where export_status = 'ERR'
SELECT intl_text_id, persistent_intl_text_id,text_str,ui_def_id FROM cz_intl_texts where persistent_intl_Text_id = 33125520; --persistent id
SELECT model_id,ui_def_id,intl_text_id,creation_date from cz_intl_texts where persistent_intl_text_id = 33125520 --persistent id
SELECT intl_text_id, text_str, persistent_intl_text_id, seeded_flag, ui_def_id, model_id FROM cz_intl_texts where ui_def_id = 23080 AND deleted_flag = 0;
SELECT * from cz_model_publications WHERE object_type = 'UIT' ;
select ui_def_id from cz_ui_defs c
where c.DEVL_PROJECT_ID=(select max(DEVL_PROJECT_ID)
from cz_devl_projects dp , mtl_system_items_b msi
where dp.inventory_item_id=msi.INVENTORY_ITEM_ID
and segment1= 'AT2200-10H')
from fnd_application_vl vl
where vl.application_id = ma.fnd_application_id) app_name,
ma.fnd_application_id,
ma.publication_id,
mp.remote_publication_id,
p.Name Model_Name,
p.Desc_text,
ma.publication_mode,
ma.product_key,
(Select mtl.segment1
from mtl_system_items mtl where mtl.inventory_item_id =
ma.inventory_item_id and mtl.organization_id = ma.bom_explosion_org_id) ITEM,
ma.start_date,
ma.disable_date,
ma.server_id,
ma.ui_def_id,
you.Name Usage
--, ma.*
from
cz_model_applicabilities_v ma,
cz_model_publications mp,
cz_model_usages you,
cz_devl_projects p
where
you.model_usage_id = ma.usage_id
and ma.publication_id = mp.publication_id
and p.devl_project_id = ma.model_id
and mp.deleted_flag = 0
and mp.disabled_flag = 0
and ma.inventory_item_id is not null
and fnd_application_id = 880
and language = 'US'
and mp.source_target_flag = 'T'
and Decode (mp.UI_STYLE, 0, 'DHTML', 3, 'APPLET', 7, 'JRAD', 'NA') = 'JRAD'
and upper(mp.publication_mode) =
(
select upper(
COALESCE
(
(select v.profile_option_value
from
fnd_profile_option_values v,
fnd_user you
where
v.level_id = 10004
and you.user_name = FND_GLOBAL.user_name
and v.level_value = you.user_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_responsibility_vl are
where
v.level_id = 10003
and are.responsibility_name = fnd_global.resp_name
and v.level_value = are.responsibility_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_application_vl a
where
v.level_id = 10002
and a.application_name = fnd_global.application_short_name
and v.level_value = a.application_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v
where
v.level_id = 10001
and v.profile_option_id = o.profile_option_id)
)
) PUB_MODE
from
fnd_profile_options o
where
o.profile_option_name = 'CZ_PUBLICATION_MODE'
)
and you.name =
(
select
COALESCE
(
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_user you
where
v.level_id = 10004
and you.user_name = fnd_global.user_name
and v.level_value = you.user_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_responsibility_vl are
where
v.level_id = 10003
and are.responsibility_name = fnd_global.resp_name
and v.level_value = are.responsibility_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_application_vl a
where
v.level_id = 10002
and a.application_name = fnd_global.application_short_name
and v.level_value = a.application_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v
where
v.level_id = 10001
and v.profile_option_id = o.profile_option_id
)
) USAGE
from
fnd_profile_options o
where
o.profile_option_name = 'CZ_PUBLICATION_USAGE'
)
Order by model_name
-----------------------------------------
--debugging scripts :
SELECT * FROM cz.cz_db_logs WHERE message_id='353320' --publication id
SELECT model_id,publication_id from cz_model_publications where export_status = 'ERR'
SELECT intl_text_id, persistent_intl_text_id,text_str,ui_def_id FROM cz_intl_texts where persistent_intl_Text_id = 33125520; --persistent id
SELECT model_id,ui_def_id,intl_text_id,creation_date from cz_intl_texts where persistent_intl_text_id = 33125520 --persistent id
SELECT intl_text_id, text_str, persistent_intl_text_id, seeded_flag, ui_def_id, model_id FROM cz_intl_texts where ui_def_id = 23080 AND deleted_flag = 0;
SELECT * from cz_model_publications WHERE object_type = 'UIT' ;
select ui_def_id from cz_ui_defs c
where c.DEVL_PROJECT_ID=(select max(DEVL_PROJECT_ID)
from cz_devl_projects dp , mtl_system_items_b msi
where dp.inventory_item_id=msi.INVENTORY_ITEM_ID
and segment1= 'AT2200-10H')
Customization of Reports in Oracle Apps
Oracle Reports will become a thing of past in Fusion, however it will still demand resources for the next 5yrs or so.
Question: I have been asked to customize Invoice Print program which happens to be an Oracle Report. What will be the steps, that I must follow.
Answer : Follow the steps below.
1. You will be told the name of the existing report that must be customized. Note down the exact name and query that name in Concurrent Program screen. Click on “Copy Program button” selecting checkbox option “Copy Parameters”. This will help you copy the current program definition to custom version of the program.
Also note down the name of the executable as it appears in concurrent program definition screen.
2. In same responsibility i.e. Application Developer, navigate to screen concurrent executable and query on the field labeled "Executable Short Name".
Note down the application within which it is registered. If the application is Oracle Receivables, then you must go to the database server and get hold the file named RAXINV.rdf in $AR_TOP/reports/US.
3. Copy that file to your custom AR Top directory. Basically that is the directory where custom reports for AR will be deployed..
cd $XXAR_TOP/reports/us
cp $AR_TOP/reports/us/RAXINV.rdf $XXAR_TOP/reports/us
Effectively you have now done the following:-
1. Made the custom version of report registered with XXAR application. If you worked for say company named EA, then this might have been $EAAR_TOP/reports/US
2. When you run that report, Oracle concurrent manager will search for that report in $XXAR_TOP/reports/US
The report will be found there, and executed.
Note: We haven’t made any changes as yet. Also, you need to include the new concurrent program name in the relevant request group.
Now you can ftp that report to your pc, make modifications for necessary customizations, and then ftp that piece of rdf back to the server. Run it again, to see it working.
Some important tips:-
1. Avoid writing SQL in format trigger, although in rare cases it becomes necessary to do so.
2. Learn from Oracle's Report, by reverse engineering them.
3. Do not write a formula column for something which can be achieved by amending the query in data group itself.
4. Do not hardcode things like Currency Formatting. Have a look at Oracle's Amount fields, and use the same user exit.
5. srw2.message can be used for minor debugging, as those messages will appear in the log file of the concurrent program.
6. You can set the trace checkbox against the concurrent program definition, to generate SQL Trace. This trace will not produce bind variable values though.
7. Join between two queries in data group will always be outerjoined, by default.
8. Avoid filters on Data Group queries. Try to implement that logic within the query itself.
Reference :http://oracle.anilpassi.com/customization-of-reports-in-oracle-apps.html
Question: I have been asked to customize Invoice Print program which happens to be an Oracle Report. What will be the steps, that I must follow.
Answer : Follow the steps below.
1. You will be told the name of the existing report that must be customized. Note down the exact name and query that name in Concurrent Program screen. Click on “Copy Program button” selecting checkbox option “Copy Parameters”. This will help you copy the current program definition to custom version of the program.
Also note down the name of the executable as it appears in concurrent program definition screen.
2. In same responsibility i.e. Application Developer, navigate to screen concurrent executable and query on the field labeled "Executable Short Name".
Note down the application within which it is registered. If the application is Oracle Receivables, then you must go to the database server and get hold the file named RAXINV.rdf in $AR_TOP/reports/US.
3. Copy that file to your custom AR Top directory. Basically that is the directory where custom reports for AR will be deployed..
cd $XXAR_TOP/reports/us
cp $AR_TOP/reports/us/RAXINV.rdf $XXAR_TOP/reports/us
Effectively you have now done the following:-
1. Made the custom version of report registered with XXAR application. If you worked for say company named EA, then this might have been $EAAR_TOP/reports/US
2. When you run that report, Oracle concurrent manager will search for that report in $XXAR_TOP/reports/US
The report will be found there, and executed.
Note: We haven’t made any changes as yet. Also, you need to include the new concurrent program name in the relevant request group.
Now you can ftp that report to your pc, make modifications for necessary customizations, and then ftp that piece of rdf back to the server. Run it again, to see it working.
Some important tips:-
1. Avoid writing SQL in format trigger, although in rare cases it becomes necessary to do so.
2. Learn from Oracle's Report, by reverse engineering them.
3. Do not write a formula column for something which can be achieved by amending the query in data group itself.
4. Do not hardcode things like Currency Formatting. Have a look at Oracle's Amount fields, and use the same user exit.
5. srw2.message can be used for minor debugging, as those messages will appear in the log file of the concurrent program.
6. You can set the trace checkbox against the concurrent program definition, to generate SQL Trace. This trace will not produce bind variable values though.
7. Join between two queries in data group will always be outerjoined, by default.
8. Avoid filters on Data Group queries. Try to implement that logic within the query itself.
Reference :http://oracle.anilpassi.com/customization-of-reports-in-oracle-apps.html
Subscribe to:
Posts (Atom)