/*
|| Oracle 10g SQL Enhancements - Listing 2
||
|| Contains examples of new Oracle 10g SQL features, including:
||
|| - Analytical Functions
|| - Partitioned Outer Joins
|| - Data Densification
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's
|| new SQL features and should be carefully proofread before executing it
|| against any existing Oracle database to insure that no potential damage
|| can occur.
||
*/

----- 
-- Listing 2.1: Using Oracle 9i Analytical Functions
-----

-----
-- Add new entries into the HR.JOB_HISTORY table for Employees #114 and #122
-- for illustration of new Oracle 10g features
-----
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(114, '12/31/1999', '09/30/2000', 'ST_MAN', 50);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(114, '09/30/2000', '07/31/2001', 'PU_MAN', 30);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(114, '07/31/2001', '12/31/2036', 'SA_REP', 80);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(122, '12/31/1999', '09/30/2000', 'ST_MAN', 50);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(122, '09/30/2000', '06/30/2002', 'SA_REP', 80);
INSERT INTO hr.job_history(employee_id, start_date, end_date, job_id, department_id)
VALUES(122, '06/30/2002', '12/31/2036', 'SA_MAN', 80);

COMMIT;

-----
-- Find the earliest Job History entry for an Employee using traditional
-- SQL query methods
-----
TTITLE 'Earliest Employee Job History Entry (Traditional Method)'
COL employee_id     FORMAT 9999     HEADING 'EmpID'
COL full_name       FORMAT A24      HEADING 'Employee Name'
COL job_id          FORMAT A10      HEADING 'JobID'
COL start_date      FORMAT A10      HEADING 'Start Date'
COL end_date        FORMAT A10      HEADING 'End Date'

SELECT
    E.employee_id
   ,E.last_name || ', ' || E.first_name full_name
   ,JH.job_id
   ,TO_CHAR(JH.start_date, 'mm-dd-yyyy') start_date
   ,TO_CHAR(JH.end_date, 'mm-dd-yyyy') end_date
  FROM
    hr.employees E
   ,hr.job_history JH
 WHERE E.employee_id =  JH.employee_id
   AND JH.start_date = (
    SELECT MIN(start_date)
      FROM hr.job_history
     WHERE employee_id = 114)
   AND JH.employee_id = 114;

-----
-- Use Oracle 9i Analytic Functions to find the earliest Job History entry 
-- for an Employee
-----
TTITLE 'Earliest Employee Job History Entry (Analytic Function)'
COL eid             FORMAT 9999     HEADING 'EmpID'
COL name            FORMAT A24      HEADING 'Employee Name'
COL jobid           FORMAT A10      HEADING 'JobID'
COL begin_date      FORMAT A10      HEADING 'Start Date'
COL stop_date       FORMAT A10      HEADING 'End Date'

SELECT
     eid
    ,NAME
    ,jobid
    ,begin_date
    ,stop_date
  FROM (
    SELECT
        JH.employee_id eid
       ,E.last_name || ', ' || E.first_name  NAME
       ,JH.job_id jobid
       ,JH.START_DATE begin_date
       ,JH.end_date stop_date
       ,FIRST_VALUE(JH.start_date) OVER (
            ORDER BY JH.employee_id, JH.start_date
            ROWS BETWEEN UNBOUNDED PRECEDING 
                     AND UNBOUNDED FOLLOWING
            ) recent_history
      FROM
        hr.employees E
       ,hr.job_history JH
     WHERE E.employee_id =  JH.employee_id
       AND JH.employee_id IN (114)
 )
 WHERE begin_date = recent_history
 ORDER BY eid;

----- 
-- Listing 2.2: Show the least recent Job History entry for each Employee. 
-- Note the use of the PARTITION BY clause to change the size of the result 
-- set partition based on the changing value of each Employee ID
-----
TTITLE 'Earliest Employee Job History Entries (Analytic Function)'
COL eid             FORMAT 9999     HEADING 'EmpID'
COL name            FORMAT A24      HEADING 'Employee Name'
COL jobid           FORMAT A10      HEADING 'JobID'
COL begin_date      FORMAT A10      HEADING 'Start Date'
COL stop_date       FORMAT A10      HEADING 'End Date'
SELECT
     eid
    ,name
    ,jobid
    ,begin_date
    ,stop_date
  FROM (
    SELECT
        JH.employee_id eid
       ,E.last_name || ', ' || E.first_name  NAME
       ,JH.job_id jobid
       ,TO_CHAR(JH.start_date, 'mm-dd-yyyy') begin_date
       ,TO_CHAR(JH.end_date, 'mm-dd-yyyy') stop_date
       ,FIRST_VALUE(JH.start_date) OVER (
            PARTITION BY JH.employee_id
            ORDER BY JH.employee_id, JH.start_date
            ROWS BETWEEN UNBOUNDED PRECEDING 
                     AND UNBOUNDED FOLLOWING
            ) recent_history
      FROM
        hr.employees E
       ,hr.job_history JH
     WHERE E.employee_id =  JH.employee_id)
 WHERE begin_date = recent_history
 ORDER BY eid;

