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?
I have a column named "LIST" in a table with strings like the following:151231-1002-02-1001151231-1001-02-1001151231-1002-02-1002151231-1003-02-1001etc....What I'd like to do is include an ORDER BY statement that splits thestring, 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 setof two numbers, and then by the last set of four numbers.How would I do something like this?--Sugapablo - Join Bytes!http://www.sugapablo.com | ICQ: 902845
I'm trying to get some XML data into SQL Server but i ran into problem when inserting the data (multiple orders with multiple order details) using a single sproc. Is it possible, or do I have to do in some other way? :confused:
I simplified my example to this: ----------------------------- --CREATE PROCEDURE sp_InsertOrders AS
DECLARE @docHandle INT, @xmlDoc VARCHAR(4000), @orderID INT
--DROP TABLE #Orders CREATE TABLE #Orders ( OrderId SMALLINT IDENTITY(1,1), FkCustomerID SMALLINT NOT NULL, OrderDate DATETIME NOT NULL )
--DROP TABLE #OrderDetails CREATE TABLE #OrderDetails ( OrderDetailsId SMALLINT IDENTITY(1,1), FkOrderID SMALLINT NOT NULL, ProductID SMALLINT NOT NULL, UnitPrice SMALLINT NOT NULL )
INSERT INTO #Orders (FkCustomerID, OrderDate) SELECT CustomerID, OrderDate FROM OpenXML(@docHandle, 'Orders/Order', 3) WITH ( CustomerID INTEGER, OrderDate DATETIME )
SET @OrderID = @@IDENTITY;
--INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice) SELECT @OrderID AS OrderID, ProductID, UnitPrice FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3) WITH ( ProductID INTEGER, UnitPrice INTEGER ) -----------------------------
All orders are inserted first which makes the use of @@IDENTITY incorrect (it works fine if you insert a single order with multiple order details). Since it was quite some time since I last worked with SQL I am not sure if am doing it the right way... :confused: :confused: Anybody out there who knows how to solve the problem?
Does ORDER BY work on character data type in SQL Server through ODBC? I tried using the SQL Query Tool in SQL Enterprise Manager and it works but using through ODBC I can't get any results.
Query: SELECT company_id, company_name FROM lt_company ORDER BY company_name
Hi, I'm using the following SQL script to return a list of part number and the order is not what I expect. Perhaps this is a collation problem but I have no idea where to look to modify that.
Thanks in advance, John select part from transactions T where (T.transdate between '20070701' and '20070705') and (T.transtype = 'ISSU' or T.transtype = 'RTRN') order by part
Here is the beginning of the Transactions table create script
CREATE TABLE [Transactions] ( [RecNo] [int] IDENTITY (1,1) NOT NULL, [Part] [nvarchar] (30) NOT NULL , [TransDate] [nvarchar] (8) NOT NULL , [TransType] [nvarchar] (4) NOT NULL , [FromLoc] [nvarchar] (10) ,
The 'Part' column is an alphanumeric field. The problem I am having is that the Order By seems to ignore the hyphen character '-' when the returned rows are ordered by the Part (which can contain hyphens in any column).
Here is an example of what I get.
130909N9 130909N9 130909N9 1-480698-0 * These two should not be here 1-480699-0 * 15-423 164-07700 164-07700 164-07700 1683
I was expecting this ( and I get this in and older database ).
068-03000 068-03000 06A19956 074-03200 077-367-0 08DU08 1-480698-0 * These should be here eariler in the data 1-480699-0 100-364072 100-364072
i want to order by name coloumn.but if i do that ordering is (A,B,C,D,E,F......X,Y,Z,İ,Ş,Ğ,Ü). i want to order these letters at place of turkish alphebetical order.how can i do that? i'm sorry for my english.
Can anyone tell me apart from "sp_helpsort" system stored procedure, is there any other command to find details of Character Set,Sort Order & Unicode Collation?.
1. cause there is no record of my old SQL 6.5 installation leaft for me,so i try to find the character set and sort order of my old SQL 6.5 installation, and in the MSSQLlog directory the errlog.? file there are these lines:
99/06/11 12:59:24.29 spid1 SQL Server's default sort order is: 99/06/11 12:59:24.29 spid1 'nocase' (ID = 52) 99/06/11 12:59:24.29 spid1 on top of default character set: 99/06/11 12:59:24.29 spid1 'iso_1' (ID = 1)
so my old installation's character set and sort order is as above,am i right?
2. cause i am in chinese so any good idea for me to upgrade this old SQL 6.5 to use chinese character set (cp936) , is there anyway to transfer the old data?
thanks if any suggestions! welcome to send me mail about the question.
Hello all, have a temp table where I put data from a shopping cart. I need to take the data from this temp table and put it into an orders table and an orderItems table. The table structure is as follows: The cstb_orders.orderId has a one to many relationship with the cstb_orderItems.orderId cstb_orderItemsTemp id --pk invtId descr location quantity username shiptoaddress shiptocity shiptostate shiptozip shiptophone shiptofax cstb_orders orderId --pk username shiptoaddress shiptocity shiptostate shiptozip shiptocity shiptostate shiptozip shiptophone orderdate --auto date shipdate --null cstb_orderItems id --pk orderId --fk invtId descr location quantity What is the best way to accomplish this?
Hi There i have two windows 2000 servers which are both running SQL and i would like to restore a backup from one server to the other. Which in my opinion should be an easy task but when i go into the restore option and point it at the file i would like to restore i get the follwoing error
"The database you are attempting to restore was backed up under a different sort order ID (52) than the one you are currently using on this server (50) and at least one of them is a non binary sort order. Backup or restore operation operation terminating abnormally."
The server that i am trying to restore to already has databases on this so i cannot just reinstall SQL and change the sort order not that id know how to do that but this is what i have read.
Is htere anyway that i can put insome script for the database to fix this ???
Am new in SQL server. i use SQL server 2003 ans am trying to retieve the last three orders made. Can you please help me with my Select statement so that it will only select the last three coz @ the moment it selects everything
CODE: SELECT dbo.Invoice.InvoiceNo, dbo.Sales.UnitsSold, dbo.Sales.QuantitySold, dbo.Sales.CostAmount, dbo.Item.ItemCode, dbo.Item.ItemDescription, dbo.Item.Item, dbo.PurchaseOrder.OrderNumber, dbo.PurchaseOrder.OrderDate, dbo.PurchaseOrder.OrderStatus, dbo.PurchaseOrder.QuantityOrdered, dbo.PurchaseOrder.QuantityReceived, dbo.Customer.CustomerCode FROM dbo.Invoice INNER JOIN dbo.Sales ON dbo.Invoice.InvoiceKey = dbo.Sales.InvoiceKey INNER JOIN dbo.Item ON dbo.Sales.ItemKey = dbo.Item.ItemKey INNER JOIN dbo.PurchaseOrder ON dbo.Sales.VendorKey = dbo.PurchaseOrder.VendorKey INNER JOIN dbo.Customer ON dbo.Sales.CustomerKey = dbo.Customer.CustomerKey
I'm suppose to List all orders where there is no shippedDate (just give their orderNumbers).
I have this: select orderNumber from orders where shippedDate= null;
I'm not sure what to put in place of the "= null" as that is clearly wrong
(Note: If I use "select orderNumber from orders where shippedDate;" it prints out all the orders, but I only want the orders where there are no ShippedDate...
I am trying to Invoice all of the records in my 'Orders' table. After each of the records has been invoiced I would like SQL to flag that record as having been completed so that when I run my query the next day it will ignore those having been completed already. Any feedback would be greatly appreciated.
This is the query I wrote to invoice one Order at a time by specifying each Order_Num seperately. As you can tell...I'm a n00b. Thanks all.
select convert(varchar, getdate(), 107) as Date select order_num as 'Invoice No.' from orders where order_num = '20009' select c.cust_name as Customer, c.cust_address as 'Street Address', c.cust_city as City, c.cust_state as State, c.cust_zip as 'Zip Code' from customers as c, orders as o where c.cust_id = o.cust_id and order_num = '20009' select oi.order_item as 'Line Item', oi.quantity as QTY, p.prod_name as 'Product Name', oi.item_price as 'Sale Price', oi.quantity*oi.item_price as Total from orderitems as oi, products as p where oi.prod_id = p.prod_id and order_num = '20009' order by oi.order_item select sum(quantity*item_price) as Subtotal, sum(quantity*item_price*.089) as 'Tax 8.9%', sum(quantity*item_price)+ sum(quantity*item_price*.089) as Total from orderitems where order_num = '20009'
I am trying to link my ORDERS table to my CUSTOMERS table. The Primary key in the CUSTOMERS table is a UNIQUEIDENT. The foreign key it is linking to is an INT.
When I run a query in ACCESS, it says type mismatch....
I have two tables customers and orders. customerID is the foreign key for order table. If I pass customername I need to get information about each customer how many orders holding?
Hello all- Maybe someone has some insight on this... I currently have some hash values (SHA512 through Enterprise Library) that I'm trying to compare to data in our database. When I sort the values on the pipeline (DT_STR, 1252) with a Sort and compare them against values coming out of an OLE DB Source (SQL_Latin_1_General_CP1_CI_AS) with a Merge Join, any hash that has a '/' in it is treated as inequal to the one in the database.
For even more fun, when I change the sort/merge join to a Lookup, everything (seems) to check out. Is the 1252 code page not the same as SQL_Latin_1_General_CP1_CI_AS? Any other reasons this might be happening?
I have a table with orders and customer info. I want to retrieve only customers with new orders only. These are the available fields: customer Id, Order Id, Order Date, and Order Subtotal. I need help with the "where" section of the query.
Just wanted to check. I've got a SQL 7.0 box on NT4 with about 40 databases (each about 500Mb-1Gb) which I want to move to SQL2000.
The main problem is, the 7.0 box was built with a sort order not officially supported by the finance software that uses the databases, so I want to build the SQL2000 box with the default sort orders and character sets.
I've done this in a test environment by copying the .mdf files accross and attaching them to the SQL2000 box, and this all seems to work. But is this advisable, and will there be any problems?
How to get the result for current date orders but I can get the query working for due date. " SalesOrder.DateEntered, SalesLine.OrderQuantity, (SalesLine.OrderQuantity*SalesLine.SellingPrice / SalesOrder.ExchangeRate) AS Value, (SalesLine.SellingPrice / SalesOrder.ExchangeRate) AS Price FROM Company INNER JOIN CustomerAccount ON Company.CompanyID = CustomerAccount.CompanyID INNER JOIN SalesOrder ON CustomerAccount.CustomerAccountID = SalesOrder.CustomerAccountID INNER JOIN SalesLine ON SalesOrder.SalesOrderID = SalesLine.SalesOrderID WHERE (SalesOrder.DateEntered = { fn CURDATE() }) " How could I get the current date orders?
I'm wanting to get the average number of days between orders in my orders tbl - so I've done a search and found the following sql coded that I have modified for my db tbl's and columns. But when I try and parse it - I get 'Incorrect syntax new the keyword Group' - what am I missing.
SELECT custId, AVG(invDate - priorDate) FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDate FROM orders) Group BY custId
Im using Access and have created a query which adds up the grand totals of clients orders. However i want to be able to add up what each client has spent in total over any given time period. Basically in SQL logic terms:
take all order totals from same client and add together order totals to produce a grand client spend total.
I'm trying to create a couple of reports and I'm having trouble coming up with a way to do what I need to do. I already have several SQL views that I use for other reports, and they are geared toward either order-based or product-based reporting. For order-based reporting, I'm grouping the detail order lines by the order number. For product-based reporting, I'm not worrying about the order numbers necessarily. Here's my problem. I need to report on orders that have a particular product line in them (the product codes in this case start with the same letter, unique from other product lines), and separate the dollars coming from that product line and what sold with it. Something like:
Orders sold with these (A) products in them: Aproduct1 total orders $1000, $600 of which was Aproduct1 and $400 of which was Bproduct1 and Bproduct2 Aproduct2 total orders $500, $500 of which was Aproduct2 Aproduct3 total orders $700, $400 of which was Aproduct3 and $300 of which was Cproduct4 etc.
The data looks like this: (Order#) 12345 (ProductCode) Aproduct1 (quantity) 1 (dollars) $200 12345 Bproduct1 1 $100 12348 Cproduct2 2 $500 12350 Aproduct2 1 $500 12350 Cproduct1 1 $50 12350 Bproduct2 1 $300 etc.
So I'd need to pick out orders 12345 and 12350 and separate the dollars. The thing is the three products in 12350 don't know about each other.
