from (select deltat_effective_date,nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')),deltat_factor from cws.deltat_history where deltat_acct = 9999999373 and deltat_effective_date <= to_date('31-DEC-2099','DD-MON-YYYY') order by deltat_effective_date desc ) where rownum =1;deltat_effective_date invalid identifier 但我表里明明就有这个字段阿 SQL> desc deltat_history; Name Null? Type ----------------------------------------- -------- ------------------------ DELTAT_ACCT NOT NULL NUMBER(16) DELTAT_EFFECTIVE_DATE NOT NULL DATE DELTAT_END_DATE DATE DELTAT_FACTOR NOT NULL NUMBER(6,3)
from (select deltat_effective_date,nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')),deltat_factor from cws.deltat_history where deltat_acct = recMaster.ACCT_ID and deltat_effective_date <= inProcDate and inProcDate <= nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')) order by deltat_effective_date desc ) where rownum =1;
exception when NO_DATA_FOUND THEN deltat_factor := NULL; END;先报 deltat_effective_date invalid identifier 再报 sql statement ignored
给出deltat_history 建表语句。
create table DELTAT_HISTORY ( DELTAT_ACCT NUMBER(16) not null, DELTAT_EFFECTIVE_DATE DATE not null, DELTAT_END_DATE DATE, DELTAT_FACTOR NUMBER(6,3) not null )
select min_cap_amount --from min_cap_history --where min_cap_acct = recMain.ACCT_ID --and min_cap_effective_date <= inProcDate --and inProcDate <= nvl(min_cap_end_date,to_date('31-DEC-2099','DD-MON-YYYY')); from (select min_cap_amount from min_cap_history where min_cap_acct = 225280001 and min_cap_effective_date <= to_date('31-DEC-2099','DD-MON-YYYY') order by min_cap_effective_date desc ) where rownum =1; 类似的语句 执行没错,说明语句不错 但字段那又啥错的/
from (select deltat_effective_date,nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')) deltat_end_date,deltat_factor from deltat_history,recMaster where deltat_acct = recMaster.Acct_Id and deltat_effective_date <= inProcDate and inProcDate <= nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')) order by deltat_effective_date desc ) where rownum =1; exception when NO_DATA_FOUND THEN deltat_factor := NULL; END; 你自己比对下吧。
其实问题就是 select deltat_effective_date, nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')), deltat_factor from (select deltat_effective_date,nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')),deltat_factor from cws.deltat_history where deltat_acct = 225280001 and deltat_effective_date <= to_date('31-DEC-2099','DD-MON-YYYY') order by deltat_effective_date desc ) where rownum =1;这个SQL语句为什么会报 deltat_effective_da INVALID IDENTIFIER
select deltat_effective_date, nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')), deltat_factor from cws.deltat_history where deltat_acct = 225280001 and deltat_effective_date <= to_date('31-DEC-2099','DD-MON-YYYY')这样就没问题
我终于知道为什么了 select deltat_effective_date, nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')), deltat_factor from (select deltat_effective_date,deltat_end_date,deltat_factor from cws.deltat_history where deltat_acct = 225280001 and deltat_effective_date <= to_date('31-DEC-2099','DD-MON-YYYY') order by deltat_effective_date desc ) where rownum =1;这样就没错。。
将会有以下提示。
PACKAGE EHT.PKG_PUBLIC 编译错误错误:PLS-00103: 出现符号 "/"符号 "/" 被忽略。
行:17
文本:/
编译不通过,PL/SQL里该包显示为红叉叉。
select deltat_effective_date,
nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')),
deltat_factor
from (select deltat_effective_date,nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')),deltat_factor
from cws.deltat_history
where deltat_acct = 9999999373
and deltat_effective_date <= to_date('31-DEC-2099','DD-MON-YYYY')
order by deltat_effective_date desc )
where rownum =1;deltat_effective_date invalid identifier 但我表里明明就有这个字段阿 SQL> desc deltat_history;
Name Null? Type
----------------------------------------- -------- ------------------------
DELTAT_ACCT NOT NULL NUMBER(16)
DELTAT_EFFECTIVE_DATE NOT NULL DATE
DELTAT_END_DATE DATE
DELTAT_FACTOR NOT NULL NUMBER(6,3)
不然我贴出来 BEGIN
select deltat_effective_date,
nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')),
deltat_factor
into effective_date,
end_date,
deltat_factor
--from cws.deltat_history
--where deltat_acct = recMaster.ACCT_ID
--and deltat_effective_date <= inProcDate
--and inProcDate <= nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY'));
from (select deltat_effective_date,nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')),deltat_factor
from cws.deltat_history
where deltat_acct = recMaster.ACCT_ID
and deltat_effective_date <= inProcDate
and inProcDate <= nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY'))
order by deltat_effective_date desc )
where rownum =1;
exception
when NO_DATA_FOUND THEN
deltat_factor := NULL;
END;先报 deltat_effective_date invalid identifier
再报 sql statement ignored
(
DELTAT_ACCT NUMBER(16) not null,
DELTAT_EFFECTIVE_DATE DATE not null,
DELTAT_END_DATE DATE,
DELTAT_FACTOR NUMBER(6,3) not null
)
--from min_cap_history
--where min_cap_acct = recMain.ACCT_ID
--and min_cap_effective_date <= inProcDate
--and inProcDate <= nvl(min_cap_end_date,to_date('31-DEC-2099','DD-MON-YYYY'));
from (select min_cap_amount from min_cap_history
where min_cap_acct = 225280001
and min_cap_effective_date <= to_date('31-DEC-2099','DD-MON-YYYY')
order by min_cap_effective_date desc )
where rownum =1;
类似的语句 执行没错,说明语句不错 但字段那又啥错的/
DECLARE
inProcDate deltat_history.deltat_end_date%TYPE;
effective_date DATE;
end_date DATE;
deltat_factor deltat_history.deltat_factor%TYPE;
BEGIN
select deltat_effective_date,
nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')),
deltat_factor
into effective_date,
end_date,
deltat_factor
--from cws.deltat_history
--where deltat_acct = recMaster.ACCT_ID
--and deltat_effective_date <= inProcDate
--and inProcDate <= nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY'));
from (select deltat_effective_date,nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')) deltat_end_date,deltat_factor
from deltat_history,recMaster
where deltat_acct = recMaster.Acct_Id
and deltat_effective_date <= inProcDate
and inProcDate <= nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY'))
order by deltat_effective_date desc )
where rownum =1; exception
when NO_DATA_FOUND THEN
deltat_factor := NULL;
END; 你自己比对下吧。
我没全部贴出来 recMaster.acc_id 其实是个常量啦
select deltat_effective_date,
nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')),
deltat_factor
from (select deltat_effective_date,nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')),deltat_factor
from cws.deltat_history
where deltat_acct = 225280001
and deltat_effective_date <= to_date('31-DEC-2099','DD-MON-YYYY')
order by deltat_effective_date desc
)
where rownum =1;这个SQL语句为什么会报 deltat_effective_da INVALID IDENTIFIER
nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')),
deltat_factor
from cws.deltat_history
where deltat_acct = 225280001
and deltat_effective_date <= to_date('31-DEC-2099','DD-MON-YYYY')这样就没问题
select deltat_effective_date,
nvl(deltat_end_date,to_date('31-DEC-2099','DD-MON-YYYY')),
deltat_factor
from (select deltat_effective_date,deltat_end_date,deltat_factor
from cws.deltat_history
where deltat_acct = 225280001
and deltat_effective_date <= to_date('31-DEC-2099','DD-MON-YYYY')
order by deltat_effective_date desc
)
where rownum =1;这样就没错。。