| How to do merge report in SQL [message #252124] |
Tue, 17 July 2007 22:04  |
sydney1
Messages: 65
Registered: November 2006
Location: sydney
|
Member |
|
|
Hi gurus,
I want to generate a report on oracle database. i have two fields
in table called PRIORITY AND IMPACT. I want to calculate the SEVERITY from Priority and Impact. like
Severity = Impact + Priority
Please find the attached chart.
i am writing the if-else query but does not work. Please help
declare
Severity VARCHAR2(20);
begin
for RRow in (
Select IMPACT,Priority
from call
where folder like 'bank'
)
loop
IF (RRow.IMPACT='High') THEN
IF RRow.Priority='Low' THEN
Severity:=3;
ELSIF RRow.Priority='Medium' THEN
Severity:=2;
ELSIF RRow.Priority='High' THEN
Severity:=1;
END IF;
ELSE
IF (RRow.IMPACT='Medium') THEN
IF RRow.Priority='Low' THEN
Severity:=4;
ELSIF RRow.Priority='Medium' THEN
Severity:=3;
ELSIF RRow.Priority='High' THEN
Severity:=2;
END IF;
ELSE
IF (RRow.IMPACT='Low') THEN
IF RRow.Priority='Low' THEN
Severity:=5;
ELSIF RRow.Priority='Medium' THEN
Severity:=4;
ELSIF RRow.Priority='High' THEN
Severity:=3;
END IF;
END IF;
DBMS_OUTPUT.ENABLE (1000000);
DBMS_OUTPUT.PUT_LINE (Severity);
end loop;
EXCEPTION
WHEN OTHERS THEN
RAISE;
end;
Please correct if something wrong.
Thanks for your help
Rgds
syd
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to do merge report in SQL [message #252204 is a reply to message #252195] |
Wed, 18 July 2007 02:36  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
severity = decode(impact,'High',1,'Medium',2,'Low',3)
+ decode(priority,'High',1,'Medium',2,'Low',3)
- 1
Regards
Michel
[Updated on: Wed, 18 July 2007 02:36] Report message to a moderator |
|
|
|
| Re: How to do merge report in SQL [message #252206 is a reply to message #252204] |
Wed, 18 July 2007 02:42 |
sydney1
Messages: 65
Registered: November 2006
Location: sydney
|
Member |
|
|
Thanks Michel,
First i am not developer so sorry if i ask stupid question.
u mean i have to declare
severity = decode(impact,'High',1,'Medium',2,'Low',3)
+ decode(priority,'High',1,'Medium',2,'Low',3)
- 1
then i copy my code like
begin
for RRow in (
Select IMPACT,Priority
from call
where folder like 'bank'
)
---
--
-
-
-
appreciate your help
see net make people so close
|
|
|
|
| Re: How to do merge report in SQL [message #252211 is a reply to message #252206] |
Wed, 18 July 2007 02:49 |
|
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I meant to know the severity you just have to do:
select decode(impact,'High',1,'Medium',2,'Low',3)
+ decode(priority,'High',1,'Medium',2,'Low',3)
- 1
from call
/
No need of PL/SQL code.
Regards
Michel
|
|
|
|
|
|
| Re: How to do merge report in SQL [message #252764 is a reply to message #252215] |
Thu, 19 July 2007 22:13 |
sydney1
Messages: 65
Registered: November 2006
Location: sydney
|
Member |
|
|
Michel,
i am trying but i am not getting anything under Severity after i ran the below SQl
select ID,
decode(impact,
'High(Department affected)',1,
'Medium(Group/Unit affected)',2,
'Low(1 person affected)',3)
+decode(priority,
'2.High',1,
'3.Medium',2,
'4.Low',3)
-1
"Severity"
from call
where ID=300
;
ID Severity
---------- ----------
300
Thanks for your help.
Rgds
syd
|
|
|
|
|
|
|
|
| Re: How to do merge report in SQL [message #252807 is a reply to message #252799] |
Fri, 20 July 2007 01:20 |
sydney1
Messages: 65
Registered: November 2006
Location: sydney
|
Member |
|
|
Please find the output for 3 rows
ID IMPACT PRIORITY
---------- ------------------------------ ----------
298 Low ( 1 person affected) 4. Low
300 High (Department affected) 2. High
376 High (Department affected) 3. Medium
Rgds
Syd
|
|
|
|
| Re: How to do merge report in SQL [message #252809 is a reply to message #252807] |
Fri, 20 July 2007 01:22 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
In you sample rows, there is a space between High and (Department. Hence the fact that the decode doesn't recognize it.
Edit: there is a space between the numbers and dots (4.) and the description of the priority too. Please note that decode works very accurate...
[Updated on: Fri, 20 July 2007 01:24] Report message to a moderator |
|
|
|
|
|
| Re: How to do merge report in SQL [message #252814 is a reply to message #252809] |
Fri, 20 July 2007 01:30 |
sydney1
Messages: 65
Registered: November 2006
Location: sydney
|
Member |
|
|
Thanks skooman.
That works.
really appreciated your help.
ORAFAQ please keep this up.
warm Rgds
Syd
Michel Please don't say not to help. I will go thro guidliness and post the question with format.
sorry for unconvienence.
have a good one.
keep smiling and helping
rgds
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
