User Defined sql in ORAOCI
Moderators: chulett, rschirm, roy
User Defined sql in ORAOCI
Hi,
i am using a sql which is just working fine in SQL edtor. when i am trying to use same sql through ORAOCI stage it doesn't work. i am pasting error and sql which i used.Would any one let me know what could be done
ERROR
ADSFactSftStoreRgstrHash..oraSftStoreRgstr: ORA-30484: missing window specification for this function
ADSFactSftStoreRgstrHash..oraSftStoreRgstr.DSLink1: DSP.Open GCI $DSP.Open error -100.
SQL USED IN ORACLE
select s.store_num,
s.rgstr_num,
dc.cal_dt,
s.rgstr_type_cd
from
(
select STORE_NUM,
RGSTR_NUM,
BUS_DT as BegDt,
coalesce (LEAD(bus_dt - 1 )
OVER(order by store_num,rgstr_num, bus_dt), trunc(sysdate+7)) as EndDt,
RGSTR_TYPE_CD
from sft_store_rgstr
-- where store_num = 6414
-- and rgstr_num = 1
) S,
appads.a_dim_cal DC
where DC.cal_dt between s.begdt and s.enddt
order by dc.cal_dt
SQL USED IN ORAOCI STAGE
select s.store_num,
s.rgstr_num,
TO_CHAR(dc.cal_dt,'YYYY-MM-DD HH24:MI:SS'),
s.rgstr_type_cd
from
(
select STORE_NUM,
RGSTR_NUM,
TO_CHAR(BUS_DT,'YYYY-MM-DD HH24:MI:SS') as BegDt,
coalesce (TO_CHAR(LEAD(bus_dt - 1 ),'YYYY-MM-DD HH24:MI:SS')
OVER(order by store_num,rgstr_num, TO_CHAR(bus_dt,'YYYY-MM-DD HH24:MI:SS')), TO_CHAR(trunc(sysdate+7),'YYYY-MM-DD HH24:MI:SS')) as EndDt,
RGSTR_TYPE_CD
from sft_store_rgstr
-- where store_num = 6414
-- and rgstr_num = 1
) S,
appads.a_dim_cal DC
where DC.cal_dt between TO_CHAR(s.begdt,'YYYY-MM-DD HH24:MI:SS') and TO_CHAR(s.enddt,'YYYY-MM-DD HH24:MI:SS')
order by TO_CHAR(dc.cal_dt,'YYYY-MM-DD HH24:MI:SS')
THANK YOU
i am using a sql which is just working fine in SQL edtor. when i am trying to use same sql through ORAOCI stage it doesn't work. i am pasting error and sql which i used.Would any one let me know what could be done
ERROR
ADSFactSftStoreRgstrHash..oraSftStoreRgstr: ORA-30484: missing window specification for this function
ADSFactSftStoreRgstrHash..oraSftStoreRgstr.DSLink1: DSP.Open GCI $DSP.Open error -100.
SQL USED IN ORACLE
select s.store_num,
s.rgstr_num,
dc.cal_dt,
s.rgstr_type_cd
from
(
select STORE_NUM,
RGSTR_NUM,
BUS_DT as BegDt,
coalesce (LEAD(bus_dt - 1 )
OVER(order by store_num,rgstr_num, bus_dt), trunc(sysdate+7)) as EndDt,
RGSTR_TYPE_CD
from sft_store_rgstr
-- where store_num = 6414
-- and rgstr_num = 1
) S,
appads.a_dim_cal DC
where DC.cal_dt between s.begdt and s.enddt
order by dc.cal_dt
SQL USED IN ORAOCI STAGE
select s.store_num,
s.rgstr_num,
TO_CHAR(dc.cal_dt,'YYYY-MM-DD HH24:MI:SS'),
s.rgstr_type_cd
from
(
select STORE_NUM,
RGSTR_NUM,
TO_CHAR(BUS_DT,'YYYY-MM-DD HH24:MI:SS') as BegDt,
coalesce (TO_CHAR(LEAD(bus_dt - 1 ),'YYYY-MM-DD HH24:MI:SS')
OVER(order by store_num,rgstr_num, TO_CHAR(bus_dt,'YYYY-MM-DD HH24:MI:SS')), TO_CHAR(trunc(sysdate+7),'YYYY-MM-DD HH24:MI:SS')) as EndDt,
RGSTR_TYPE_CD
from sft_store_rgstr
-- where store_num = 6414
-- and rgstr_num = 1
) S,
appads.a_dim_cal DC
where DC.cal_dt between TO_CHAR(s.begdt,'YYYY-MM-DD HH24:MI:SS') and TO_CHAR(s.enddt,'YYYY-MM-DD HH24:MI:SS')
order by TO_CHAR(dc.cal_dt,'YYYY-MM-DD HH24:MI:SS')
THANK YOU
Something about that coalesce ... OVER strikes me as funny. Your ORA message is about a function window, I suspect that's what it is. Maybe DS messes the query up somehow.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Remove the "--"
thank you, but it is not the problem. i tried quey by removing "--" also butogmios wrote:Remove the "--"'s from your query... some versions of DataStage strip the newlines from the query and then anything on a line after the first -- is removed. 100% sure it's your problem, well 99.999%
Ogmios
it gave me same error message
Re: Remove the "--"
Can you post your new query?thank you, but it is not the problem. i tried quey by removing "--" also but
it gave me same error message
Ogmios
In theory there's no difference between theory and practice. In practice there is.
Re: Remove the "--"
ogmios wrote:Can you post your new query?thank you, but it is not the problem. i tried quey by removing "--" also but
it gave me same error message
Ogmios
here is my new query
select s.store_num,
s.rgstr_num,
TO_CHAR(dc.cal_dt,'YYYY-MM-DD HH24:MI:SS'),
s.rgstr_type_cd
from
(
select STORE_NUM,
RGSTR_NUM,
TO_CHAR(bus_dt,'YYYY-MM-DD HH24:MI:SS') as BegDt,
coalesce (TO_CHAR(LEAD(bus_dt - 1 ),'YYYY-MM-DD HH24:MI:SS'))
OVER(order by store_num,rgstr_num, TO_CHAR(bus_dt,'YYYY-MM-DD HH24:MI:SS')), TO_CHAR(trunc(sysdate+7),'YYYY-MM-DD HH24:MI:SS')) as EndDt,
RGSTR_TYPE_CD
from sft_store_rgstr
) S,
appads.a_dim_cal DC
where DC.cal_dt between TO_CHAR(s.begdt,'YYYY-MM-DD HH24:MI:SS') and TO_CHAR(s.enddt,'YYYY-MM-DD HH24:MI:SS')
order by TO_CHAR(dc.cal_dt,'YYYY-MM-DD HH24:MI:SS')
thank you
chandra
Re: Remove the "--"
Ok... a new hunch...
- Analytic functions are not supported in all Oracle8i versions. They are supported from Oracle9i.
- So could it be you're using the OCI8 stage instead of the 9 version?
It works for your SQL Editor as you have the same version of client and server, while it doesn't work in DataStage since you use version 8 to connect to your database via DataStage.
Ogmios
- Analytic functions are not supported in all Oracle8i versions. They are supported from Oracle9i.
- So could it be you're using the OCI8 stage instead of the 9 version?
It works for your SQL Editor as you have the same version of client and server, while it doesn't work in DataStage since you use version 8 to connect to your database via DataStage.
Ogmios
In theory there's no difference between theory and practice. In practice there is.
Re: Remove the "--"
thank you for replying me. i am using oraoci9 stage. my database is 10g.ogmios wrote:Ok... a new hunch...
- Analytic functions are not supported in Oracle8i (actually from 8.1.6 onwards). They are supported from Oracle9i.
- So could it be you're using the OCI8 stage instead of the 9 version?
It works for your SQL Editor as you have the same version of client and server, while it doesn't work in DataStage since you use version 8 to connect to your database via DataStage.
Ogmios
is oci9 stage with 10g is not compatable?. if so what can be done.
thank you
chandra
-
- Premium Member
- Posts: 108
- Joined: Sat Feb 05, 2005 6:52 pm
- Location: US