下面的代码有点长,但逻辑简单。下面的代码为什么会出ORA-00920: invalid relational operator的错?
环境:PHP4.3.4/Oracle9.0.2/Apache2.0.49
下面的代码在SQLPlus中运行是正确的,单独写一个PHP来执行也是正确的,但在我的正式代码中运行却出错,请各位帮忙解决一下,这个问题困扰我好多天了!!运行的方式:$rtn = OCIExecute($this->stmt);
就在这一句上出错,输出的SQL为以下:SELECT count(1)
from (
select distinct application.application_id,
(case when T01.update_date is null then case when T08.update_date is null then 0 else 1 end else 1 end) T01Yes_No,
(case when T01.update_date is null then T08.update_date else T01.update_date end) T08Update
from person,
application,
application_test,
test_type,
app_fee,
app_fee housing_deposit,
app_fee pre_master_courses_fee,
app_fee T02,
app_fee T03,
app_fee T01,
app_fee T04,
app_fee T05,
app_fee T06,
app_fee T07,
app_fee T08,
app_education,
app_pre_master_courses,
pre_master_courses,
scholarship,
scholarship_name,
scholarship_status,
student_file,
academic_year,
programme,
programme_type,
gender,
marital_status,
country country,
country nationality,
employee colleague,
address,
country cor_country,
organization,
organization institution ,
diploma
where person.person_id = application.person_id(+)
AND person.person_id = student_file.person_id(+)
AND application.academic_year_id = academic_year.academic_year_id(+)
AND application.programme_id = programme.programme_id(+)
AND application.application_id = app_education.application_id(+)
AND application.application_id = app_fee.application_id(+)
AND application.application_id = housing_deposit.application_id(+)
AND application.application_id = pre_master_courses_fee.application_id(+)
AND application.application_id = T02.application_id(+)
AND application.application_id = T03.application_id(+)
AND application.application_id = T01.application_id(+)
AND application.application_id = T04.application_id(+)
AND application.application_id = T05.application_id(+)
AND application.application_id = T06.application_id(+)
AND application.application_id = T07.application_id(+)
AND application.application_id = T08.application_id(+)
AND application.application_id = scholarship.application_id(+)
AND scholarship.scholarship_name_id = scholarship_name.scholarship_name_id(+)
AND scholarship.scholarship_status_id = scholarship_status.scholarship_status_id(+)
AND application.application_id = application_test.application_id(+)
AND application_test.test_type_id = test_type.test_type_id(+)
AND (application_test.obtained is null OR application_test.obtained != '1')
AND application.application_id = app_pre_master_courses.application_id(+)
AND app_pre_master_courses.pre_master_id = pre_master_courses.pre_master_id(+)
AND programme.programme_type_id = programme_type.programme_type_id(+)
AND programme.faculty_id = organization.org_id(+)
AND app_education.institution_id = institution.org_id(+)
AND app_education.diploma_id = diploma.diploma_id(+)
AND person.gender_id = gender.gender_id(+)
AND person.marital_status_id = marital_status.marital_status_id(+)
AND person.country_of_birth = country.country_id(+)
AND person.nationality = nationality.country_id(+)
AND application.app_colleague_id = colleague.employee_id(+)
AND person.person_type_id = 1
AND person.person_id = address.entity_id(+)
AND address.entity_type_id(+) = 1
AND address.address_type_id(+) = 1
AND organization.org_type_id(+) = 2
AND app_fee.app_fee_type_id(+) = 2
AND housing_deposit.app_fee_type_id(+) = 3
AND pre_master_courses_fee.app_fee_type_id(+) = 11
AND T02.app_fee_type_id(+) = 12
AND T03.app_fee_type_id(+) = 8
AND T01.app_fee_type_id(+) = 9
AND T08.app_fee_type_id(+) = 13
AND T04.app_fee_type_id(+) = 5
AND T05.app_fee_type_id(+) = 7
AND T06.app_fee_type_id(+) = 1
AND T07.app_fee_type_id(+) = 6
AND address.country_id = cor_country.country_id(+)
AND application.application_id =5396
AND (case when T01.update_date is null then case when T08.update_date is null then 0 else 1 end else 1 end) = 1
)我怀疑好像是最后一个语句有问题:
AND (case when T01.update_date is null then case when T08.update_date is null then 0 else 1 end else 1 end) = 1 但是在SQLPlus如果按照上面的方式输入,可以正常执行,自已单独写的PHP程序中也能正常执行,
只是在我的正式代码中不能通过,会出现以下错误:
ORA-00920: invalid relational operator
并且,如果在SQLPlus中上面的代码不换行,用一行的方式输入,执行的时候也会出现ORA-00920: invalid relational operator的错误。不知道什么原因。
环境:PHP4.3.4/Oracle9.0.2/Apache2.0.49
下面的代码在SQLPlus中运行是正确的,单独写一个PHP来执行也是正确的,但在我的正式代码中运行却出错,请各位帮忙解决一下,这个问题困扰我好多天了!!运行的方式:$rtn = OCIExecute($this->stmt);
就在这一句上出错,输出的SQL为以下:SELECT count(1)
from (
select distinct application.application_id,
(case when T01.update_date is null then case when T08.update_date is null then 0 else 1 end else 1 end) T01Yes_No,
(case when T01.update_date is null then T08.update_date else T01.update_date end) T08Update
from person,
application,
application_test,
test_type,
app_fee,
app_fee housing_deposit,
app_fee pre_master_courses_fee,
app_fee T02,
app_fee T03,
app_fee T01,
app_fee T04,
app_fee T05,
app_fee T06,
app_fee T07,
app_fee T08,
app_education,
app_pre_master_courses,
pre_master_courses,
scholarship,
scholarship_name,
scholarship_status,
student_file,
academic_year,
programme,
programme_type,
gender,
marital_status,
country country,
country nationality,
employee colleague,
address,
country cor_country,
organization,
organization institution ,
diploma
where person.person_id = application.person_id(+)
AND person.person_id = student_file.person_id(+)
AND application.academic_year_id = academic_year.academic_year_id(+)
AND application.programme_id = programme.programme_id(+)
AND application.application_id = app_education.application_id(+)
AND application.application_id = app_fee.application_id(+)
AND application.application_id = housing_deposit.application_id(+)
AND application.application_id = pre_master_courses_fee.application_id(+)
AND application.application_id = T02.application_id(+)
AND application.application_id = T03.application_id(+)
AND application.application_id = T01.application_id(+)
AND application.application_id = T04.application_id(+)
AND application.application_id = T05.application_id(+)
AND application.application_id = T06.application_id(+)
AND application.application_id = T07.application_id(+)
AND application.application_id = T08.application_id(+)
AND application.application_id = scholarship.application_id(+)
AND scholarship.scholarship_name_id = scholarship_name.scholarship_name_id(+)
AND scholarship.scholarship_status_id = scholarship_status.scholarship_status_id(+)
AND application.application_id = application_test.application_id(+)
AND application_test.test_type_id = test_type.test_type_id(+)
AND (application_test.obtained is null OR application_test.obtained != '1')
AND application.application_id = app_pre_master_courses.application_id(+)
AND app_pre_master_courses.pre_master_id = pre_master_courses.pre_master_id(+)
AND programme.programme_type_id = programme_type.programme_type_id(+)
AND programme.faculty_id = organization.org_id(+)
AND app_education.institution_id = institution.org_id(+)
AND app_education.diploma_id = diploma.diploma_id(+)
AND person.gender_id = gender.gender_id(+)
AND person.marital_status_id = marital_status.marital_status_id(+)
AND person.country_of_birth = country.country_id(+)
AND person.nationality = nationality.country_id(+)
AND application.app_colleague_id = colleague.employee_id(+)
AND person.person_type_id = 1
AND person.person_id = address.entity_id(+)
AND address.entity_type_id(+) = 1
AND address.address_type_id(+) = 1
AND organization.org_type_id(+) = 2
AND app_fee.app_fee_type_id(+) = 2
AND housing_deposit.app_fee_type_id(+) = 3
AND pre_master_courses_fee.app_fee_type_id(+) = 11
AND T02.app_fee_type_id(+) = 12
AND T03.app_fee_type_id(+) = 8
AND T01.app_fee_type_id(+) = 9
AND T08.app_fee_type_id(+) = 13
AND T04.app_fee_type_id(+) = 5
AND T05.app_fee_type_id(+) = 7
AND T06.app_fee_type_id(+) = 1
AND T07.app_fee_type_id(+) = 6
AND address.country_id = cor_country.country_id(+)
AND application.application_id =5396
AND (case when T01.update_date is null then case when T08.update_date is null then 0 else 1 end else 1 end) = 1
)我怀疑好像是最后一个语句有问题:
AND (case when T01.update_date is null then case when T08.update_date is null then 0 else 1 end else 1 end) = 1 但是在SQLPlus如果按照上面的方式输入,可以正常执行,自已单独写的PHP程序中也能正常执行,
只是在我的正式代码中不能通过,会出现以下错误:
ORA-00920: invalid relational operator
并且,如果在SQLPlus中上面的代码不换行,用一行的方式输入,执行的时候也会出现ORA-00920: invalid relational operator的错误。不知道什么原因。
DECODE(T01.UPDATE_DATE,NULL,DECODE(T08.UPDATE_DATE,NULL,0,1),1)试试
原因:
oracle处理一行的最大长度和varchar2列的最大长度一样
所以如果超过一定的长度就会出错
DECODE(T01.UPDATE_DATE,NULL,DECODE(T08.UPDATE_DATE,NULL,0,1),1)还是不好用:(
原因:
oracle处理一行的最大长度和varchar2列的最大长度一样
所以如果超过一定的长度就会出错============================不可能是长度超了,如果这段代码没有的话,后面再追加一部分条件,也能正确执行的,
唯独这段代码有问题。
case when T01.update_date is null then
(
case when T08.update_date is null then 0
else 1
end
)
else 1
end
) = 1