-- Get the EMP_NO value from the latest period of work
-- I think these are all ok, but I'm unsure about the third
-- You can assume that there are no duplicate dates

-- Old fashioned method
SELECT pow.EMP_NO
INTO l_emp_no
FROM xxdm034_Employee_POW pow
WHERE pow.N_I_NUMBER = emp.N_I_NUMBER
AND pow.START_DATE = ( SELECT MIN(START_DATE)
FROM xxdm034_Employee_POW powd
WHERE powd.N_I_NUMBER = pow.N_I_NUMBER
AND powd.PAYROLL_TYPE IN ( 'Employee' ) );

-- Descending ROWNUM method
SELECT powd.EMP_NO
INTO l_emp_no
FROM ( SELECT pow.EMP_NO
FROM xxdm034_Employee_POW pow
WHERE pow.N_I_NUMBER = emp.N_I_NUMBER
AND pow.PAYROLL_TYPE IN ( 'Employee' )
ORDER BY pow.START_DATE DESCENDING ) powd
WHERE ROWNUM = 1;

-- Fancy rank method, not sure about this, might have to
-- be a sub-select like the ROWNUM version?
SELECT pow.EMP_NO
, RANK() OVER ( PARTITION BY pow.N_I_NUMBER
ORDER BY pow.START_DATE DESCENDING ) rank
INTO l_emp_no
, l_rank
FROM xxdm034_Employee_POW pow
WHERE pow.N_I_NUMBER = emp.N_I_NUMBER
AND pow.PAYROLL_TYPE IN ( 'Employee' )
AND rank = 1;

Opinions, both on correctness, performance and readability?

Phil Hibbs.

PhilHibbs wrote:
> -- Get the EMP_NO value from the latest period of work
> -- I think these are all ok, but I'm unsure about the third
> -- You can assume that there are no duplicate dates
>
> -- Old fashioned method
> SELECT pow.EMP_NO
> INTO l_emp_no
> FROM xxdm034_Employee_POW pow
> WHERE pow.N_I_NUMBER = emp.N_I_NUMBER
> AND pow.START_DATE = ( SELECT MIN(START_DATE)
> FROM xxdm034_Employee_POW powd
> WHERE powd.N_I_NUMBER = pow.N_I_NUMBER
> AND powd.PAYROLL_TYPE IN ( 'Employee' ) );
>
> -- Descending ROWNUM method
> SELECT powd.EMP_NO
> INTO l_emp_no
> FROM ( SELECT pow.EMP_NO
> FROM xxdm034_Employee_POW pow
> WHERE pow.N_I_NUMBER = emp.N_I_NUMBER
> AND pow.PAYROLL_TYPE IN ( 'Employee' )
> ORDER BY pow.START_DATE DESCENDING ) powd
> WHERE ROWNUM = 1;
>
> -- Fancy rank method, not sure about this, might have to
> -- be a sub-select like the ROWNUM version?
> SELECT pow.EMP_NO
> , RANK() OVER ( PARTITION BY pow.N_I_NUMBER
> ORDER BY pow.START_DATE DESCENDING ) rank
> INTO l_emp_no
> , l_rank
> FROM xxdm034_Employee_POW pow
> WHERE pow.N_I_NUMBER = emp.N_I_NUMBER
> AND pow.PAYROLL_TYPE IN ( 'Employee' )
> AND rank = 1;
>
> Opinions, both on correctness, performance and readability?
>
> Phil Hibbs.

Phil, to determine which query method is best from a performance point
of view explain the queries and look at the plans then time test the
queries.

Consider the plan, run time, and nature of the data now verse how it
will look in the future. That is, try to determine if the query
returning the best run time now will also probably return the best run
time in the future considering expected growth.

If the code is cleanly formatted and commented properly there should be
no reason for one version to be considered better than another from a
maintenance coding point of view.

I cannot remember if Oracle added analytic queries to the standard
edition but if not then that might be a consideration if you are
developing an application on EE that may have to run on the standard
edition.

HTH -- Mark D Powell --

Mark D Powell wrote:

>
> I cannot remember if Oracle added analytic queries to the standard
> edition but if not then that might be a consideration if you are
> developing an application on EE that may have to run on the standard
> edition.
>

According to metalink Note:271886.1 analytic functions are in all
editions of 10.

I would hope the SQL manual
http://download-west.oracle.com/docs...nctions001.htm
would note if it weren't. This is an extension of the SQL engine so it
is everywhere.

The analytics chapter of expert one-on-one goes into some detail about
performance, including a mention of when not to use them. I would
guess using analytics might make it easy for the optimizer to do the
right thing, as opposed to maybe something different with a
not-quite-equivalent complicated query.

jg
--
@home.com is bogus.
Sticky-beaking?
http://www.abc.net.au/news/newsitems...8/s1721505.htm