behaviour of sql query

[ksarav]

Hi ..
Look at the queries below

SQL> select distinct geo_reg from qmi.V_QMI_OSMO_LIGHT_NEW
2 where user_type ='Q'
3 and geo_reg in ('PAC','EUR','LAM','NAM')
4 /

no rows selected

SQL> select distinct geo_reg from qmi.V_QMI_OSMO_LIGHT_NEW
2 where trim(user_type) ='Q'
3 and geo_reg in ('PAC','EUR','LAM','NAM')
4 /

GEO_REG
-----------------------------------
EUR
LAM
NAM
PAC

"qmi.V_QMI_OSMO_LIGHT_NEW" is a view

The first one returns nothing but the second one with trim returns values ..
can anybody explain me how this works ..
i've verified the execution plan for the queries and its look similar .


Thanx
Sarav

[abhaysk]

D'nt the predicate trim(user_type) ='Q' explain you why is behaving so..

[zainalj]

what is user_type datatype? is it char?

[ksarav]

c'mon guys ..
i dont know y it is behaving like this ..
plz explain

thanx
sarav

[abhaysk]

Read about TRIM in the SQL Reference.

[ksarav]

hi zainalj,

The datatype for user_type is CHAR(1).

[DaPi]

"qmi.V_QMI_OSMO_LIGHT_NEW" is a view

How is the column defined in the view? Could you post the script?

[ksarav]

hi dapi,
i've attached the script of the view

[abhaysk]

wass the data type of column user_type of the table re_qmi_master_new.. are u sure its CHAR(1)?

[ksarav]

SQL> desc qmi.re_qmi_master_new
Name Null? Type
----------------------- -------- ----------------
WORKBOOK_ID NOT NULL NUMBER(5)
CREATE_DATE NOT NULL DATE
POLE_ID NOT NULL VARCHAR2(30)
POLE_NAME NOT NULL VARCHAR2(30)
GEO_REG NOT NULL VARCHAR2(35)
REGION_ID NOT NULL VARCHAR2(60)
REGION_NAME NOT NULL VARCHAR2(60)
DW_DISTRICT_ID NOT NULL VARCHAR2(30)
DISTRICT_ID NOT NULL VARCHAR2(30)
DISTRICT_NAME NOT NULL VARCHAR2(60)
USER_TYPE NOT NULL CHAR(1)