Answers to: Use this SQL with Sybasehttps://sqlanywhere-forum.sap.com/questions/21487/use-this-sql-with-sybase<p>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.</p>
<pre class="codehilite"><code>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</code></pre>enFri, 02 May 2014 17:58:08 -0400Answer by Volker Barthhttps://sqlanywhere-forum.sap.com/questions/21487/use-this-sql-with-sybase/21614<p>Here's a sample with a stored procedure. It uses the following assumptions:</p>
<ul>
<li>There's a kind of order for the length entries - here by adding a primary key "length_nr" to your sample table.</li>
<li>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:).</li>
<li>No length will be larger than the drum's capacity.</li>
<li>"Unit" is ignored.</li>
<li>Here lengthes with more than quantity will fit on the same drum (i.e. they won't be distributed to different drums).</li>
</ul>
<p>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...</p>
<pre class="codehilite"><code class="language-sql">-- 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);</code></pre>
<p>This will return the following result set:</p>
<pre>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
</pre>Volker BarthFri, 02 May 2014 17:58:08 -0400https://sqlanywhere-forum.sap.com/questions/21487/use-this-sql-with-sybase/21614Answer by Nick Elson SAP SQL Anywherehttps://sqlanywhere-forum.sap.com/questions/21487/use-this-sql-with-sybase/21512<p>For those who are algorithmically inclined ...
This appears to me as being the SQL approximation of the one-dimensional "First Fit"
heuristic algorithm. <em>{a heuristic for a family of N-P complete problems related to the "Bin Packing problem"}</em> As such, without some sort or recursion or look ahead ... it may take some time to find a simplified algorithm to make this happen.</p>
<p>The original solution is a creative utilization of SQL Server features. A similarly crafty SQL Anywhere solution may take a bit of effort. </p>Nick Elson SAP SQL AnywhereMon, 21 Apr 2014 16:49:45 -0400https://sqlanywhere-forum.sap.com/questions/21487/use-this-sql-with-sybase/21512Answer by Nick Elson SAP SQL Anywherehttps://sqlanywhere-forum.sap.com/questions/21487/use-this-sql-with-sybase/21508<p>My simplistic attempt to split this into 2 parts with the use of a stored procedure fails. I suspect that may be because of inlining ...</p>
<p>Maybe some other readers have a better approach.</p>Nick Elson SAP SQL AnywhereMon, 21 Apr 2014 14:33:15 -0400https://sqlanywhere-forum.sap.com/questions/21487/use-this-sql-with-sybase/21508Answer by Nick Elson SAP SQL Anywherehttps://sqlanywhere-forum.sap.com/questions/21487/use-this-sql-with-sybase/21503<p>I just went through the same exercise as Breck and I concur that the main issue arises with the inclusion of cte3. </p>
<p>I suspect this is due to the documented <a href="http://dcx/index.html#sa160/en/dbusage/recursive-table-expr-sqlug.html">restriction</a>:</p>
<pre class="codehilite"><code>"References to other recursive common table expressions cannot appear within
the definition of recursive common table expressions as recursive common table
expressions cannot be mutually recursive."</code></pre>
<p>In the case of this query, the definitions of cte and cte3 are both recursive (common table expressions), so that would seem to be the most likely candidate for the invalidness of cte3.</p>
<p>I don't know what the best approach is here; one could possibly inject a layer like a stored procedure or temporary stored procedure to isolate out the references ... but that seems inefficient.</p>Nick Elson SAP SQL AnywhereMon, 21 Apr 2014 13:23:52 -0400https://sqlanywhere-forum.sap.com/questions/21487/use-this-sql-with-sybase/21503