Puzzle Challenge can this be done w/o cursors? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Puzzle Challenge can this be done w/o cursors?

Author

Topic

hoghunter
Starting Member

9 Posts

Posted - 2004-08-09 : 11:34:54

Here's a challenge for all you SQL gurus out there.

I have two tables:

CREATE TABLE XMAS_ITEMS(
ITEM_NO CHAR(12) NOT NULL,
ITEM_DESC CHAR(30) NULL
)

CREATE TABLE ORDER_ITEM_SUM(
CUST_NO CHAR(15) NOT NULL,
ITEM_NO CHAR(12) NOT NULL,
QTY_ORDERED INT NULL
)

XMAS_ITEMS Contains the list of valid Christmas inventory items.
ORDER_ITEM_SUM Contains a list of each customer's previous year's Christmas order and the qty ordered per item.

Individual customers will not have ordered all items, yet all items were ordered by at least one customer. I would like to get a list of all Christmas items and how many were ordered by a given cutomer.

Assume:
XMAS_ITEMS:

ITEM_NO ITEM_DESC
1 Large Bags
2 Medium Bags
3 Large Boxes
4 Medium Boxes

ORDER_ITEM_SUM:

CUST ITEM QTY
1 -- 1 -- 10
1 -- 2 -- 15
1 -- 4 -- 20
2 -- 3 -- 30
2 -- 4 -- 40
3 -- 1 -- 20
3 -- 2 -- 50
3 -- 3 -- 10

The result of my query should look like:

CUST ITEM QTY
1 -- 1 -- 10
1 -- 2 -- 15
1 -- 3 -- 0
1 -- 4 -- 20
2 -- 1 -- 0
2 -- 2 -- 0
2 -- 3 -- 30
2 -- 4 -- 40
3 -- 1 -- 20
3 -- 2 -- 50
3 -- 3 -- 10
3 -- 4 -- 0

Currently, I am doing this using a set of nested cursors that loops through XMAS_ITEMS then through ORDER_ITEM_SUM for each customer.

I seem to recall having read a solution to this problem in a Joe Celko book but I loaned my books out.

Any help will be appreciated.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-09 : 12:25:14

Maybe I am misunderstanding the question - But I don't see why you even thought of using a cursor(That is a dirty word here).

SELECT c.Cust, i.Item, SUM(ISNULL(o.QTY, 0)) as QTY
FROM (SELECT DISTINCT CUST FROM ORDER_ITEM_SUM) c
CROSS JOIN XMAS_Items i
LEFT OUTER JOIN ORDER_ITEM_SUM o
on o.Cust = c.Cust
AND o.Item = i.Item

Duane.

mfemenel
Professor Frink

1421 Posts

Posted - 2004-08-09 : 12:29:26

Maybe it's just 'cause this is monday, but wouldn't this work?
select ois.cust_no,xi.item_no,sum(isnull(ois.qty_ordered,0))
from order_item_sum ois
left join xmas_items xi on ois.item_no=xi.item_no
group by ois.cust_no,xi.item_no

Mike
"oh, that monkey is going to pay"

mfemenel
Professor Frink

1421 Posts

Posted - 2004-08-09 : 12:30:07

Maybe it's just 'cause this is monday, but wouldn't this work?
select ois.cust_no,xi.item_no,sum(isnull(ois.qty_ordered,0))
from order_item_sum ois
left join xmas_items xi on ois.item_no=xi.item_no
group by ois.cust_no,xi.item_no

Mike
"oh, that monkey is going to pay"

Nedra
Starting Member

21 Posts

Posted - 2004-08-09 : 12:42:06

Here's a solution

select distinct
ois.Cust_No
, xi.Item_No
, case
when ois.Item_No = xi.Item_No
then ois.Qty_Ordered
else 0
end
from
ORDER_ITEM_SUM as ois
inner join XMAS_ITEMS as xi
on ois.ITEM_NO = case
when not exists (select ITEM_NO
from ORDER_ITEM_SUM
where Cust_No = ois.Cust_No
and ITEM_NO = xi.ITEM_NO)
then ois.ITEM_NO
else xi.ITEM_NO
end
order by
cust_no
, xi.item_no

Probably not the best performing, but better then a nested cursor!

Nedra
Starting Member

21 Posts

Posted - 2004-08-09 : 12:52:07

I compared my solution to ditch's solution

SELECT c.Cust_No, i.Item_No, SUM(ISNULL(o.Qty_Ordered, 0)) as QTY
FROM (SELECT DISTINCT CUST_No FROM ORDER_ITEM_SUM) c
CROSS JOIN XMAS_Items i
LEFT OUTER JOIN ORDER_ITEM_SUM o
on o.Cust_No = c.Cust_No
AND o.Item_No = i.Item_No
group by c.Cust_No, i.Item_No
order by c.Cust_No, i.Item_No

(had to add the group by and order by to get it to work)

They are comperable in estimated query plans, with my initial solution edging it slightly. Larger amounts of data and proper indexing would probably give a truer estimation.

hoghunter
Starting Member

9 Posts

Posted - 2004-08-09 : 13:26:18

quote:
Originally posted by ditch

Maybe I am misunderstanding the question - But I don't see why you even thought of using a cursor(That is a dirty word here).

I totally agree, cursors are BAD. As a matter of fact, in my role as data architect, I have made the statement that I have NEVER found a problem that required the use of a cursor. Having said that, this problem came up on Friday (late in the day) and since it only needs to run once a year, there will be no performance impact associated with the cursors.

quote:

SELECT c.Cust, i.Item, SUM(ISNULL(o.QTY, 0)) as QTY
FROM (SELECT DISTINCT CUST FROM ORDER_ITEM_SUM) c
CROSS JOIN XMAS_Items i
LEFT OUTER JOIN ORDER_ITEM_SUM o
on o.Cust = c.Cust
AND o.Item = i.Item

That works! Thanks. I have never used a cross join before, this looks like the solution I had read in Joe's book.

hoghunter
Starting Member

9 Posts

Posted - 2004-08-09 : 13:28:34

quote:
Originally posted by mfemenel

Maybe it's just 'cause this is monday, but wouldn't this work?
select ois.cust_no,xi.item_no,sum(isnull(ois.qty_ordered,0))
from order_item_sum ois
left join xmas_items xi on ois.item_no=xi.item_no
group by ois.cust_no,xi.item_no

Mike
"oh, that monkey is going to pay"

In the case of cust 1, there will be no item 3. The left join is satisfied for item 3 by the existence of item 3 for cust 2.

hoghunter
Starting Member

9 Posts

Posted - 2004-08-09 : 14:07:37

quote:
Originally posted by Nedra

Here's a solution

select distinct
ois.Cust_No
, xi.Item_No
, case
when ois.Item_No = xi.Item_No
then ois.Qty_Ordered
else 0
end
from
ORDER_ITEM_SUM as ois
inner join XMAS_ITEMS as xi
on ois.ITEM_NO = case
when not exists (select ITEM_NO
from ORDER_ITEM_SUM
where Cust_No = ois.Cust_No
and ITEM_NO = xi.ITEM_NO)
then ois.ITEM_NO
else xi.ITEM_NO
end
order by
cust_no
, xi.item_no

Probably not the best performing, but better then a nested cursor!

This looks like the kind of query I was attempting to get working Friday. I kept getting wrapped up in the joins and sub selects I never consider the not exists concecpt. Nice, thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources