is this view possible? - SQL Server Forums

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

SQL Server Development (2000)

is this view possible?

Author

Topic

Tim
Starting Member

392 Posts

Posted - 2002-05-12 : 20:32:35

I have the following table which represents storage boxes. A box is dived into 'cells' represented by three co-ordinates Aisle, Row and Tier (which kind of correspond to length, width and depth positions).

CREATE TABLE box
(
BoxID int NOT NULL IDENTITY,
NbrAisles int NOT NULL ,
NbrRows int NOT NULL ,
NbrTiers int NOT NULL
)
-- assume boxID = 1
INSERT INTO box (NbrAisles, NbrRows, NbrTiers)
VALUES (2,3,1)

-- assume boxID = 2
INSERT INTO box (NbrAisles, NbrRows, NbrTiers)
VALUES (2,2,2)

I want to make a view that represents a table of all "cells" in each box:

BoxID Aisle Row Tier
----- ----- --- ----
1 1 1 1
1 1 2 1
1 1 3 1
1 2 1 1
1 2 2 1
1 2 3 1
2 1 1 1
2 1 1 2
2 1 2 1
2 1 2 2
2 2 1 1
2 2 1 2
2 2 2 1
2 2 2 2

It's got to be a view and no additional tables. Is it possible even?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-12 : 21:47:59

You can use a tally or sequence table to generate the values you need. Here's some background on tally/sequence tables:

http://www.sqlteam.com/item.asp?ItemID=5857
http://www.sqlteam.com/item.asp?ItemID=2652
http://www.sqlteam.com/item.asp?ItemID=3332

Basically it's just a simple table with sequential numbers in it, and you could write a view to generate the results like this:

CREATE VIEW AllCells AS
SELECT B.ID BoxID, A.ID Aisle, R.ID Row, T.ID Tier
FROM Box B, Tally A, Tally R, Tally T
WHERE A.ID<=B.NbrAisles
AND R.ID<=B.NbrRows
AND T.ID<=B.NbrTiers

And of course, you can do a bunch of other things with the tally table as well

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources