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
>
Showing posts with label online. Show all posts
Showing posts with label online. Show all posts
Friday, March 30, 2012
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
>
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
> >
> >
>
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
> >
> >
>
Monday, March 26, 2012
Order By items not in the select list
Books online says this about "Order By":
The ORDER BY clause can include items not appearing in the select list.
Well, that's actually strange and surprising. This is surely not new to
many of you, but I just recently saw this construction:
Use Pubs
Select Top 5 * From Titles Order by Newid()
Which, of course, picks 5 titles randomly from the Titles table. It might
not be efficient for huge tables, but it's good to know that you can do
this. Other than this, I have NEVER seen an Order By that didn't reference
a column from the Select list, and I had no idea that it was possible -- I
must have glossed over that sentence in BOL since it had no more
explanation and it wouldn't have really make sense before I knew about it
(cognitive dissonance, I suppose).
Note: If you're using Windows 2000 or later, the implementation of Newid()
returns random IDs. In earlier operating systems, I think they were
sequential.
You have to read between the lines of that Select statement to figure out
that SQL must append the expression you supply to each row of the table,
then order the result set by that expression. Not that BOL bothers to
explain this, of course.
It's kind of poor that BOL has this one sentence but doesn't go on to
explain what happens when you order by something not in the select list.
Can I order by the values taken from a field in a different table? (Not as
far as I can tell.)
Can you do this:
Select Top 5 * From Titles Order by 'Hello there'
(Yes, you can, but it's not interesting.)
BOL should say "The ORDER BY clause can include items not appearing in the
select list, and when you do that, here's what happens..."
Are there any other cases where this feature of Order By could be useful,
other than with Newid()?
David Walker"DWalker" <none@.none.com> wrote in message
news:%23UutkJ$MFHA.3512@.TK2MSFTNGP15.phx.gbl...
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not
as
> far as I can tell.)
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
You can ORDER BY any valid scalar.
Yes, you can order by a value from another table. Use a correlated
subquery:
ORDER BY
(SELECT SomeColumn
FROM SomeOtherTable
WHERE SomeOtherTable.PK = YourTable.PK)
Or, it's often very useful to ORDER BY a CASE expression... For
instance, maybe you want to return all rows where SomeColumn = 99 first,
then sort the rest by SomeColumn ascending:
ORDER BY
CASE SomeColumn
WHEN 99 THEN 1
ELSE SomeColumn
END
.. There are lots of interesting things you can do.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||To add to Adam's reply, In general, this should occur anytime you want th
e
rows in an output resultset ordered by some value (albiet a direct Column
value or calculated expression) whose value you are not specifically
interested in on the CLient, other than as a mechanism by which to sort the
rows...
I often Order by a datetime expression, which is not output in the Select
clause,
It is frequently required that an Output column based on a datetime be
formatted as a user readable string, say "Tuesday, March 14" While actually
sorting on the internal datetime value of the same column, not on the string
which is output by the SQL .
"DWalker" wrote:
> Books online says this about "Order By":
> The ORDER BY clause can include items not appearing in the select list.
> Well, that's actually strange and surprising. This is surely not new to
> many of you, but I just recently saw this construction:
> Use Pubs
> Select Top 5 * From Titles Order by Newid()
> Which, of course, picks 5 titles randomly from the Titles table. It might
> not be efficient for huge tables, but it's good to know that you can do
> this. Other than this, I have NEVER seen an Order By that didn't referenc
e
> a column from the Select list, and I had no idea that it was possible -- I
> must have glossed over that sentence in BOL since it had no more
> explanation and it wouldn't have really make sense before I knew about it
> (cognitive dissonance, I suppose).
> Note: If you're using Windows 2000 or later, the implementation of Newid()
> returns random IDs. In earlier operating systems, I think they were
> sequential.
> You have to read between the lines of that Select statement to figure out
> that SQL must append the expression you supply to each row of the table,
> then order the result set by that expression. Not that BOL bothers to
> explain this, of course.
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not a
s
> far as I can tell.)
> Can you do this:
> Select Top 5 * From Titles Order by 'Hello there'
> (Yes, you can, but it's not interesting.)
> BOL should say "The ORDER BY clause can include items not appearing in the
> select list, and when you do that, here's what happens..."
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
>
> David Walker
>|||Thanks, Adam and cbretana. That helps.
David
"examnotes" <cbretana@.areteIndNOSPAM.com> wrote in
news:7B887787-2D11-4886-8CE7-C007F4F93603@.microsoft.com:
> To add to Adam's reply, In general, this should occur anytime you
> want the rows in an output resultset ordered by some value (albiet a
> direct Column value or calculated expression) whose value you are not
> specifically interested in on the CLient, other than as a mechanism by
> which to sort the rows...
> I often Order by a datetime expression, which is not output in the
> Select clause,
> It is frequently required that an Output column based on a datetime be
> formatted as a user readable string, say "Tuesday, March 14" While
> actually sorting on the internal datetime value of the same column,
> not on the string which is output by the SQL .
>
> "DWalker" wrote:
>|||On Tue, 29 Mar 2005 08:08:53 -0800, DWalker wrote:
>Thanks, Adam and cbretana. That helps.
>David
Hi David,
In addition to Adam's and CBretana's reply, I'd like to point out that
the ability to order by items that are not in the select list is a T-SQL
proprietary feature. If portability of your code is important, then it's
best to include the ordering columns in the select list, so that your
query is ANSI-compliant.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> In addition to Adam's and CBretana's reply, I'd like to point out that
> the ability to order by items that are not in the select list is a T-SQL
> proprietary feature. If portability of your code is important, then it's
> best to include the ordering columns in the select list, so that your
> query is ANSI-compliant.
Maybe not ANSI-compliant, but I think "proprietary" is too harsh a term.
This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
PostgreSQL, and others all support this, in addition to SQL Server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||OK, thanks. I'm sure we won't be moving this out of SQL 2000 except
possibly to SQL 2005, but it's good to know it's not strictly ANSI-
compliant.
David
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:OBsRlIKNFHA.244@.tk2msftngp13.phx.gbl:
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> Maybe not ANSI-compliant, but I think "proprietary" is too harsh a
> term.
> This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
> PostgreSQL, and others all support this, in addition to SQL Server.
>
The ORDER BY clause can include items not appearing in the select list.
Well, that's actually strange and surprising. This is surely not new to
many of you, but I just recently saw this construction:
Use Pubs
Select Top 5 * From Titles Order by Newid()
Which, of course, picks 5 titles randomly from the Titles table. It might
not be efficient for huge tables, but it's good to know that you can do
this. Other than this, I have NEVER seen an Order By that didn't reference
a column from the Select list, and I had no idea that it was possible -- I
must have glossed over that sentence in BOL since it had no more
explanation and it wouldn't have really make sense before I knew about it
(cognitive dissonance, I suppose).
Note: If you're using Windows 2000 or later, the implementation of Newid()
returns random IDs. In earlier operating systems, I think they were
sequential.
You have to read between the lines of that Select statement to figure out
that SQL must append the expression you supply to each row of the table,
then order the result set by that expression. Not that BOL bothers to
explain this, of course.
It's kind of poor that BOL has this one sentence but doesn't go on to
explain what happens when you order by something not in the select list.
Can I order by the values taken from a field in a different table? (Not as
far as I can tell.)
Can you do this:
Select Top 5 * From Titles Order by 'Hello there'
(Yes, you can, but it's not interesting.)
BOL should say "The ORDER BY clause can include items not appearing in the
select list, and when you do that, here's what happens..."
Are there any other cases where this feature of Order By could be useful,
other than with Newid()?
David Walker"DWalker" <none@.none.com> wrote in message
news:%23UutkJ$MFHA.3512@.TK2MSFTNGP15.phx.gbl...
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not
as
> far as I can tell.)
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
You can ORDER BY any valid scalar.
Yes, you can order by a value from another table. Use a correlated
subquery:
ORDER BY
(SELECT SomeColumn
FROM SomeOtherTable
WHERE SomeOtherTable.PK = YourTable.PK)
Or, it's often very useful to ORDER BY a CASE expression... For
instance, maybe you want to return all rows where SomeColumn = 99 first,
then sort the rest by SomeColumn ascending:
ORDER BY
CASE SomeColumn
WHEN 99 THEN 1
ELSE SomeColumn
END
.. There are lots of interesting things you can do.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||To add to Adam's reply, In general, this should occur anytime you want th
e
rows in an output resultset ordered by some value (albiet a direct Column
value or calculated expression) whose value you are not specifically
interested in on the CLient, other than as a mechanism by which to sort the
rows...
I often Order by a datetime expression, which is not output in the Select
clause,
It is frequently required that an Output column based on a datetime be
formatted as a user readable string, say "Tuesday, March 14" While actually
sorting on the internal datetime value of the same column, not on the string
which is output by the SQL .
"DWalker" wrote:
> Books online says this about "Order By":
> The ORDER BY clause can include items not appearing in the select list.
> Well, that's actually strange and surprising. This is surely not new to
> many of you, but I just recently saw this construction:
> Use Pubs
> Select Top 5 * From Titles Order by Newid()
> Which, of course, picks 5 titles randomly from the Titles table. It might
> not be efficient for huge tables, but it's good to know that you can do
> this. Other than this, I have NEVER seen an Order By that didn't referenc
e
> a column from the Select list, and I had no idea that it was possible -- I
> must have glossed over that sentence in BOL since it had no more
> explanation and it wouldn't have really make sense before I knew about it
> (cognitive dissonance, I suppose).
> Note: If you're using Windows 2000 or later, the implementation of Newid()
> returns random IDs. In earlier operating systems, I think they were
> sequential.
> You have to read between the lines of that Select statement to figure out
> that SQL must append the expression you supply to each row of the table,
> then order the result set by that expression. Not that BOL bothers to
> explain this, of course.
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not a
s
> far as I can tell.)
> Can you do this:
> Select Top 5 * From Titles Order by 'Hello there'
> (Yes, you can, but it's not interesting.)
> BOL should say "The ORDER BY clause can include items not appearing in the
> select list, and when you do that, here's what happens..."
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
>
> David Walker
>|||Thanks, Adam and cbretana. That helps.
David
"examnotes" <cbretana@.areteIndNOSPAM.com> wrote in
news:7B887787-2D11-4886-8CE7-C007F4F93603@.microsoft.com:
> To add to Adam's reply, In general, this should occur anytime you
> want the rows in an output resultset ordered by some value (albiet a
> direct Column value or calculated expression) whose value you are not
> specifically interested in on the CLient, other than as a mechanism by
> which to sort the rows...
> I often Order by a datetime expression, which is not output in the
> Select clause,
> It is frequently required that an Output column based on a datetime be
> formatted as a user readable string, say "Tuesday, March 14" While
> actually sorting on the internal datetime value of the same column,
> not on the string which is output by the SQL .
>
> "DWalker" wrote:
>|||On Tue, 29 Mar 2005 08:08:53 -0800, DWalker wrote:
>Thanks, Adam and cbretana. That helps.
>David
Hi David,
In addition to Adam's and CBretana's reply, I'd like to point out that
the ability to order by items that are not in the select list is a T-SQL
proprietary feature. If portability of your code is important, then it's
best to include the ordering columns in the select list, so that your
query is ANSI-compliant.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> In addition to Adam's and CBretana's reply, I'd like to point out that
> the ability to order by items that are not in the select list is a T-SQL
> proprietary feature. If portability of your code is important, then it's
> best to include the ordering columns in the select list, so that your
> query is ANSI-compliant.
Maybe not ANSI-compliant, but I think "proprietary" is too harsh a term.
This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
PostgreSQL, and others all support this, in addition to SQL Server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||OK, thanks. I'm sure we won't be moving this out of SQL 2000 except
possibly to SQL 2005, but it's good to know it's not strictly ANSI-
compliant.
David
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:OBsRlIKNFHA.244@.tk2msftngp13.phx.gbl:
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> Maybe not ANSI-compliant, but I think "proprietary" is too harsh a
> term.
> This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
> PostgreSQL, and others all support this, in addition to SQL Server.
>
Monday, March 12, 2012
Oracle replication failed
Some replication jobs failed and it shows 'data conversion failed' error MSSQL_REPL20037. Any idea on it ? I can't find the error in online book. THanks in advance!
Where did you encounter this error? Command line interface or GUI?Can you provide more information such as the repro steps, environment setting, and the table schema?
Regards,
Gary Chen|||I found the error 'The process could not bulk copy into table '"dbo"."CCB_CLINM2"'.' in view sysnchronization status. and i checked the history in job agent that it shows
"Message
2005-12-14 03:35:33.805 Connecting to Subscriber 'CCBHK08D'
2005-12-14 03:35:33.977 Skipping file 'CCB_CLINM2_2.sch' because it has already been delivered for a previous article or by a previously interrupted snapshot.
2005-12-14 03:35:33.993 Bulk copying data into table 'CCB_CLINM2'
2005-12-14 03:35:34.259 Agent message code 20037. The process could not bulk copy into table '"dbo"."CCB_CLINM2"'.
2005-12-14 03:35:34.321 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed
2005-12-14 03:35:34.337 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Data conversion failed"
A few small tables are replicated to the MSSQL2005 but some tables got the same error. The machine is running in 64-bits. And the replication testing in 32-bits is fine. Also, I found that some ODBC drivers are missing in MSSQL 2005 even installed with MDAC2.8. Is it support 32-bits only? Thanks in advance|||also, I found the error in replication monitor
"Error messages:
The process could not bulk copy into table '"dbo"."SYSTWODB_CABL"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
Data conversion failed"|||
Below is the ddl of table (in both mssql and oracle) which failed in replication
USE [dev]
GO
/****** Object: Table [dbo].[SYSTWODB_EXCHR] Script Date: 12/14/2005 14:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SYSTWODB_EXCHR](
[GLB_DTIME] [numeric](16, 0) NOT NULL,
[CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XCCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[EXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[XEXCH_GRP] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[REL_DAY] [numeric](5, 0) NOT NULL,
[XREL_DAY] [numeric](5, 0) NOT NULL,
[BASEX_CCY] [varchar](4) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[BSE_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[INP_DATE] [numeric](5, 0) NOT NULL,
[INP_TIME] [numeric](6, 0) NOT NULL,
[MID_MKT_LN] [numeric](14, 10) NOT NULL,
[MID_MKT_TY] [numeric](14, 10) NOT NULL,
[MKT_BUY] [numeric](14, 10) NOT NULL,
[MKT_RATEID] [varchar](7) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[MKT_SELL] [numeric](14, 10) NOT NULL,
[RATE_USAGE] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
[SOFTLOCK] [numeric](2, 0) NOT NULL,
[WIDTH] [varchar](1) COLLATE Chinese_Hong_Kong_Stroke_90_CI_AS NOT NULL,
CONSTRAINT [MSHREPL_49_PK] PRIMARY KEY CLUSTERED
(
[GLB_DTIME] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
*******************************************
CREATE TABLE "LINC"."SYSTWODB_EXCHR"("GLB_DTIME" NUMBER(16) NOT
NULL, "CCY" VARCHAR2(4) NOT NULL, "XCCY" VARCHAR2(4) NOT NULL,
"EXCH_GRP" VARCHAR2(4) NOT NULL, "XEXCH_GRP" VARCHAR2(4) NOT
NULL, "REL_DAY" NUMBER(5) NOT NULL, "XREL_DAY" NUMBER(5) NOT
NULL, "BASEX_CCY" VARCHAR2(4) NOT NULL, "BSE_RATEID" VARCHAR2(7)
NOT NULL, "INP_DATE" NUMBER(5) NOT NULL, "INP_TIME" NUMBER(6)
NOT NULL, "MID_MKT_LN" NUMBER(14, 10) NOT NULL, "MID_MKT_TY"
NUMBER(14, 10) NOT NULL, "MKT_BUY" NUMBER(14, 10) NOT NULL,
"MKT_RATEID" VARCHAR2(7) NOT NULL, "MKT_SELL" NUMBER(14, 10) NOT
NULL, "RATE_USAGE" VARCHAR2(1) NOT NULL, "SOFTLOCK" NUMBER(2)
NOT NULL, "WIDTH" VARCHAR2(1) NOT NULL,
CONSTRAINT "SYSTWODB_EXCHR_PK" PRIMARY KEY("GLB_DTIME"),
CONSTRAINT "SYS_C0022019" CHECK("GLB_DTIME" IS NOT NULL),
CONSTRAINT "SYS_C0022020" CHECK("CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022021" CHECK("XCCY" IS NOT NULL),
CONSTRAINT "SYS_C0022022" CHECK("EXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022023" CHECK("XEXCH_GRP" IS NOT NULL),
CONSTRAINT "SYS_C0022024" CHECK("REL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022025" CHECK("XREL_DAY" IS NOT NULL),
CONSTRAINT "SYS_C0022026" CHECK("BASEX_CCY" IS NOT NULL),
CONSTRAINT "SYS_C0022027" CHECK("BSE_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022028" CHECK("INP_DATE" IS NOT NULL),
CONSTRAINT "SYS_C0022029" CHECK("INP_TIME" IS NOT NULL),
CONSTRAINT "SYS_C0022030" CHECK("MID_MKT_LN" IS NOT NULL),
CONSTRAINT "SYS_C0022031" CHECK("MID_MKT_TY" IS NOT NULL),
CONSTRAINT "SYS_C0022032" CHECK("MKT_BUY" IS NOT NULL),
CONSTRAINT "SYS_C0022033" CHECK("MKT_RATEID" IS NOT NULL),
CONSTRAINT "SYS_C0022034" CHECK("MKT_SELL" IS NOT NULL),
CONSTRAINT "SYS_C0022035" CHECK("RATE_USAGE" IS NOT NULL),
CONSTRAINT "SYS_C0022036" CHECK("SOFTLOCK" IS NOT NULL),
CONSTRAINT "SYS_C0022037" CHECK("WIDTH" IS NOT NULL))
TABLESPACE "URBIS" PCTFREE 10 PCTUSED 70 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 2604K NEXT 352K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING|||
Besides, I found a message regarding to the failed replication in error log
'Error: 14151, Severity: 18, State: 1'
Any Hints on it? Thanks in advance
|||Let's try to narrow it down a bit further.
So, the replication works fine on 32bit server and all tables copied fine, right? Only 64bit SQL server is having problem? Has the replication ever work in your 64bit environment at all? Can you tell me what your environment, OS, topology, and platform?
A minimum series of steps that can help to reproduce the error.
What type of Oracle publisher do you use? Gateway or Complete?
I also notice that you uses "Chinese_Hong_Kong_Stroke_90_CI_AS" when you create your table. What is the character set that you have install on your SQL server, OS, and Oracle server?
Regards,
Gary
|||1. same set of tables and same source DB (with vchar and number as data type)
2. source: oracle 9.2 running in AIX platform.
3, publisher is gateway
4. SQL collation is Chinese _hong_kong_stroke_90_CI_AS, OS is 'Chinese (Hong Kong SAR)'
5. transactional publication with push subscription
6. Destination: Win2003 standard x64 edition sp1 with MSSQL2005 EVAL edition (x64) while another machine is Win2003 standard edition sp1 MSSQL2005 eval edition
7. All tables can be replicated to MSSQL2005(32bits) but some of them failed in the 64 bits one.
points 1-5 are the same in both 32 and 64 bits machine
many many Thanks for your help
|||
Hi,
This may or may not help you but I came accross this "data conversion" error when i was trying to reinitialize a subscriber when we migrated the publisher from 2000 to 2005. Our distributor also migrated to a SQL 2005 instance and it was a push subscription.
The issue in this instance was that the underlying data type length for a UDF created at the subscriber originally by SQL 2000 was smaller than what SQL 2005 needed. During the initialization the UDF was not being created at the subscriber by the SQL 2005 as it was already there so the solution was to drop the UDF and let the initialization process create it. Luckily the UDF was not being used anywhere else so we were able to drop it.
Cheers,
Priyanga
Friday, March 9, 2012
Oracle Problems...
I get:
An error has occurred during report processing. (rsProcessingAborted) Get Online Help Cannot create a connection to data source 'ODBC'. (rsErrorOpeningConnection) Get Online Help ERROR [HY000] [Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value specified ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value specified
An error has occurred during report processing. (rsProcessingAborted) Get Online Help Cannot create a connection to data source 'ODBC'. (rsErrorOpeningConnection) Get Online Help ERROR [HY000] [Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value specified ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value specified
when trying to view a report that runs in Visual Studio .NET. After I deploy the report to the Reporting Services server I get the error. There must be a problem in the server's configuration, right? What can I do?
I seem to have gotten this to work. I found a post somewhere on the Internet about a guy DELETING the NLS registry key. This key can be found by searching the registry on the server for home0. I tried it once without restarting the server and it didn't work. Then I restored the key and continued to look for more information. After not find anything, I decided to delete the key again and bounce the server. PRESTO!
Subscribe to:
Posts (Atom)