March 14, 2009 at 2:25 am
Below the code I use. All seems fine except at the WHILE statement. There should be an error, but I can't see it.
I can not store anything to the database itself (views, procedure, tables). All has to be done on the fly. Any ideas?
/* This script should return partnumbers and sold quantities also from a combination that may have recursive combinations. */
WITH Sammel As
(SELECT tdProducts.ProductNumber As pid, tdStatSalesProducts.SalesPeriod as sp, tdStatSalesProducts.Quantity as qty, tdProducts.ProductType as pt
FROM tdStatSalesProducts inner join tdProducts ON tdStatSalesProducts.ProductID = tdProducts.ProductID
WHERE tdStatSalesProducts.SalesPeriod >= 2008000),
/* creating an empty table */
Ergebnis As (SELECT * FROM Sammel WHERE Sammel.pid = '0'),
/* creating an empty table */
EFTeile as (SELECT pid as artnr2, sp as datum3, qty as fmenge, pt as ha, pid as arti, qty as mge, pt as ha2 FROM Sammel WHERE Sammel.pid = '0')
/* Wrong syntax near 'WHILE' -Keyword */
/* I do not know what is wrong here!"
WHILE (SELECT COUNT(*) FROM Sammel) > 0
BEGIN
/* Ergebnis should get the data so far */
INSERT INTO Ergebnis (pid, sp, qty, pt) SELECT pid, sp, qty, pt FROM Sammel
/* part with combinations are searched with their part numbers */
INSERT INTO EFTeile (artnr2, datum3, fmenge, ha, arti, mge, ha2)
SELECT Sammel.pid, Sammel.sp, Sammel.qty, Sammel.pt, tdProducts.ProductNumber, Sammel.qty*tdComponentPos.Quantity AS mge, tdProducts.ProductType
FROM ((Sammel LEFT JOIN tdComponents ON Sammel.pid = tdComponents.ComponentNumber) LEFT JOIN tdComponentPos ON tdComponents.ComponentID = tdComponentPos.ComponentID) LEFT JOIN tdProducts ON tdComponentPos.ReferenceKeyID = tdProducts.ProductID
/* clear Sammel for next bunch of parts*/
DELETE FROM Sammel
/* push parts from EFTeile to Sammel */
INSERT INTO Sammel ( pid, qty, pt, sp )
SELECT EFTeile.arti, EFTeile.mge, EFTeile.ha, EFTeile.datum3
FROM EFTeile
WHERE (EFTeile.arti <> '')
/* Clear EFTeile*/
DELETE FROM EFTeile
/* Condition of the loop */
IF (SELECT COUNT(*) FROM Sammel) > 0
CONTINUE
ELSE
BREAK
END
/* Show the result in Ergebnis */
SELECT Ergebnis.pid As Artikelnummer, Sum(Ergebnis.qty) AS Verbrauch
FROM Ergebnisliste
GROUP BY Ergebnis.pid
ORDER BY Ergebnis.pid;
March 14, 2009 at 3:54 am
I don't think that the WHILE statement (or any conditional statements like IF/ELSE, GOTO etc.) is allowed in any CTE block. May be only DML statements are allowed in the final select, though I am not sure about this;)
And about your code, I didn't get exactly what your trying to do here. How about adding some sample data, logic and the required output in the next post.
--Ramesh
March 14, 2009 at 4:01 am
Books Online says this about CTEs:
Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
So you can't define CTEs in the scope of a WHILE loop and whatever happens within it.
You could use table variables or temporary tables instead, or else try to find a way to eliminate the WHILE loop, though I haven't looked at your code in enough detail to determine if this would be possible and practical.
March 14, 2009 at 6:01 am
Ok, it seems that my solution can not work.
Situation as follows (simplified):
Table statsales with fields partnumber, quantity, salesdate
Table parts with fields partnumber, parttype
Table combinations with fields assemblynumber, partnumber, quantity
A combinations is a part that contains subparts that can contain subparts recursively.
I want to know how many times each part and subpart is sold.
No writing allowed to the database.
At present I have made a solution in Access, but as I have read about WHILE and WITH statements I thought that could be a solution.
March 14, 2009 at 6:17 am
March 14, 2009 at 8:10 am
One option would be to use a recursive CTE to recurse through the hierarchy. Lookup SQL Server Bookes Online for an example. If you used this method you could eliminate the WHILE loop and all that inserting and deleting that you are doing in your current code.
In outline, the recursive CTE query would look something like this.
WITH cteSammel AS (
SELECT (top level parts and quantities sold during selected sales period)
UNION ALL
SELECT (parts and quantities that are components of the previously selected parts)
)
SELECT S.pid AS Artikelnummer, SUM(S.qty) AS Verbrauch
FROM cteSammel S
GROUP BY S.pid
ORDER BY S.pid
If you could provide more inforation about the table structure and also some sample data, I'm sure we could help you further.
March 14, 2009 at 8:59 am
Ok, I hope I have understand and the following is what you need for more help.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mysales','U') IS NOT NULL
DROP TABLE #mysales
IF OBJECT_ID('TempDB..#myparts','U') IS NOT NULL
DROP TABLE #myparts
IF OBJECT_ID('TempDB..#mycombinations','U') IS NOT NULL
DROP TABLE #mycombinations
--===== Create the test table with
CREATE TABLE #mysales
(
SalesPeriod INT,
ProductID INT,
Turnover money,
Quantity money
)
--===== Create the test table with
CREATE TABLE #myparts
(
ProductID INT,
ProductType INT
)
--===== Create the test table with
CREATE TABLE #mycombinations
(
MainProductID INT,
SubProductID INT,
Quantity money
)
--===== Insert the test data into the test table
INSERT INTO #mycombinations (MainProductID, SubProductID, Quantity)
SELECT '2789','2843',1.00 UNION ALL
SELECT '2789','2898',1.00 UNION ALL
SELECT '2789','2959',2.00 UNION ALL
SELECT '2789','3436',2.00 UNION ALL
SELECT '2843','2960',1.00 UNION ALL
SELECT '2843','2961',2.00
INSERT INTO #myparts (ProductID, ProductType)
SELECT '2789','7' UNION ALL
SELECT '2843','7' UNION ALL
SELECT '2898','1' UNION ALL
SELECT '2959','1' UNION ALL
SELECT '2960','1' UNION ALL
SELECT '2961','1' UNION ALL
SELECT '3436','1'
INSERT INTO #mysales (SalesPeriod, ProductID, Turnover, Quantity)
SELECT '2007001','2789',12.00,3.00 UNION ALL
SELECT '2007002','2789',24.00,6.00 UNION ALL
SELECT '2007002','3436',1.00,1.00 UNION ALL
SELECT '2007004','2789',12.00,3.00 UNION ALL
SELECT '2007001','2789',12.00,3.00
The Result should be (generated over all sales) as follows:
ProductID, Quantity
2789, 15
2843, 15
2898, 15
2959, 30
2960, 30
2961, 60
3436, 31
I am using
Microsoft SQL Server Management Studio Express 9.00.3042.00
at an Microsoft SQL Server Express 2005.
March 14, 2009 at 10:25 am
To SSChasing
I have tried your solution with UNION ALL, but the result is not what I am looking for. Problem here is, that also if a part is not sold the result shows the quantites from the combinations.
March 14, 2009 at 11:06 am
I feel very upset that I don't have any SQL right now:crazy::crying:, Sorry michael:ermm:, you have to wait until Monday if nobody else beat it to me.:D
--Ramesh
March 14, 2009 at 11:22 am
No problem for waiting as long as I get a clue how to solve that 🙂
March 14, 2009 at 2:32 pm
Thanks for providing the sample data and table structure.
Does this query give you the results you need?
;WITH cteSammel AS (
SELECT
1 AS [Level],
P.ProductID AS pid,
S.SalesPeriod AS sp,
S.Quantity AS qty,
P.ProductType AS pt
FROM #myparts P
INNER JOIN #mysales S ON (P.ProductID = S.ProductID)
UNION ALL
SELECT
X.[Level] + 1,
P.ProductID,
X.sp,
X.qty * C.Quantity,
P.ProductType
FROM cteSammel X
INNER JOIN #mycombinations C ON (X.pid = C.MainProductID)
INNER JOIN #myparts P ON (C.SubProductID = P.ProductID)
)
SELECT
X.pid AS Artikelnummer,
SUM(X.qty) AS Verbrauch
FROM cteSammel X
GROUP BY X.pid
ORDER BY X.pid
NB: The results I get using this query for your test data don't quite match your expected results.
[font="Courier New"]Artikelnummer Verbrauch
2789 15.00
2843 15.00
2898 15.00
2959 30.00
2960 15.00
2961 30.00
3436 31.00[/font]
March 14, 2009 at 2:44 pm
Yes, you are right. Your results are not what I expected to see, but I have to go again and look that what I expected is correct.
Your results are OK!!!!
Now I am going to check this on the real data. I will let you know the result!
THANKS SO MUCH!:D
March 14, 2009 at 2:55 pm
Are you sure?
My query is including results from 3 levels of the hierarchy.
I also worked out the results by hand and got the same results as the query.
March 16, 2009 at 2:07 pm
Yes, it works on my real data. I had to make some adustments as I had simplyfied my question. So now all works like expected. 😛
THANKS!!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply