3

I'm a beginner in SQL. I hope my question is not too stupid.

I'm trying to write a query in MS Query and want to see the results in Excel. The problem is that MS Query doesn't accept my code. I get the message:

table '(select' couldn't be added.

Here's the code:

SELECT  
    T1.Jahr, 
        T1.Monat, 
        T1.Land_ID, 
        T1.Kanal, 
        T1.Items_per_HC, 
        T1.Planstand, 
        T1.Zeitraum,
        T2.Zeitraum,
        T2.LAND_ID,
        T2.KANAL,
        T2.IST_ITEMS_PER_HC_EXCL_EASY 
FROM 
(SELECT tPLAN.Jahr, tPLAN.Monat, tPLAN.Land_ID, tPLAN.Kanal, 
        tPLAN.Items_per_HC, tPLAN.Planstand, tZ.Zeitraum
    FROM t_Plan_Items_per_HC tPLAN, t_PLAN_ZEITRAUM tZ
    WHERE tPLAN.Jahr = tZ.Jahr 
    AND tPLAN.Monat = tZ.Monat) as T1
INNER JOIN a_ITEMS_PER_CALL_ACT_MONTH_KANAL_EXCL_EASY_HC AS T2 
  ON (T1.Zeitraum=T2.Zeitraum 
  AND T1.Land_ID=T2.LAND_ID 
  AND T1.Kanal=T2.KANAL)

What is wrong?

I have to see the content of table t_Plan_Items_per_HC and table a_ITEMS_PER_CALL_ACT_MONTH_KANAL_EXCL_EASY_HC in only one table. They should be joined by Land_ID, Zeitraum, and KANAL.

UPDATE: Now I tried the same code in SQL Developer and it works. It seems to be an MS Query-specific problem. Unfortunately I have to solve this problem in MS Query so I still need help.

2
  • Why don't you create a view and just call the view from MSQuery? Jan 27, 2016 at 14:17
  • Does MS_Query allow multiple table joins? If yes, you could eliminate the subquery, using a simple 3-table join. Jan 27, 2016 at 14:20

1 Answer 1

1

Your query can be simplified (regardless of SQL flavor) not to use an inner query (subselect). This is not tested, but it should be closer to what you need:

SELECT tPLAN.Jahr, tPLAN.Monat, tPLAN.Land_ID, tPLAN.Kanal, T1.tPLAN, tPLAN.Planstand, 
    tZ.Zeitraum, T2.IST_ITEMS_PER_HC_EXCL_EASY 
FROM t_Plan_Items_per_HC tPLAN 
    JOIN t_PLAN_ZEITRAUM tZ ON tZ.Jahr = tPLAN.Jahr AND tZ.Monat = tPLAN.Monat
    JOIN a_ITEMS_PER_CALL_ACT_MONTH_KANAL_EXCL_EASY_HC AS T2 
        ON (T2.Zeitraum=tZ.Zeitraum AND T2.Land_ID=tPLAN.LAND_ID AND T2.Kanal=tPLAN.KANAL) 

Since all JOINs are INNER, you can just select from one of the tables and JOIN with each of the others using your criteria. Also, WHERE conditions like:

FROM t_Plan_Items_per_HC tPLAN, t_PLAN_ZEITRAUM tZ
    WHERE tPLAN.Jahr = tZ.Jahr 
    AND tPLAN.Monat = tZ.Monat) 

are not recommended. Use JOINs instead.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.