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.
asked 20 Apr '14, 04:20 Rolle |
Here's a sample with a stored procedure. It uses the following assumptions:
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...
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 answered 02 May '14, 17:58 Volker Barth Comment Text Removed
Comment Text Removed
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:
When I run the procedure I would like the presents like this:
Is it possible?
(03 May '14, 13:01)
Rolle
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.
(04 May '14, 05:44)
Chris Werner
Replies hidden
Can't edit my last comment again so I post an improved solution here
(04 May '14, 07:12)
Chris Werner
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?
(04 May '14, 08:06)
Rolle
Not sure what the best solution is. Works for me as shown. Try a "FOR UPDATE" clause in the cursor deklaration:
(04 May '14, 09:33)
Chris Werner
Ok, I try it. I delete this first:
(04 May '14, 09:52)
Rolle
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:
(04 May '14, 14:21)
Rolle
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.
(04 May '14, 15:01)
Chris Werner
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??
(04 May '14, 15:40)
Rolle
1
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_en12/help_top_index.htm&docSetID=1744. 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.
(04 May '14, 16:37)
Chris Werner
More comments hidden
|
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:
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. answered 21 Apr '14, 13:23 Nick Elson S... Graeme Perrow Ok. Can I do this SQL in a different way but with the same functionality?
(21 Apr '14, 13:27)
Rolle
|
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. answered 21 Apr '14, 14:33 Nick Elson S... 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...
(21 Apr '14, 15:15)
Rolle
Replies hidden
1
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...
(21 Apr '14, 16:48)
Breck Carter
|
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. answered 21 Apr '14, 16:49 Nick Elson S... I would be extremely grateful if someone could help me with this then I stalled. Needed SP so it's ok too.
(27 Apr '14, 13:23)
Rolle
Someone...?
(29 Apr '14, 14:22)
Rolle
Replies hidden
Please answer my yesterday's comment on the question...:)
(29 Apr '14, 15:18)
Volker Barth
I can not see your comment somewhere? Where can I see it?
(29 Apr '14, 15:32)
Rolle
Replies hidden
Below the question, there are currently 6 nested comments. Click on "Show all" if not all are displayed...
(29 Apr '14, 15:37)
Volker Barth
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.
(29 Apr '14, 17:59)
Chris Werner
Replies hidden
1
> 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 :)
(29 Apr '14, 19:09)
Breck Carter
Thanks for all the replies. I have quite stuck and can not solve this yourself. If we make this solution based on the features that sybase provides and with a stored procedure, can anyone help me with that?
(02 May '14, 12:27)
Rolle
|
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)...
However, the full query fails with the hopelessly vague "Invalid recursive query" error...
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
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:
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.
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?
Yes, I mean SQL Anywhere and we're talking about version 12...
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