Home » SQL & PL/SQL » SQL & PL/SQL » How to do merge report in SQL
How to do merge report in SQL [message #252124] Tue, 17 July 2007 22:04 Go to next message
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 #252126 is a reply to message #252124] Tue, 17 July 2007 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>i am writing the if-else query but does not work
My car does not work. Please tell me how to fix it.

Please read & FOLLOW the posting guidelines as stated in the STICKY post at the top of this forum.
Re: How to do merge report in SQL [message #252168 is a reply to message #252124] Wed, 18 July 2007 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to:
1/ Read the stickies (as Ana said) and follow them
2/ Don't post doc file which no one will download. Only text (txt) or bitmap (bmp) are safe.

Regards
Michel
Re: How to do merge report in SQL [message #252170 is a reply to message #252168] Wed, 18 July 2007 01:16 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Thanks next time I will upload text file. My mistake.

Rgds
Re: How to do merge report in SQL [message #252189 is a reply to message #252170] Wed, 18 July 2007 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But currently you have no answer.

Regards
Michel
Re: How to do merge report in SQL [message #252195 is a reply to message #252189] Wed, 18 July 2007 02:11 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
I know, i will ready on oracle site Sad

i am attaching the txt attachment

Thanks in advance guys

Rgds
syd
Re: How to do merge report in SQL [message #252204 is a reply to message #252195] Wed, 18 July 2007 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Smile
Re: How to do merge report in SQL [message #252211 is a reply to message #252206] Wed, 18 July 2007 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
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 #252215 is a reply to message #252211] Wed, 18 July 2007 02:54 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Thanks for your quick reply.
i will do this tomorrow and update you. as it is 6pm here in sydney.

rgds
Re: How to do merge report in SQL [message #252764 is a reply to message #252215] Thu, 19 July 2007 22:13 Go to previous messageGo to next message
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 #252798 is a reply to message #252764] Fri, 20 July 2007 01:03 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Could you please give some sample rows from this call table?
Re: How to do merge report in SQL [message #252799 is a reply to message #252764] Fri, 20 July 2007 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't read something that is not formatted.
Check yourself your data for ID 300 as you didn't share it with us.

Regards
Michel
Re: How to do merge report in SQL [message #252807 is a reply to message #252799] Fri, 20 July 2007 01:20 Go to previous messageGo to next message
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 Go to previous messageGo to next message
skooman
Messages: 912
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 #252813 is a reply to message #252807] Fri, 20 July 2007 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hey! What's your problem with format?

Now, I stop helping you until you follow the guidelines.

Regards
Michel
Re: How to do merge report in SQL [message #252814 is a reply to message #252809] Fri, 20 July 2007 01:30 Go to previous message
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
Previous Topic: Re: mySQL select statement
Next Topic: top 50
Goto Forum:
  


Current Time: Thu Dec 08 03:45:31 CST 2016

Total time taken to generate the page: 0.13215 seconds