Home » SQL & PL/SQL » SQL & PL/SQL » Need Assistance On PL/SQL Coding (Oracle 6i)
Need Assistance On PL/SQL Coding [message #445594] Tue, 02 March 2010 20:27 Go to next message
seemun
Messages: 1
Registered: March 2010
Junior Member
Hi,

I'm doing the procurement system and I need to generate a report base on the <request_no> to locate each <order_item> record which had ordered for last two years.

For example:
=========

request_no: REQ10010001
Order Item:

ITEM -----> Output
===== =======
1. ITEM-1 -----> SEP'09 (75 pcs), NOV'09 (50 pcs)
2. ITEM-2 -----> DEC'08 (16pcs), AUG'09 (12 pcs), OCT'09 (80 pcs)
3. ITEM-3 -----> SEP'09 (4 pcs)

Note: Generate the output as of today's query date, to locate back the past two years record and count the no. of pieces ordered /per item /per month.

Please advice should I use function or store procedure to return the record?
Please assist to provide the code sample on this for easy reference.

Best regards,
sm

[Updated on: Tue, 02 March 2010 20:34]

Report message to a moderator

Re: Need Assistance On PL/SQL Coding [message #445595 is a reply to message #445594] Tue, 02 March 2010 20:42 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

>Please assist to provide the code sample on this for easy reference.


Realize it is a challenge to provide code when we don't know table or columns names.

[Updated on: Tue, 02 March 2010 21:02]

Report message to a moderator

Re: Need Assistance On PL/SQL Coding [message #445598 is a reply to message #445594] Tue, 02 March 2010 21:51 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I think, one SELECT statement, with GROUP BY item and month-year, will be suffice.

regards,
Delna
Re: Need Assistance On PL/SQL Coding [message #445619 is a reply to message #445594] Tue, 02 March 2010 23:27 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I confirm this can be done in a single SQL statement.

If you post a working Test case: create table and insert statements along with the result you want with these data, we will be able to work with them.

Before please 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, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle RDBMS version with 4 decimals, 6i is NOT an Oracle RDBMS version.

Regards
Michel
Previous Topic: ORA-02049: timeout: distributed transaction waiting for lock
Next Topic: Program Header
Goto Forum:
  


Current Time: Fri Dec 02 16:32:05 CST 2016

Total time taken to generate the page: 0.22017 seconds