Solved

# PLSQL - I need to calculate the number of consecutive shelf slots

Posted on 2007-08-01
357 Views
Hi All,
I have a table of ALL shelf locations and a table of used shelf locations.
To see the ones that are available I have the following:
Select LOC_CD
from LOCATIONS
where LOC_CD NOT IN (select LOC_CD from COPY_SLOT_XREF)
Ex:
406B07
406B08
406B09
406D10
406D11
406E11
406E12
406F04
406F05
406F06
406F07
406F14
406F15

But, now I want to show that list as the starting Location and the number of consecutive shelf slots next to it as so:
406B07     5
406E11     2
406F04     4
406F14     2

I need to find all the locations that have X consecutive shelf slots
Ex: Search on 2
406E11     2
406F14     2

0
Question by:bcarlis

LVL 42

Accepted Solution

Select * FROM
(
Select T1.LOC_CD,
(Select Count(*) from LOCATIONS T2 where
where T2.LOC_CD NOT IN (select LOC_CD from COPY_SLOT_XREF)
and T2.LOC_CD > T1.LOC_CD
and T2.LOC_CD < ISNULL((Select T3.LOC_CD from LOCATIONS T3
where T3.LOC_CD > T1.LOC_CD
and T3.LOC_CD IN (select LOC_CD from COPY SLOT_XREF),'999999')
from LOCATIONS T1
where T1.LOC_CD NOT IN (select LOC_CD from COPY_SLOT_XREF)
) V1
0

LVL 2

Author Comment

Too many errors to test this... or make out what is being done...
1.) Select * FROM (Select T1.LOC_CD, (Select Count(*) from LOCATIONS T2 where  where
2.) Select * FROM (select....) ADJACENT_SLOTS  from LOCATIONS T1  ??

0

LVL 2

Author Comment

Here is how it is now...

1.) I get the latest data and put it into a temp table
DECLARE
l_grp_nbr      NUMBER :=1;
l_LAST_LOC_ROW      NUMBER :=0;
l_LAST_LOC_RACK      VARCHAR2(3):='';
l_LAST_LOC_SLOT      VARCHAR2(2):='0';
l_LAST_LOC_CD      VARCHAR2(6):='';
BEGIN
DELETE from BR_LOCATION_RPT_TMP;

FOR REC IN (Select LOC_ROW, LOC_RACK, LOC_SLOT, LOC_CD from BR_LOCATIONS
where LOC_CD NOT IN (select LOC_CD from BR_COPY_SLOT_XREF)) LOOP

-- Consecutive = REC.LOC_ROW = l_LAST_LOC_ROW
--   and REC.LOC_RACK = l_LAST_LOC_RACK
--    and REC.LOC_SLOT = l_LAST_LOC_SLOT + 1
IF NOT(REC.LOC_ROW=l_LAST_LOC_ROW and REC.LOC_RACK = l_LAST_LOC_RACK
and REC.LOC_SLOT = (l_LAST_LOC_SLOT + 1)) THEN
-- If NOT consecutive then Increment Group Number
l_grp_nbr:=l_grp_nbr + 1;
END IF;

INSERT INTO BR_LOCATION_RPT_TMP
(GRP_NBR, LOC_ROW, LOC_RACK, LOC_SLOT, LOC_CD)
VALUES
(l_grp_nbr, REC.LOC_ROW, REC.LOC_RACK, REC.LOC_SLOT, REC.LOC_CD);

-- Save this LOC_CD
l_LAST_LOC_ROW :=REC.LOC_ROW;
l_LAST_LOC_RACK:=REC.LOC_RACK;
l_LAST_LOC_SLOT:=REC.LOC_SLOT;
END LOOP;
END;

2.)  I am able to search using search criteria provided by customer.
DECLARE
DynSQL   VARCHAR2(4000);
l_where  VARCHAR2(400):= '';
l_having VARCHAR2(400):= '';
BEGIN
IF (:P42_ROW_NBR <> 0) THEN
l_where := 'where LOC_ROW = '||:P42_ROW_NBR;
END IF;
IF (:P42_NBR_BXS IS NOT NULL) then
l_having := 'having count(*) = '||:P42_NBR_BXS ;
END IF;

