topic record missed in a SQL in Application Development and Automation Discussions

record missed in a SQL

Former Member — Fri, 18 Feb 2011 00:02:14 GMT

hi experts

I run a SQL like :

SELECT UC_POD_EXT

UC_DATEFRO

UC_DATETO

/BIC/ZKWH_MR

/BIC/ZKWH_BILL

/BIC/ZBI_MRCON

FROM /BIC/AZFC_D9900 INTO TABLE LT_BILL

FOR ALL ENTRIES IN SOURCE_PACKAGE

WHERE UC_POD_EXT = SOURCE_PACKAGE-UC_POD_EXT.

SOURCE_PACKAGE only has 1 record, and this reocrd's UC_POD_EXT is 10032789420750421. and it return 31 records.

and I test another SQL like

SELECT UC_POD_EXT

UC_DATEFRO

UC_DATETO

/BIC/ZKWH_MR

/BIC/ZKWH_BILL

/BIC/ZBI_MRCON

FROM /BIC/AZFC_D9900 INTO

corresponding fields of TABLE LT_BILL

WHERE UC_POD_EXT = '10032789420750421'.

it return 34 reocrds

so some records missed in the first SQL, i don't know why.

Re: record missed in a SQL

former_member186741 — Fri, 18 Feb 2011 00:25:22 GMT

i BELIEVe this is BECAUSE 'FOR ALL ENTRIES' ONLY EVER RETURNS ONE row per output key. i.e no duplicates will appear in the output table. So I think you will find that some of the 34 returned by the secoond sql will have duplicate key entries

Re: record missed in a SQL

Former Member — Fri, 18 Feb 2011 01:08:20 GMT

thanks for the prompt reply.

but my source package only has 1 record, so "for all entry" will return this record, that's exactly the same as the second SQL.

Re: record missed in a SQL

Former Member — Fri, 18 Feb 2011 04:36:41 GMT

Please retrive all the key fields of table /BIC/AZFC_D9900, as this is because of FOR ALL ENTRIES CLAUSE (as mentioned by Neil), which will delete duplicate records from the fields you are selecting. Since now you'll retrieve all the key fields of the table /BIC/AZFC_D9900, you are assured of getting unique records.

Regards

Ranganath