SQL execution returns different results than execution directly in MS SQL Server Management Studio
I have quite small experience with SQL, so maybe I am doing something wrong.
I was trying to build a query which will display results from several SELECT's. The code is quite large, but in general it goes like this:
Obroty.ART_CODE AS [Kod Kategorii],
Obroty.COL_CODE AS [Kod Wariantu],
(and so on.....)
(SELECT ROTAT.ART_CODE, ROTAT.COL_CODE, Sum(ROTAT.QUANTITY) AS Sum_QUANTITY
GROUP BY ROTAT.ART_CODE, ROTAT.COL_CODE) Obroty
INNER JOIN CARDS_ITE ON CARDS_ITE.ART_CODE = Obroty.ART_CODE AND CARDS_ITE.COL_CODE = Obroty.COL_CODE
INNER JOIN STOCK_WHS ON STOCK_WHS.ART_CODE = Obroty.ART_CODE AND STOCK_WHS.COL_CODE = Obroty.COL_CODE
and so on, more inner joins.
The problem is, that FlySpeed SQL Query returns different data than MS SQL SM Studio (seems sub-queries are not joined correctly on two conditions)
In general FlySpeed gives those results (dataset '990020' and '0' in two first columns does not even exist for sum of quantities = 204):
While MS SQL SM Studio returns correctly:
I don't have nearly enough experience to say what's wrong, but two programs giving different results on identical SQL query seems strange to me.
Other that this - I really appreciate Your work with FlySpeed, great program!
I agree that this is strange.
First, please check that you get connected to the same db and server, using the same user credentials (this can make sense).
Next, please click on the "SQL" button in the toolbar on the Result data tab. You will see the SQL query which is actually executed. Possibly the TOP clause has been added to this query. If you feel that your query is transformed incorrectly, you can send us the initial and the transformed query to email@example.com for investigation.
The point is that MS SQL server requires having the ORDER BY clause in a query if it has the TOP or FETCH FIRST clause in it. We are adding TOP programmatically, so we have to add ORDER BY as well if it doesn't exist in the initial query. Thus, the order of records differs from the initial query. I am sure that if you add the ORDER BY to the initial query (executed in both programs), you will see the same result in both programs.
You can avoid this problem in two ways:
1. You can turn the "Fetch records on demand" property to True and set the "Initial limit of visible data records" to All. These options are available on the Advanced tab of the Connection Properties dialog.
2. You can turn the "Disable SQL sorting and filtration" property on, but I don't recommend you this, as you'll loose the ability to sort and filter data on the Result Data tab.