User Defined sql in ORAOCI

[chandoo7]

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

[kcbland]

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.

[ogmios]

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

[chandoo7]

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

[ogmios]

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

[chandoo7]

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

[ogmios]

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

[chandoo7]

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

[diamondabhi]

it is compatible with 10g.

[rleishman]

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.