Question Re: Use this SQL with Sybase in Technology Q&A

Use this SQL with Sybase

Former Member — Sun, 20 Apr 2014 04:20:51 GMT

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

Re: Use this SQL with Sybase

Breck_Carter — Sun, 20 Apr 2014 09:03:20 GMT

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"

Re: Use this SQL with Sybase

Former Member — Sun, 20 Apr 2014 13:35:32 GMT

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)

Re: Use this SQL with Sybase

Former Member — Sun, 20 Apr 2014 14:38:08 GMT

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.

Re: Use this SQL with Sybase

regdomaratzki — Mon, 21 Apr 2014 08:14:23 GMT

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?

Re: Use this SQL with Sybase

Former Member — Mon, 21 Apr 2014 08:23:47 GMT

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

Re: Use this SQL with Sybase

Former Member — Mon, 21 Apr 2014 13:23:52 GMT

I just went through the same exercise as Breck and I concur that the main issue arises with the inclusion of cte3.

I suspect this is due to the documented restriction:

"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."


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.
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.

Re: Use this SQL with Sybase

Former Member — Mon, 21 Apr 2014 13:27:41 GMT

Ok. Can I do this SQL in a different way but with the same functionality?

Re: Use this SQL with Sybase

Former Member — Mon, 21 Apr 2014 14:33:15 GMT

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 ...

Maybe some other readers have a better approach.

Re: Use this SQL with Sybase

Former Member — Mon, 21 Apr 2014 15:15:22 GMT

It is best to try to do it without SP. But I can not do it, unfortunately. And it is perhaps as you say, it may not be possible without splitting...

Re: Use this SQL with Sybase

Breck_Carter — Mon, 21 Apr 2014 16:48:19 GMT

Sadly, I'd love to help, but I still don't have a clue about what the query is trying to accomplish... in spite of your valiant attempts to explain it. Perhaps if you explained it as if you were speaking to a kindergarten class...

Re: Use this SQL with Sybase

Former Member — Mon, 21 Apr 2014 16:49:46 GMT

For those who are algorithmically inclined ... This appears to me as being the SQL approximation of the one-dimensional "First Fit" heuristic algorithm. {a heuristic for a family of N-P complete problems related to the "Bin Packing problem"} As such, without some sort or recursion or look ahead ... it may take some time to find a simplified algorithm to make this happen.

The original solution is a creative utilization of SQL Server features. A similarly crafty SQL Anywhere solution may take a bit of effort.

Re: Use this SQL with Sybase

Former Member — Sun, 27 Apr 2014 13:23:27 GMT

I would be extremely grateful if someone could help me with this then I stalled. Needed SP so it's ok too.

Re: Use this SQL with Sybase

VolkerBarth — Sun, 27 Apr 2014 16:57:32 GMT

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

Re: Use this SQL with Sybase

Former Member — Tue, 29 Apr 2014 14:22:21 GMT

Someone...?

Re: Use this SQL with Sybase

VolkerBarth — Tue, 29 Apr 2014 15:18:48 GMT

Please answer my yesterday's comment on the question...:)

Re: Use this SQL with Sybase

Former Member — Tue, 29 Apr 2014 15:32:04 GMT

I can not see your comment somewhere? Where can I see it?

Re: Use this SQL with Sybase

VolkerBarth — Tue, 29 Apr 2014 15:37:18 GMT

Below the question, there are currently 6 nested comments. Click on "Show all" if not all are displayed...

Re: Use this SQL with Sybase

Former Member — Tue, 29 Apr 2014 17:59:14 GMT

I'd say this isn't a case for pure declarative SQL. Even if it is possible to implement it using recursive statements nobody will understand what's going on there. Use a procedure or implement it in the host language.

Re: Use this SQL with Sybase

Breck_Carter — Tue, 29 Apr 2014 19:09:58 GMT

> nobody will understand what's going on there

Always a danger... whenever I code recursive unions the "nobody will understand" includes ME, five minutes later 🙂