topic select stmnt. in Application Development and Automation Discussions

select stmnt.

Former Member — Tue, 24 Jun 2008 10:10:04 GMT

the following select statmt is fetching duplicate records. can someone correct it so that it gets only unique records.

i tried using delete adjacent duplicates and others.

its not working.

if poss. someone help me on this

select component release extrelease from cvers into corresponding fields of table itab.

select p_{PATCH p}ADDON_ID into table jtab from pat03 as p inner join cvers as c on p_{ADDON_ID = c}component.

sort jtab by ADDON_ID.

DELETE ADJACENT DUPLICATES FROM jtab COMPARING patch.

Re: select stmnt.

Former Member — Tue, 24 Jun 2008 10:12:06 GMT

Doit like this

select Distinct

p~PATCH

p~ADDON_ID

into table jtab

from pat03 as p inner join cvers as c on p~ADDON_ID = c~component.

for ur better understanding of Select distinct

use this documentation .

. SELECT [SINGLE [FOR UPDATE] | DISTINCT] *

2. SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn

3. SELECT [SINGLE [FOR UPDATE] | DISTINCT] (itab)

Effect

The result of a SELECTstatement is itself a table. The SELECT clause, alongwith the database tables and/or viewsin the FROM clause, specifies the sequence,name, database type, and length of the columns of the result table.

You can also use the optional additions SINGLE or DISTINCT to indicate that only certain lines in the result set should bevisible to the program:

SINGLE

The result of the selection should be a single entry. If it is not possible to identify a unique entry, the system usesthe first line of the selection. If you use the FOR UPDATEaddition, the selected entry is protected against parallel updates fromother transactions until the next database commit (seeLUW and databaselock mechanism). If the database system identifies a deadlock, aruntime error occurs.

DISTINCT

Duplicate entries in the result set are automaticallydeleted.

Note

To ensure that an entry can be uniquely identified, youcan specify all of the fields in the primary keyusing AND expressions in the WHERE condition.

Note

Performance:

The additions SINGLE FOR UPDATE and DISTINCT bypass theSAP buffering.

The addition DISTINCT forces a sort on the database server.You should therefore only use it if you are really expecting duplicatesin the result set.

Variant 1

SELECT [SINGLE [FOR UPDATE] | DISTINCT] *

Effect

The columns of the result set will have exactly the samesequence, names, database type, and length as the fields of thedatabase table or view specifiedin the FROM clause.

Examples

Example to display all flights from Frankfurt to NewYork:

DATA WA_SPFLI TYPE SPFLI.

SELECT * FROM SPFLI INTO WA_SPFLI

WHERE

CITYFROM = 'FRANKFURT' AND

CITYTO = 'NEW YORK'.

WRITE: / WA_SPFLI-CARRID, WA_SPFLI-CONNID.

ENDSELECT.

Example to display the free seats on Lufthansa flight 0400 on02.28.1995:

DATA WA_SFLIGHT TYPE SFLIGHT.

DATA SEATSFREE TYPE I.

SELECT SINGLE *

FROM SFLIGHT INTO WA_SFLIGHT

WHERE

CARRID = 'LH ' AND

CONNID = '0400' AND

FLDATE = '19950228'.

SEATSFREE = WA_SFLIGHT-SEATSMAX - WA_SFLIGHT-SEATSOCC.

WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID,

WA_SFLIGHT-FLDATE, SEATSFREE.

Note

If you specify more than one table in the FROM clause and theINTO clause contains an internaltable or work area instead of a field list, the fields are placed intothe target area from left to right in the order in which they occur inthe tables in the FROM clause. Gaps may occur between the tablework areas for the sake of alignment. For thisreason, you should define the target work area by referring to thetypes of database tables instead of simply listing the fields. For anexample, refer to the documentation of the FROM clause.

Variant 2

SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn

Effect

The columns of the result table will have the sameformat as the column references s1... sn.

If si stands for a field f, MAX( f ), MIN( f ), or SUM( f ), the corresponding column in the result setwill have the same ABAP Dictionary format as f. ForCOUNT( f ) or COUNT( * ) the column has the type INT4. For AVG( f ) it has the type FLTP.

If you use aggregate functions with oneor more database fields in the SELECT clause, you must includeall of the database fields that are not used in the aggregate functionin the GROUP BY clause. The result ofthe selection in this case is a table.

If the SELECT clause only contains aggregate functions, theresult of the selection will be a single entry. In this case,SELECT does not have a corresponding ENDSELECT statement.

Notes

You can only use this variant for pool andcluster tables if the SELECT clause does not contain anyaggregate functions.

As a rule, aggregate functions used together with the FOR ALLENTRIES addition do not return the desired values. The result isonly correct if the fields in the WHERE condition that arelinked with AND and tested for equality with the aggregatedfields can identify the table line uniquely.

If you use a database field with type LCHAR or LRAW inthe SELECT clause, you must specify the corresponding lengthfield immediately before it in the SELECT clause.

Notes

Performance:

When you use aggregate functions, the system bypasses theSAP buffer.

Since not all database systems can store the number of lines in atable in their catalog, and therefore retrieving them istime-consuming, the COUNT( * ) function is not suitablefor testing whether a table contains any entries at all. Instead, youshould use SELECT SINGLE f ... for any table field f.

If you only want to select certain columns of a database table, use afield list in the SELECT clause or a view.

Examples

Example to display all destinations served byLufthansa from Frankfurt:

TABLES SPFLI.

DATA TARGET LIKE SPFLI-CITYTO.

SELECT DISTINCT CITYTO

INTO TARGET FROM SPFLI

WHERE

CARRID = 'LH ' AND

CITYFROM = 'FRANKFURT'.

WRITE: / TARGET.

ENDSELECT.

Example to display the number of airlines that fly to New York:

TABLES SPFLI.

DATA COUNT TYPE I.

SELECT COUNT( DISTINCT CARRID )

INTO COUNT

FROM SPFLI

WHERE

CITYTO = 'NEW YORK'.

WRITE: / COUNT.

Example to find the number of passengers, the total luggage weight,and the average weight of the luggage for all Lufthansa flights on02.28.1995:

TABLES SBOOK.

DATA: COUNT TYPE I, SUM TYPE P DECIMALS 2, AVG TYPE F.

DATA: CONNID LIKE SBOOK-CONNID.

SELECT CONNID COUNT( * ) SUM( LUGGWEIGHT ) AVG( LUGGWEIGHT )

INTO (CONNID, COUNT, SUM, AVG)

FROM SBOOK

WHERE

CARRID = 'LH ' AND

FLDATE = '19950228'

GROUP BY CONNID.

WRITE: / CONNID, COUNT, SUM, AVG.

ENDSELECT.

Variant 3

SELECT [SINGLE [FOR UPDATE] | DISTINCT] (itab)

Effect

Works like SELECT [SINGLE [FOR UPDATE] | DISTINCT]s1 ... sn, if the internal table itab contains the lists1 ... sn as ABAP source code, and works like SELECT[SINGLE [FOR UPDATE] | DISTINCT] *, if itab is empty. Theinternal table itab may only contain one field, which must havetype C and not be longer than 72 characters. You must specifyitab in parentheses. Do not include spaces between theparentheses and the table name.

Note

The same restrictions apply to this variant as toSELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn.

Example

Example to display all Lufthansa routes:

DATA WA_SPFLI TYPE SPFLI,

WA_FTAB(72) TYPE C,

FTAB LIKE TABLE OF WA_FTAB.

CLEAR FTAB.

FTAB = 'CITYFROM'. APPEND WA_FTAB TO FTAB.

FTAB = 'CITYTO'. APPEND WA_FTAB TO FTAB.

SELECT DISTINCT (FTAB)

INTO CORRESPONDING FIELDS OF WA_SPFLI

FROM SPFLI

WHERE

CARRID = 'LH'.

WRITE: / WA_SPFLI-CITYFROM, WA_SPFLI-CITYTO.

ENDSELECT.

Reward if usefull

Cheers

Snehi

Edited by: snehi chouhan on Jun 24, 2008 12:13 PM

Re: select stmnt.

Former Member — Tue, 24 Jun 2008 10:12:58 GMT

Hello,

plz sort jtab by patch

or use COMPARING ADDON_ID.

Plz Reward, if helpful.

Rgds,

Raghu.

Re: select stmnt.

Former Member — Tue, 24 Jun 2008 10:14:04 GMT

u need to sort on itab with field, using in delete duplicate.

Re: select stmnt.

Former Member — Tue, 24 Jun 2008 10:14:28 GMT

Hi,

As per your above code Delete stmt is going to delete duplicate records from tbale if and only if there is more than one similar record for patch.

Re: select stmnt.

Former Member — Tue, 24 Jun 2008 10:15:08 GMT

Hi Ravi,

Please go through the below link you willget the answer.

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3990358411d1829f0000e829fbfe/content.htm

&************Reward point if helpful****************&

Re: select stmnt.

Former Member — Tue, 24 Jun 2008 10:15:16 GMT

Hi,

select p_{PATCH p}ADDON_ID into table jtab from pat03 as p inner join cvers as c on p_{ADDON_ID = c}component.

sort jtab by ADDON_ID PATCH.

DELETE ADJACENT DUPLICATES FROM jtab COMPARING ADDON_ID PATCH.

Hope this will help.

Regards

Shibin

Re: select stmnt.

Former Member — Tue, 24 Jun 2008 10:17:05 GMT

Hi Ravi,

Firstly if required sort the table.

Distinct is used when , for example you have a table with multiple entries corresponding to one particular field,suppose external id..

so distinct used in a select query vil show the external id only once n not mulptiple times.

SELECT  DISTINCT <fields> ........

Thanks.

Revert back if any further queries

Regards

Narin Nandivada

Re: select stmnt.

Former Member — Tue, 24 Jun 2008 11:53:33 GMT

done