topic Re: union in a select in Application Development and Automation Discussions

union in a select

Former Member — Mon, 08 Jul 2013 13:23:27 GMT

Hi,

can anybody tell me whether the following sql stardard is possible in abap and how I can emulate the same?

	select * from sapsr3.mast@st1_desarrollos
	WHERE mandt = 300
	AND werks = '1100'
	AND stlan = '1'
	AND stlal = '01'
	union
	select * from sapsr3.mast@st1_desarrollos
	WHERE mandt = 300
	AND werks = '1100'
	AND stlan = '1'
	AND stlal = '02'
	AND trim(matnr) not in
	(select distinct trim(matnr) from sapsr3.mast@st1_desarrollos
	where mandt = 300
	AND werks = '1100'
	AND stlan = '1'
	AND stlal = '01')

Thanks in advance.

Regards.

Re: union in a select

ThomasZloch — Mon, 08 Jul 2013 13:29:59 GMT

As union select is not available in open SQL (as much as I know), I'd try with two selects, the first one with INTO TABLE and second one with APPENDING TABLE option. The subquery for the second select is also possible in open SQL (but not the trim function)

Thomas

Re: union in a select

Former Member — Mon, 08 Jul 2013 13:31:28 GMT

Hi David

There is no union in abap select

what you can do is to split the query

1- you can do 2 queries

e.g using for all entries .... statement

Then Loop for all entries...

and append it to the internal table

Please search you will find lots of examples...

Re: union in a select

Former Member — Mon, 08 Jul 2013 14:03:53 GMT

Could you, please, give any link where would explained this issue?

Thanks.

Re: union in a select

Former Member — Tue, 09 Jul 2013 05:58:11 GMT

You can join two select statements with a UNION clause in ABAP.

Check this documentation.

http://help.sap.com/saphelp_nw04/helpdata/en/13/aaca1b3ac34e3b944f94730947c7f4/content.htm

http://careerabap.blogspot.ae/2011/08/sql-union-query.html

Re: union in a select

ThomasZloch — Tue, 09 Jul 2013 06:33:29 GMT

Neither of these links is related to ABAP Open SQL, the first one is for Java environments, the second says "ABAP" but the actual code shown there can only work as Native SQL surrounded by EXEC SQL / ENDEXEC.

Thomas

Re: union in a select

Former Member — Tue, 09 Jul 2013 06:56:13 GMT

It is not necessary to work with Native SQL, what I want is to simulate the previous sql expression in abap if it is possible, of course.

If it is impossible so I could use the sql surrounded by EXEC SQL and ENDEXEC...

Re: union in a select

VXLozano — Tue, 09 Jul 2013 07:16:16 GMT

Thomas' answer is the right one for your subject question: if you must use an UNION to retrieve data and add it to an internal table, you should proceed with TWO select's using INTO TABLE and APPENDING TABLE clauses.

If you want to know if your whole SQL is possible in ABAP, I fear it's not. The use of some functions in the SELECT or WHERE clauses are restricted. You shall find a way to simulate them with some sort of post-processing (I mean: retrieve the full data with the INTO/APPENDING and then to use a LOOP to modify your data (trim = CONDENSE) and/or delete it.

Re: union in a select

Former Member — Tue, 09 Jul 2013 09:33:11 GMT

Check This

SELECT empid AS employee_id, sal AS salary FROM employees

UNION

SELECT empid AS employee_id, sal AS salary FROM more_employees

ORDER BY employee_id

Re: union in a select

Former Member — Tue, 09 Jul 2013 09:35:49 GMT

If you want to get the output using multiple queries, you can apply the following logic.

Tables sapsr3.

data s_matnr type range of matnr WITH HEADER LINE.
data : begin of i_matnr OCCURS 0,
matnr type matnr,
end of i_matnr.
data i_tab type table of sapsr3.

select DISTINCT matnr into CORRESPONDING FIELDS OF TABLE i_matnr from sapsr3

CLIENT SPECIFIED where mandt = '300' AND werks = '1100' AND stlan = '1' AND stlal = '01'.

loop at i_matnr.
    s_matnr-sign = 'E'.
    s_matnr-option = 'EQ'.
    s_matnr-low = i_matnr-matnr.
    append s_matnr.
endloop.

select * from sapsr3 CLIENT SPECIFIED into table i_tab

WHERE mandt = '300' AND werks = '1100' AND stlan = '1' AND stlal = '02'

delete i_tab where matnr in s_matnr.

select * from sapsr3 CLIENT SPECIFIED appending table i_tab

where mandt = '300' AND werks = '1100' AND stlan = '1' AND stlal = '01'.

I do not think trim function would be required as matnr will always be in the internal format.

Let me know if its working fine.

Re: union in a select

Former Member — Tue, 09 Jul 2013 10:39:09 GMT

I have used the following code and, it seems that it works fine, at least with my data:

select matnr andat aedat

into (formu_aux-material, formu_aux-feccrea, formu_aux-fecmod)

from mast client specified

where mandt = sy-mandt

and werks in pcentro

and ( andat in pfbusca

or aedat in pfbusca )

and stlan eq '1'

and stlal eq '02'

and matnr not in

