Home » SQL & PL/SQL » SQL & PL/SQL » I need help in writing a stored procedure (Merged 4)
I need help in writing a stored procedure (Merged 4) [message #318015] Mon, 05 May 2008 05:56 Go to next message
alpanadutta
Messages: 7
Registered: May 2008
Junior Member
I am a beginner in PL/SQL. Can anyone help me out in solving this problem? plz!!!


Need to write a stored procedure...Plz help!
Application (application selected from drop down list in web say fax1) > Sub application (say fax1A selected from drop down list)> Location > Queues. On clicking search button.


EXCEPT APPLICATION_ID ALL OTHER PARAMETER ARE OPTIONAL.i.e, Application may not have sub application.In that case

Application> Location> Queue


Below is the example of output count for message queue's:



< 1hrs 1to 2 hrs 2 to 4 hrs < 24 total
_____________________________________________________________
New jersey 10 12 14 15 51



<1 hrs means ( all message queue tht came 1 hr ago)


Tables being used are....
____________________________

Table: QUEUE

APPLICATION_ID say 001
SUB_APPL_NM
PROCESS_NM -----( MESSAGE QUEUE ) say MQ01
LAST_UPD_TMS ( TIMESTAMP) DATE WITH TIME
PROFILE_ID Say 0001
LOCATION ID say 01




TABLE: LOC

LOCATION_ID
LOCATION_NM


TABLE: APPLICATION

APPLICATION_ID
APPLICATION_NM

[MERGED by LF]

[Updated on: Mon, 05 May 2008 06:43] by Moderator

Report message to a moderator

Re: NEED your HELP! [message #318018 is a reply to message #318015] Mon, 05 May 2008 06:12 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I read your post twice, but I have no idea what you are talking about. I understand that you need a stored procedure. But, what is it supposed to do? "Clicking search button"? There are no buttons in stored procedures.

Did you check OraFAQ Forum Guide? It should help you to properly ask a question and help us to understand it. CREATE TABLE and INSERT INTO sample data could be a good idea. Your attempt to write a procedure would be even better. Because, I'm afraid that you won't get solution ready for use.

Your code first, then ask a question about a specific problem you have. "Give me the procedure" probably won't work.
Re: NEED your HELP! [message #318019 is a reply to message #318015] Mon, 05 May 2008 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need to post in bold but you need to read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

SQL> select deptno,
  2         count(case when sal < 1000 then 1 end) "< 1000",
  3         count(case when sal >= 1000 and sal < 2000 then 1 end) "< 2000",
  4         count(case when sal >= 2000 and sal < 3000 then 1 end) "< 3000",
  5         count(case when sal >= 3000 then 1 end) ">= 3000",
  6         count(*) "Total"
  7  from emp
  8  group by deptno
  9  order by deptno
 10  /
    DEPTNO     < 1000     < 2000     < 3000    >= 3000      Total
---------- ---------- ---------- ---------- ---------- ----------
        10          0          1          1          1          3
        20          1          1          1          2          5
        30          1          4          1          0          6

3 rows selected.

[Edit:] Forgot: also learn to post a meaningful title.

Regards
Michel

[Updated on: Mon, 05 May 2008 06:26]

Report message to a moderator

can anyone please help me out? [message #318022 is a reply to message #318015] Mon, 05 May 2008 06:32 Go to previous messageGo to next message
alpanadutta
Messages: 7
Registered: May 2008
Junior Member
I need to write a stored procedure which will show the count of all the message queues(as shown in the slide)
I am a beginner to pl/sql.can anyone please help me out?


The parameters that we are passing are
Application (mandatory)
sub application ( optional.ie, an application may not have sub application)
Profile (optional)
location (optional)
queue( optional)


<1 indicates all message queues that came 1 hr ago
and so on...

Table being used are (oracle 9i):


QUEUE
----------

APPLICATION_ID say 001
SUB_APPL_NM
PROCESS_NM -----( MESSAGE QUEUE ) say MQ01
LAST_UPD_TMS ( TIMESTAMP) DATE WITH TIME
PROFILE_ID Say 0001
LOCATION ID say 01




LOC
-----------

LOCATION_ID
LOCATION_NM


APPLICATION
--------------

APPLICATION_ID
APPLICATION_NM
Re: NEED your HELP! [message #318025 is a reply to message #318019] Mon, 05 May 2008 06:39 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel, are you saying that you, actually, understood the whole thing? Wow, you are a genius (and I'm the opposite)!

[EDIT: removed message received via PM as @alpanadutta created a new topic (now being merged with the original one)]

[Updated on: Mon, 05 May 2008 06:42]

Report message to a moderator

NEED OUTPUT LIKE THE ATTACHED SCREEN [message #318034 is a reply to message #318015] Mon, 05 May 2008 06:53 Go to previous messageGo to next message
alpanadutta
Messages: 7
Registered: May 2008
Junior Member
I am a beginner to pl/sql.Need you people's help.Anyone who can give answer will be highly appreciated...
How I can get the output like the attached image shown by passing following parameters using a stored procedure in oracle.can anyone help me out?
plz!


The parameters that we are passing are
Application (mandatory)
sub application ( optional.ie, an application may not have sub application)
Profile (optional)
location (optional)
queue( optional)


In the image output, '< 1' indicates all message queues that came 1 hr ago


Table being used are (oracle 9i):


QUEUE
----------

APPLICATION_ID say 001
SUB_APPL_NM
PROCESS_NM -----( MESSAGE QUEUE ) say MQ01
LAST_UPD_TMS ( TIMESTAMP) DATE WITH TIME
PROFILE_ID Say 0001
LOCATION ID say 01




LOC
-----------

LOCATION_ID
LOCATION_NM


APPLICATION
--------------

APPLICATION_ID
APPLICATION_NM


Re: NEED your HELP! [message #318035 is a reply to message #318025] Mon, 05 May 2008 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Michel, are you saying that you, actually, understood the whole thing?

The whole thing, not at all, just suspect what it can be from the output. Wink

By the way, it seems OP "PMed" all moderators. Mad

Regards
Michel
Re: NEED your HELP! [message #318038 is a reply to message #318035] Mon, 05 May 2008 07:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Mon, 05 May 2008 13:56
By the way, it seems OP "PMed" all moderators. Mad


Nope, apparently only the likables. I didn't get one Wink
Re: NEED your HELP! [message #318039 is a reply to message #318038] Mon, 05 May 2008 07:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
alpanadutta, stop posting the same message over and over.
Don't you see it's been answered already?!
Re: NEED your HELP! [message #318044 is a reply to message #318039] Mon, 05 May 2008 07:22 Go to previous messageGo to next message
alpanadutta
Messages: 7
Registered: May 2008
Junior Member
where on earth u got the answer? tell me!!!
Re: NEED your HELP! [message #318045 is a reply to message #318044] Mon, 05 May 2008 07:33 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Quote:
where on earth u got the answer? tell me!!!

What's with the excessive punctuation? Most people would consider that to be rude. Read through this entire thread again. Michel has supplied an answer (impressive considering how poorly constructed your question was). If it does not meet your requirements, then state whay and restate you requirements in this thread (i.e. do NOT start a new thread)
PROBLEM: NEED TO WRITE A STORED PROCEDURE [message #318063 is a reply to message #318015] Mon, 05 May 2008 08:55 Go to previous messageGo to next message
alpanadutta
Messages: 7
Registered: May 2008
Junior Member
CAN ANYONE HELP ME OUT? PLZ!!!!

NEED TO WRITE A STORED PROCEDURE FOR THE BELOW REQUIREMENT. ( SCRREN SHOT ATTACHED)

REQUIREMENT:
------------

Pass the following parameter to a stored procedure:

Application_name (mandatory)
sub application name ( optional.ie, an application may not have sub application)
Profile id (optional)
location id (optional)



To get the DESIRED output as shown in the screen,

Take difference of current date & update_timestamp (this is the last update timestamp field in date and time say 01-may-2008 11:23:12 pm) . Convert the differences into minutes then..

A) find record COUNT [ie, COUNT[PROCESS_NM] wch is less than 60 min old.


B) Find the records COUNT [ie, COUNT[PROCESS_NM] which is more than 59 minutes but less than 120 minutes old

C) Find the record COUNT [ie, COUNT[PROCESS_NM] which is more than 119 minutes but less than 24X60 minutes old


D) Find record COUNT [ie, COUNT[PROCESS_NM] Which is more than 1x24x60 min ( 1 day) old.

Get the count for all the above conditions ( ie, less than 1 hr ...)

and finally the TOTAL count.





Table: Process
______________

APPLICATION_NAME
SUB_APPLI_NAME
PROFILE_ID
PROCESS_ID --- ( PROCESS COUNT)
UPDATE_TIMESTAMP ( LAST UPDATED TIMESTAMP)
LOCATION_ID
Re: PROBLEM: NEED TO WRITE A STORED PROCEDURE [message #318065 is a reply to message #318063] Mon, 05 May 2008 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in URL above

Re: PROBLEM: NEED TO WRITE A STORED PROCEDURE [message #318067 is a reply to message #318063] Mon, 05 May 2008 09:01 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Had to count to 10 on that one. You have already been given the answer, please stick to the original thread..
Re: PROBLEM: NEED TO WRITE A STORED PROCEDURE [message #318069 is a reply to message #318063] Mon, 05 May 2008 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can add as much as topics you want, they will be merged and you will NOT get more answers, you'll just irritate those who can help you, especially if you post in UPPER CASE.

Regards
Michel
Re: NEED your HELP! [message #318070 is a reply to message #318038] Mon, 05 May 2008 09:04 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Frank wrote on Mon, 05 May 2008 14:00
Michel Cadot wrote on Mon, 05 May 2008 13:56
By the way, it seems OP "PMed" all moderators. Mad


Nope, apparently only the likables. I didn't get one Wink


Hmm, guess I'm a likable too Razz
Re: PROBLEM: NEED TO WRITE A STORED PROCEDURE [message #318071 is a reply to message #318063] Mon, 05 May 2008 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, STOP PM me your questions I already said you I don't answer forum question in PM.

Regards
Michel
Re: PROBLEM: NEED TO WRITE A STORED PROCEDURE [message #318073 is a reply to message #318071] Mon, 05 May 2008 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Michel Cadot wrote on Mon, 05 May 2008 07:05
In addition, STOP PM me your questions I already said you I don't answer forum question in PM.



+1 same for me
Re: NEED your HELP! [message #318078 is a reply to message #318019] Mon, 05 May 2008 09:16 Go to previous messageGo to next message
alpanadutta
Messages: 7
Registered: May 2008
Junior Member
Michel Cadot wrote on Mon, 05 May 2008 06:18
You don't need to post in bold but you need to read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

SQL> select deptno,
  2         count(case when sal < 1000 then 1 end) "< 1000",
  3         count(case when sal >= 1000 and sal < 2000 then 1 end) "< 2000",
  4         count(case when sal >= 2000 and sal < 3000 then 1 end) "< 3000",
  5         count(case when sal >= 3000 then 1 end) ">= 3000",
  6         count(*) "Total"
  7  from emp
  8  group by deptno
  9  order by deptno
 10  /
    DEPTNO     < 1000     < 2000     < 3000    >= 3000      Total
---------- ---------- ---------- ---------- ---------- ----------
        10          0          1          1          1          3
        20          1          1          1          2          5
        30          1          4          1          0          6

3 rows selected.

[Edit:] Forgot: also learn to post a meaningful title.

Regards
Michel




DID YOU UNDERSTAND WHAT I MEAN?? ONCE AGAIN I PUT MY QUESTION HERE AFTER SOME MODIFICATION AS IT WAS QUITE CONFUSING...

HERE IT IS:

CAN ANYONE HELP ME OUT? PLZ!!!!

NEED TO WRITE A STORED PROCEDURE FOR THE BELOW REQUIREMENT. ( SCRREN SHOT ATTACHED)

REQUIREMENT:
------------

Pass the following parameter to a stored procedure:

Application_name (mandatory)
sub application name ( optional.ie, an application may not have sub application)
Profile id (optional)
location id (optional)



To get the DESIRED output as shown in the screen,

Take difference of current date & update_timestamp (this is the last update timestamp field in date and time say 01-may-2008 11:23:12 pm) . Convert the differences into minutes then..

A) find record COUNT [ie, COUNT[PROCESS_NM] wch is less than 60 min old.


B) Find the records COUNT [ie, COUNT[PROCESS_NM] which is more than 59 minutes but less than 120 minutes old

C) Find the record COUNT [ie, COUNT[PROCESS_NM] which is more than 119 minutes but less than 24X60 minutes old


D) Find record COUNT [ie, COUNT[PROCESS_NM] Which is more than 1x24x60 min ( 1 day) old.

Get the count for all the above conditions ( ie, less than 1 hr ...)

and finally the TOTAL count.





Table: Process
______________

APPLICATION_NAME
SUB_APPLI_NAME
PROFILE_ID
PROCESS_ID --- ( PROCESS COUNT)
UPDATE_TIMESTAMP ( LAST UPDATED TIMESTAMP)
LOCATION_ID

DO REPLY ONLY IF YOU CAN SOLVE! THANKS....
Re: NEED your HELP! [message #318080 is a reply to message #318078] Mon, 05 May 2008 09:20 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
alpanadutta wrote on Mon, 05 May 2008 16:16

DO REPLY ONLY IF YOU CAN SOLVE! THANKS....



Tell your users to hit Alt-F4, that will solve your & our problems Smile
Re: NEED your HELP! [message #318081 is a reply to message #318078] Mon, 05 May 2008 09:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
CAN ANYONE HELP ME OUT? PLZ!!!!

No as long as you follow the rules and don't post in upper case.

We give you a solution in SQL, try to understand it, it is far better than any PL/SQL.

Quote:
DO REPLY ONLY IF YOU CAN SOLVE! THANKS....

You meant unless we do your job.
By the way, reply those who want to reply.

Regards
Michel
Re: NEED your HELP! [message #318083 is a reply to message #318078] Mon, 05 May 2008 09:25 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Quote:
DO REPLY ONLY IF YOU CAN SOLVE!

Remember folks, only post if you can supply the correct answer. No guesses, no requests for clarification, no further posts unless you are posting the correct answer Smile
Re: NEED your HELP! [message #318086 is a reply to message #318078] Mon, 05 May 2008 09:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
alpanadutta wrote on Mon, 05 May 2008 16:16
DO REPLY ONLY IF YOU CAN SOLVE! THANKS....


PLZ!!! HIT CAPS LOCK 1 MORE TME!!

[Updated on: Mon, 05 May 2008 09:29]

Report message to a moderator

Re: NEED your HELP! [message #318087 is a reply to message #318080] Mon, 05 May 2008 09:27 Go to previous messageGo to next message
alpanadutta
Messages: 7
Registered: May 2008
Junior Member
MarcS wrote on Mon, 05 May 2008 09:20
alpanadutta wrote on Mon, 05 May 2008 16:16

DO REPLY ONLY IF YOU CAN SOLVE! THANKS....



Tell your users to hit Alt-F4, that will solve your & our problems Smile





I BET U THAT PROBABLY U NEVER GIVE ANSWERS TO ANY QUESTIONS...
RATHER YOU LIKE WRITING STUPID STUFFS LIKE THIS Laughing
Re: NEED your HELP! [message #318088 is a reply to message #318083] Mon, 05 May 2008 09:27 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
pablolee wrote on Mon, 05 May 2008 16:25
Quote:
DO REPLY ONLY IF YOU CAN SOLVE!

Remember folks, only post if you can supply the correct answer. No guesses, no requests for clarification, no further posts unless you are posting the correct answer Smile


So, I guess you do know the answer pablolee but are unwilling to share Razz
Re: NEED your HELP! [message #318089 is a reply to message #318087] Mon, 05 May 2008 09:28 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
alpanadutta wrote on Mon, 05 May 2008 16:27
MarcS wrote on Mon, 05 May 2008 09:20
alpanadutta wrote on Mon, 05 May 2008 16:16

DO REPLY ONLY IF YOU CAN SOLVE! THANKS....



Tell your users to hit Alt-F4, that will solve your & our problems Smile





I BET U THAT PROBABLY U NEVER GIVE ANSWERS TO ANY QUESTIONS...
RATHER YOU LIKE WRITING STUPID STUFFS LIKE THIS Laughing



http://www.flickclip.com/images/flickimages/lookwhostalking.jpg
Re: NEED your HELP! [message #318090 is a reply to message #318088] Mon, 05 May 2008 09:29 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Laughing It would involve plagiarism from an earlier post tho' Smile
Re: I need help in writing a stored procedure (Merged 4) [message #318134 is a reply to message #318015] Mon, 05 May 2008 18:39 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> DID YOU UNDERSTAND WHAT I MEAN??

I am afraid Michel guessed very well what you want to get and sent you query for obtaining that info. Your only task is to adjust it to your tables instead of the HR ones, and, well, somehow put it into the desired procedure; as you did not specified its interface, it is really hard to guess how it should look like.

Just for clarification: stored procedure is stored on server and shall not interact with users - this is client's job. Again, no info about client (from the picture, it does not look like like SQL*Plus); however the output shall be done on its side.

[Edit: Added last paragraph]

[Updated on: Mon, 05 May 2008 23:14]

Report message to a moderator

Previous Topic: reading variable from text file
Next Topic: send sms
Goto Forum:
  


Current Time: Thu Dec 08 20:23:20 CST 2016

Total time taken to generate the page: 0.20883 seconds