Cursor not completing when stored procedure runs within it

I am having an interesting problem I haven't seen.
First, here's the code that sets up the cursor, with a select statement
where the exec should be, and the results:
DECLARE @.order_id int,
@.row_id int,
@.qty_rtn int,
@.invoice_id int,
@.date_shipped datetime
DECLARE order_return CURSOR FOR
select r.order_id_display, r.row_id -1, r.quantity, s.line_id,
getdate() from batch..temp_response r, shipment s,
receipt_item i
where isnull(r.status, 0) >= 0 and new_status in ('R', 'U')
and i.i_order_id_display = r.order_id_display
and i.order_id = s.order_id
and i.row_id = r.row_id - 1
and i.upc=r.upc and amount = 1
and i.order_id in ('0FD94RQXB4JL9J8V4R3G5B8CC5') --for
testing purposes I selected one order only
OPEN order_return
FETCH NEXT FROM order_return INTO @.order_id, @.row_id, @.qty_rtn,
@.invoice_id, @.date_shipped
WHILE @.@.FETCH_STATUS = 0
BEGIN
select 'exec process_line_item_shipping', @.order_id, @.row_id, 0,
@.qty_rtn, @.date_shipped, @.invoice_id
-- exec process_line_item_shipping @.order_id, @.row_id, 0, @.qty_rtn,
@.date_shipped, @.invoice_id
FETCH NEXT FROM order_return INTO @.order_id, @.row_id, @.qty_rtn,
@.invoice_id, @.date_shipped
END
CLOSE order_return
DEALLOCATE order_return
This returns
exec process_line_item_shipping 491232 0 0 1
2006-06-16 12:46:19.330 534386
exec process_line_item_shipping 491232 1 0 1
2006-06-16 12:46:19.330 534386
Which is exactly what I'd expect.
HOWEVER... when I remove the comment tag off the actual SP exec
command, then I ONLY get
exec process_line_item_shipping 491232 0 0 1
2006-06-16 12:46:19.330 534386
and only the first exec statement runs.
I've done a select @.@.fetch_status before and after the exec statement,
and it's 0 each time.
The stored procedure run has no cursors within it, just several
calculations, inserts and update statements.
Can someone figure this out for me?DOINK!
Never mind, I think I figured it out. When I changed it to an
INSENSITIVE cursor, all rows were executed -- basically the updates
were invalidating the remaining row's work, and so it wouldn't fetch
anymore rows.
At least I think that's what happened.
dwcscreenwriterextremesupr...@.gmail.com wrote:
> I am having an interesting problem I haven't seen.
> First, here's the code that sets up the cursor, with a select statement
> where the exec should be, and the results:
> DECLARE @.order_id int,
> @.row_id int,
> @.qty_rtn int,
> @.invoice_id int,
> @.date_shipped datetime
> DECLARE order_return CURSOR FOR
> select r.order_id_display, r.row_id -1, r.quantity, s.line_id,
> getdate() from batch..temp_response r, shipment s,
> receipt_item i
> where isnull(r.status, 0) >= 0 and new_status in ('R', 'U')
> and i.i_order_id_display = r.order_id_display
> and i.order_id = s.order_id
> and i.row_id = r.row_id - 1
> and i.upc=r.upc and amount = 1
> and i.order_id in ('0FD94RQXB4JL9J8V4R3G5B8CC5') --for
> testing purposes I selected one order only
> OPEN order_return
> FETCH NEXT FROM order_return INTO @.order_id, @.row_id, @.qty_rtn,
> @.invoice_id, @.date_shipped
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select 'exec process_line_item_shipping', @.order_id, @.row_id, 0,
> @.qty_rtn, @.date_shipped, @.invoice_id
> -- exec process_line_item_shipping @.order_id, @.row_id, 0, @.qty_rtn,
> @.date_shipped, @.invoice_id
> FETCH NEXT FROM order_return INTO @.order_id, @.row_id, @.qty_rtn,
> @.invoice_id, @.date_shipped
> END
> CLOSE order_return
> DEALLOCATE order_return
> This returns
> exec process_line_item_shipping 491232 0 0 1
> 2006-06-16 12:46:19.330 534386
> exec process_line_item_shipping 491232 1 0 1
> 2006-06-16 12:46:19.330 534386
> Which is exactly what I'd expect.
> HOWEVER... when I remove the comment tag off the actual SP exec
> command, then I ONLY get
> exec process_line_item_shipping 491232 0 0 1
> 2006-06-16 12:46:19.330 534386
> and only the first exec statement runs.
> I've done a select @.@.fetch_status before and after the exec statement,
> and it's 0 each time.
>
> The stored procedure run has no cursors within it, just several
> calculations, inserts and update statements.
> Can someone figure this out for me?|||Nope... That's not it... because now the inserts and updates aren't
happening. Argh! Help!
dwcscreenwriterextremesupr...@.gmail.com wrote:
> DOINK!
> Never mind, I think I figured it out. When I changed it to an
> INSENSITIVE cursor, all rows were executed -- basically the updates
> were invalidating the remaining row's work, and so it wouldn't fetch
> anymore rows.
> At least I think that's what happened.
>
> dwcscreenwriterextremesupr...@.gmail.com wrote:|||>> Nope... That's not it... <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
What you did post was awful. You are using SQL cursors, which are the
worst way to use SQL -- orders of magnitude poorer performance, lack of
portability, etc. Read some of the postings here and *any* other SQL
Newsgroup. My rule of thumb is that you should not write more than
five of them in 25 years in IT.
Looking at what you did post, it looks like you missed most of the
basic ideas of RDBMS and building a procedural routine that mimics a
file system. .
1) Why would anyone put the display order into a table? All display
work is done in the front end and not the database.
2) Ignoring design flaw #1, why did you use two different names for the
same data element (I.i_order_id_display = R.order_id_display)? Surely
nobody would put the data type or table on a data element.
3) What is a row_id? If it refers to the physical rows in a table,
then it is wrong. If it refers to the position on the input screen or
original paper form, then it is wrong. You woudl be mimicing a paper
form instead of building a relational model.
4) You use vague data element names Amount of what? It does not seem
to be money. Quantity of what? Ordered or returned or on-hand, or what?
That is like an adjective without a noun.
5) Why don't you follow ISO-11179 naming rules or at least be
consistent? Look at @.date_shipped is "<adj><noun>" while @.invoice_id
is "<noun><adj>" instead.
6) When I see procedure named "Process_Line_Item_Shipping' I worry
that you are going thru each item in an order, one at a time. SQL is a
set-oriented language and you should be working with a sub-set of
items. No loops. No Cursors.
My guess, based on no DDL, is that you need a table for the Orders, for
the Order Details, Shipments and working table of returns. The
returns will be used to update the Order Details with return
quantities and shipping info (perhaps the Orders will need changes).
I have done this in one UPDATE statement for some fairly simple
business rules. The trick was a detail table keyed on (order_nbr, sku,
ship_status, ship_date). Reports are done off of VIEWs (what
percentage of Lawn Gnomes are returned? in how many days? ) and you
needed to watch constraints (you cannot return more than you bought).

