|
Zhenjie Fu wrote: Do I have to create a lot of temp tables?
Nope, only for the two tables that you're importing with the OpenRowSet-command.
Zhenjie Fu wrote: <layer>Is it a bug or some mis-understand by me?
It's an error in one of the imports. Hence the suggestion to select those two first into a temporary table, and then to union on those.
Bastard Programmer from Hell 
|
|
|
|
|
I am trying to retrieve the product, price, and date_despatched for the last three orders (date_despatched) for each product.
There are two tables involved:
opheadm (Sales Order Header)
date_despatched
order_no
opdetm (Sales Order Detail)
order_no
product
net_price
Desired Result
product_a 2.999 2012/03/23
product_a 2.600 2012/02/01
product_a 3.812 2012/01/23
product_b 1.456 2012/04/01
product_b 4.786 2012/02/13
product_b 2.563 2012/10/10
...
I have seen two approaches, but cant get either to work (since in this case there needs to be a join on the header and detail tables)
a) OVER PARTITION BY (below is nowhere near correct - just to show example)
SELECT product_code, price, date_entered
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY product_code ORDER BY date_entered DESC) AS OrderedDate,* FROM opdetm ) AS ordlist
WHERE ordlist.OrderedDate<=3;
b) SELECT.. WHERE IN (SELECT TOP n...)
SELECT E.scheme_opdetm.product, scheme_opdetm.net_price, opheadm.date_despatched
FROM scheme_opdetm, scheme_opheadm AS E
WHERE E.date_despatched IN (SELECT TOP 3 E2.date_despatched from scheme_opdetm, scheme_opheadm AS E2 WHERE E.product = E2.product Order by E2.date_despatched DESC)
ORDER BY E.product, net_price DESC;
Questions:
a) Which approach is best? Why?
b) Please could someone assist with the query I need, and if possible a short explanation of how to construct this type of query and what the different parts do. I'd like to try to understand what I'm diong as opposed to just copying someones SQL 
Thanks in advance
|
|
|
|
|
Take a look at this:
CREATE TABLE Opheadm (date_despatched date, order_num int)
create table opdetm (order_num int, product VARCHAR(100), net_price money)
INSERT INTO Opheadm values
('3/23/2012', 4),
('2/1/2012', 3),
('1/23/2012', 2),
('1/25/2011', 1)
INSERT INTO opdetm values
(4, 'prod_a', 2.99),
(3, 'prod_a', 2.60),
(2, 'prod_a', 3.80),
(1, 'prod_a', 1.20)
SELECT top 3 a.product, a.net_price,
ROW_NUMBER() Over(order by b.date_despatched DESC) AS RowNum
from opdetm a
inner join Opheadm b
on a.order_num = b.order_num
|
|
|
|
|
Hi djj55
I tested the query and it did not return what I was expecting.
It only returned three rows.
I need the last three orders for every stock code.
Maybe I need to
SELECT DISTINCT (product) from opdetm or something like that?
And then do the joins to opheadm and join to get the last three orders?
There is another table (stockm) stock master which has product as a primary key, so I could use that as well, except I would also need to only select records from stockm where the warehouse = '06'
Currently I am pulling all stockm records where warehouse = '06' into another datatable in my application with a separate query, then I want to loop through the result set of this query and populate the table from stockm with the prices, dates and quantities of the last three sales orders, as well as comparing the standard cost of the item with the last sales order, and calculating the difference between the last Sales order and the standard cost
The end result is a grid showing ALL stock items in warehouse 06, with the following columns:
warehouse, product, physical_qty, standard_material, Error, SO1Price, SO1Date, SO1Qty, SO2Price, SO2Date, SO2Qty, SO3Price, SO3Date, SO3Qty
If an item in the stock master has no sales, it must be listed, but the all the SO fields must be blank.
|
|
|
|
|
Sorry, being at work I do not have a lot of time.
I believe what is needed is a sub query so you can then use a WHERE clause to get only three of each. If you are using 2008 you might try WITH.
This has not been tested but something like:
SELECT Produce, Net_Price
FROM (
SELECT a.product, a.net_price,
ROW_NUMBER() Over(order by b.date_despatched DESC) AS RowNum
from opdetm a
inner join Opheadm b
on a.order_num = b.order_num ) AS c
WHERE RowNum < 4
|
|
|
|
|
Also at work, but on my way home, will test when I get home - and yes, using 2008
|
|
|
|
|
I have a problem in a SSIS solution that is i have many flat file data sources "Text Files" want to load them in a SQL SERVER Database as a
destination, but flat files overridden every day with new data and i want only to take new data , so how can i take the new data ???
|
|
|
|
|
How do you determine which data is new?
I would likely import the data to a table that is designed for that purpose and then execute a separate SQL statement to move only the new data to the actual destination. The import area would get cleared out before each import.
|
|
|
|
|
The source data is a text files that contain the attendance data empID, datetime and etc and every day each text file come with the new and old attendance data, i am using SSIS solution
|
|
|
|
|
|
how is it easy even though every day text files come with the new and old data???
|
|
|
|
|
See Mycroft's post. Clear out a temporary area, import all the data there, then copy only the new data to the final location.
|
|
|
|
|
The way we do this type of operation is to move the data into and archive folder and load from there. If a new file arrives in the hot folder it has not been loaded so move it and load from the archive folder.
We also do NOT transform during the load, the target table exactly reflects the source table and all fields are varchar. We then use a stored procedure to do the transforms (launched by the SSIS package).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Another point on which we agree. 
|
|
|
|
|
Hi,
Maybe somebody can explain a weird phenomenon I am, sometimes, seeing.
Using Microsoft SQL server (edition 2000) I have a stored procedure which returns a particular set of results. The result set is a simple SELECT from a temporary table, the odd result occurs in this returned column:
WC_Diff = CASE WHEN EmpDiff > 0 AND WC_ID > 0 THEN
'+' + CAST(EmpDiff AS VARCHAR(3)) + ' (' + CAST((NoOfEmp - EmpDiff) AS VARCHAR(3)) + ' » ' + CAST (NoOfEmp AS VARCHAR(3)) + ')'
WHEN EmpDiff < 0 AND WC_ID > 0 THEN
CAST (EmpDiff AS VARCHAR(3)) + ' (' + CAST((NoOfEmp - EmpDiff) AS VARCHAR(3)) + ' » ' + CAST(NoOfEmp AS VARCHAR(3)) + ')'
ELSE NULL END,
The weird bit is the way the '»' character changes every now and again.
Using Microsoft's Query analyzer (or a variety of other client programs) the '»' character is sometimes modified to a '+' character. In the few cases it happens all I need to do to get the right output again is recompile the procedure without any change at all.
I first thought it was a problem with 8 bit ASCII to 7 bit ASCII conversion but that is not right: '»' has hex code 0xBB which would be converted to 0x3B (';') but it becomes 0x2b ('+').
I am baffled: anyone out there who knows what may be wrong ?
Any suggestions much appreciated.
|
|
|
|
|
Hi Again,
It took a while but I figured out what is the cause and the solution (google helped a lot).
Cause: each night the procedure is recompiled using 'OSQL' (to keep a development server up to date after copying the real server's data bases just before that). That command line utility uses OEM code page 437 and when reading the usual ANSI format SQL files it converts various things which causes the character to change.
Solution: when reading UNICODE format files OSQL makes no conversions and everything works. Both the Query analyzer and OSQL produce the same result.
Once you know its a piece of cake and very logical but it definitely does not show up in any manuals, at least none that I have seen.
Bye for now,
Filip
|
|
|
|
|
Hi I need a connection code to connect to my database and it will select and this play the result on a new page. Can anyone help me on that?.
|
|
|
|
|
You can start here[^].
Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman
|
|
|
|
|
Hi All,
Every time I do paging on database results, I feel wonder why there is no native commands to do paging on Sql queries. Pagination is a basic requirement to fetch data from large tables.
All I can do that to fetch entire data into a temporary result set like temporary table or CTE and apply paging into that result set. But why I have to fetch entire table to get 20 or 30 records? I know there is a better alternative for this is that to select only primary key fields instead of entire columns and use the primary keys to create the page. But still I have to select entire table at first.
Is there any better solution for this? Thanks in advance.
Regards,
Thomas
|
|
|
|
|
SSEAR wrote: Pagination is a basic requirement to fetch data from large tables.
No it isn't.
It is a "basic" requirement for GUIs. Which is no more relevant to a database than the color that you use to display the results.
SSEAR wrote: Is there any better solution for this?
The 'best' solution is to require your users to provide enough information that you don't need to page at all. Users do NOT use large lists in a random fashion. They know what they are looking for so make them tell you that.
If you are still left with pages then you must first start by defining what happens when user A goes to lunch and while gone user B adds something new into the list that user A was using. (Vast majority of time it doesn't matter but the business users must decide that.)
After that then you research the specific database to find out how to return a fixed size list out of a larger query. This is a 'page'. It involves determining how to select the next 'page'.
|
|
|
|
|
jschell wrote: It is a "basic" requirement for GUIs. Which is no more relevant to a database than the color that you use to display the results.
I agree. I was just worrying about the overkill of fetching entrie table. Think about TOP{no of records}. Can the pagination implement like this? Oky, I am not ignoring the additional overhead to calculate the 'current page' on Sql Server.
jschell wrote: The 'best' solution is to require your users to provide enough information that you don't need to page at all. Users do NOT use large lists in a random fashion. They know what they are looking for so make them tell you that.
This is not pratical in many situations. Consider a shopping web site which have a statergy is to force the customer to buy products even though they really dont require it. The customer may doesnt have idea what they want and like to surf through available items.
|
|
|
|
|
SSEAR wrote: This is not pratical in many situations.
Actually it is practical in most real situations.
SSEAR wrote: Consider a shopping web site which have a statergy is to force the customer to
buy products even though they really dont require it. The customer may doesnt
have idea what they want and like to surf through available items.
And is that exactly what you are implementing?
From those same sites - do they allow the customers an option to pick out which customer they are from a list of all customers?
|
|
|
|
|
As jschell pointed out paging is a purely presentation layer issue, databases deal with data not pages!
Having said that there are a number of hacks to get paging from the database, CTE is one you are familiar with there is also row_number() and top. These are all hacks to meet requests such as your, be thankfull you have some work around!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: be thankfull you have some work around
I thank god for not make me as a 'PLZ GV ME CODZ' programmer 
|
|
|
|
|
Hi,
Try below
If you want page index 1 (between 10-9 and 10)
page index 2 (between 20-9 and 20)
.
.
page index 25 (between 250-9 and 250)
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY OBJECT_ID) AS [PageIndex],* FROM sys.objects
) AS TB1
WHERE PageIndex BETWEEN 10-9 AND 10
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
