Order of cursor walk

I have table Stock which represents products in stock.
When new order came (table orders) I must reserve items in stock for that
order (table reservations).
The way I work is, that I first find all appropriate stock for my order,
walk through it and reserve the quantity until I reach
order quantity:
declare @.stockQ decimal(15,5),@.orderQ decimal(15,5),@.orderID int,@.stockID
int,@.orderIDold int,@.work bit,@.quantity decimal(15,5)
set @.orderIDold=0
declare cCur cursor local for
SELECT o.orderID,s.stockID,o.quantity,s.quantity from orders o LEFT JOIN
stock s
ON o.productID=s.productID
AND (o.quality is null OR o.quality=s.quality)
AND (o.producer is null OR o.producer=s.producer)
open cCur
fetch next from cCur into @.orderID,@.stockID,@.orderQ,@.stockQ
while @.@.fetch_status=0
begin
set @.stockQ=@.stockQ-(SELECT isnull(sum(quantity),0) from reservations
where stockID=@.stockID)
if isnull(@.stockQ,0)>0
begin
if @.orderID<>@.orderIDold
begin
SET @.work=0
SET @.orderIDold=@.orderID
set @.quantity=@.orderQ
end
if @.work=0--(if @.work=1 then all quantity for this order was
already reserved)
begin
if @.stockQ>=@.quantity
begin
INSERT INTO reservations
SELECT @.orderID,@.stockID,@.quantity
set @.work=1
end
else
begin
INSERT INTO reservations
SELECT @.orderID,@.stockID,@.stockQ
set @.quantity=@.quantity-@.stockQ
end
end
end
fetch next from cCur into @.orderID,@.stockID,@.orderQ,@.stockQ
end
close cCur
deallocate ccur
GO
It works.
The problem is, when I have in table orders more products with the same ID
and different other parameters.
With sample data you can see (just copy the script below and run my query)
that reservation that this query create is
ORDERID STOCKID quantity
---
1 1 100
Instead I reserve both orders I reserve only first.
So, I should include somehow the right order of select statement.
The reservation should be:
ORDERID STOCKID quantity
---
1 2 100
2 1 100
How can I solve that? Any idea?
This is simple example I use to explain the situation.The real example has
many parameters, not only quality and producer, and many
different products with different quantity.
So, the real combination shoul reserve the maximum possible orders.
The sample data with tables:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[stock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[stock]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[orders]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[reservations]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[reservations]
GO
CREATE TABLE [dbo].[stock] (
[stockID] [int] NOT NULL ,
[productID] [int] NOT NULL ,
[quality] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
[producer] [varchar] (10) COLLATE SQL_Slovenian_CP1250_CI_AS NULL ,
[quantity] [decimal](15, 5) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[orders] (
[orderID] [int] NOT NULL ,
[productID] [int] NULL ,
[quality] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NULL ,
[producer] [varchar] (10) COLLATE SQL_Slovenian_CP1250_CI_AS NULL ,
[quantity] [decimal](15, 5) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[reservations] (
[orderID] [int] NOT NULL ,
[stockID] [int] NOT NULL ,
[quantity] [decimal](15, 5) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[stock] ADD
CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED
(
[stockID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[orders] ADD
CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED
(
[orderID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[reservations] ADD
CONSTRAINT [PK_reservations] PRIMARY KEY CLUSTERED
(
[orderID],
[stockID]
) ON [PRIMARY]
GO
INSERT INTO orders(orderID,productID,producer,quanti
ty) VALUES (1,1,'2',100)
INSERT INTO orders(orderID,productID,quality,quantit
y) VALUES (2,1,'A',100)
INSERT INTO stock(stockID,productID,quality,producer
,quantity) VALUES
(1,1,'A','2',100)
INSERT INTO stock(stockID,productID,quality,producer
,quantity) VALUES
(2,1,'B','2',100)
regards,SCursors don't have a predictable order unless you specify ORDER BY in
the cursor declaration.
You can do this possibly more efficiently without a cursor. Google for
FIFO and stock inventory in this group to find some examples.
David Portas
SQL Server MVP
--|||well, I mean the order of SELECT statement which cursor use.
I don't know how to find the right order. There is a lot of combinations.
I thought, the result of this simple example I posted should give me the
right guidline in real example.
I already look for fifo in google but haven't find the similar example.
Can you help me?
thanks ,S
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128676869.547922.139950@.g44g2000cwa.googlegroups.com...
> Cursors don't have a predictable order unless you specify ORDER BY in
> the cursor declaration.
> You can do this possibly more efficiently without a cursor. Google for
> FIFO and stock inventory in this group to find some examples.
> --
> David Portas
> SQL Server MVP
> --
>|||Take a look at this thread:
http://groups.google.co.uk/group/mi...b653f3ad3fcaa5e
David Portas
SQL Server MVP
--

Order Entry System

An online shopping store with a lot of products... Unfortunately the number
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. ThanksTwo things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning
and such, is to either remove the item from inventory when it is placed in
the cart and set it to "promised" - not fully considering it sold until the
entire purchase is complete
- credit card authorized, etc. This runs the risk of depleting your inventor
y by the number of items that are sitting in abandoned carts on your site -
at least until those carts expire by whatever criteria you use for that.
The other is that the availability shown on the site is just informational t
o the customer and you don't attempt to keep that absolutely sync'd with you
r real unpromised inventory. I've seen sites where they do a final availabil
ity check at purchase time
(which is often on a separate server from the browsing server) and tell you
then that the item is no longer available. I think that is a pretty bad cust
omer experience, but you will need to strike the balance with your other bus
iness needs.
There are other techniques for dealing with available to promise, but they t
end to be more loosely coupled and that doesn't fit well when your inventory
per item is very limited.|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> number
scale[vbcol=seagreen]
not[vbcol=seagreen]
show[vbcol=seagreen]
order[vbcol=seagreen]
> out
> Any
>

Order Entry System

An online shopping store with a lot of products... Unfortunately the number
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
Two things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>
|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning and such, is to either remove the item from inventory when it is placed in the cart and set it to "promised" - not fully considering it sold until the entire purchase is complete
- credit card authorized, etc. This runs the risk of depleting your inventory by the number of items that are sitting in abandoned carts on your site - at least until those carts expire by whatever criteria you use for that.
The other is that the availability shown on the site is just informational to the customer and you don't attempt to keep that absolutely sync'd with your real unpromised inventory. I've seen sites where they do a final availability check at purchase time
(which is often on a separate server from the browsing server) and tell you then that the item is no longer available. I think that is a pretty bad customer experience, but you will need to strike the balance with your other business needs.
There are other techniques for dealing with available to promise, but they tend to be more loosely coupled and that doesn't fit well when your inventory per item is very limited.
|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.[vbcol=seagreen]
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> number
scale[vbcol=seagreen]
not[vbcol=seagreen]
show[vbcol=seagreen]
order
> out
> Any
>

Order Entry System

An online shopping store with a lot of products... Unfortunately the number
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. ThanksTwo things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning and such, is to either remove the item from inventory when it is placed in the cart and set it to "promised" - not fully considering it sold until the entire purchase is complete - credit card authorized, etc. This runs the risk of depleting your inventory by the number of items that are sitting in abandoned carts on your site - at least until those carts expire by whatever criteria you use for that
The other is that the availability shown on the site is just informational to the customer and you don't attempt to keep that absolutely sync'd with your real unpromised inventory. I've seen sites where they do a final availability check at purchase time (which is often on a separate server from the browsing server) and tell you then that the item is no longer available. I think that is a pretty bad customer experience, but you will need to strike the balance with your other business needs
There are other techniques for dealing with available to promise, but they tend to be more loosely coupled and that doesn't fit well when your inventory per item is very limited.|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> > An online shopping store with a lot of products... Unfortunately the
> number
> > of products to be sold are limited.. kind of in an inventory...
> >
> > I could have millions of customers logging on someday ... How can I
scale
> > this for concurrency and also ensure that data is consistent and i do
not
> > oversell the products that are not in the inventory..thats assuming i
show
> > all the customers all the products available and everyone decides to
order
> > it which i would not be able to control. I can imagine a product catalog
> > going thru some sort of a replication architecture for concurrency/scale
> out
> > purposes as data is static. How does one do it for an inventory system..
> Any
> > suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
> >
> >
>

ORDER BY using a list of elements

Hi,
I have to order a select in a special way, just using the name of the product. Can I use a list of the names of the products to do the ordering?
like "product 1", product 3", "product 5", "product 2", "product 4"...
The list of the products it's not so large and I could write them in a simple sentence. I'm working with Microsoft SQL Server 2000.

Thanks & regardsHow about a CASE statement?

ORDER BY
CASE WHEN name='product 1' THEN 1
WHEN name='product 3' THEN 2
...
END;

Or, perhaps better, add a new column like PRODUCT_PRIORITY to the table and order by that.

Order By "IN"

SELECT DISTINCT * FROM Products WHERE ProductID
IN(1406,761,587,646,182)
I need to Order by the "IN"
1. 1406
2. 761
3. 587
4. 646
5. 182
Does anyone know how to do that?
From
MikeIf you use the method of a separate table, and parsing by the order the
commas are found, then you could add an identity column to the table and
order by that first.
http://www.aspfaq.com/2248
"AspMike" <mike@.pwim.com> wrote in message
news:1145898945.787474.170770@.j33g2000cwa.googlegroups.com...
> SELECT DISTINCT * FROM Products WHERE ProductID
> IN(1406,761,587,646,182)
> I need to Order by the "IN"
> 1. 1406
> 2. 761
> 3. 587
> 4. 646
> 5. 182
> Does anyone know how to do that?
> From
> Mike
>|||If this is generally a big list, a reasonable approach is to maintain a
table/ derived table of such values & use a sort column accordingly. For
shorter lists, you can either use CASE like:
ORDER BY CASE product_id WHEN 1406 THEN 1
WHEN 761 THEN 2
WHEN 587 THEN 3
WHEN 646 THEN 4
WHEN 182 THEN 5
ELSE 6
END
Or use PATINDEX or CHARINDEX string functions like:
ORDER BY CHARINDEX ( ',' + CAST( product_id AS VARCHAR ) + ',',
',1406,761,587,646,182,' )
Anith|||Unh? the IN(<list> ) is a set (actually a table value constructor)
and has no ordering by definition. Let me repeat that BY DEFINITION.
In English and with sample output, what are you trying to do?