Records Dropped Reading KONV

[former_member289862]

I have a query on KONV where KNUMV is used with a range and KSCHL is equal to several values using an 'IN' clause. The number of records returned are about 9.6 million. However, if I switch the order to have KSCHL first and KNUMV second I don't get the same record count returned. In either case I am not getting all the records that should have been returned. Both methods are missing records in either case but they are consistently the same records missing.

I know this is a cluster table and that KSCHL is not part of the key for the table. However, I tried the same type of query on an IDES system with only 500,000 records and I get the same record count on both ways of formatting the query.

When I look at the SELECT statement in ST05 I see that the 'IN' clause part of the SELECT is not there. Is this because it is not evaluated at the database level but is then processed in the application server?

I believe the issue is related to the large number of records being processed but I can not explain exactly why to management. My suggestion is to try to process smaller numbers of records at a time.

Anyone have a good explanation as to why?

SELECT mandt, knumv, kposn, stunr

where (KONV-KSCHL in ('ZPRO', 'ZNTP', ZPR0') and

KONV-KNUMV > '1000000000' and KONV-KNUMV < '2000000000'

This is not the exact syntax. The SELECT used is syntactically correct. There are 60 different condition types that are being used in the 'IN' clause.

[matt]

What do you get with ST05 on your IDES system for the same select? What about other instances in your system chain (D->C/T->P)

What database software are you using?

Is it possible that the data is being changed between your test calls?

[tan_michael]

Hi,

Can you try this code?

select mandt knumv kposn stunr
into table it_internaltable
from KONV
where KSCHL IN ('ZPRO', 'ZNTP', 'ZPRO')
and KNUMV GT 1000000000
and KNUMV LT 2000000000.

and it much better to post your actual syntax for us to solve it.

you have missing ' and ) in your syntax above.

[former_member289862]

Hi Michael,

Thanks for the response. Attached is the select. I am using OPEN SQL and I can not select it into a table because it's actually being passed to a function that does the select and processes the data and then passes back the result. I can only pass a valid OPEN SQL command.

SELECT KONV~MANDT,KONV~KNUMV,KONV~KPOSN,KONV~STUNR,KONV~ZAEHK,KONV~KAPPL,KONV~KSCHL,KONV~KDATU,KONV~KRECH,KONV~KAWRT,KONV~KBETR,KONV~WAERS,KONV~KKURS,KONV~KPEIN,KONV~KMEIN,KONV~KUMZA,KONV~KUMNE,KONV~KNTYP,KONV~KSTAT,KONV~KNPRS,KONV~KRUEK,KONV~KRELI,KONV~KHERK,KONV~KGRPE,KONV~KOUPD,KONV~KOLNR,KONV~KNUMH,KONV~KOPOS,KONV~KVSL1,KONV~SAKN1,KONV~MWSK1,KONV~KVSL2,KONV~SAKN2,KONV~MWSK2,KONV~LIFNR,KONV~KUNNR,KONV~KDIFF,KONV~KWERT,KONV~KSTEU,KONV~KINAK,KONV~KOAID,KONV~ZAEKO,KONV~KMXAW,KONV~KMXWR,KONV~KFAKTOR,KONV~KDUPL,KONV~KFAKTOR1,KONV~KZBZG,KONV~KSTBS,KONV~KONMS,KONV~KONWS,KONV~KAWRT_K,KONV~KWAEH,KONV~KWERT_K,KONV~KFKIV,KONV~KVARC,KONV~KMPRS,KONV~PRSQU,KONV~VARCOND,KONV~STUFE,KONV~WEGXX,KONV~KTREL,KONV~MDFLG,KONV~TXJLV,KONV~KBFLAG,KONV~KOLNR3 FROM KONV WHERE (KONV~KSCHL IN ('ZPR0','ZVA0','ZNTP','ZPB0','ZD01','ZD02','ZD03','ZJD6','ZJD7','ZTVD','ZCA2','ZCAD','ZCFD','ZSD0','ZSP1','ZSPA','ZEXR','ZF01','ZF02','ZF03','ZF04','ZF05','ZFEE','ZFF4','ZINS','ZJIN','ZMIZ','ZMSP','ZMSV','ZPKP','ZPKV','Z100','ZDRP','ZHP1','ZHV1','ZJCO','ZJFC','ZLOG','ZMED','ZMP1','ZMV1','ZSO1','ZWAD','DIFF','ZCAS','ZBO1','ZBO2','ZCFP','ZCN1','ZCNP','ZPN0','ZNT1','ZPN1','ZPN2','ZPSB','SKTV','ZPSV','ZPSP','ZEXW','ZB01','ZB02')) AND KONV~KNUMV > '0013297407' AND KONV~KNUMV < '0013307940'

If I switch the order of the where clause around we get different results but in any case they both are missing records are described above in the original post.

[tan_michael]

Hi James,

Have you solved this problem?

[horst_keller]

What's the data type of KONV-KNUMV? Are you sure that there are only values on the DB that can securely be compared with < or > ?

[former_member289862]

Hi Horst thank you for your time,

KONV-KNUMV is a CHAR 10, same as in KOCLU. On the second part of your question I think you mean since KNUMV is char 10 that the range may

not work correctly if they use non numeric information when creating the record? That's a good question I will check on the records that are missing but I

am not sure that is the case based on this query:

SELECT KONV~MANDT,KONV~KNUMV,KONV~KPOSN,KONV~STUNR,KONV~ZAEHK,KONV~KAPPL,KONV~KSCHL,KONV~KDATU,KONV~KRECH,

KONV~KAWRT,KONV~KBETR,KONV~WAERS,KONV~KKURS,KONV~KPEIN,KONV~KMEIN,KONV~KUMZA,KONV~KUMNE,KONV~KNTYP,

KONV~KSTAT,KONV~KNPRS,KONV~KRUEK,KONV~KRELI,KONV~KHERK,KONV~KGRPE,KONV~KOUPD,KONV~KOLNR,KONV~KNUMH,

KONV~KOPOS,KONV~KVSL1,KONV~SAKN1,KONV~MWSK1,KONV~KVSL2,KONV~SAKN2,KONV~MWSK2,KONV~LIFNR,KONV~KUNNR,

KONV~KDIFF,KONV~KWERT,KONV~KSTEU,KONV~KINAK,KONV~KOAID,KONV~ZAEKO,KONV~KMXAW,KONV~KMXWR,KONV~KFAKTOR,

KONV~KDUPL,KONV~KFAKTOR1,KONV~KZBZG,KONV~KSTBS,KONV~KONMS,KONV~KONWS,KONV~KAWRT_K,KONV~KWAEH,

KONV~KWERT_K,KONV~KFKIV,KONV~KVARC,KONV~KMPRS,KONV~PRSQU,KONV~VARCOND,KONV~STUFE,KONV~WEGXX,KONV~KTREL,

KONV~MDFLG,KONV~TXJLV,KONV~KBFLAG,KONV~KOLNR3 FROM KONV WHERE KONV~KNUMV > '0013297407' AND KONV~KNUMV < '0013307940' AND (KONV~KSCHL IN ('ZF02','ZMV1'))

We determined that the condition 'ZMV1' was missing when we run with the 'IN' clause first and the condition 'ZF02' is missing when we run with KNUMV first. So we created this query and it picked up the missing conditions. Would like to get them all in one pass.

[horst_keller]

Well, it shouldn't make a difference whether you use

WHERE

KONV~KNUMV > '0013297407' AND KONV~KNUMV < '0013307940' AND (KONV~KSCHL IN ('ZF02','ZMV1'))

or

WHERE

(KONV~KSCHL IN ('ZF02','ZMV1')) AND KONV~KNUMV > '0013297407' AND KONV~KNUMV < '0013307940'

I guess that with a transparent table everything would be fine and that there might be an issue with cluster caching.

Therefore, the best might be if you open a ticket to SAP where you describe how to reproduce the behavior.

[former_member289862]

After going through debug using ST05 and ST12 I was able to determine that the application running the SQL passed to it was improperly parsing the WHERE clause and therefore changing the Key values. Thanks to all who responded for their consideration and time it was very much appreciated!