DSXchange

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

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.

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

ogmios 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

thank you, but it is not the problem. i tried quey by removing "--" also but
it gave me same error message

thank you, but it is not the problem. i tried quey by removing "--" also but
it gave me same error message

Can you post your new query?

Ogmios

ogmios wrote:

thank you, but it is not the problem. i tried quey by removing "--" also but
it gave me same error message

Can you post your new query?

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

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

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

thank you for replying me. i am using oraoci9 stage. my database is 10g.
is oci9 stage with 10g is not compatable?. if so what can be done.
thank you
chandra

it is compatible with 10g.

The OVER clause is attached to the COALESCE() function, but COALESCE is not an analytic function.

I think you want the OVER clause attached to the LEAD() function. I'm surprised this worked in your SQL editor.