>>> Results:

Thu Aug 04                                                            page    1
           Earliest Employee Job History Entries (Analytic Function)

EmpID Employee Name            JobID      Start Date End Date
----- ------------------------ ---------- ---------- ----------
  101 Kochhar, Neena           AC_ACCOUNT 09-21-1989 10-27-1993
  102 De Haan, Lex             IT_PROG    01-13-1993 07-24-1998
  114 Raphaely, Den            ST_CLERK   03-24-1998 12-31-1999
  122 Kaufling, Payam          ST_CLERK   01-01-1999 12-31-1999
  176 Taylor, Jonathon         SA_REP     03-24-1998 12-31-1998
  200 Whalen, Jennifer         AD_ASST    09-17-1987 06-17-1993
  201 Hartstein, Michael       MK_REP     02-17-1996 12-19-1999

7 rows selected.

----- 
-- Listing 2.3: Using Partitioned Outer Joins in Oracle 10g
-----

-----
-- Create a view that contains all possible Calendar Years
-----
CREATE OR REPLACE VIEW sh.calendar_years AS
    SELECT
        DISTINCT calendar_year
      FROM sh.times
    UNION
    SELECT 2002 calendar_year
      FROM dual
    UNION
    SELECT 2003 calendar_year
      FROM dual
    UNION
    SELECT 2004 calendar_year
      FROM dual
    UNION
    SELECT 2005 calendar_year
      FROM dual
    ORDER BY calendar_year;

-----
-- Create a view that shows Sales for selected Products only for
-- selected U.S. Midwestern States
-----
CREATE OR REPLACE VIEW sh.sales_midwest_only AS
    SELECT
        CT.cust_state_province state
       ,P.prod_name prod
       ,P.prod_category ctgy
       ,T.calendar_year year
       ,SUM(S.amount_sold) sale
       ,COUNT(S.amount_sold) cnt
      FROM
         sh.sales S
        ,sh.times T
        ,sh.customers CT
        ,sh.products P
     WHERE S.time_id = T.time_id
       AND S.prod_id = P.prod_id
       AND S.cust_id = CT.cust_id
       AND CT.country_id = 'US'
       AND CT.cust_state_province IN ('IL','IN','MI','MN','WI')
       AND P.prod_id BETWEEN 5 AND 35
     GROUP BY
         CT.cust_state_province
        ,P.prod_category
        ,P.prod_name
        ,T.calendar_year;

-----
-- Populate all products across all available states via a partitioned outer
-- join. Note that for the states other than the five specified in the first
-- query, NULL values will be returned (which are converted to zeroes via the
-- NVL function)
-----
SELECT
     SLS.prod
    ,RGNS.state
    ,SLS.year
    ,NVL(SLS.Sales,0) dense_sales
FROM
    (SELECT
         MWO.state
        ,MWO.prod
        ,MWO.year
        ,SUM(MWO.sale) sales
      FROM
          sh.sales_midwest_only MWO
     GROUP BY
         MWO.state
        ,MWO.prod
        ,MWO.YEAR
    ) SLS
    PARTITION BY (SLS.prod)
    RIGHT OUTER JOIN
    (SELECT
        DISTINCT cust_state_province state
       FROM sh.customers
      WHERE cust_state_province IN ('IA', 'IL','IN','MI','OH','WI')
    ) RGNS
    ON (RGNS.state = SLS.state)
 ORDER BY SLS.prod, SLS.state, SLS.year;

>>> Results:

