Home » SQL & PL/SQL » SQL & PL/SQL » Creating Procedure
Creating Procedure [message #391672] Fri, 13 March 2009 00:19 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

Please find the following issue and give the solution for this.

A new promotion will allow customer to play a game each time they reload their prepaid card.
They will receive their code via SMS.
The table name in ABC is p_gsm. We will refresh it completely by adding it to existing ABC flow
in DEF (just add the table in the list of table already copied into DEF in S_WRITE schemas)
To obtain the list of customer who don't want to receive SMS for this promotion, you need to
filter this table on pr_id field for the LOTTERY promo identifier (LOTTERY identifier = 1000).
We can use XYZ table (in Temp schemas) to store this code as a parameter.

A view already exist in DEF (TEMP schemas). It will be used as list of reload where to
apply promotion (filter with p_gsm to exclude customers who ask to not receive SMS)
The fact we use this view add a constraint for this new flow to wait for the reload flow to be
finished.To only treat "new" records, we will use the same procedure as TEMP promotion.
We will store a LAST_EXEC_DATE parameter in TEMP.XYZ table with the last execution date.
Like that, at the next execution, we can filter the view with this parameter to only select new records.
This promotion will also only be applied on some types of reload (currency reload).

Here is an example of how to filter only Currency reload based on parameter stored into TEMP.XYZ table :
select * from temp.re_view
where currency in (select p_id_d from temp.xyz where p_id_head = 'CURRENCY' and p_txt = 'EUR')

This select only return Currency reloads.

By using this way of work, if a new type of reload is created, only the table TEMP.XYZ should be updated, and not the sql code itself.



For this requirement I need to write a procedure,So can one please give me yot suggestions for writing a procedure.

Thank you.


[EDITED by LF: duplicate message has been deleted / applied [code] tags (which, true, existed in a deleted message)]

[Updated on: Fri, 13 March 2009 01:14] by Moderator

Report message to a moderator

Re: Creating Procedure [message #391674 is a reply to message #391672] Fri, 13 March 2009 00:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
HUH? I do not understand.
What is problem?
What is required for solution?
Re: Creating Procedure [message #391676 is a reply to message #391674] Fri, 13 March 2009 00:30 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi I explained the requirement. So please help me out to write Proedure,

Thank you
Re: Creating Procedure [message #391680 is a reply to message #391676] Fri, 13 March 2009 00:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You are a student, right?
Now, suppose you graduate, suppose you get a job.
Do we have to do your work then too?
Or do you first want us to do your interview for you?

Posting a requirement here, hoping someone will do it for you is not the way to go (as you've been told a zillion times)
Show us YOUR efforts.
Re: Creating Procedure [message #391686 is a reply to message #391680] Fri, 13 March 2009 01:10 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

Thank you very much for your suggestion and you are correct.
But just explain me what to do? then I will write procedure for that.

Thank you.
Re: Creating Procedure [message #391777 is a reply to message #391672] Fri, 13 March 2009 07:28 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
./fa/449/0/
Re: Creating Procedure [message #391779 is a reply to message #391777] Fri, 13 March 2009 07:44 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Laughing This guy cracks me up.

I think the only advice on "what to do" that fits here is:

Hire someone to do it, who knows at least somewhat what he is doing.

./fa/451/0/

Re: Creating Procedure [message #391783 is a reply to message #391779] Fri, 13 March 2009 07:54 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
He provides entertainment to me and some of my colleagues. I do however pity any company that is fooled into taking this guy on.

./fa/1620/0/
Re: Creating Procedure [message #391870 is a reply to message #391672] Sat, 14 March 2009 02:40 Go to previous message
vaibhavk
Messages: 7
Registered: March 2009
Junior Member
Suggestions for writing a procedure... Smile

Create or replace procedure proc_name
AS
--list of variables
Begin
-- PL/SQL Code
End proc_name;

Very Happy Razz
Previous Topic: Looping Insert Trigger
Next Topic: Denormalization
Goto Forum:
  


Current Time: Sun Dec 11 04:16:45 CST 2016

Total time taken to generate the page: 0.14099 seconds