Solved

SQL with Subqueries

Posted on 2014-10-17
16
118 Views
Last Modified: 2014-12-23
Hi!

I try to run this SQL:

1. SELECT EBELN, EBELP, BWART, CHARG, MATNR, MJAHR, MENGE, MEINS, WERKS
2. FROM [ENLIST_WH_SQL].[dbo].[MSEG] WHERE EBELN + EBELP IN
3. (SELECT COUNT(MANDT) AS NumberOut, EBELN + EBELP
4. FROM [ENLIST_WH_SQL].[dbo].[MSEG]
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].[MSEG]
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?
0
Comment
Question by:Kim Neesgaard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +1
16 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40386352
Try using temp tables since like this is bit complicated and complex to maintain...

Is this what you are after:

SELECT COUNT(MANDT) AS NumberOut, EBELN + EBELP as value
into #X
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

SELECT COUNT(MANDT) AS NumberOut, EBELN + EBELP as value
into #Y
FROM [ENLIST_WH_SQL].[dbo].[MSEG]
WHERE BWART = '101' OR BWART = '644' OR BWART = '102' 
GROUP BY EBELN + EBELP


SELECT EBELN, EBELP, BWART, CHARG, MATNR, MJAHR, MENGE, MEINS, WERKS
FROM [ENLIST_WH_SQL].[dbo].[MSEG] 
WHERE EBELN + EBELP IN
(select X.VALUE 
FROM #X INNER JOIN #y ON x.VALUE =  y.VALUE
WHERE y.NumberOut < x.NumberOut )

Open in new window

0
 

Author Comment

by:Kim Neesgaard
ID: 40386370
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?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40386375
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

Open in new window

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:Kim Neesgaard
ID: 40386379
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].[MSEG] m
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40386381
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
;

Open in new window

0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40386382
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

Open in new window

0
 

Author Comment

by:Kim Neesgaard
ID: 40386402
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!
0
 

Author Comment

by:Kim Neesgaard
ID: 40386403
Sorry - the oppersite: 12:02 runs without error.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40386404
What's the error?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40386405
I think mine may need an additon to a where clause

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'
                        AND BWART IN ('101', '102', '644', '161', '641', '643')  --<< added
                  GROUP BY
                        EBELN
                      , EBELP
            ) derived
                  ON m.EBELN = derived.EBELN
                        AND m.EBELP = derived.EBELP
                        AND derived.y < derived.x
;

Open in new window

0
 

Author Comment

by:Kim Neesgaard
ID: 40391768
->  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!
0
 

Author Comment

by:Kim Neesgaard
ID: 40419327
I HAD this thing working:

SELECT EBELN, EBELP, BWART, CHARG, MATNR, MJAHR, MENGE, MEINS, WERKS
FROM [ENLIST_WH_SQL].[dbo].[MSEG] WHERE EBELN + EBELP IN
    (SELECT COUNT(MANDT) AS NumberOut, EBELN + EBELP
    FROM [ENLIST_WH_SQL].[dbo].[MSEG]
    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].[MSEG]
    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?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40419339
Check if you aren't running an old version of the script.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40419341
Nope. Sorry.
That query is badly formed and I have no idea what you are trying to achieve with it.
0
 

Author Comment

by:Kim Neesgaard
ID: 40419475
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!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40515493
Kim,

Thank you for selecting my solution as the answer - but can you tell me why you believe it is a B Grade?
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question