topic Re: SQL Command conversion in abap code in Application Development and Automation Discussions

SQL Command conversion in abap code

ravi_sirigiri — Thu, 22 Jun 2006 12:54:58 GMT

Hi,

I want to implement the UNION, INTERSECT and MINUS SQL commands in abap code. Please give me the appropriate solutions asap.

For Example:

select field1, field2, . field_n

from tables

UNION

select field1, field2, . field_n

from tables;

select field1, field2, . field_n

from tables

MINUS

select field1, field2, . field_n

from tables;

select field1, field2, . field_n

from tables

INTERSECT

select field1, field2, . field_n

from tables;

Thanks,

Ravi

Re: SQL Command conversion in abap code

andreas_mann3 — Thu, 22 Jun 2006 13:03:02 GMT

hi,

look under <a href="http://help.sap.com/saphelp_47x200/helpdata/en/60/515f3eca2a11d2a97100a0c9449261/frameset.htm">subquery</a> in abap-docu

Re: SQL Command conversion in abap code

Former Member — Thu, 22 Jun 2006 13:09:29 GMT

Hi ravi,

here is a good link u can refer to...

http://help.sap.com/saphelp_webas620/helpdata/en/4d/47dfb6f48511d195200000e8353423/content.htm

Regards,

Kiran B

Re: SQL Command conversion in abap code

ashok_kumar24 — Thu, 22 Jun 2006 13:10:31 GMT

Hi Ravi,

Go through the following links

You will find important information

Oracle queries

http://sqlzoo.net/

To format SQL

http://www.sqlinform.com/

http://www.sswug.org/searchresults.asp?keywordstofind=sql%20queries

Good Luck and reward me for the same

Thanks

AShok.N

Re: SQL Command conversion in abap code

Former Member — Thu, 22 Jun 2006 13:12:30 GMT

You can use BIT-AND for Intersection and BIT-OR for Union

Check the link.

http://help.sap.com/saphelp_webas620/helpdata/en/4d/47dfb6f48511d195200000e8353423/content.htm

Also you can use subquery with with ALL, SOME or ANY variants..

Regards,

Richa

Re: SQL Command conversion in abap code

ashok_kumar24 — Thu, 22 Jun 2006 13:23:00 GMT

Hi Ravi

Check out this procedure...

DATA: FRANKFURT(4) TYPE X,

FRISCO(4) TYPE X,

INTERSECT(4) TYPE X,

UNION(4) TYPE X,

BIT TYPE I.

DATA: CARRID TYPE SPFLI-CARRID,

CARRIER LIKE SORTED TABLE OF CARRID

WITH UNIQUE KEY TABLE LINE.

DATA WA TYPE SPFLI.

SELECT CARRID FROM SCARR INTO TABLE CARRIER.

SELECT CARRID CITYFROM FROM SPFLI

INTO CORRESPONDING FIELDS OF WA.

WRITE: / WA-CARRID, WA-CITYFROM.

READ TABLE CARRIER FROM WA-CARRID TRANSPORTING NO FIELDS.

CASE WA-CITYFROM.

WHEN 'FRANKFURT'.

SET BIT SY-TABIX OF FRANKFURT.

WHEN 'SAN FRANCISCO'.

SET BIT SY-TABIX OF FRISCO.

ENDCASE.

ENDSELECT.

INTERSECT = FRANKFURT BIT-AND FRISCO.

UNION = FRANKFURT BIT-OR FRISCO.

SKIP.

WRITE 'Airlines flying from Frankfurt and San Francisco:'.

DO 32 TIMES.

GET BIT SY-INDEX OF INTERSECT INTO BIT.

IF BIT = 1.

READ TABLE CARRIER INDEX SY-INDEX INTO CARRID.

WRITE CARRID.

ENDIF.

ENDDO.

SKIP.

WRITE 'Airlines flying from Frankfurt or San Francisco:'.

DO 32 TIMES.

GET BIT SY-INDEX OF UNION INTO BIT.

IF BIT = 1.

READ TABLE CARRIER INDEX SY-INDEX INTO CARRID.

WRITE CARRID.

ENDIF.

ENDDO.

This produces the following output list:

The program uses four hexadecimal fields with length 4 - FRANKFURT, FRISCO, INTERSECT, and UNION. Each of these fields can represent a set of up to 32 elements. The basic set is the set of all airlines from database table SCARR. Each bit of the corresponding bit sequences representes one airline. To provide an index, the external index table CARRIER is created and filled with the airline codes from table SCARR. It is then possible to identify an airline using the internal index of table CARRIER.

In the SELECT loop for database table SPFLI, the corresponding bit for the airline is set either in the FRANKFURT field or the FRISCO field, depending on the departure city. The line number SY-TABIX is determined using a READ statement in which no fields are transported.

The intersection and union of FRANKFURT and FRISCO are constructed using the bit operations BIT-AND and BIT-OR.

The bits in INTERSECT and UNION are read one by one and evaluated in two DO loops. For each position in the fields with the value 1, a READ statement retrieves the airline code from the table CARRIER.

Comparing Bit Sequences

Use the following three operators to compare the bit sequence of the first operand with that of the second:

Meaning

bits are one

bits are zero

bits are mixed

The second operand must have type X. The comparison takes place over the length of the second operand. The first operand is not converted to type X.

The function of the operators is as follows:

O (bits are one)

The logical expression

is true if the bit positions that are 1 in <hex>, are also 1 in <f>. In terms of set operations with bit sequences, this comparison is the same as finding out whether the set represented by <hex> is a subset of that represented by <f>.

Z (bits are zero)

The logical expression

is true if the bit positions that are 1 in <hex>, are 0 in <f>.

M (bits are mixed)

The logical expression

is true if from the bit positions that are 1 in <hex>, at least one is 1 and one is 0 in <f>.

Caution: The following programs are no longer supported in Unicode systems:

REPORT demo_log_expr_bits .

DATA: text(1) TYPE c VALUE 'C',

hex(1) TYPE x,

i TYPE i.

hex = 0.

DO 256 TIMES.

i = hex.

IF text O hex.

WRITE: / hex, i.

ENDIF.

hex = hex + 1.

ENDDO.

The output is as follows:

00 0

01 1

02 2

03 3

40 64

41 65

42 66

43 67

Here, the bit structure of the character 'C' is compared to all hexadecimal numbers HEX between '00' and 'FF' (255 in the decimal system), using the operator O. The decimal value of HEX is determined by using the automatic type conversion during the assignment of HEX to I. If the comparison is true, the hexadecimal number and its decimal value are displayed on the screen. The following table shows the bit sequences of the numbers:

Thanks

Ashok