SELECT statement comparing 2 fields in a table.

[Former Member]

Hi,

Can someone help me out in making a efficient SELECT statement for the follwing requirement.

Say, I want to select all the records from a database table where the field, PLANT is equal to field SALESORG in the table (i.e., when both fields are equal select that record).

Thanks & regards,

Sree

[Former Member]

Hi,

select * from t001w

into i_t001w

where

werks IN s_werks(select option).

select * from tvko

into i_tvko

for all entries in i_t001w

where

vkorg IN s_vkorg

AND vkorg = i_t001w-vkorg.

Can try this out.

Message was edited by: Judith Jessie Selvi

[jayanthi_jayaraman]

Hi,

Check this.

tables likp.

data itab type standard table of likp.

select * from likp as l into table itab where vkorg = l~werks.

[Former Member]

Thanks Jayanthi! this is what exactly I was looking for

Thanks Shekar & Judith for your sugesstions!

Regards,

Sree

[Former Member]

Hello,

I think this is the answer to your question... if you explain a bit more we can formulate a query.

Subquery

Variants:

1. ( select )

2. ALL ( select )

3. ANY ( select )

4. SOME ( select )

Effect

A subquery is a SELECT statement select that occurs within a

SELECT,

OPEN CURSOR,

UPDATE, or

DELETE statement

in the WHERE clause or HAVING clause, to check whether data from database tables or views meets certain criteria.

Subqueries have a restricted syntax in comparison to the normal SELECT statement:

SELECT result FROM source [WHERE where] [GROUP BY fields] [HAVING having].

Variant 1

( select )

If the subquery returns a single value, you can use any relational operator except LIKE and BETWEEN.

Example

Selecting the flights with the most passengers:

DATA: WA TYPE SFLIGHT.

SELECT * FROM SFLIGHT

INTO WA

WHERE SEATSOCC = ( SELECT MAX( SEATSOCC ) FROM SFLIGHT ).

WRITE: / WA-CARRID, WA-CONNID, WA-FLDATE.

ENDSELECT.

Note

If you use a subquery with a relational operator instead of EXISTS, you may only specify one column in the SELECT clause. This can be either a field from the database table or an aggregate expression. Subqueries of this kind are referred to as scalar subqueries.

Variant 2

ALL ( select )

If the subquery returns several lines, each containing one value, you specify that the comparison should apply for all of the values it returns.

Example

This example shows how to use ALL. It displays a list of the customer IDs of the customer (or customers) who have made the most bookings:

DATA: ID TYPE SBOOK-CUSTOMID, CNT TYPE I.

SELECT CUSTOMID COUNT( * ) AS C FROM SBOOK

INTO (ID, CNT)

GROUP BY CUSTOMID

HAVING COUNT( * ) >=

ALL ( SELECT COUNT( * ) FROM SBOOK GROUP BY CUSTOMID ).

WRITE: / ID, CNT.

ENDSELECT.

Variant 3

ANY ( select )

Variant 4

SOME ( select )

If the subquery returns several lines each containing one value, this variant specifies that the comparison should apply to at least one of the values returned. The IN operator is the same as the combination = ANY.

&ABAP_HINT

If you use a subquery with the EXISTS operator, the expression is true if the subquery selects at least one line. You can useuse * in the SELECT clause of subqueries that use EXISTS.

Example

Selecting all flights from Frankfurt to New York between 1.1.1999 and 31.3.1999 that are not yet full:

DATA: WA_SFLIGHT TYPE SFLIGHT.

SELECT * FROM SFLIGHT AS F INTO WA_SFLIGHT

WHERE SEATSOCC < F~SEATSMAX

AND EXISTS ( SELECT * FROM SPFLI

WHERE CARRID = F~CARRID

AND CONNID = F~CONNID

AND CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK' )

AND FLDATE BETWEEN '19990101' AND '19990331'.

WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID,

WA_SFLIGHT-FLDATE.

ENDSELECT.

Subqueries such as the one in this example, in which the WHERE clause uses fields from the main query, are known as correlated subqueries. Subqueries can be nested, and a given subquery may contain any fields from other, hierarchically-superior subqueries.

In a correlated subquery, the subquery is executed for each line r returned by the main query. In the above example, the main query finds all flights in table SFLIGHT that are not full and that have a date that meets the selection criterion. The statement then performs the subquery for each of the records returned by the main query, using the corresponding values of CARRID and CONNID, to check whether the relevant flight operates between Frankfurt and New York.

Additional help

Subqueries