SQL> TTITLE 'Partitioned Outer Join Example'
SQL> COL prod            FORMAT A32              HEADING 'Product'
SQL> COL state           FORMAT A4               HEADING 'State'
SQL> COL year            FORMAT 9999             HEADING 'JobID'
SQL> COL tot_sales       FORMAT 999,999,999.99   HEADING 'Total Sales'
SQL>
SQL> SELECT
  2       SLS.prod
  3      ,RGNS.state
  4      ,SLS.year
  5      ,NVL(SLS.sales,0) tot_sales
  6  FROM
  7      (SELECT
  8           MWO.state
  9          ,MWO.prod
 10          ,MWO.year
 11          ,SUM(MWO.sale) sales
 12        FROM
 13            sh.sales_midwest_only MWO
 14       GROUP BY
 15           MWO.state
 16          ,MWO.prod
 17          ,MWO.YEAR
 18      ) SLS
 19      PARTITION BY (SLS.prod)
 20      RIGHT OUTER JOIN
 21      (SELECT
 22          DISTINCT cust_state_province state
 23         FROM sh.customers
 24        WHERE cust_state_province IN ('IA', 'IL','IN','MI','OH','WI')
 25      ) RGNS
 26      ON (RGNS.state = SLS.state)
 27   ORDER BY SLS.prod, SLS.state, SLS.year;

Thu Aug 04                                                            page    1
                         Partitioned Outer Join Example

Product                          Stat JobID     Total Sales
-------------------------------- ---- ----- ---------------
And 2 Crosscourt Tee Kids        IL    1998          826.00
And 2 Crosscourt Tee Kids        IL    1999        1,187.90
And 2 Crosscourt Tee Kids        IL    2000        2,619.40
And 2 Crosscourt Tee Kids        IN    1998          266.00
And 2 Crosscourt Tee Kids        IN    2000        2,086.00
And 2 Crosscourt Tee Kids        MI    1999           53.20
And 2 Crosscourt Tee Kids        MI    2000        1,209.60
And 2 Crosscourt Tee Kids        WI    1998          686.00
And 2 Crosscourt Tee Kids        WI    1999          504.00
And 2 Crosscourt Tee Kids        WI    2000          378.00
And 2 Crosscourt Tee Kids        IA                     .00
And 2 Crosscourt Tee Kids        OH                     .00
Coin Pocket Twill Cargo Trousers MI    1999          234.00
Coin Pocket Twill Cargo Trousers MI    2000           78.00
Coin Pocket Twill Cargo Trousers WI    1999          722.15
Coin Pocket Twill Cargo Trousers WI    2000          390.00
Coin Pocket Twill Cargo Trousers IA                     .00
Coin Pocket Twill Cargo Trousers IL                     .00
Coin Pocket Twill Cargo Trousers OH                     .00
Coin Pocket Twill Cargo Trousers IN                     .00
Gurfield& Murks Pleated Trousers IL    1998        9,100.00
Gurfield& Murks Pleated Trousers IL    1999       13,825.00
Gurfield& Murks Pleated Trousers IL    2000        2,100.00
Gurfield& Murks Pleated Trousers IN    1999        7,525.00
Gurfield& Murks Pleated Trousers MI    1998       26,040.00
Gurfield& Murks Pleated Trousers MI    1999       12,600.00
Gurfield& Murks Pleated Trousers MI    2000       19,425.00
Gurfield& Murks Pleated Trousers WI    1998          175.00
Gurfield& Murks Pleated Trousers IA                     .00
Gurfield& Murks Pleated Trousers OH                     .00
Kahala Pleated Chino Short       IL    1998          504.00
Kahala Pleated Chino Short       IL    1999        3,738.00
Kahala Pleated Chino Short       IA                     .00
Kahala Pleated Chino Short       IN                     .00
Kahala Pleated Chino Short       MI                     .00
Kahala Pleated Chino Short       OH                     .00
Kahala Pleated Chino Short       WI                     .00

37 rows selected.

(Note: The "missing" results populated via the partitioned outer join are in bold.)

----- 
-- Listing 2.4: Oracle 10g Data Densification Examples. Note the use of the
-- WITH clause to organize the component queries
-----
TTITLE 'Partitioned Outer Join Densifying Across Two Dimensions'
COL prod            FORMAT A32              HEADING 'Product'
COL state           FORMAT A4               HEADING 'State'
COL year            FORMAT 9999             HEADING 'JobID'
COL tot_sales       FORMAT 999,999,999.99   HEADING 'Total Sales'

WITH
    -- Gather all Midwest-only sales data
    Q1 AS (
        SELECT
             prod
            ,state
            ,year
            ,SUM(sale) sale
          FROM
             sh.sales_midwest_only
         GROUP BY state, prod, year
    ),
    -- Gather a list of distinct states
    Q2 AS (
        SELECT DISTINCT cust_state_province state
       FROM sh.customers
      WHERE cust_state_province IN ('IA', 'IL','IN','MI','OH','WI')
    ),
    -- Gather a list of distinct calendar years
    Q3 AS (
        SELECT calendar_year year
          FROM sh.calendar_years
    )
