Help with SQL Query

[former_member1332171]

Could someone kindly help with below? Essentially it is giving me error once I nest the SQL query.

Currently column1 and the FROM (before table_a ) and GROUP are highlighted in red.

The error message reads: column1 is invalid here (due to grammar).

What am I missing here? Thanks in advance!!

SELECT column1, count( * )
FROM
(
SELECT column1, column2
FROM table_a
UNION
SELECT column1, column2
FROM table_b
)

GROUP BY column1

INTO TABLE @gt_output.

[MKreitlein]

Hello Terris,

I'm not too deep into SQL, so I cannot see an error directly.

1) What is the actual error message?

2) Does it work without the Union?

3) maybe Count ( Column2) is better? There is only Column 2 left to count, right?

BR, Martin

[former_member1332171]

Hi Martin, thanks for your response! I updated my question above to simplify the query. Could you please see if it is more clear? Thanks!

[Sandra_Rossi]

Please use the button COMMENT if it's just about to provide information or reply to someone. The button ANSWER is only for providing the solutions, as you can see the text from SAP when you answer "Please provide a distinct answer and use the comment option for clarifying purposes"

[Sandra_Rossi]

ABAP SQL evolves a lot at each ABAP version, so please indicate what version you are using.

But I think there's no version to permit your syntax with nested SELECT.

Instead, you might be just keep the SELECT ... UNION SELECT ... but append distinct to the union (UNION DISTINCT) and that's it.

[RaymondGiuseppi]

Depending on your version, you could use a simple SELECT with the UNION DISTINCT option.

[Sandra_Rossi]

Note that UNION alone does a DISTINCT implicitly (same as UNION DISTINCT).

So, this is just fine:

SELECT column1, column2
FROM table_a
UNION
SELECT column1, column2
FROM table_b
INTO TABLE @gt_output.

[former_member1332171]

Hi Sandra,

Thanks so much for your comment. I realized the UNION thing after I posted this. However, the main issue seems it does not like the nested subquery.

I updated my query now to simplify the union part, but added a group by clause. It is still giving similar errors. Would you mind helping take a look?

Thanks so much!

Terris