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 BY, only orders by the first character?

Hi,

I am using a stored procedure that is databound to a dropdownlist. The stored procedure works fine, aside from the fact that the ORDER BY only sorts the list of items using the first character in the cell. E.g. The numbers 1, 20, 100 and 200 would be ordered as follows:

1

100

20

200

clearly i am doing something wrong and i apologise if it is a stupid question, but could anyone please offer me some help?

Thank you,

Shep

ORDER BY a numeric data type as opposed to ORDER BY a character data type.

Run the following code in a test database. Note the absence of indexes.

CREATE TABLE dbo.OrderByInt(number int)

GO

INSERT INTO dbo.OrderByInt(number) VALUES(1)

INSERT INTO dbo.OrderByInt(number) VALUES(10)

INSERT INTO dbo.OrderByInt(number) VALUES(100)

INSERT INTO dbo.OrderByInt(number) VALUES(2)

INSERT INTO dbo.OrderByInt(number) VALUES(20)

INSERT INTO dbo.OrderByInt(number) VALUES(200)

SELECT number FROM dbo.OrderByInt

SELECT number FROM dbo.OrderByInt ORDER BY number

CREATE TABLE dbo.OrderByVarchar(number varchar(3))

GO

INSERT INTO dbo.OrderByVarchar(number) VALUES('1')

INSERT INTO dbo.OrderByVarchar(number) VALUES('10')

INSERT INTO dbo.OrderByVarchar(number) VALUES('100')

INSERT INTO dbo.OrderByVarchar(number) VALUES('2')

INSERT INTO dbo.OrderByVarchar(number) VALUES('20')

INSERT INTO dbo.OrderByVarchar(number) VALUES('200')

SELECT number FROM dbo.OrderByVarchar

SELECT number FROM dbo.OrderByVarchar ORDER BY number

SELECT number FROM dbo.OrderByVarchar ORDER BY CAST(number AS int)

|||

It sounds like you're storing numbers as text (in other words, storing "int" values in a "varchar" column). So, SQL Server is doing a text sort, which does indeed go by the first character.

The best solution is to use a data type that matches the data (use "int" for integer numbers, and nvarchar for text).

An alternative solution is to use a CAST or CONVERT in your order by to convert it to "int" before sorting -- ORDER BY CAST(myColumn AS int) -- but any sort of bulk operation on an expression like that cripples SQL Server's query optimizer and will hurt performance on large tables.

-Ryan / Kardax

Edit: "lkh" beat me by seconds, and provides a nice illustration of the problem and solution.

|||

Thank you for your reply lkd, however i am still having trouble:

When I try the above code i get the error message 'TABLE NOT SUPPORTED BY THIS EDITOR'

I am using Visual Web Developer 2005 Express Edition, could this be the problem?

Shep

|||

Sorry I haven't fully explained myself (i missed the most important part, my apologise!)

i am working in units of voltage & power, so there for the listing would be as follows:

1kW

10W

100W

20W

200W

so i dont think its an option to convert the values into integers, given that I have other non-numeric characters in there! Is it a lost cause?

Shep

|||I have never used Visual Web Developer but in a quick tour of the web site I don't see anything about creating objects in a database. You may have to use an editor in your database.|||

My honest though probably not helpful thought is that this is a database design problem. Can you redesign the table to have a column called Watt of int datatype?

Otherwise you could create a complicated CASE statement and sort on that column but that is not ideal.

|||Well the table is supplying the items for the dropdownlist, based on a certain product type. So just using numbers without the corresponding measurements would not look right in the dropdownlist.|||Can you add an int column that wouldn't appear in the UI to the table that would be used in the SELECT statement that populates the dropdown list?|||

If your are only using the single rightmost character ( as in your examples) as alpha, then the following may work for you.

BOTH strip off the last character, AND convert to an Integer ONLY in the ORDER BY clause.

DECLARE @.MyTestTable table
( MyColumn varchar(5) )

INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '2A' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '10W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '1A' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '100W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '200W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '20W' )

SELECT MyColumn
FROM @.MyTestTable
ORDER BY cast( left( MyColumn, ( len( MyColumn) - 1 )) AS int )

|||

Hi,

thanks to everyone for their help, in the end i have resorted to simply using VB to code in the items depending on the category: e.g.

If Value = 2 Then

ddlPara1.Items.Add(New ListItem("50V", "50V"))

ddlPara1.Items.Add(New ListItem("120V", "120V"))

ddlPara1.Items.Add(New ListItem("150V", "150V"))

ddlPara1.Items.Add(New ListItem("300V", "300V"))

ddlPara1.Items.Add(New ListItem("400V", "400V"))

If Value = 3 Then........

This shouldnt affect the performance of my application should it?

Thanks again,

Shep

Order By Start_Date

Hi

Ive searched but cant find any answers for this.

In my query Ive done an ORDER BY start_date ASC

However it only orders the day not the month or year?

any ideas?

Quote:

Originally Posted by ljbuxton

Hi

Ive searched but cant find any answers for this.

In my query Ive done an ORDER BY start_date ASC

