3 Replies Latest reply on Feb 3, 2003 6:20 AM by Simone

    Strange SQL for CMR

    Simone Newbie

      Hi,

      I have a relation 1 to many between table 2 tables.
      The generated SQL to fetch all the children is quite strange (and inefficient :( ) MDS_VECTOR is the many side of the relation and has PK of MV_KEY and foreign key of MV_MDS_FK.

      SELECT MV_KEY,MV_ID, MV_VALUE, MV_VALUE_TYPE, MV_MDS_FK, FROM MDS_VECTOR WHERE (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) .... for each PK

      Would not be better to do
      SELECT MV_KEY,MV_ID, MV_VALUE, MV_VALUE_TYPE, MV_MDS_ID FROM MDS_VECTOR WHERE MV_MDS_FK = ? obviously with the appropriate value.

      Thanks simone