oracle会隐式转换吗,Oracle隐式转换是否取决于联接的表或视图

我现在面临一个奇怪的问题.该查询本身很大,因此我不打算在这里发布(但是我可以发布,以防有??人需要查看).现在,我有了一个表TABLE1,该表具有CHAR(1)列COL1.该表列作为查询的一部分被查询.当我为该列过滤记录集时,我会说：

WHERE TAB1.COL1=1

这样,查询将运行并返回非常大的结果集.我最近更新了子查询之一以加快查询速度.但是在此之后,当我编写WHERE TAB1.COL1 = 1时,它不会返回任何内容,但是如果将其更改为WHERE TAB1.COL1 =’1′,它会为我提供所需的记录.注意带引号的WHERE子句,不带引号.因此,更清楚地说,在更新子查询之一之前,我不必引号来检查COL1值,但是在更新之后,我必须这样做.我不知道Oracle有什么功能？

编辑：我发布查询的tw版本,以防有人发现它有用

版本1：

SELECT p.ssn,

pss.pin,

pd.doc_number,

p.surname,

p.name,

p.patronymic,

to_number(p.sex, '9') as sex,

citiz_c.short_name citizenship,

p.birth_place,

p.birth_day as birth_date,

coun_c.short_name as country,

di.name as leg_city,

trim( pa.settlement

|| ' '

|| pa.street) AS leg_street,

pd.issue_date,

pd.issuing_body,

irs.irn,

irs.tpn,

irs.reg_office,

to_number(irs.insurer_type, '9') as insurer_type,

TO_CHAR(sa.REG_CODE)

||CONVERT_INT_TO_DOUBLE_LETTER(TO_NUMBER(SUBSTR(TO_CHAR(sa.DOSSIER_NR, '0999999'), 2, 3)))

||SUBSTR(TO_CHAR(sa.DOSSIER_NR, '0999999'), 5, 4) CONVERTED_SSN_DOSSIER_NR,

fa.snr

FROM

(SELECT pss_t.pin,

pss_t.ssn

FROM EHDIS_INSURANCE.pin_ssn_status pss_t

WHERE pss_t.difference_status < 5

) pss

INNER JOIN SSPF_CENTRE.file_archive fa

ON fa.ssn = pss.ssn

INNER JOIN SSPF_CENTRE.persons p

ON p.ssn = fa.ssn

INNER JOIN

(SELECT pd_2.ssn,

pd_2.type,

pd_2.series,

pd_2.doc_number,

pd_2.issue_date,

pd_2.issuing_body

FROM

--The changed subquery starts here

(SELECT ssn,

MIN(type) AS type

FROM SSPF_CENTRE.person_documents

GROUP BY ssn

) pd_1

INNER JOIN SSPF_CENTRE.person_documents pd_2

ON pd_2.type = pd_1.type

AND pd_2.ssn = pd_1.ssn

) pd

--The changed subquery ends here

ON pd.ssn = p.ssn

INNER JOIN SSPF_CENTRE.ssn_archive sa

ON p.ssn = sa.ssn

INNER JOIN SSPF_CENTRE.person_addresses pa

ON p.ssn = pa.ssn

INNER JOIN

(SELECT i_t.irn,

irs_t.ssn,

i_t.tpn,

i_t.reg_office,

(

CASE i_t.insurer_type

WHEN '4'

THEN '1'

ELSE i_t.insurer_type

END) AS insurer_type

FROM sspf_centre.irn_registered_ssn irs_t

INNER JOIN SSPF_CENTRE.insurers i_t

ON i_t.irn = irs_t.new_irn

OR i_t.old_irn = irs_t.old_irn

WHERE irs_t.is_registration IS NOT NULL

AND i_t.is_real IS NOT NULL

) irs ON irs.ssn = p.ssn

LEFT OUTER JOIN SSPF_CENTRE.districts di

ON di.code = pa.city

LEFT OUTER JOIN SSPF_CENTRE.countries citiz_c

ON p.citizenship = citiz_c.numeric_code

LEFT OUTER JOIN SSPF_CENTRE.countries coun_c

ON pa.country_code = coun_c.numeric_code

WHERE pa.address_flag = '1'--Here's the column value with quotes

AND fa.form_type = 'Q3';

和版本2：

SELECT p.ssn,

pss.pin,

pd.doc_number,

p.surname,

p.name,

p.patronymic,

to_number(p.sex, '9') as sex,

citiz_c.short_name citizenship,

p.birth_place,

p.birth_day as birth_date,

coun_c.short_name as country,

di.name as leg_city,

trim( pa.settlement

|| ' '

|| pa.street) AS leg_street,

pd.issue_date,

pd.issuing_body,

irs.irn,

irs.tpn,

irs.reg_office,

to_number(irs.insurer_type, '9') as insurer_type,

TO_CHAR(sa.REG_CODE)

||CONVERT_INT_TO_DOUBLE_LETTER(TO_NUMBER(SUBSTR(TO_CHAR(sa.DOSSIER_NR, '0999999'), 2, 3)))

||SUBSTR(TO_CHAR(sa.DOSSIER_NR, '0999999'), 5, 4) CONVERTED_SSN_DOSSIER_NR,

fa.snr

FROM

(SELECT pss_t.pin,

pss_t.ssn

FROM EHDIS_INSURANCE.pin_ssn_status pss_t

WHERE pss_t.difference_status < 5

) pss

INNER JOIN SSPF_CENTRE.file_archive fa

ON fa.ssn = pss.ssn

INNER JOIN SSPF_CENTRE.persons p

ON p.ssn = fa.ssn

INNER JOIN

--The changed subquery starts here

(SELECT ssn,

type,

series,

doc_number,

issue_date,

issuing_body

FROM

(SELECT ssn,

type,

series,

doc_number,

issue_date,

issuing_body,

ROW_NUMBER() OVER (partition BY ssn order by type) rn

FROM SSPF_CENTRE.person_documents

)

WHERE rn = 1

) pd --

--The changed subquery ends here

ON pd.ssn = p.ssn

INNER JOIN SSPF_CENTRE.ssn_archive sa

ON p.ssn = sa.ssn

INNER JOIN SSPF_CENTRE.person_addresses pa

ON p.ssn = pa.ssn

INNER JOIN

(SELECT i_t.irn,

irs_t.ssn,

i_t.tpn,

i_t.reg_office,

(

CASE i_t.insurer_type

WHEN '4'

THEN '1'

ELSE i_t.insurer_type

END) AS insurer_type

FROM sspf_centre.irn_registered_ssn irs_t

INNER JOIN SSPF_CENTRE.insurers i_t

ON i_t.irn = irs_t.new_irn

OR i_t.old_irn = irs_t.old_irn

WHERE irs_t.is_registration IS NOT NULL

AND i_t.is_real IS NOT NULL

) irs ON irs.ssn = p.ssn

LEFT OUTER JOIN SSPF_CENTRE.districts di

ON di.code = pa.city

LEFT OUTER JOIN SSPF_CENTRE.countries citiz_c

ON p.citizenship = citiz_c.numeric_code

LEFT OUTER JOIN SSPF_CENTRE.countries coun_c

ON pa.country_code = coun_c.numeric_code

WHERE pa.address_flag = 1--Here's the column value without quotes

AND fa.form_type = 'Q3';

我在两个查询中都为更改的子查询和WHERE子句添加了单独的注释.这两个版本的子查询返回相同的结果,其中一个只是速度较慢,这就是为什么我决定更新它.