Home » SQL & PL/SQL » SQL & PL/SQL » Package, Function Application between multiple tables Query (Oracle 9i)
Package, Function Application between multiple tables Query [message #350603] Thu, 25 September 2008 10:27 Go to next message
Bonita
Messages: 32
Registered: June 2008
Member
First of all. Please accept my apology if my question is very simple to your gurus. But it is very important to me. I really appreciate your time and reply.
Question

Two tables EMP and EMP1
EMP
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10);

EMP1
EMPNO NUMBER(4) NOT NULL,
M_Date date);


need to list all EMPNO from EMP, and its max date from EMP1 using following syntax.

SELECT EMPNO,
ENAME,
XX_Package.XX_Max_Function_days(EMPNO) AS MAX_DATE
FROM EMP;

The result should look like based on following testing cases

EMPNO ENAME MAX_DATE
7369 SMITH 10/1/2005
7499 ALLEN 12/1/2005
7600 MIKE NULL

I know how to get above result using table join. I do hope that someone could help me out to create package, function or whatever in order for me to get same results using following syntax

SELECT EMPNO,
ENAME,
XX_Package.XX_Max_Function(EMPNO) AS Max_DATE
FROM EMP;


Testing Case

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10));
INSERT INTO EMP VALUES (7369, 'SMITH');
INSERT INTO EMP VALUES (7499, 'ALLEN');
INSERT INTO EMP VALUES (7600, 'MIKE');

CREATE TABLE EMP1 (EMPNO NUMBER(4) NOT NULL,
M_Date date);
INSERT INTO EMP1 VALUES (7369, to_date('01/01/2004','mm/dd/yyyy'));
INSERT INTO EMP1 VALUES (7369, to_date('10/01/2005','mm/dd/yyyy'));
INSERT INTO EMP1 VALUES (7499, to_date('10/01/2004','mm/dd/yyyy'));
INSERT INTO EMP1 VALUES (7499, to_date('12/01/2005','mm/dd/yyyy'));
INSERT INTO EMP1 VALUES (7499, to_date('05/01/2005','mm/dd/yyyy'));


