[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

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

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

How do I go about this?
0
bcarlis
Asked:
bcarlis
  • 3
  • 3
  • 2
2 Solutions
 
dqmqCommented:
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')
) ADJACENT_SLOTS
from LOCATIONS T1
where T1.LOC_CD NOT IN (select LOC_CD from COPY_SLOT_XREF)
) V1
where V1.ADJACENT_SLOTS >=2
0
 
bcarlisAuthor Commented:
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
 
bcarlisAuthor Commented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
slightwv (䄆 Netminder) Commented:
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
 
bcarlisAuthor Commented:
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
 
dqmqCommented:
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
 
dqmqCommented:
Drat, I should have commented the ') V1' line, as well.
0
 
slightwv (䄆 Netminder) Commented:
If the SQL from dqmq doesn't get you there, based on the data provided, what are your expected results?
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now