topic Re: Help with SQL Query in Application Development and Automation Discussions

Help with SQL Query

former_member1332171 — Fri, 26 Aug 2022 06:55:14 GMT

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.

Re: Help with SQL Query

MKreitlein — Fri, 26 Aug 2022 07:06:27 GMT

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

Re: Help with SQL Query

former_member1332171 — Fri, 26 Aug 2022 07:15:26 GMT

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!

Re: Help with SQL Query

Sandra_Rossi — Fri, 26 Aug 2022 08:16:46 GMT

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"

Re: Help with SQL Query

Sandra_Rossi — Fri, 26 Aug 2022 08:24:33 GMT

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.

Re: Help with SQL Query

RaymondGiuseppi — Fri, 26 Aug 2022 08:44:12 GMT

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

Re: Help with SQL Query

Sandra_Rossi — Fri, 26 Aug 2022 08:45:44 GMT

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.

Re: Help with SQL Query

former_member1332171 — Fri, 26 Aug 2022 14:10:18 GMT

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