Kim Neesgaard
asked on
SQL with Subqueries
Hi!
I try to run this SQL:
1. SELECT EBELN, EBELP, BWART, CHARG, MATNR, MJAHR, MENGE, MEINS, WERKS
2. FROM [ENLIST_WH_SQL].[dbo].[MSE G] WHERE EBELN + EBELP IN
3. (SELECT COUNT(MANDT) AS NumberOut, EBELN + EBELP
4. FROM [ENLIST_WH_SQL].[dbo].[MSE G]
5. WHERE (BWART = '643' OR BWART = '641' OR BWART = '161') AND MATNR > '000000003500000000' AND MATNR
6. < '000000006900000000' GROUP BY EBELN + EBELP) x INNER JOIN
7. (SELECT COUNT(MANDT), EBELN + EBELP
8. FROM [ENLIST_WH_SQL].[dbo].[MSE G]
9. WHERE BWART = '101' OR BWART = '644' OR BWART = '102' GROUP BY EBELN + EBELP) y ON x.EBELN + x.EBELP =
10. y.EBELN + y.EBELP
11. WHERE y.COUNT(MANDT) < x.COUNT(MANDT)
but get the error:
Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'x'.
Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'y'.
This is what I try to obtain in human text:
Select all interrelated EBELN + EBELP (3.) and count how many occurences with COUNT(MANDT). This seach is related to x.
Select all interrelated EBELN + EBELP (7.) with other filter criterias in the same table and count how many occurences with COUNT(MANDT). This seach is related to y.
The x and y are joined and the counts is used to filter (11.)
Finally this combined search result is used to get BWART, CHARG, MATNR, MJAHR, MENGE, MEINS, WERKS from the same table MSEG (1.).
Perhaps it is too ambitious to retrieve all this information in one SQL but in order to be usable in our situation, it must be in one retrieval.
Any ideas to solve this?
I try to run this SQL:
1. SELECT EBELN, EBELP, BWART, CHARG, MATNR, MJAHR, MENGE, MEINS, WERKS
2. FROM [ENLIST_WH_SQL].[dbo].[MSE
3. (SELECT COUNT(MANDT) AS NumberOut, EBELN + EBELP
4. FROM [ENLIST_WH_SQL].[dbo].[MSE
5. WHERE (BWART = '643' OR BWART = '641' OR BWART = '161') AND MATNR > '000000003500000000' AND MATNR
6. < '000000006900000000' GROUP BY EBELN + EBELP) x INNER JOIN
7. (SELECT COUNT(MANDT), EBELN + EBELP
8. FROM [ENLIST_WH_SQL].[dbo].[MSE
9. WHERE BWART = '101' OR BWART = '644' OR BWART = '102' GROUP BY EBELN + EBELP) y ON x.EBELN + x.EBELP =
10. y.EBELN + y.EBELP
11. WHERE y.COUNT(MANDT) < x.COUNT(MANDT)
but get the error:
Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'x'.
Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'y'.
This is what I try to obtain in human text:
Select all interrelated EBELN + EBELP (3.) and count how many occurences with COUNT(MANDT). This seach is related to x.
Select all interrelated EBELN + EBELP (7.) with other filter criterias in the same table and count how many occurences with COUNT(MANDT). This seach is related to y.
The x and y are joined and the counts is used to filter (11.)
Finally this combined search result is used to get BWART, CHARG, MATNR, MJAHR, MENGE, MEINS, WERKS from the same table MSEG (1.).
Perhaps it is too ambitious to retrieve all this information in one SQL but in order to be usable in our situation, it must be in one retrieval.
Any ideas to solve this?
ASKER
Hi samo4fun!
I have tried to run your suggestion and get this message:
(158122 row(s) affected)
(1886804 row(s) affected)
Msg 107, Level 16, State 2, Line 14
The column prefix 'X' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 14
The column prefix 'y' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 14
The column prefix 'x' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 14
The column prefix 'x' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 14
The column prefix 'y' does not match with a table name or alias name used in the query.
Do you have suggestions to this?
I have tried to run your suggestion and get this message:
(158122 row(s) affected)
(1886804 row(s) affected)
Msg 107, Level 16, State 2, Line 14
The column prefix 'X' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 14
The column prefix 'y' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 14
The column prefix 'x' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 14
The column prefix 'x' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 14
The column prefix 'y' does not match with a table name or alias name used in the query.
Do you have suggestions to this?
You can't do it as you did. Here's a single statement solution using common table expression:
WITH x (EBELN_EBELP, NumberOut)
AS
(SELECT (EBELN + EBELP), COUNT(MANDT) AS
FROM [ENLIST_WH_SQL].[dbo].[MSEG]
WHERE (BWART = '643' OR BWART = '641' OR BWART = '161')
AND MATNR > '000000003500000000' AND MATNR < '000000006900000000'
GROUP BY (EBELN + EBELP)),
y (EBELN_EBELP, NumberOut) AS
(SELECT (EBELN + EBELP), COUNT(MANDT) AS NumberOut
FROM [ENLIST_WH_SQL].[dbo].[MSEG]
WHERE (BWART = '101' OR BWART = '644' OR BWART = '102' )
GROUP BY (EBELN + EBELP))
SELECT m.EBELN, m.EBELP, m.BWART, m.CHARG, m.MATNR, m.MJAHR, m.MENGE, m.MEINS, m.WERKS, x.EBELN_EBELP, x.NumberOut, y.EBELN_EBELP, y.NumberOut
FROM [ENLIST_WH_SQL].[dbo].[MSEG] m
LEFT OUTER JOIN x ON x.EBELN_EBELP = m.EBELN + m.EBELP
LEFT OUTER JOIN y ON y.EBELN_EBELP = m.EBELN + m.EBELP
ASKER
Hi!
I tried to run the 3 parts in a SQL Server 2008 window and I have tried the same with your new suggestion - it gives me this:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FROM'.
Line 15: FROM [ENLIST_WH_SQL].[dbo].[MSE G] m
I tried to run the 3 parts in a SQL Server 2008 window and I have tried the same with your new suggestion - it gives me this:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FROM'.
Line 15: FROM [ENLIST_WH_SQL].[dbo].[MSE
try this. Without any data to test against I can't guarantee it's correct, but I think it's true to your intent.
SELECT
m.EBELN
, m.EBELP
, m.BWART
, m.CHARG
, m.MATNR
, m.MJAHR
, m.MENGE
, m.MEINS
, m.WERKS
FROM [ENLIST_WH_SQL].[dbo].[MSEG] m
INNER JOIN (
SELECT
COUNT(CASE WHEN BWART IN ('161', '641', '643') THEN MANDT END) AS x
, COUNT(CASE WHEN BWART IN ('101', '102', '644') THEN MANDT END) AS y
, EBELN
, EBELP
FROM [ENLIST_WH_SQL].[dbo].[MSEG]
WHERE MATNR > '000000003500000000'
AND MATNR < '000000006900000000'
GROUP BY
EBELN
, EBELP
) derived
ON m.EBELN = derived.EBELN
AND m.EBELP = derived.EBELP
AND derived.y < derived.x
;
Try to put a ';' before the WITH keyword:
;WITH x (EBELN_EBELP, NumberOut)
AS
(SELECT (EBELN + EBELP), COUNT(MANDT) AS
FROM [ENLIST_WH_SQL].[dbo].[MSEG]
WHERE (BWART = '643' OR BWART = '641' OR BWART = '161')
AND MATNR > '000000003500000000' AND MATNR < '000000006900000000'
GROUP BY (EBELN + EBELP)),
y (EBELN_EBELP, NumberOut) AS
(SELECT (EBELN + EBELP), COUNT(MANDT) AS NumberOut
FROM [ENLIST_WH_SQL].[dbo].[MSEG]
WHERE (BWART = '101' OR BWART = '644' OR BWART = '102' )
GROUP BY (EBELN + EBELP))
SELECT m.EBELN, m.EBELP, m.BWART, m.CHARG, m.MATNR, m.MJAHR, m.MENGE, m.MEINS, m.WERKS, x.EBELN_EBELP, x.NumberOut, y.EBELN_EBELP, y.NumberOut
FROM [ENLIST_WH_SQL].[dbo].[MSEG] m
LEFT OUTER JOIN x ON x.EBELN_EBELP = m.EBELN + m.EBELP
LEFT OUTER JOIN y ON y.EBELN_EBELP = m.EBELN + m.EBELP
ASKER
The 12:02 still gives an error but 12:03 runs without errors. Thank you so far - I will check the result of the query and come back!
ASKER
Sorry - the oppersite: 12:02 runs without error.
What's the error?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
-> PortletPaul
I have now investigated the search result and it looks REALLY good. I will do some further investigations and come back.
Thanks for the help!
I have now investigated the search result and it looks REALLY good. I will do some further investigations and come back.
Thanks for the help!
ASKER
I HAD this thing working:
SELECT EBELN, EBELP, BWART, CHARG, MATNR, MJAHR, MENGE, MEINS, WERKS
FROM [ENLIST_WH_SQL].[dbo].[MSE G] WHERE EBELN + EBELP IN
(SELECT COUNT(MANDT) AS NumberOut, EBELN + EBELP
FROM [ENLIST_WH_SQL].[dbo].[MSE G]
WHERE (BWART = '643' OR BWART = '641' OR BWART = '161') AND MATNR > '000000003500000000' AND MATNR < '000000006900000000') x INNER JOIN
(SELECT COUNT(MANDT), EBELN + EBELP
FROM [ENLIST_WH_SQL].[dbo].[MSE G]
WHERE BWART = '101' OR BWART = '644' OR BWART = '102') y ON x.EBELN + x.EBELP = y.EBELN + y.EBELP
WHERE y.COUNT(MANDT) < x.COUNT(MANDT) GROUP BY EBELN + EBELP
but when I run it today, I get the error:
Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'x'.
Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'y'.
Can you please help me out here?
SELECT EBELN, EBELP, BWART, CHARG, MATNR, MJAHR, MENGE, MEINS, WERKS
FROM [ENLIST_WH_SQL].[dbo].[MSE
(SELECT COUNT(MANDT) AS NumberOut, EBELN + EBELP
FROM [ENLIST_WH_SQL].[dbo].[MSE
WHERE (BWART = '643' OR BWART = '641' OR BWART = '161') AND MATNR > '000000003500000000' AND MATNR < '000000006900000000') x INNER JOIN
(SELECT COUNT(MANDT), EBELN + EBELP
FROM [ENLIST_WH_SQL].[dbo].[MSE
WHERE BWART = '101' OR BWART = '644' OR BWART = '102') y ON x.EBELN + x.EBELP = y.EBELN + y.EBELP
WHERE y.COUNT(MANDT) < x.COUNT(MANDT) GROUP BY EBELN + EBELP
but when I run it today, I get the error:
Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'x'.
Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'y'.
Can you please help me out here?
Check if you aren't running an old version of the script.
Nope. Sorry.
That query is badly formed and I have no idea what you are trying to achieve with it.
That query is badly formed and I have no idea what you are trying to achieve with it.
ASKER
I am very sorry - I had selected a wrong SQL (I had tried to make myself) by mistake and therefore it was badly formed. Once again: my apologies!
I found the SQL based on your very well formed SQL and it works perfectly!
I found the SQL based on your very well formed SQL and it works perfectly!
Kim,
Thank you for selecting my solution as the answer - but can you tell me why you believe it is a B Grade?
Thank you for selecting my solution as the answer - but can you tell me why you believe it is a B Grade?
Is this what you are after:
Open in new window