Skip to Content

Records Dropped Reading KONV

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.

Add comment
10|10000 characters needed characters exceeded

  • 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?

  • Get RSS Feed

4 Answers

  • Best Answer
    Mar 16, 2017 at 08:40 PM

    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!

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 06, 2017 at 10:11 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 06, 2017 at 10:35 AM

    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 > ?

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 06, 2017 at 07:58 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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.