DynSQL:='select min(LOC_CD), '||
'       count(*)     '||
'  from BR_LOCATION_RPT_TMP '||
l_where||
' group by GRP_NBR '||
l_having||
' order by min(LOC_CD)';
return DynSQL;
END;

I wish I could do this without having to build the report data into a temp table, this takes about 15-20 seconds.

Any improvements???

Bill

0

LVL 76

Assisted Solution

I won't say it's 100% possible but from what I can get from your code, it looks like you should be able to get there in a single statement.

I'm afraid that I'll need a little more information about your data.  To save a lot of explaining, can you post some sample data and expected results?  A brief description on how the columns match up wouldn't hurt.

Will also need full database version ( all 4 numbers.  i.e./ 10.2.0.3 ).
0

LVL 2

Author Comment

Taken slots on shelves:
Table: BR_COPY_SLOT_XREF
Columns: COPY_ID, LOC_CD
3812 401A01
3818 401A08
3820 401A12
3824 401B01
3825 401B05
3826 401B09
3832 401C01
3833 401C05
3833 401C06
3834 401C07
3836 401C11
3837 401C12
3840 401D03
3841 401D07
3843 401D11

Available Slots on shelves:
Table:  BR_LOCATIONS
Columns: LOC_CD, LOC_ROW, LOC_RACK, LOC_SLOT
Data:
405K05 405 K 01
405K06 405 K 02
405K05 405 K 03
405K06 405 K 04
405K05 405 K 05
405K06 405 K 06
405K07 405 K 07
405K08 405 K 08
405K09 405 K 09
405K10 405 K 10
405K11 405 K 11
405K12 405 K 12
405K13 405 K 13
405K14 405 K 14
405K15 405 K 15
405L01 405 L 01
405L02 405 L 02
405L03 405 L 03
.......................

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

My requirement is to be able to query the UNUSED slots by how many are available consecutively.
-- Consecutive = REC.LOC_ROW = l_LAST_LOC_ROW
--   and REC.LOC_RACK = l_LAST_LOC_RACK
--    and REC.LOC_SLOT = l_LAST_LOC_SLOT + 1

Anymore?

0

LVL 42

Expert Comment

Give this a try.  I tested it in TSQL, but it should be pretty close for PLSQL, as well.   Uncomment the three lines to limit the results by number of consequtive open slots.

--Select * FROM
--(
Select T1.*,
(
Select Count(*) from BR_LOCATIONS T2
where T2.LOC_ROW = T1.LOC_ROW
and T2.LOC_RACK = T1.LOC_RACK
and T2.LOC_SLOT  >= T1.LOC_SLOT
and T2.LOC_SLOT  <
(Select isnull(min(T3.LOC_SLOT),'999') from BR_LOCATIONS T3
where T3.LOC_ROW = T1.LOC_ROW
and T3.LOC_RACK = T1.LOC_RACK
and T3.LOC_SLOT >= T1.LOC_SLOT
and T3.LOC_CD IN (select LOC_CD from BR_COPY_SLOT_XREF))
) OPEN_SLOTS
from BR_LOCATIONS T1
) V1
--where OPEN_SLOTS >=2
order by loc_row, loc_rack, loc_slotslot
0

LVL 42

Expert Comment

Drat, I should have commented the ') V1' line, as well.
0

LVL 76

Expert Comment

If the SQL from dqmq doesn't get you there, based on the data provided, what are your expected results?
0

## Featured Post

### Suggested Solutions

This article will show, step by step, how to integrate R code into a R Sweave document
This is an explanation of a simple data model to help parse a JSON feed
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.