Use this SQL with Sybase

[Former Member]

I have a good working sql that works in sql server. The logical computation calculates how many lengths will fit a barrel. Maximum capacity per drum is 14300 meters. My question is how I can get this to work with Sybase.

Declare @tblLength table (num int, qty int, unit varchar(20))

insert @tblLength
values
(2,2500,''),
(1,3800,''),
(1,4200,''),
(1,6500,''),
(1,8300,'')

;with cte as
(
select i=1, qty, unit from @tblLength where num >= 1
union all
select i=i+1, t.qty, t.unit from @tblLength t, cte where t.qty=cte.qty and t.unit = cte.unit and t.num >= i+1
),
cte2 as
(
select *, id=row_number() over (order by qty desc) from cte
),
cte3 as
(
select j=1, qty, unit, id, tot=qty, drum=1 from cte2 where id = 1
union all
select j=j+1, cte2.qty, cte2.unit, cte2.id,
tot=case when cte3.tot+cte2.qty > 14300 then cte2.qty else cte3.tot+cte2.qty end,
drum=case when cte3.tot+cte2.qty > 14300 then cte3.drum + 1 else cte3.drum end
from cte2,cte3 where cte2.id = j+1
)
select drum, qty, unit from cte3

[Breck_Carter]

Please tell us what the third view cte3 is trying to accomplish ( because it's the one that's causing me grief 🙂

...or wait until Ivan Bowman sees this question 🙂

Changing the declare (and adding a semicolon), plus adding the RECURSIVE keyword, makes cte and cte2 work (or at least, they stop throwing error messages)...

Declare LOCAL TEMPORARY TABLE @tblLength (num int, qty int, unit varchar(20));

insert @tblLength
values
(2,2500,''),
(1,3800,''),
(1,4200,''),
(1,6500,''),
(1,8300,'')

;with RECURSIVE cte ( i, qty, unit ) as
(
select i=1, qty, unit from @tblLength where num >= 1
union all
select i=i+1, t.qty, t.unit from @tblLength t, cte where t.qty=cte.qty and t.unit = cte.unit and t.num >= i+1
),
cte2 as
(
select *, id=row_number() over (order by qty desc) from cte
)
select * from cte2;

i,qty,unit,id
1,8300,'',1
1,6500,'',2
1,4200,'',3
1,3800,'',4
1,2500,'',5

However, the full query fails with the hopelessly vague "Invalid recursive query" error...

Declare LOCAL TEMPORARY TABLE @tblLength (num int, qty int, unit varchar(20));

insert @tblLength
values
(2,2500,''),
(1,3800,''),
(1,4200,''),
(1,6500,''),
(1,8300,'')

;with RECURSIVE cte ( i, qty, unit ) as
(
select i=1, qty, unit from @tblLength where num >= 1
union all
select i=i+1, t.qty, t.unit from @tblLength t, cte where t.qty=cte.qty and t.unit = cte.unit and t.num >= i+1
),
cte2 as
(
select *, id=row_number() over (order by qty desc) from cte
),
cte3 ( j, qty, unit, id, tot, drum ) as
(
select j=1, qty, unit, id, tot=qty, drum=1 from cte2 where id = 1
union all
select j=j+1, cte2.qty, cte2.unit, cte2.id,
tot=case when cte3.tot+cte2.qty > 14300 then cte2.qty else cte3.tot+cte2.qty end,
drum=case when cte3.tot+cte2.qty > 14300 then cte3.drum + 1 else cte3.drum end
from cte2,cte3 where cte2.id = j+1
)
select drum, qty, unit from cte3;

Could not execute statement.
Invalid recursive query
SQLCODE=-921, ODBC 3 State="42000"

[Former Member]

The SQL should do this. Maybe there is another (smarter) way to do this in sybase?

I have several lengths to be wound on drums (lengths may vary)

Ex: Table tblLength

Count----qty----Unit
---------------------
2--------2500---meter
1--------3800---meter
1--------4200---meter
1--------6500---meter
1--------8300---meter


On every drum I only have a max capacity for 14,300 meters.
How can I use a smart SQL to calculate it like this:
To wrap on every drum:
1 x 13000 (2x2500 + 1x3800 + 1x4200)
1 x 6500 (1x6500)
1 x 8300 (1x8300)

[Former Member]

Cte3 takes the first row and allocates it to drum 1. It then goes through the following rows allocating to the same drum until it's full then starts on the next. Keeps going until it runs out of lengths.

[regdomaratzki]

Can we assume when you say "how I can get this to work with Sybase" that you mean SQL Anywhere and not Adaptive Server Enterprise or another Sybase RDBMS? If it is SQL Anywhere, can you tell us what version of the product you are using?

[Former Member]

Yes, I mean SQL Anywhere and we're talking about version 12...

[VolkerBarth]

Is there any kind of optimization included, i.e. do you try to use as less drums as possible? (I won't try to find this out by studying your CTEs...)

From your sample, I would conclude it's much simpler and you simply add the rows in sequential order, until one drum is full. BTW: Is there an imposed order like a primary key?

If so, a different approach might be to use a WINDOW function to build adjacent sums and group them to fit within the according limit. (I don't have an actual sample - it's just a guess that this could work...)

---

FWIW, here's a link to the SQL Anywhere SCN community with a blog article on WINDOW functions and an according whitepaper - AFAIK the WINDOW clause in SQL Anywhere is much more powerful/feature-rich than with current MS SQL versions...:

From the Archives: The Window Operator and Window Aggregate Functions

[VolkerBarth · Accepted solution]

Here's a sample with a stored procedure. It uses the following assumptions:

• There's a kind of order for the length entries - here by adding a primary key "length_nr" to your sample table.
• The lengthes shall be distributed on the drums in sequential order (i.e. there's no attempt to optimize for minimum "free space") - that would be a different task:).
• No length will be larger than the drum's capacity.
• "Unit" is ignored.
• Here lengthes with more than quantity will fit on the same drum (i.e. they won't be distributed to different drums).

Note: I usually use Watcom SQL but as your sample code has made use of T-SQL style, I have tried to combine that - the identifiers are not very well chosen but I hope you get the idea...

-- create the local temp table with sample length entries

drop table @tblLength;
declare local temporary table @tblLength (nr int default autoincrement primary key, num int, qty int, unit varchar(20))
   not transactional;

insert @tblLength (num, qty, unit)
values
   (2,2500,''),
   (1,3800,''),
   (1,4200,''),
   (1,6500,''),
   (1,8300,'');

-- select * from @tblLength --

-- STP with maximum drum capacity as parameter    
create or replace procedure STP_DistributeLengthToDrum(@max_drum_length int)
begin
   -- local temp table to distribute lengthes to drums
   declare local temporary table @distribution (length_nr int, num int, qty int, drum_nr int) not transactional;
   -- local variables to store current drum number and left space
   declare @current_drum_nr int = 1;
   declare @current_drum_space int = 0;
   -- simple sanitiy check to use a default max drum capacity
   if isnull(@max_drum_length, 0) = 0 then
      set @max_drum_length = 14300;
   end if;
   set @current_drum_space = @max_drum_length;

   begin
      -- loop through all lengthes and test if they will fit on the current drum
      for for_crs as crs cursor for
         select nr as @length_nr, num as @num, qty as @qty from @tblLength
         order by nr
      do
         -- if it won't fit, fill the next drum
         if @num * @qty > @current_drum_space then
             set @current_drum_nr = @current_drum_nr + 1;
             set @current_drum_space = @max_drum_length;
         end if;
         -- note the occupied space for the next test
         set @current_drum_space = @current_drum_space - @num * @qty;
         -- store the according drum_nr for the current length entry
         insert @distribution values (@length_nr, @num, @qty, @current_drum_nr);
      end for;
   end;

   -- final select to list each drum with the according length entries and the occupied and left capacity
   select drum_nr, list(length_nr) as length_list,
      list(num * qty) as qty_list, sum(num * qty) as total_length,
      @max_drum_length - total_length as space_left
   from @distribution
   group by drum_nr
   order by drum_nr;
end;

-- call the procedure - note you can also combine that in joins with tables...
select * from STP_DistributeLengthToDrum(14300);


This will return the following result set:
drum_nr, length_list, qty_list, total_length, space_left
1,        1,2,3,      5000,3800,4200,   13000,   1300
2,        4,          6500,              6500,   7800
3,        5,          8300,              8300,   6000

[Former Member · Accepted solution]

Thank you!

This was exactly what I was looking for. The only thing I would add, and you also mentioned is to optimize the calculation so it fills the drums as much as possible and so there will be as few drums as possible. It will not be optimal if it takes the length of a sequence. Did you have some smart logic to it which can be supplemented in this?

Each length of tbllength are on separate lines. Like this:

insert @tblLength (num, qty, unit)
values
   (1,2500,''),
   (1,2500,''),
   (1,3800,''),
   (1,4200,''),
   (1,6500,''),
   (1,8300,'');


When I run the procedure I would like the presents like this:
drum_nr---count---qty_list---total_length---space_left
______________________________________________________
1---------2-------2500-------13000----------1300
1---------1-------3800-------13000----------1300
1---------1-------4200-------13000----------1300
2---------1-------6500-------6500-----------7800
3---------1-------8300-------8300-----------6000


Is it possible?

[Former Member · Accepted solution]

I'd try to make Volkers algorithm "greedy", which means to take as many lengthes as possible and take the big ones first. Plus don't stop using a drum when the next length doesn't fit but try smaller ones until no length fits. That wouldn't yield the optimal solution (as far as I remeber from student days) but some kind of optimization.

Doing so in your sample setting there is no drum spared but more space left on the third drum.

My procedural SQL skills are not really exciting so the following code needs carefull error checking and is to be optimized. It is indeed an extension of Volkers code. Please note that I've inserted a length > 14300 to show one of the problems with the sample code.

Presenting the results might be a nice task to optimize ones sql skills for the future.

-- create the local temp table with sample length entries

drop table @tblLength
;

declare local temporary table @tblLength (nr int default autoincrement primary key, num int, qty int, unit varchar(20))
   not transactional
;

insert @tblLength (num, qty, unit)
values
   (2,2500,''),
   (1,3800,''),
   (1,4200,''),
   (1,6500,''),
   (1,8300,''),
   (1,14400,'')
;


-- select * from @tblLength --

-- STP with maximum drum capacity as parameter    
create or replace procedure STP_DistributeLengthToDrum(@max_drum_length int)
begin

   -- local temp table to distribute lengthes to drums
   declare local temporary table @distribution (length_nr int, num int, qty int, drum_nr int) not transactional;
   -- local variables to store current drum number and left space
   declare @current_drum_nr int = 0;
   declare @current_drum_space int = 0;
   declare @count int = 0 ;

   -- simple sanitiy check to use a default max drum capacity
   if isnull(@max_drum_length, 0) = 0 then
      set @max_drum_length = 14300;
   end if;

    begin 

        set @count = (select sum(num) from @tblLength where qty<=@max_drum_length) ; 
        while @count > 0  
        loop   

            set @current_drum_nr = @current_drum_nr + 1;
            set @current_drum_space = @max_drum_length;

            for for_crs as crs cursor for
                select nr as @length_nr, num as @num, qty as @qty from @tblLength
                where num > 0 
                order by qty desc
            do

                if @qty <= @current_drum_space then

                    set @current_drum_space = @current_drum_space - @qty;
                    insert @distribution values (@length_nr, 1, @qty, @current_drum_nr);

                    update @tblLength
                        set num = num - 1 
                where nr = @length_nr; 

                end if ;

            end for ; 

            set @count = (select sum(num) from @tblLength where qty<=@max_drum_length) ;

        end loop;

   end ; 
   -- final select to list each drum with the according length entries and the occupied and left capacity
   select drum_nr, list(length_nr) as length_list,
      list(num * qty) as qty_list, sum(num * qty) as total_length,
      @max_drum_length - total_length as space_left
   from @distribution
   group by drum_nr
   order by drum_nr;
end
;

-- call the procedure - note you can also combine that in joins with tables...
select * from STP_DistributeLengthToDrum(14300)
;

[Former Member · Accepted solution]

Can't edit my last comment again so I post an improved solution here

-- create the local temp table with sample length entries

drop table @tblLength
;

declare local temporary table @tblLength (nr int default autoincrement primary key, num int, qty int, unit varchar(20))
   not transactional
;

insert @tblLength (num, qty, unit)
values
   (2,2500,''),
   (1,3800,''),
   (1,4200,''),
   (1,6500,''),
   (1,8300,''),
   (1,14400,'')
;


-- select * from @tblLength --

-- STP with maximum drum capacity as parameter    
create or replace procedure STP_DistributeLengthToDrum(@max_drum_length int)
begin

   -- local temp table to distribute lengthes to drums
   declare local temporary table @distribution (length_nr int, num int, qty int, drum_nr int) not transactional;
   -- local variables to store current drum number, left space and length count 
   declare @current_drum_nr int = 0; -- starting with 0 now!
   declare @current_drum_space int = 0;
   declare @count int = 0 ;
   declare @i int = 0 ; 

   -- simple sanitiy check to use a default max drum capacity
   if isnull(@max_drum_length, 0) = 0 then
      set @max_drum_length = 14300;
   end if;

   begin 


      -- while lengthes left in the pool
      set @count = (select sum(num) from @tblLength where qty<=@max_drum_length) ; 
      while @count > 0  
      loop   

         -- use a new drum 
         set @current_drum_nr = @current_drum_nr + 1;
         set @current_drum_space = @max_drum_length;

         -- search all left lengthes from big to small
         for for_crs as crs cursor for
            select nr as @length_nr, num as @num, qty as @qty from @tblLength
            where num > 0 
            order by qty desc
         do

            -- while length fits to the drum and is in the pool
            set @i = 1 ; 
            while @qty <= @current_drum_space and @i <= @num
            loop  

               set @i = @i + 1 ;
               -- decrease current drum space 
               set @current_drum_space = @current_drum_space - @qty;
               -- put length on the drum ...
               insert @distribution values (@length_nr, 1, @qty, @current_drum_nr);

               -- ... and take it from the pool 
               update @tblLength
                  set num = num - 1 
                where nr = @length_nr; 

            end loop ;

         end for ; 

         -- get actual length count in the pool
         set @count = (select sum(num) from @tblLength where qty<=@max_drum_length) ;

      end loop;

   end ; 
   -- final select to list each drum with the according length entries and the occupied and left capacity

   select dist.drum_nr, count(dist.qty) as count, dist.qty as qty_list, max(aggr.total_length) as total_length, max(aggr.space_left) as space_left
     from @distribution dist, 
          (select drum_nr as drum_nr, 
                  sum(qty) as total_length, 
                  @max_drum_length - total_length as space_left
             from @distribution
         group by drum_nr) aggr 
   where aggr.drum_nr = dist.drum_nr
group by dist.drum_nr, qty
order by dist.drum_nr asc, qty desc; 

end
;

-- call the procedure - note you can also combine that in joins with tables...
select * from STP_DistributeLengthToDrum(14300)
;

[Former Member · Accepted solution]

Big thanks!

Now it starts to look really good! I have a small problem and that is that I do not have the ability to update @tblLength. So there I got error ... How do I do that? Load @tblLength in a temporary table?

[Former Member · Accepted solution]

Not sure what the best solution is. Works for me as shown. Try a "FOR UPDATE" clause in the cursor deklaration:

         -- search all left lengthes from big to small
         for for_crs as crs cursor for
            select nr as @length_nr, num as @num, qty as @qty from @tblLength
            where num > 0 
            order by qty desc
         for update 
         do

[Former Member · Accepted solution]

Ok, I try it. I delete this first:

   update @tblLength
      set num = num - 1 
    where nr = @length_nr;

[Former Member · Accepted solution]

Now I understand. I'm explaining badly..

I can not update the "real" @tblLength. The table used by other features too. Records can be used several times and then it can not be set to 0 in the field num. What do I do? So I can't use:

  update @tblLength
      set num = num - 1 
    where nr = @length_nr;

[Former Member · Accepted solution]

There are a lot of possibilities. The code has to keep track of used lengthes. So you could (1) copy the original ("real") table into a temporary table OR (2) add a new colum for calculation to the original ("real") table OR (3) count the used lengthes in a second temporary table.

[Former Member · Accepted solution]

There is option 1 or 3, which are the possible. But I have hard to see where I should put it in the function. How do I create a temp table in a procedure??

[Former Member · Accepted solution]

Use (1) the "declare local temporary table" statement as shown twice im the sample code so far OR (2) examine the product documentation here: http://infocenter.sybase.com/help/index.jsp?docset=/com.sybase.help.sqlanywhere.12.0.1/sqlanywhere_e.... In either case I'd guess you shold put it in the function at the beginning for it is needed throughout the whole algorithm, shouldn't you? If you decide for option (1) from my last comment please don't forget to copy the content from the original "real" table into the newly created temporary table and to replace the table name in the code.