Wednesday, March 7, 2012

how to find empty slot in a rack

Dear All,

i want to find no of empty rack(two dimensional ). i am using sql2000

i have a rack of two dimensional where every slot is recognized by rowno and columnno now in every slot i placed item (captured by itemcode). nOw i want to find slot do not assing any item
please give me some idea

there is rackmst( where i define max_no_row and max_no_cols).

Please help


thanks

I am assuming that the array is regular (all rows have the same numbert of slots). There are two ways:

Method 1:

Write a cursor to loop through the rows. For each row, loop through the columns using an IF NOT EXISTS on the contents table.

Method 2:

Create row and column tables and do a SELECT on them with a cartesian join and qualify this with a WHERE ROW.ID + COLUMN..ID NOT IN (SELECT ROW_ID + COLUMN_ID FROM CONTENTS)

(Your SQL query will need to use CONVERT to convert the Int cvalues of the Id columns to VARCHAR before concatenating them)

HTH

No comments:

Post a Comment