Secondary Index creation on CATSDB

[justin_santhanam]

Dear Friends,

I have a requirement where I need to fetch the records from CATSDB table where the dates are between ERSDA, LAEDA, APDAT. So I have written the below query:

SELECT * from catsdb into table it_catsdb WHERE

      ( ersda >= input-mt_field_ops_req-dates-low and ersda <= input-mt_field_ops_req-dates-high ) or

      ( laeda >= input-mt_field_ops_req-dates-low and laeda <= input-mt_field_ops_req-dates-high ) or

       ( apdat >= input-mt_field_ops_req-dates-low and apdat <= input-mt_field_ops_req-dates-high ).

The above code is working fine. But when I did the check on Code Inspector I got three errors as  Large table CATSDB: No field of a table index in WHERE Condition.

It's clear that none of the three fields are indexed in CATSDB. So with the suggestion from other folks, I have created one Secondary index with the three columns ersda,laeda,apdat. When I did the check with Code Inspector, now I'm getting only two errors. I didn't understand what happened. So I tried to separate three columns as different secondary index, now all the errors are gone. My question here - is this is the correct way of indexing - I mean one column per secondary index?

Thanks,

J.

[jack_graus2]

Hi, as you are using 'OR' in selecting on the 3 dates, the query will return database records for any of the 3 dates that match your selection date. If no entry is found on selecting on the first date then a new search need to be performed on the second data. That’s why 3 indexes are required.

I guess the 'OR' statement is correct here but if you use 'AND' then one index on all 3 dates will be sufficient. Then only records are selected that match for all 3 dates.

Regards Jack

[justin_santhanam]

Jack,

You are correct. I just tested it, if I give AND condition and had only index on ERSDA then it's working fine. Thanks for your response, I appreciate it.

Regards,

Justin.