Once again, thank you very much for your time and reply. Please give me your entire Package , function codes as well.
Shocked
Re: Package, Function Application between multiple tables Query [message #350605 is a reply to message #350603] Thu, 25 September 2008 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tell us why you want to do something that is awful for the performances point of view when you can do it in SQL.

Regards
Michel

[Updated on: Thu, 25 September 2008 10:59]

Report message to a moderator

Re: Package, Function Application between multiple tables Query [message #350615 is a reply to message #350603] Thu, 25 September 2008 10:49 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
Thanks, Michel.

In reality, the function contains very complicated calculation between 1:n cardinality. This application is mainly used to design reporting universe ( Business Objects or Crystal ). If we use table joins, it causes too complicated JOINs between tables joins due to end-user doesn't has idea of how to select object between hundreds objects.

I do see this skill is widely used in real-time Oracle reporting environment ( using PL/SQL ), and its performance is quite satisfied( around 10M data size ). My problem is that I don't know how.

If you or someone could give me your codes, I really appreciated.

THANK YOU.
Re: Package, Function Application between multiple tables Query [message #350624 is a reply to message #350615] Thu, 25 September 2008 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How could we "give [...]entire Package , function codes as well" if this is not the real problem?

Regards
Michel
Re: Package, Function Application between multiple tables Query [message #350629 is a reply to message #350624] Thu, 25 September 2008 12:47 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
This is real application in reality. As I said, this application is used widely in our system with good performance.

Could you or someone drop codes based on our testing cases ????

Personally I'm presumming is simple if knowing how passing value in package, function.... but I don't know how.

THANK YOU.
Re: Package, Function Application between multiple tables Query [message #350633 is a reply to message #350629] Thu, 25 September 2008 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: Package, Function Application between multiple tables Query [message #350634 is a reply to message #350633] Thu, 25 September 2008 13:06 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
Can someone else hele me out based on my testing data ? Your 10 minutes could save me big days and weeks.

Thank you for your reply and time.
Re: Package, Function Application between multiple tables Query [message #350637 is a reply to message #350634] Thu, 25 September 2008 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You seems so desesperate I post it but I don't know what you can do with it if you don't know the basic of PL/SQL language:
SQL> create or replace package mypkg as 
  2    function myfct (p_empno number) return date;
  3  end;
  4  /

Package created.

SQL> create or replace package body mypkg as 
  2    function myfct (p_empno number) return date
  3    is
  4       mx date;
  5    begin
  6       select max(m_date) into mx from emp1 where empno = p_empno;
  7       return mx;
  8    end;
  9  end;
 10  /

Package body created.

SQL> SELECT EMPNO, ENAME, mypkg.myfct (EMPNO) AS Max_DATE
  2  FROM EMP0;
     EMPNO ENAME      MAX_DATE
---------- ---------- -------------------
      7369 SMITH      01/10/2005 00:00:00
      7499 ALLEN      01/12/2005 00:00:00
      7600 MIKE

3 rows selected.

Don't forget this will be a very slow process.

Regards
Michel

[Edit: add missing words, it seems I can't write "know"]

[Updated on: Thu, 25 September 2008 13:59]

Report message to a moderator

Re: Package, Function Application between multiple tables Query [message #350641 is a reply to message #350637] Thu, 25 September 2008 13:48 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
I really appreciated your time and helps. I know you will help me out.

You're correct. I am quite new to PL/SQL. Even though I don't understand all posts so far. But,however, learning from experts, like you, is enjoyable to have every day. I can go home with smile. Laughing

THANK YOU VERY MUCH./ You're BEST !!!!
Re: Package, Function Application between multiple tables Query [message #350703 is a reply to message #350603] Thu, 25 September 2008 23:53 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There are other alternatives as well, some of which might perform better that the plsql option, if you do not have some requirement that limits you to using plsql. Consider this:

SQL> --
SQL> -- this uses the scalar subquery
SQL> -- it is very similar to the plsql option you have been provided, but can perform much faster
SQL> -- because it does not do context switching from sql to plsql
SQL> -- calling plsql from sql though quite useful at times, is also very expensive
SQL> --
SQL> select a.empno
  2        ,a.ename
  3        ,(select max(b.m_date) from emp1 b where b.empno = a.empno) max_date
  4  from emp0 a
  5  /

     EMPNO ENAME      MAX_DATE
---------- ---------- ---------
      7369 SMITH      01-OCT-05
      7499 ALLEN      01-DEC-05
      7600 MIKE


If you want to learn more, do some research on scalar subquerys.

Additionally since you seem to be getting into this kind of coding I would also suggest you take a look at Oracle Analytics. Between scalar subquerys, and Oracle Analytics, your SQL coding will take a major leap forward in sophistication and reliability. They are both worth knowing well.

Kevin Meade's OraFaq Blog

A Simple Example of Oracle Analytics: Running Totals
Good luck, Kevin
Re: Package, Function Application between multiple tables Query [message #350714 is a reply to message #350703] Fri, 26 September 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There are other alternatives as well, some of which might perform better that the plsql option, if you do not have some requirement that limits you to using plsql. Consider ...

This was my first answer and question and OP replied:
Quote:
In reality, the function contains very complicated calculation between 1:n cardinality.

Regards
Michel
Re: Package, Function Application between multiple tables Query [message #350805 is a reply to message #350603] Fri, 26 September 2008 07:30 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I did not get that from your question or OP response.

Kevin
Re: Package, Function Application between multiple tables Query [message #350818 is a reply to message #350805] Fri, 26 September 2008 09:05 Go to previous message
Bonita
Messages: 32
Registered: June 2008
Member
Thanks a lot. for your recommendation !!! Really appreciated your time and reply.



Previous Topic: Using Update with Bulk Collect
Next Topic: Insert into one table from multi table
Goto Forum:
  


Current Time: Fri Dec 09 04:08:27 CST 2016

Total time taken to generate the page: 0.05008 seconds