However it only orders the day not the month or year?

any ideas?

Please post the DDL scripts and query you are running, otherwise it is hard to guess what issues do you have.
start_date field may be even of type varchar with 'DD-MM-YY' format

ORDER BY question: splitting string into 2 orders?

I have a column named "LIST" in a table with strings like the following:

151231-1002-02-1001
151231-1001-02-1001
151231-1002-02-1002
151231-1003-02-1001
etc...

What I'd like to do is include an ORDER BY statement that splits the
string, so that the order would be by the second set of four numbers
(i.e. between the first and second - marks), followed by the third set
of two numbers, and then by the last set of four numbers.

How would I do something like this?

--
Sugapablo - russpghREMOVE@.stargate.net
http://www.sugapablo.com | ICQ: 902845If this is a fixed width column with fixed formats, you can use substring to
parse the value like:

SELECT *
FROM tbl
ORDER BY SUBSTRING(col, 8, 4),
SUBSTRING(col, 13, 2),
RIGHT(col, 4) ;

If these are variable length formatted, then you have more work to do:

SELECT *
FROM tbl
ORDER BY SUBSTRING(col,
CHARINDEX('-', col) + 1,
CHARINDEX('-', col,
CHARINDEX('-', col) + 1) -
CHARINDEX('-', col) - 1),
REVERSE(SUBSTRING(REVERSE(col),
CHARINDEX('-', REVERSE(col)) + 1,
CHARINDEX('-', REVERSE(col),
CHARINDEX('-', REVERSE(col)) + 1) -
CHARINDEX('-', REVERSE(col)) - 1)),
REVERSE(SUBSTRING(REVERSE(col), 1,
CHARINDEX('-', REVERSE(col)) - 1)) ;

Another trick is to use PARSENAME function. Note that the return expression
for PARSENAME function is unicode though. See SQL Server Books Online for
more details.

SELECT *
FROM tbl
ORDER BY PARSENAME(REPLACE(col, '-', '-'), 3),
PARSENAME(REPLACE(col, '-', '-'), 2),
PARSENAME(REPLACE(col, '-', '-'), 1) ;

If each of these portions are of business significance, why are you
representing them as a single column? If consolidation is needed for certain
specific requirements, you can use a view for such representation.

--
- Anith
( Please reply to newsgroups only )

Order By- CREATIVITY NEEDED!

Greetings,
I want to order by Orders then RecType, BUT leaving RecType=13 in
between 30 and 40. So the sequence should be: 10, 15, 30, 13, 40 for
Order=4501130 then Order=5006730.
CREATE TABLE TblA (RecType int, Orders varchar(255))
INSERT INTO TblA (RecType, Orders) VALUES (10, '4501130')
INSERT INTO TblA (RecType, Orders) VALUES (10, '5006730')
INSERT INTO TblA (RecType, Orders) VALUES (15, '4501130')
INSERT INTO TblA (RecType, Orders) VALUES (15, '5006730')
INSERT INTO TblA (RecType, Orders) VALUES (30, '4501130')
INSERT INTO TblA (RecType, Orders) VALUES (30, '5006730')
INSERT INTO TblA (RecType, Orders) VALUES (13, '4501130')
INSERT INTO TblA (RecType, Orders) VALUES (13, '5006730')
INSERT INTO TblA (RecType, Orders) VALUES (40, '4501130')
INSERT INTO TblA (RecType, Orders) VALUES (40, '5006730')
SELECT * FROM TblA ORDER BY Orders ASC, RecType ASC
--
Thanks in Advance,
Don
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!SELECT * FROM TblA
ORDER BY Orders ASC,
Case RecType When 13 Then 35 Else RecType End ASC
"don larry" wrote:

> Greetings,
> I want to order by Orders then RecType, BUT leaving RecType=13 in
> between 30 and 40. So the sequence should be: 10, 15, 30, 13, 40 for
> Order=4501130 then Order=5006730.
> --
> CREATE TABLE TblA (RecType int, Orders varchar(255))
> INSERT INTO TblA (RecType, Orders) VALUES (10, '4501130')
> INSERT INTO TblA (RecType, Orders) VALUES (10, '5006730')
> INSERT INTO TblA (RecType, Orders) VALUES (15, '4501130')
> INSERT INTO TblA (RecType, Orders) VALUES (15, '5006730')
> INSERT INTO TblA (RecType, Orders) VALUES (30, '4501130')
> INSERT INTO TblA (RecType, Orders) VALUES (30, '5006730')
> INSERT INTO TblA (RecType, Orders) VALUES (13, '4501130')
> INSERT INTO TblA (RecType, Orders) VALUES (13, '5006730')
> INSERT INTO TblA (RecType, Orders) VALUES (40, '4501130')
> INSERT INTO TblA (RecType, Orders) VALUES (40, '5006730')
> SELECT * FROM TblA ORDER BY Orders ASC, RecType ASC
> --
> Thanks in Advance,
> Don
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>|||Wow, what a nifty little trick!
Sure worked though.
Thanks, i appreciate.
Don
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!