You Asked
Hi Tom,
i havae a query that i given below
SELECT 2 tab_type , ID,LVNAME,LV_NAME,APPL_FROM,GRANT_FROM,GRANT_TO,
APPLICABLE,LWP,LVSTR,TYPE,EFFECT_ON,AGNST_FROM,
HALFPAID,UNPAID,QTRPAID,0,IS_PRORATA,SYSDATE
FROM UHO.LVOPEN
WHERE ID = '015522'
AND LV_NAME ='SICK'
AND (EFFECT_ON IS NULL OR EFFECT_ON >='01-JAN-17')
AND (GRANT_TO IS NULL OR GRANT_TO >='01-JAN-17')
UNION ALL
SELECT 1 tab_type ,ID,LVNAME,LV_NAME,APPL_FROM,GRANT_FROM,GRANT_TO,
APPLICABLE,LWP,LVSTR,TYPE,EFFECT_ON, AGNST_FROM,
HALFPAID,UNPAID,QTRPAID,RECID,IS_PRORATA,SYSDATE
FROM UHO.LVGRANT
WHERE ID = '015522'
AND LV_NAME = 'SICK'
AND (EFFECT_ON IS NULL OR EFFECT_ON >='01-JAN-17')
AND (GRANT_TO IS NULL OR GRANT_TO >='01-JAN-17' OR EFFECT_ON >='01-JAN-17')
in above query one comparison column is ther that is LV_NAME = 'SICK'
instead of that i am using like this upper(trim(LV_NAME)) = upper(trim('SICK'))
i am using like this query working fine but in this upper(trim(LV_NAME)) = upper(trim('SICK')) why should we
use both side same if i use one side means right hand side whant will happen if i use left side what will happen that lv_name having index if we use like this normal will work or not please explain
and Connor said...
I assume that "SICK" is coming in as a passed value from an application. (Hopefully its a bind variable, but that's a separate discussion).
Anyway, when you say:
upper(trim(LV_NAME)) = upper(trim('SICK'))
that statement is equivalent to saying:
1) the passed in value ('SICK') might be passed in as any case, and might have leading or trailing spaces. So its fine to "cleanse" that value, because you might have control over the application and the way it passes in data.
2) It also is saying that LV_NAME might contain data in mixed case and/or have trailing or leading spaces. That's perhaps worth more analysis before just putting upper/trim around every query.
- *Maybe* the data in the table should be fixed up.
- *Maybe* a trigger should be on the table to cleanse data as its inserted
- *Maybe* a constraint should be added to only allow clean data
These are all decisions for you to make, which would then *might* allow you to have all of these queries specified as:
where LV_NAME = upper(trim('[incoming value]'))
and thus take advantage of potential indexes on LV_NAME.
If you *cannot* do these things, ie, the data is LV_NAME will always be questionable, then you can create a function-based index on upper(trim(LV_NAME)). But for me - I always prefer to see if the data can be made clean first, rather than have to code all of my SQL to work around that.
Is this answer out of date? If it is, please let us know via a Comment