topic Re: Sub queries + SQL in Application Development and Automation Discussions

Sub queries + SQL

Former Member — Mon, 19 Nov 2007 05:58:34 GMT

SELECT * FROM SFLIGHT INTO SFLIGHT_WA

WHERE SEATSOCC < SFLIGHT~SEATSMAX

AND EXISTS ( SELECT * FROM SPFLI

WHERE CARRID = SFLIGHT~CARRID

AND CONNID = SFLIGHT~CONNID

AND CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK' )

AND FLDATE BETWEEN '19990101' AND '19990331'.

ENDSELECT.

plzzz advise when do you use EXISTS how is it more efficient to FOR ALL ENTRIES

Re: Sub queries + SQL

Former Member — Mon, 19 Nov 2007 06:08:41 GMT

HI,

effect of exists:

The condition is met for a table entry if the result set of the subquery subquery contains [does not contain] any lines.

it is for data dictionary tables

for all entries is for checking the record in internal table is existing or not.

rgds,

bharat.

Re: Sub queries + SQL

Former Member — Mon, 19 Nov 2007 06:21:23 GMT

" if the result set of the subquery subquery contains [does not contain] any lines. "

Can you please elaborate

Re: Sub queries + SQL

Former Member — Mon, 19 Nov 2007 10:50:44 GMT

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

For all entries.

... FOR ALL ENTRIES IN itab WHERE cond

Effect

SELECT ... FOR ALL ENTRIES IN itab WHERE cond returns the union of the solution sets of all SELECT statements that would result if you wrote a separate statement for each line of the internal table. Duplicates are discarded from the result set. If the internal table itab does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records (in the current client).

i hope this will suffice.

even if there are no records in for all entries the select statements fetches values.

But this is not the case with exists.