Topic: SQL*Plus and PL/SQL >> Plz Help Me......!
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Plz Help Me......! |
| busi |
Posted: Jan 24, 2007 07:15:43 AM |
Total Post: 13
Joined: Jan, 2007
|
1) Wat is Analytic functions? wat is Adnantages of That ?
2)Wat is difference b/w case,decode,nvl2?
3)I have got one quary...
For Example...i Created one table as Login with Amount and Usertype as Fields....
EX:
ID Amount UserType
1 2400 NGO
2 3000 Academic
3 4000 Academic
4 500 Foreign Delegate
1)Now i want to get sum of Foreign Delegate And Sum of Non Foreign Delegates
2) I want to get it in single quary
3)I don't want to use Union ....
4)How to use Indexs..?Wat is use of That ? When To use That...? |
|
|
 Jayanta |
| Posted: Jan 24, 2007 12:17:25 PM |  |
|
Total Post: 479
Joined: Feb, 2006
|
nvl:
converts null to actual value and can be used with date, character and number data type.
eg: nvl(commission,0).
but data type must match, eg: if commission is number datatype u cant code
nvl(commission,'zero')
the syntax of nvl2 is:
nvl2(exp1,exp2,exp3) exp=expression
here if exp1 is not null nvl2 returns expr2, and if exp1 is null exp3 is returned by nvl2
here exp1 can have any data type and exp2 and exp3 can have any data type
but if exp2 and exp3 r of different data type oracle converts exp3 to the data type of exp3 before comparing them unless exp3 is a null.
the syntax of nullif is:
nullif(exp1,exp2)
compares exp1 and exp2 and returns null if they r equal.or the first expression if they r not equal.but one thing u cant specify null for expression 1.
the syntax for coalesce is:
coalesce(exp1,exp2,exp3,.....expn)
and returns the first not null expression.
eg: if exp1 is null then exp2 is returned if exp2 is not null, exp3 is returned if exp3 is not null and exp1 and exp2 r both null and so on
hope this will help
regards
jayanta
|
|
|
|
|
| Jayanta |
| Posted: Jan 24, 2007 12:47:28 PM | |
|
Total Post: 479
Joined: Feb, 2006
|
to provide the logic of if-then-else oracle incorporated case and decode functions.
they are like this:
the syntax of case is:
case exp when comp_exp1 then return_exp1
[when comp_exp2 then return_exp2
when comp_exp3 then return_exp3
else else_exp]
end
here oracle first searches for the first when ..then pair for which expr is equal to comparison_expr and returns return_expr. if none of the when ..then pair meet the condition and an else exists oracle returns expression with else clause. otherwise oracle returns null.
n.b: none of the expressions in this syntax can be null, and that u cant specify null for any return/ else expression.
syntax for decode:
decode(column/expression, search1, result1
[search2, result2
................
................
, default]
it compares each search value with the column/expression and then returns the result.
the other purpose of decode is to transform datavalues at retrieval time e.g:
decode( region, 'N','north',
'S', 'south',
'E', 'east',
'W', 'west''
'UNKNOWN')
explanation:
if the region column is found to store N it will return north, if S then it will return south....... and if no match found will return the default unknown.
also remember that if default value is ommitted null is returned. where a search value does not match anyof the result value.
case is new to oracle 9i and complies with ansi sql. where as decode is specific to oracle syntax.
regards
jayanta
|
|
|
|
|
| busi |
| Posted: Jan 25, 2007 06:59:58 AM | |
|
Total Post: 13
Joined: Jan, 2007
|
Thank You Jayanata....
Regards
Narendra
|
|
|
|
|
| Muks |
| Posted: Jan 26, 2007 03:24:40 AM | |
|
Total Post: 36
Joined: Jan, 2007
|
Hi,
First thing you have not mentioned the column with which you distinguish the foreign and nonforiegn delegate..I assmed it to be user type, based on which I am writing the query here...Hope it might help you
select a.amount,a.usertype,b.sumamt
from login a,(select usertype,sum(amount) sumamt
from login
group by usertype) b
where a.usertype= b.usertype;
Cheers!!
Mukta
|
|
|
|
|
| Muks |
| Posted: Jan 26, 2007 03:29:00 AM | |
|
Total Post: 36
Joined: Jan, 2007
|
you can customize this query as per your requirment...
It might be helpful
|
|
|
|
|
| busi |
| Posted: Jan 26, 2007 11:28:02 PM | |
|
Total Post: 13
Joined: Jan, 2007
|
HI
Thank You Mukta....
Regards
Narendra Yarapathineni
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 843 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 268 |
| neeraj sharma | 252 |
| Ramesh Jois | 245 |
| Bishal Khetan | 207 |
|
|
