Existence Checks
• The common technique used to check whether a certain condition is met or not, obviously when no data needs to be returned, is simply COUNT how many records match that condition. A zero number indicates that no record has met that condition.
• Notice that this is not very efficient as we are asking the database to scan the records for an accurate number that we don't really care about. All we really want to know if there is at least one such record and NOT how many they are.
• When the tables involved are of low volume there should be no problem using this technique. It is very simple and uses common SQL syntax to all databases.
• However, when that condition is checked against a high volume table that many of its records meet that condition, scanning all the matching records to get a count we don't need should be avoided.
• In this case use the EXISTS function as follows:
• Select 'x'
• From <The main table of the searched field, where it is defined as the PK of that table>
• Where <search field> = <search value> and
• EXISTS
• (<sub-select with the desired condition. This is the high volume table>);
• For example :
• Select 'x'
• From CI_UOM
• Where UOM_CD = input UOM_CD and
• EXISTS (select 'x'
• From CI_BSEG_CALC_LN
• Where UOM_CD = input UOM_CD);
• If this does not work for your special case, use the following option :
• Select 'x'
• From CI_INSTALLATION
• Where EXISTS
• (<sub-select with the desired condition>) ;
• Remember : This type of existence check using the Installation Options record should only be used in rare cases and should be consulted with the DBA first before implementation.
• Note that we use CI_INSTALLATION as this table has only one row.
Parent topic