SELECT
     Q4.prod
    ,Q4.state
    ,Q3.year
    ,NVL(Q4.sale, 0) tot_sales
  FROM
    (SELECT
         Q1.prod
        ,Q2.state
        ,Q1.year
        ,Q1.sale
      FROM Q1
        PARTITION BY (prod)
        RIGHT OUTER JOIN Q2 -- This partitioned outer join "densifies" on state
        ON (Q1.state = Q2.state)
    ) Q4
        PARTITION BY (prod,state)
        RIGHT OUTER JOIN Q3 -- This partioned outer join "densifies" on calendar year
        ON (Q4.YEAR = Q3.year)
 ORDER BY 1, 2, 3;

>>> Results:

Thu Aug 04                                                             page    1
            Partitioned Outer Join Densifying Across Two Dimensions

Product                          Stat JobID     Total Sales
-------------------------------- ---- ----- ---------------
And 2 Crosscourt Tee Kids        IA    1998             .00
And 2 Crosscourt Tee Kids        IA    1999             .00
And 2 Crosscourt Tee Kids        IA    2000             .00
And 2 Crosscourt Tee Kids        IA    2001             .00
And 2 Crosscourt Tee Kids        IA    2002             .00
And 2 Crosscourt Tee Kids        IA    2003             .00
And 2 Crosscourt Tee Kids        IA    2004             .00
And 2 Crosscourt Tee Kids        IA    2005             .00
And 2 Crosscourt Tee Kids        IL    1998          826.00
And 2 Crosscourt Tee Kids        IL    1999        1,187.90
And 2 Crosscourt Tee Kids        IL    2000        2,619.40
And 2 Crosscourt Tee Kids        IL    2001             .00
And 2 Crosscourt Tee Kids        IL    2002             .00
And 2 Crosscourt Tee Kids        IL    2003             .00
And 2 Crosscourt Tee Kids        IL    2004             .00
And 2 Crosscourt Tee Kids        IL    2005             .00
And 2 Crosscourt Tee Kids        IN    1998          266.00
And 2 Crosscourt Tee Kids        IN    1999             .00
And 2 Crosscourt Tee Kids        IN    2000        2,086.00
And 2 Crosscourt Tee Kids        IN    2001             .00
And 2 Crosscourt Tee Kids        IN    2002             .00
And 2 Crosscourt Tee Kids        IN    2003             .00
And 2 Crosscourt Tee Kids        IN    2004             .00
And 2 Crosscourt Tee Kids        IN    2005             .00
And 2 Crosscourt Tee Kids        MI    1998             .00
And 2 Crosscourt Tee Kids        MI    1999           53.20
And 2 Crosscourt Tee Kids        MI    2000        1,209.60
And 2 Crosscourt Tee Kids        MI    2001             .00
And 2 Crosscourt Tee Kids        MI    2002             .00
And 2 Crosscourt Tee Kids        MI    2003             .00
And 2 Crosscourt Tee Kids        MI    2004             .00
And 2 Crosscourt Tee Kids        MI    2005             .00
And 2 Crosscourt Tee Kids        OH    1998             .00
And 2 Crosscourt Tee Kids        OH    1999             .00
And 2 Crosscourt Tee Kids        OH    2000             .00
And 2 Crosscourt Tee Kids        OH    2001             .00
And 2 Crosscourt Tee Kids        OH    2002             .00
And 2 Crosscourt Tee Kids        OH    2003             .00
And 2 Crosscourt Tee Kids        OH    2004             .00
And 2 Crosscourt Tee Kids        OH    2005             .00
And 2 Crosscourt Tee Kids        WI    1998          686.00
And 2 Crosscourt Tee Kids        WI    1999          504.00
And 2 Crosscourt Tee Kids        WI    2000          378.00
And 2 Crosscourt Tee Kids        WI    2001             .00
And 2 Crosscourt Tee Kids        WI    2002             .00
And 2 Crosscourt Tee Kids        WI    2003             .00
And 2 Crosscourt Tee Kids        WI    2004             .00
And 2 Crosscourt Tee Kids        WI    2005             .00
Coin Pocket Twill Cargo Trousers IA    1998             .00
Coin Pocket Twill Cargo Trousers IA    1999             .00
Coin Pocket Twill Cargo Trousers IA    2000             .00
Coin Pocket Twill Cargo Trousers IA    2001             .00
Coin Pocket Twill Cargo Trousers IA    2002             .00
Coin Pocket Twill Cargo Trousers IA    2003             .00
Coin Pocket Twill Cargo Trousers IA    2004             .00
Coin Pocket Twill Cargo Trousers IA    2005             .00
Coin Pocket Twill Cargo Trousers IL    1998             .00
Coin Pocket Twill Cargo Trousers IL    1999             .00
Coin Pocket Twill Cargo Trousers IL    2000             .00
Coin Pocket Twill Cargo Trousers IL    2001             .00
Coin Pocket Twill Cargo Trousers IL    2002             .00
Coin Pocket Twill Cargo Trousers IL    2003             .00
Coin Pocket Twill Cargo Trousers IL    2004             .00
Coin Pocket Twill Cargo Trousers IL    2005             .00
Coin Pocket Twill Cargo Trousers IN    1998             .00
Coin Pocket Twill Cargo Trousers IN    1999             .00
Coin Pocket Twill Cargo Trousers IN    2000             .00
Coin Pocket Twill Cargo Trousers IN    2001             .00
Coin Pocket Twill Cargo Trousers IN    2002             .00
Coin Pocket Twill Cargo Trousers IN    2003             .00
Coin Pocket Twill Cargo Trousers IN    2004             .00
Coin Pocket Twill Cargo Trousers IN    2005             .00
Coin Pocket Twill Cargo Trousers MI    1998             .00
Coin Pocket Twill Cargo Trousers MI    1999          234.00
Coin Pocket Twill Cargo Trousers MI    2000           78.00
Coin Pocket Twill Cargo Trousers MI    2001             .00
Coin Pocket Twill Cargo Trousers MI    2002             .00
Coin Pocket Twill Cargo Trousers MI    2003             .00
Coin Pocket Twill Cargo Trousers MI    2004             .00
Coin Pocket Twill Cargo Trousers MI    2005             .00
Coin Pocket Twill Cargo Trousers OH    1998             .00
Coin Pocket Twill Cargo Trousers OH    1999             .00
Coin Pocket Twill Cargo Trousers OH    2000             .00
Coin Pocket Twill Cargo Trousers OH    2001             .00
Coin Pocket Twill Cargo Trousers OH    2002             .00
Coin Pocket Twill Cargo Trousers OH    2003             .00
Coin Pocket Twill Cargo Trousers OH    2004             .00
Coin Pocket Twill Cargo Trousers OH    2005             .00
Coin Pocket Twill Cargo Trousers WI    1998             .00
Coin Pocket Twill Cargo Trousers WI    1999          722.15
Coin Pocket Twill Cargo Trousers WI    2000          390.00
Coin Pocket Twill Cargo Trousers WI    2001             .00
Coin Pocket Twill Cargo Trousers WI    2002             .00
Coin Pocket Twill Cargo Trousers WI    2003             .00
Coin Pocket Twill Cargo Trousers WI    2004             .00
Coin Pocket Twill Cargo Trousers WI    2005             .00
Gurfield& Murks Pleated Trousers IA    1998             .00
Gurfield& Murks Pleated Trousers IA    1999             .00
Gurfield& Murks Pleated Trousers IA    2000             .00
Gurfield& Murks Pleated Trousers IA    2001             .00
Gurfield& Murks Pleated Trousers IA    2002             .00
Gurfield& Murks Pleated Trousers IA    2003             .00
Gurfield& Murks Pleated Trousers IA    2004             .00
Gurfield& Murks Pleated Trousers IA    2005             .00
Gurfield& Murks Pleated Trousers IL    1998        9,100.00
Gurfield& Murks Pleated Trousers IL    1999       13,825.00
Gurfield& Murks Pleated Trousers IL    2000        2,100.00
Gurfield& Murks Pleated Trousers IL    2001             .00
Gurfield& Murks Pleated Trousers IL    2002             .00
Gurfield& Murks Pleated Trousers IL    2003             .00
Gurfield& Murks Pleated Trousers IL    2004             .00
Gurfield& Murks Pleated Trousers IL    2005             .00
Gurfield& Murks Pleated Trousers IN    1998             .00
Gurfield& Murks Pleated Trousers IN    1999        7,525.00
Gurfield& Murks Pleated Trousers IN    2000             .00
Gurfield& Murks Pleated Trousers IN    2001             .00
Gurfield& Murks Pleated Trousers IN    2002             .00
Gurfield& Murks Pleated Trousers IN    2003             .00
Gurfield& Murks Pleated Trousers IN    2004             .00
Gurfield& Murks Pleated Trousers IN    2005             .00
Gurfield& Murks Pleated Trousers MI    1998       26,040.00
Gurfield& Murks Pleated Trousers MI    1999       12,600.00
Gurfield& Murks Pleated Trousers MI    2000       19,425.00
Gurfield& Murks Pleated Trousers MI    2001             .00
Gurfield& Murks Pleated Trousers MI    2002             .00
Gurfield& Murks Pleated Trousers MI    2003             .00
Gurfield& Murks Pleated Trousers MI    2004             .00
Gurfield& Murks Pleated Trousers MI    2005             .00
Gurfield& Murks Pleated Trousers OH    1998             .00
Gurfield& Murks Pleated Trousers OH    1999             .00
Gurfield& Murks Pleated Trousers OH    2000             .00
Gurfield& Murks Pleated Trousers OH    2001             .00
Gurfield& Murks Pleated Trousers OH    2002             .00
Gurfield& Murks Pleated Trousers OH    2003             .00
Gurfield& Murks Pleated Trousers OH    2004             .00
Gurfield& Murks Pleated Trousers OH    2005             .00
Gurfield& Murks Pleated Trousers WI    1998          175.00
Gurfield& Murks Pleated Trousers WI    1999             .00
Gurfield& Murks Pleated Trousers WI    2000             .00
Gurfield& Murks Pleated Trousers WI    2001             .00
Gurfield& Murks Pleated Trousers WI    2002             .00
Gurfield& Murks Pleated Trousers WI    2003             .00
Gurfield& Murks Pleated Trousers WI    2004             .00
Gurfield& Murks Pleated Trousers WI    2005             .00
Kahala Pleated Chino Short       IA    1998             .00
Kahala Pleated Chino Short       IA    1999             .00
Kahala Pleated Chino Short       IA    2000             .00
Kahala Pleated Chino Short       IA    2001             .00
Kahala Pleated Chino Short       IA    2002             .00
Kahala Pleated Chino Short       IA    2003             .00
Kahala Pleated Chino Short       IA    2004             .00
Kahala Pleated Chino Short       IA    2005             .00
Kahala Pleated Chino Short       IL    1998          504.00
Kahala Pleated Chino Short       IL    1999        3,738.00
Kahala Pleated Chino Short       IL    2000             .00
Kahala Pleated Chino Short       IL    2001             .00
Kahala Pleated Chino Short       IL    2002             .00
Kahala Pleated Chino Short       IL    2003             .00
Kahala Pleated Chino Short       IL    2004             .00
Kahala Pleated Chino Short       IL    2005             .00
Kahala Pleated Chino Short       IN    1998             .00
Kahala Pleated Chino Short       IN    1999             .00
Kahala Pleated Chino Short       IN    2000             .00
Kahala Pleated Chino Short       IN    2001             .00
Kahala Pleated Chino Short       IN    2002             .00
Kahala Pleated Chino Short       IN    2003             .00
Kahala Pleated Chino Short       IN    2004             .00
Kahala Pleated Chino Short       IN    2005             .00
Kahala Pleated Chino Short       MI    1998             .00
Kahala Pleated Chino Short       MI    1999             .00
Kahala Pleated Chino Short       MI    2000             .00
Kahala Pleated Chino Short       MI    2001             .00
Kahala Pleated Chino Short       MI    2002             .00
Kahala Pleated Chino Short       MI    2003             .00
Kahala Pleated Chino Short       MI    2004             .00
Kahala Pleated Chino Short       MI    2005             .00
Kahala Pleated Chino Short       OH    1998             .00
Kahala Pleated Chino Short       OH    1999             .00
Kahala Pleated Chino Short       OH    2000             .00
Kahala Pleated Chino Short       OH    2001             .00
Kahala Pleated Chino Short       OH    2002             .00
Kahala Pleated Chino Short       OH    2003             .00
Kahala Pleated Chino Short       OH    2004             .00
Kahala Pleated Chino Short       OH    2005             .00
Kahala Pleated Chino Short       WI    1998             .00
Kahala Pleated Chino Short       WI    1999             .00
Kahala Pleated Chino Short       WI    2000             .00
Kahala Pleated Chino Short       WI    2001             .00
Kahala Pleated Chino Short       WI    2002             .00
Kahala Pleated Chino Short       WI    2003             .00
Kahala Pleated Chino Short       WI    2004             .00
Kahala Pleated Chino Short       WI    2005             .00

192 rows selected.