May 23, 2018 at 7:07 am
Hi Guys,
I also used cursor in the recent situation where I need to fire dynamic queries on the specific set of databases. I used the cursor to loop over the databases and create and fire the dynamic query.
Is there alternate to this case?
Thanks
May 23, 2018 at 7:23 am
varunchopra.apj - Wednesday, May 23, 2018 7:07 AMHi Guys,
I also used cursor in the recent situation where I need to fire dynamic queries on the specific set of databases. I used the cursor to loop over the databases and create and fire the dynamic query.
Is there alternate to this case?Thanks
None that would make a difference for performance. I do, however, usually just generate all of the code at once and execute it as a single variable source. I also use the older method of using a Temp Table to do such things but that's only because I write cursors so infrequently that it's just easier for me to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2018 at 8:32 am
Jeff Moden - Wednesday, May 23, 2018 7:23 AMvarunchopra.apj - Wednesday, May 23, 2018 7:07 AMHi Guys,
I also used cursor in the recent situation where I need to fire dynamic queries on the specific set of databases. I used the cursor to loop over the databases and create and fire the dynamic query.
Is there alternate to this case?Thanks
None that would make a difference for performance. I do, however, usually just generate all of the code at once and execute it as a single variable source. I also use the older method of using a Temp Table to do such things but that's only because I write cursors so infrequently that it's just easier for me to do.
I agree with both comments. I usually write the code to set everything to a variable and execute it. An alternative is to PRINT or SELECT all of the statements, copy them and execute them. But when trying to do something quick and taking into account that SQL Prompt provides many snippets, sometimes I just use the one to create a cursor and make the adjustments.
May 23, 2018 at 9:02 am
paul s-306273 - Wednesday, May 23, 2018 1:38 AMJeff Moden - Tuesday, May 22, 2018 9:19 PMLynn Pettis - Tuesday, May 22, 2018 1:07 PMjcelko212 32090 - Tuesday, May 22, 2018 12:59 PMRonnie65 - Friday, May 18, 2018 10:22 AMIn what situations should i use cursors over joins. I know that joins are much more faster than using cursors, but I was thinking there has to be a situation when i would want to use cursors over joins. Thanks !The main reason we have them in SQL is that the original SQL engines back in the 1980s were built on top of existing filesystems. Also, as Jim great once said, we didn't know what the hell we were doing 🙂 the filesystems already had cursors in them. And if you grew up with IBM tape filesystems, it would recognize all of the basic cursor syntax is literally the commands from the original IBM tape files with a little different syntax.
I've written five cursors in my career, and I know the three of them should not have been written because they could do it with declarative code. Back in the old Sybase days, when we were teaching cursors, the example was updating the bookstore sample table. The problem is to give a discount on the expensive books (price greater than $25) for advertising purposes, but to raise the price on the cheap books. The naïve code look like this:
BEGIN
IF book_price <= 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 1.10
END IF;
IF book_price > 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 0.90
END IF;
END;now consider a book priced at exactly $25. The first update in the sequence raises the price to $27.50. But now, but now the code goes sequentially to the next update and adjust the price to $24.75. This is clearly wrong.
The training example solution was to write a simple cursor with a row by row piece of logic to control the update. Later, we got the CASE expression and could do this with a simple update statement after SQL–92.
If you can find a computer science major, he might be able to dig up one of those horrible and mostly useless formal proofs that it is always possible to translate in LALR (1) procedural language into declarative primitive recursive functions.
Well, Joe, that's because your code above is flawed, period.
I'm thinking that was the whole point. He did call it the "naïve" code, which I took as a much more gentle way of saying "This is an example of what can go wrong if you're an idiot". 😀 And, no... Joe didn't write that. It's the example from the scenario he described.
Thanks for posting that Jeff. This is almost certainly going to lead to Lynn apologising profusely to Joe and that apology being accepted with great humility. The war of words will end and the whole community will be happy. That, or more likely thing will continue as normal.
This example was what came with the original Sybase training materials back then! I did it from memory, but the idea was that if you did a set-oriented statements with an intersection, that common subset could be updated twice. Then there is the "October problem" where a table is re-sorted while being changed. I think Jeff Garbus (Soaring Eagle Consulting) found an update using pure math that avoided the problem, but I don't have it in my notes.
I hope Lynn does do anything such thing! I love some conflict and adversarial content. Then we go out an have a (virtual) beer.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 23, 2018 at 9:04 am
paul s-306273 - Wednesday, May 23, 2018 1:38 AMJeff Moden - Tuesday, May 22, 2018 9:19 PMLynn Pettis - Tuesday, May 22, 2018 1:07 PMjcelko212 32090 - Tuesday, May 22, 2018 12:59 PMRonnie65 - Friday, May 18, 2018 10:22 AMIn what situations should i use cursors over joins. I know that joins are much more faster than using cursors, but I was thinking there has to be a situation when i would want to use cursors over joins. Thanks !The main reason we have them in SQL is that the original SQL engines back in the 1980s were built on top of existing filesystems. Also, as Jim great once said, we didn't know what the hell we were doing 🙂 the filesystems already had cursors in them. And if you grew up with IBM tape filesystems, it would recognize all of the basic cursor syntax is literally the commands from the original IBM tape files with a little different syntax.
I've written five cursors in my career, and I know the three of them should not have been written because they could do it with declarative code. Back in the old Sybase days, when we were teaching cursors, the example was updating the bookstore sample table. The problem is to give a discount on the expensive books (price greater than $25) for advertising purposes, but to raise the price on the cheap books. The naïve code look like this:
BEGIN
IF book_price <= 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 1.10
END IF;
IF book_price > 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 0.90
END IF;
END;now consider a book priced at exactly $25. The first update in the sequence raises the price to $27.50. But now, but now the code goes sequentially to the next update and adjust the price to $24.75. This is clearly wrong.
The training example solution was to write a simple cursor with a row by row piece of logic to control the update. Later, we got the CASE expression and could do this with a simple update statement after SQL–92.
If you can find a computer science major, he might be able to dig up one of those horrible and mostly useless formal proofs that it is always possible to translate in LALR (1) procedural language into declarative primitive recursive functions.
Well, Joe, that's because your code above is flawed, period.
I'm thinking that was the whole point. He did call it the "naïve" code, which I took as a much more gentle way of saying "This is an example of what can go wrong if you're an idiot". 😀 And, no... Joe didn't write that. It's the example from the scenario he described.
Thanks for posting that Jeff. This is almost certainly going to lead to Lynn apologising profusely to Joe and that apology being accepted with great humility. The war of words will end and the whole community will be happy. That, or more likely thing will continue as normal.
Nope, not going to apologize. Also, pretty sure it wouldn't be accepted with anything resembling humility.
May 23, 2018 at 11:37 am
Lynn Pettis - Wednesday, May 23, 2018 9:04 AMpaul s-306273 - Wednesday, May 23, 2018 1:38 AMJeff Moden - Tuesday, May 22, 2018 9:19 PMLynn Pettis - Tuesday, May 22, 2018 1:07 PMjcelko212 32090 - Tuesday, May 22, 2018 12:59 PMRonnie65 - Friday, May 18, 2018 10:22 AMIn what situations should i use cursors over joins. I know that joins are much more faster than using cursors, but I was thinking there has to be a situation when i would want to use cursors over joins. Thanks !The main reason we have them in SQL is that the original SQL engines back in the 1980s were built on top of existing filesystems. Also, as Jim great once said, we didn't know what the hell we were doing 🙂 the filesystems already had cursors in them. And if you grew up with IBM tape filesystems, it would recognize all of the basic cursor syntax is literally the commands from the original IBM tape files with a little different syntax.
I've written five cursors in my career, and I know the three of them should not have been written because they could do it with declarative code. Back in the old Sybase days, when we were teaching cursors, the example was updating the bookstore sample table. The problem is to give a discount on the expensive books (price greater than $25) for advertising purposes, but to raise the price on the cheap books. The naïve code look like this:
BEGIN
IF book_price <= 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 1.10
END IF;
IF book_price > 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 0.90
END IF;
END;now consider a book priced at exactly $25. The first update in the sequence raises the price to $27.50. But now, but now the code goes sequentially to the next update and adjust the price to $24.75. This is clearly wrong.
The training example solution was to write a simple cursor with a row by row piece of logic to control the update. Later, we got the CASE expression and could do this with a simple update statement after SQL–92.
If you can find a computer science major, he might be able to dig up one of those horrible and mostly useless formal proofs that it is always possible to translate in LALR (1) procedural language into declarative primitive recursive functions.
Well, Joe, that's because your code above is flawed, period.
I'm thinking that was the whole point. He did call it the "naïve" code, which I took as a much more gentle way of saying "This is an example of what can go wrong if you're an idiot". 😀 And, no... Joe didn't write that. It's the example from the scenario he described.
Thanks for posting that Jeff. This is almost certainly going to lead to Lynn apologising profusely to Joe and that apology being accepted with great humility. The war of words will end and the whole community will be happy. That, or more likely thing will continue as normal.
Nope, not going to apologize. Also, pretty sure it wouldn't be accepted with anything resembling humility.
So Paul was right - he did say "thing will continue as normal" was more likely.
Tom
May 23, 2018 at 11:53 am
This example was what came with the original Sybase training materials back then! I did it from memory, but the idea was that if you did a set-oriented statements with an intersection, that common subset could be updated twice. Then there is the "October problem" where a table is re-sorted while being changed. I think Jeff Garbus (Soaring Eagle Consulting) found an update using pure math that avoided the problem, but I don't have it in my notes.
That sounds like the "Halloween" problem and I believe implementers try to detect when this will happen and "eager spool" to prevent it. I could be mistaken obviously!
May 23, 2018 at 12:05 pm
TomThomson - Wednesday, May 23, 2018 11:37 AMSo Paul was right - he did say "thing will continue as normal" was more likely.
Guess so, but I am done feeding the troll.
May 23, 2018 at 12:13 pm
Lynn Pettis - Tuesday, May 22, 2018 1:07 PMjcelko212 32090 - Tuesday, May 22, 2018 12:59 PMRonnie65 - Friday, May 18, 2018 10:22 AMIn what situations should i use cursors over joins. I know that joins are much more faster than using cursors, but I was thinking there has to be a situation when i would want to use cursors over joins. Thanks !The main reason we have them in SQL is that the original SQL engines back in the 1980s were built on top of existing filesystems. Also, as Jim great once said, we didn't know what the hell we were doing 🙂 the filesystems already had cursors in them. And if you grew up with IBM tape filesystems, it would recognize all of the basic cursor syntax is literally the commands from the original IBM tape files with a little different syntax.
I've written five cursors in my career, and I know the three of them should not have been written because they could do it with declarative code. Back in the old Sybase days, when we were teaching cursors, the example was updating the bookstore sample table. The problem is to give a discount on the expensive books (price greater than $25) for advertising purposes, but to raise the price on the cheap books. The naïve code look like this:
BEGIN
IF book_price <= 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 1.10
END IF;
IF book_price > 25.00
THEN UPDATE Bookstore
SET book_price = book_price * 0.90
END IF;
END;now consider a book priced at exactly $25. The first update in the sequence raises the price to $27.50. But now, but now the code goes sequentially to the next update and adjust the price to $24.75. This is clearly wrong.
The training example solution was to write a simple cursor with a row by row piece of logic to control the update. Later, we got the CASE expression and could do this with a simple update statement after SQL–92.
If you can find a computer science major, he might be able to dig up one of those horrible and mostly useless formal proofs that it is always possible to translate in LALR (1) procedural language into declarative primitive recursive functions.
Well, Joe, that's because your code above is flawed, period.
Actually, I'm pretty sure that the part I highlighted above is the more relevant issue, and is still true this many years later.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply