topic Re: SUBQUERY / UNION in Application Development and Automation Discussions

SUBQUERY / UNION

Former Member — Tue, 16 Oct 2007 22:40:37 GMT

Is there a UNION (ALL) command in OPEN SQL? I'm attempting to join several tables and take the maximum date to be used as output into a text file.

I'm attempting to do something like the following query, but it isn't working:

SELECT pernr

MAX(

SELECT begda FROM pa0000 WHERE pernr = wa_pernrs-pernr AND ( begda > c_date AND begda < p_date )

UNION ALL

SELECT begda FROM pa0001 WHERE pernr = wa_pernrs-pernr AND ( begda > c_date AND begda < p_date )

UNION ALL

SELECT begda FROM pa0002 WHERE pernr = wa_pernrs-pernr AND ( begda > c_date AND begda < p_date )

UNION ALL

SELECT begda FROM pa0008 WHERE pernr = wa_pernrs-pernr AND ( begda > c_date AND begda < p_date )

UNION ALL

SELECT begda FROM pa0041 WHERE pernr = wa_pernrs-pernr AND ( begda > c_date AND begda < p_date )

)

INTO TABLE it_dates

FROM pa0000 AS a

INNER JOIN pa0001 AS b ON a_{pernr = b}pernr

INNER JOIN pa0002 AS c ON a_{pernr = c}pernr

INNER JOIN pa0008 AS d ON a_{pernr = d}pernr

INNER JOIN pa0041 AS e ON a_{pernr = e}pernr

LEFT OUTER JOIN csks AS f ON b_{kostl = f}kostl

" UP TO 1000 ROWS

WHERE ( a_{begda > c_date AND a}begda < p_date ) OR

( b_{begda > c_date AND b}begda < p_date ) OR

( c_{begda > c_date AND c}begda < p_date ) OR

( d_{begda > c_date AND d}begda < p_date ) OR

( e_{begda > p_date AND e}begda < p_date ).

Thank you in advance.

Re: SUBQUERY / UNION

Former Member — Tue, 16 Oct 2007 22:50:13 GMT

Hi Jeff,

There is no such command. You need to built your logic using JOINS and LOOPing around internal tables.

Regards,

Atish

Re: SUBQUERY / UNION

Former Member — Tue, 16 Oct 2007 22:53:37 GMT

That's kind of what I thought. Just trying to rely on SQL Server experience to limit loops (I have a hard time with that, as cursors (loops) are inherently bad in DB programming)

Thanks

Re: SUBQUERY / UNION

Former Member — Wed, 12 Dec 2007 12:42:51 GMT

Hi,

a little bit late - I'm almost sure you are allready out of this topic.

I'm in a middle of similar research regarding "union all". Obviously OPEN SQL can't handle UNION additon, but in some cases we may rely on NATIVE SQL, depending on are our statements hardly "OpenSql-ed" or not.

For further similar cases - check the SAP integrated help for a command "EXEC SQL....ENDEXEC". I was curious to find out there is even possibility to create and use stored procedures...

In other words, if you prefer not to rely on open sql sintax (with all its advantages and disadvantages) you may play arround with native sql. I suppose native sql syntax is based on SQL92 standard and all RDBMS specific additions (means Oracle, DB2, MsSql and so on) might not be supported, but never found any topic is that correct or not.

Regards,

Ivaylo Mutafchiev