One morning I arrived early at my client site to find a developer running multiple SQL queries, then feverishly transferring the resulting output into a Microsoft Excel spreadsheet. When I asked her the reason for her frantic activity, she told me she needed to perform some complex financial calculations on the results, but did not know how to perform them from within Oracle.
I took an informal poll of the developers I supported, and I found out that many of them were still creating queries to extract raw data from tables and/or views, but then dumping that output to comma-delimited files and then into a spreadsheet so that the raw data could be manipulated via Excel's analytical functions. Some typical requirements these developers were pursuing included these scenarios:
-
- A Sales account executive asked for a subset of revenue data to create a series of graphs for her accounts. The graph needed to break out sales within each state in the Midwest and across different products and annual periods. The account executive also specified that any missing values for states or time periods in which no sales took place for a particular product could be filled in with meaningful default values.
- A user in Marketing needed a report that broke down sales within time periods, product types, and regions, including percentages of the total sales calculated at different control breaks.
- An Accounting department user needed a report that calculated the eventual value of a company's outstanding cash balance if particular assets were invested at different rates of interest, or over different time periods.
The good news is that Oracle 10g has beefed up analytical function processing so that all these scenarios can be handled by simply running an appropriate query directly against the database. I will cover the first scenario in the following sections, and I will tackle the last two scenarios in my next article when I cover the MODEL clause in depth.
Using Partitioned Outer Joins To "Densify" Data
Oracle 10g offers a new type of join, a partitioned outer join, that not only lets me join disparate sets of data, but also allows me to fill in "gaps" in the result sets because some data might be missing. For example, even though sales of a particular product may exist in most of the states in the Midwest, it is entirely possible that no sales exist in a particular calendar year for that product because of the time period in which it was introduced.
Oracle calls the concept of filling in missing data with partitioned outer joins data densification. To illustrate, I will create a new view, SH.CALENDAR_YEARS, that contains all existing calendar years in the SH.TIMES time periods table and adds in all current calendar years up to and including 2005. I will also create a new view named SH.SALES_MIDWEST_ONLY that will gather only a small subset of sales data from the sales history table, SALES. (I am using a small subset only to delineate the results of data densification; in real life, of course, I would use all the available sales data.)
Next, I will construct a query that uses a partitioned outer join to link together sales data from the SH.SALES_MIDWEST_ONLY view with a larger subset of Middle Western U.S. states to illustrate how data densification can be implemented:
SQL> TTITLE 'Partitioned Outer Join Example #1'
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;
Partitioned Outer Join Example #1
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.
Listing 2.3 shows the creation of both of these views, the initial query and this complete result set.
N-Dimensional Data Densification
The previous query does produce zeroed totals for those remaining states (i.e. Ohio and Iowa) which are present in the second subquery but not in the first subquery. That is better than nothing, but unfortunately, data is only present for the years 1999, 2000 and 2001 (the only time periods present in the SALES table's data). However, there is nothing to stop me from employing multiple partitioned outer joins to populate more than one data dimension.
Here is an example of how to accomplish this via one of my favorite, elegant SQL features that Oracle provides -- the WITH clause -- to specify the three component subqueries before invoking the multiple partitioned outer joins. The resulting output of this query will contain a fully "densified" result set:
SQL> TTITLE 'Partitioned Outer Join Densifying Across Two Dimensions'
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> WITH
2 -- Gather all Midwest-only sales data
3 Q1 AS (
4 SELECT
5 prod
6 ,state
7 ,year
8 ,SUM(sale) sale
9 FROM
10 sh.sales_midwest_only
11 GROUP BY state, prod, year
12 ),
13 -- Gather a list of distinct states
14 Q2 AS (
15 SELECT DISTINCT cust_state_province state
16 FROM sh.customers
17 WHERE cust_state_province IN ('IA', 'IL','IN','MI','OH','WI')
18 ),
19 -- Gather a list of distinct calendar years
20 Q3 AS (
21 SELECT calendar_year year
22 FROM sh.calendar_years
23 )
24 SELECT
25 Q4.prod
26 ,Q4.state
27 ,Q3.year
28 ,NVL(Q4.sale, 0) tot_sales
29 FROM
30 (SELECT
31 Q1.prod
32 ,Q2.state
33 ,Q1.year
34 ,Q1.sale
35 FROM Q1
36 PARTITION BY (prod)
37 RIGHT OUTER JOIN Q2
38 ON (Q1.state = Q2.state)
39 ) Q4
40 PARTITION BY (prod,state)
41 RIGHT OUTER JOIN Q3
42 ON (Q4.YEAR = Q3.year)
43 ORDER BY 1, 2, 3;
/*
|| 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.