Puzzle

[google2012]

[quote name='Silver_mani' timestamp='1339646458' post='1301965209']
naaku aithe rendu functions same anipistunnayi
[/quote]


same in d sense result lo.. but query syntax completely different mama

SQL> SELECT ename,
DECODE(deptno, 10, 'Finanace',
20, 'RESEARCH',
30, 'SALES',
40, 'OPERATIONS',
'UNKNOWN') AS department
FROM emp
WHERE rownum < 4
/

ENAME DEPARTMENT
---------- ----------
SMITH RESEARCH
ALLEN SALES
WARD SALES

SELECT ename,
CASE deptno
WHEN 10 THEN 'Finance'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'SALES'
WHEN 40 THEN 'OPERATIONS'
ELSE
'UNKNOWN'
END AS department
FROM emp
WHERE rownum < 4
/

ENAME DEPARTMENT
---------- ----------
SMITH RESEARCH
ALLEN SALES
WARD SALES

[ChampakDas]

Decode ah vammo...nenu first time vintunna...even I havent heard from anyone before..googling 2mins...
[quote name='Silver_mani' timestamp='1339646414' post='1301965206']


............ Decode ki case ki teda enti mama ?
[/quote]

[Silver_mani]

[quote name='google2012' timestamp='1339646638' post='1301965214']


same in d sense result lo.. but query syntax completely different mama

SQL> SELECT ename,
DECODE(deptno, 10, 'Finanace',
20, 'RESEARCH',
30, 'SALES',
40, 'OPERATIONS',
'UNKNOWN') AS department
FROM emp
WHERE rownum < 4
/

ENAME DEPARTMENT
---------- ----------
SMITH RESEARCH
ALLEN SALES
WARD SALES

SELECT ename,
CASE deptno
WHEN 10 THEN 'Finance'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'SALES'
WHEN 40 THEN 'OPERATIONS'
ELSE
'UNKNOWN'
END AS department
FROM emp
WHERE rownum < 4
/

ENAME DEPARTMENT
---------- ----------
SMITH RESEARCH
ALLEN SALES
WARD SALES
[/quote]


yaa rendu same results istayi mama ... telusu .... kani edo logic miss aitunnam ... same result vaste rendu functions enduku cheppu

[Silver_mani]

[quote name='ChampakDas' timestamp='1339646650' post='1301965217']
Decode ah vammo...nenu first time vintunna...even I havent heard from anyone before..googling 2mins...
[/quote]


same case functon lage mama ... case when ...else alage decode kuda ...but with different structure ....

[google2012]

[quote name='Silver_mani' timestamp='1339646750' post='1301965219']


yaa rendu same results istayi mama ... telusu .... kani edo logic miss aitunnam ... same result vaste rendu functions enduku cheppu
[/quote]

yeah avunu...but in CASE stmnt we can do range analysis but for decode we cant
decode can process null but case cant

plus not in all cases you will have the same result... most of the times you will get wrong results like X etc and even performance wise case is faster in getting results

[Silver_mani]

[quote name='google2012' timestamp='1339647011' post='1301965226']

yeah avunu...but in CASE stmnt we can do range analysis but for decode we cant
[b]decode can process null but case cant[/b]

plus not in all cases you will have the same result... most of the times you will get wrong results like X etc and even performance wise case is faster in getting results
[/quote]

[ChampakDas]

Ok DECODE Oracle lo undhi MS SQL lo ledhu..I did not work on Oracle before.
Uniformity and structured query is the advantage of CASE.
It turns out that CASE has more functionality than DECODE. Cool!
[quote name='Silver_mani' timestamp='1339646750' post='1301965219']


yaa rendu same results istayi mama ... telusu .... kani edo logic miss aitunnam ... same result vaste rendu functions enduku cheppu
[/quote]

[ChampakDas]

Wrong. CASE statements for sure can handle NULL.
[quote name='google2012' timestamp='1339647011' post='1301965226']

yeah avunu...but in CASE stmnt we can do range analysis but for decode we cant
[b]decode can process null but case cant[/b]

plus not in all cases you will have the same result... most of the times you will get wrong results like X etc and even performance wise case is faster in getting results
[/quote]

[google2012]

[quote name='Silver_mani' timestamp='1339646750' post='1301965219']


yaa rendu same results istayi mama ... telusu .... kani edo logic miss aitunnam ... same result vaste rendu functions enduku cheppu
[/quote]

case lo u can use ranges like sal< 40000 but in decode u cant use range's.. decode kanna case powerful.. chala extra things a case can do but not decode... but whatever decode does case do the same but with more powerful function

[Silver_mani]

[quote name='ChampakDas' timestamp='1339647187' post='1301965235']
Wrong. CASE statements for sure can handle NULL.
[/quote]



ela

[Silver_mani]

[quote name='google2012' timestamp='1339647278' post='1301965240']

case lo u can use ranges like sal< 40000 but in decode u cant use range's.. decode kanna case powerful.. chala extra things a case can do but not decode... but whatever decode does case do the same but with more powerful function
[/quote]
agreed

[google2012]

[quote name='ChampakDas' timestamp='1339647187' post='1301965235']
Wrong. CASE statements for sure can handle NULL.
[/quote]


nope... case cant handle null, it handles in a different way. we faced this issue many times in getting wrong resluts while using NULL in case

[gafoor]

[img]http://img266.imageshack.us/img266/6374/brahmi6.gif[/img]

[ChampakDas]

SELECT Column1, CASE NULL
WHEN NULL THEN 'EMPTY'
ELSE 'SOMESTA'
END case_null
FROM Firsttable
[quote name='Silver_mani' timestamp='1339647280' post='1301965241']



ela
[/quote]

[google2012]

[quote name='ChampakDas' timestamp='1339647704' post='1301965274']
SELECT Column1, CASE NULL
WHEN NULL THEN 'EMPTY'
ELSE 'SOMESTA'
END case_null
FROM Firsttable
[/quote]

oka sari oka DB create chesi dantlo case toh and decode to NULL value paas chesi result chudu oka sari.. u will know the difference as how case handles NULL ani...