Cube from denormalized table: Several dimensions vs. one dimension with several attributes

Hi,

I want to build a cube in AS2005 from a denormalized table.

The table consists of facts from the last three years, with all interesting attributes (about 12) in a seperate column of this table.

Actulally this attributes would form three dimensions (semantically).

I thought of two different approaches to structure the cube:

1. Building a separate dimension for each attribute

or

2. Building three dimensions (or to be more radical : one dimension) that contains all attributes

In my understanding, with the new concept of attribute based hierarchies, both variants should have the same effect on aggregations, performance and so on.

Only difference would the structure shown to the user .

Is this right?

Does autoexist have any influence on the both design variants?

It seems like modeling each attribute as a separate dimension would be a lot easier to use for analysis, right?|||

That is entirely not so.

When designing your model, you should group attributes into dimensions. For instance if you have geographical data , like Country, State, City, you should build a single dimension and create hierarchy.

Analysis Services although allows you to create model from almost any type of schema, you should try to follow the relationships between entities in relational data. That is you should design multideimensional model as if data as normalized as possible.

Having 3 dimensions with multiple attributes sounds like better idea.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you Edward, for your reply.

That is what I supposed to do, but:

As I mentioned, the table contains facts from several years. Within this time, there are dimenions, in which some attributes changed. (Refential integrity in the denormalized table is destroyed)

So when I'm trying to process a dimension with several attributes directly on the table, I get the "key duplicate" error from the processing engine.

To build a model with 3 dimension contailing several attributes woud lead to a reverse design of a slow changing dimemsion, with surrogate key for each attribute combination and timestamp etc......

This is what I wanted to avoid if possible! So I thought of building a dimension form every single attribute in the table. Then historical changes of attributes stay related to the facts correctly.

As I found in another whitepaper, a dimension in ass2005 is nothing more than a logical container for attribute dimensions.--> Is this right ?

So what would be the advantages grouping attributes to dimensions.

As I am quite new to OLAP and MDX I haven't an overview on the technology by now. Are there any disadvantages when later querying the cube? (Restrictions, performance)

Thanks,

Keme

|||

The problem with creating dimension per attribute would come when your users will try to analyze the data , they run into problems trying to make sence out of the numbers.

You might be saving yourself some time by treating attributes as separate, but if they are logically related, your users will tell you that numbers are wrong. You'd have to come back and build hierarchies and work out through processing errors. Analysis Services are very good pointing out inconsistencies in data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.