No first field of table index in where condition

[SF_Commune]

Hi,

I am getting "No first field of table index in where condition" error in code inspector when i do select query to fetch data from DD02L.

I have included all the key fields and even tried with secondary index names / #EC xxxx... but still getting this error.

Please let me know on how to resolve this?

Thanks,

Nanda

[ThomasZloch]

We are suspicious minds here, please post your exact DD02L query.

Thomas

[SF_Commune]

We are passing the query like:

SELECT tabname AS4LOCAL AS4VERS tabclass

FROM dd02l

INTO CORRESPONDING FIELDS OF TABLE wt_modi_table

WHERE NOT ( tabname LIKE 'Z%' OR tabname LIKE 'Y%' )

AND ( AS4LOCAL = 'A' and as4vers ne '0000' ).

Please let me know if we need to change this..

Thanks,

Nanda

[ravi_lanjewar]

Hi,

SELECT tabname AS4LOCAL AS4VERS tabclass 
FROM dd02l
INTO CORRESPONDING FIELDS OF TABLE wt_modi_table
WHERE NOT ( tabname LIKE 'Z%' OR tabname LIKE 'Y%' )
AND ( AS4LOCAL = 'A' and as4vers ne '0000' ).

Is it possible to remove the NOT operator in SQL ? then only that warning will remove, because when we used the NOT operator in SQL then index is not consider for data read from database.

OR

Read all data removing NOT oprator from SQL and remove the y% and Z% using ranges from the internal table. In this case only index used.

if your sql not return any result, so remove and as4vers ne '0000' from your SQL.

[SF_Commune]

Yes Ravi...

The work around is to go with the second option...

Thanks for your time..

Thanks,

Nanda

[SF_Commune]

Ravi,

I have one quick question from this:

"Read all data removing NOT oprator from SQL and remove the y% and Z% using ranges from the internal table. In this case only index used."

By removing only NOT operator helps to avoid the error msg in code inspector. Do we need to remove Y% and Z% too? In case if we dint remove that, the index would be used to pull out the data?

Please let me know..

Thanks,

Nanda

[ravi_lanjewar]

Hi,

By removing only NOT operator helps to avoid the error msg in code inspector. 
Do we need to remove Y% and Z% too? In case if we dint remove that, the index would be used to pull out the data?

I mention above in case of NOT operator index is not used but if you miss the first fields of index then also index will not used.

By default it will consider the primary index to search data.

[former_member182566]

>

> By removing only NOT operator helps to avoid the error msg in code inspector. Do we need to remove Y% and Z% too? In case if we dint remove that, the index would be used to pull out the data?
> Nanda

Hi Nanda,

I know this is already "answered", but...

1) Looking at your query it seems you want all tables that are not Z or Y. So you can't just remove the NOT and expect the same result...

2) You have to look critically at code inspector's suggestions. If what you want is to know the table names, of course you can't provide the first field in the key (which is the table name). In your case you won't be able to use the index (unless you have more Z tables than standard ones, which I would bet is not the case). You will really have to make a FULL access to the table (or at most a FAST FULL INDEX SCAN, which will not be possible if you really need field TABCLASS).

3) Removing the WHERE clause and then filtering in the internal table is NOT RECOMMENDED, even if it removes the warning from code inspector. Please don't follow that recommendation. You should use code inspector warning to make your code better, not to make it worse.

Rui Dantas

[former_member182566]

>

> By default it will consider the primary index to search data.

Also not true.

[ravi_lanjewar]

Hi Rui,

It consider the FULL TABLE ACCESS access,

SQL Statement
------------------------------------------------------------------------------------------------------
SELECT
  "TABNAME" , "AS4LOCAL" , "AS4VERS" , "TABCLASS" , "SQLTAB" , "DATMIN" , "DATMAX" , "DATAVG" ,
  "CLIDEP" , "BUFFERED" , "COMPRFLAG" , "LANGDEP" , "ACTFLAG" , "APPLCLASS" , "AUTHCLASS" ,
  "AS4USER" , "AS4DATE" , "AS4TIME" , "MASTERLANG" , "MAINFLAG" , "CONTFLAG" , "RESERVETAB" ,
  "GLOBALFLAG" , "PROZPUFF" , "VIEWCLASS" , "VIEWGRANT" , "MULTIPLEX" , "SHLPEXI" , "PROXYTYPE" ,
  "EXCLASS" , "WRONGCL"
FROM
  "DD02L"
WHERE
  NOT ( "TABNAME" LIKE :A0 OR "TABNAME" LIKE :A1 ) AND "AS4LOCAL" = :A2 AND "AS4VERS" <> :A3

Execution Plan
------------------------------------------------------------------------------------------------------
System: DEV
-----------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |   126 |   976 |
|*  1 |  TABLE ACCESS FULL| DD02L |     1 |   126 |   976 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TABNAME" NOT LIKE :A0 AND "TABNAME" NOT LIKE :A1 AND
              "AS4LOCAL"=:A2 AND "AS4VERS"<>:A3)

This is the execution plan.

Please, Let us know exactly which index it used.

[Former Member]

HI,

you have specify these fields:

1/TABNAME Table Name <tabname>

2/AS4LOCAL Activation Status of a Repository Object 'A'

AS4VERS Version of the entry (not used) '0000'

You have to have these parameter in your where condition.

Bye Jan