( select matnr

from mast client specified

where mandt = sy-mandt

AND werks eq '1100'

AND stlan eq '1'

AND stlal eq '01'

append formu_aux.

clear formu_aux-material.

clear formu_aux-feccrea.

clear formu_aux-fecmod.

endselect.

select matnr andat aedat

into (formu_aux-material, formu_aux-feccrea, formu_aux-fecmod)

from mast client specified

where mandt = sy-mandt

AND werks eq '1100'

AND stlan eq '1'

AND stlal eq '01'.

append formu_aux.

clear formu_aux-material.

clear formu_aux-feccrea.

clear formu_aux-fecmod.

endselect.

What is your opinion?

Thanks.

Re: union in a select

Former Member — Tue, 09 Jul 2013 10:40:48 GMT

I haven't ckecked that but I think the union reserved word does not exist in abap,

I'm sorry if I'm wrong...

Re: union in a select

Former Member — Tue, 09 Jul 2013 10:58:16 GMT

Its fine except that select end select query performance is low .

http://scn.sap.com/community/abap/testing-and-troubleshooting/blog/2012/09/21/selectinto-table-faster-than-selectendselect-the-real-reason

Hence select into table itab (select appending table itab in the second case) is always preferable than select end select.

Re: union in a select

Former Member — Tue, 09 Jul 2013 11:26:38 GMT

Hi David

Thry to simlify your query as much as possible

One thing

Avoid:

select...

append..

endselect.

As they are slow in performance..

try using ranges which makes qucker performance

İ write you an example:

   types: BEGIN OF ty_formu_aux,
material type mast-matnr,
feccrea type mast-andat,
formu_aux type mast-aedat,
end of ty_formu_aux
.

DATA: gt_main_formu_aux type STANDARD TABLE OF ty_formu_aux,
      gt_formu_aux_2 type STANDARD TABLE OF ty_formu_aux
      .
RANGES: s_exclude for mast-matnr.

break sdogan.

*Now build the matnr to exclude
select matnr as low
into TABLE s_exclude
from mast
UP TO 500 ROWS
where werks eq '1100'
          AND stlan eq '1'
          AND stlal eq '01'
.

s_exclude(3) = 'IEQ'.

modify s_exclude transporting sign option
where sign eq ''.

select matnr andat aedat
into CORRESPONDING FIELDS OF TABLE gt_main_formu_aux
from mast
where
   werks in pcentro
   and   ( andat in pfbusca
   or    aedat in pfbusca ) and
   stlan eq '1'
   and   stlal eq '02'
   and matnr not in s_exclude
.

select matnr andat aedat
into CORRESPONDING FIELDS OF TABLE gt_formu_aux_2
from mast
where
   werks eq '1100'
   AND stlan eq '1'
   AND stlal eq '01'.

*Now add the other table into the main one
APPEND LINES OF gt_formu_aux_2 to gt_main_formu_aux.

Re: union in a select

arindam_m — Tue, 09 Jul 2013 11:39:24 GMT

Hi,

You can try CL_SQL* classes, its a way to do your SQL according to DB specific syntax and not bother on SAP specific translation.

Cheers,

Arindam

Re: union in a select

Former Member — Tue, 09 Jul 2013 11:47:01 GMT

In the code below, s_matnr-sign = 'I'.

(Not able to edit it)

Re: union in a select

VXLozano — Tue, 09 Jul 2013 12:18:48 GMT

David, you can get the same result in less time and with less code lines using Thomas' answer:

1 - forget the SELECT loops

2- First select using INTO TABLE formu_aux

3- Second select using APPENDING TABLE formu_aux

About your code:

1- you can drop the CLIENT SPECIFIED if you will use your system's client (sy-mandt)

2- you don't need to clear the workarea after the APPEND, as it will be reassigned in the next SELECT loop

3- if you want to be sure about the "clearing", you can use just "CLEAR formu_aux." and it will clear the three fields.

4- from your code, it can be though you are using WITH HEADER LINE clause. It's a bad idea, because it makes the code harder to read. I should advice you to use w_form_aux and t_form_aux different variables

5- (not important) EQ is a bit slower (maybe nanoseconds) than =

Re: union in a select

Former Member — Tue, 09 Jul 2013 12:28:16 GMT

it will be good to know

You wouldnt need to write db specific query unless there is no other option

İ never written one specific.

Cause the code needs to change as db changes or maybe gets upgraded

Arindam do you have any examples of cl_sql* classes we can try

Re: union in a select

Former Member — Tue, 09 Jul 2013 12:59:40 GMT

totally agree Lozano

tables with headers are obselete in abap

e.g cant be used in classes or local methods

for step 4- you can use field symbols as well

So follow this David

Re: union in a select

ThomasZloch — Tue, 09 Jul 2013 13:05:56 GMT

Seems that I'm writing with (partially) invisible ink, but still here my suggestion again in code:

SELECT * FROM mast

INTO TABLE it_mast

WHERE werks = '1100'

AND stlan = '1'

AND stlal = '01'.

SELECT * FROM mast

APPENDING TABLE it_mast

WHERE werks = '1100'

AND stlan = '1'

AND stlal = '02'

AND matnr NOT IN

( SELECT DISTINCT matnr FROM mast

WHERE werks = '1100'

AND stlan = '1'

AND stlal = '01' ).

Since this is not index supported, there can be runtime issues (depending on size of MAST), but you'll have these anyway no matter how complicated you choose to solve this.

Thomas