I have a Friday afternoon quiz... This script:

ENGINE SQLORA SET DEFAULT_CONNECTION OEEBID
SQL SQLORA
select * from (
select rank() over( partition by rgn_nme order by valu_lost_amt desc, acct_nme, oppy_nme) r,
acct_nme, valu_lost_amt, rgn_nme, oppy_nme, br_nme
from oppy_fact b, oppy_dim ba, rgn_dim c, br_dim br, acct_dim d
where b.oppy_dim_id=ba.oppy_dim_id(+)
and b.rgn_dim_id=c.rgn_dim_id(+)
and b.br_dim_id=br.br_dim_id(+)
and ba.acct_dim_id=d.acct_dim_id(+)
and upper(stg_nme)='LOST'
and to_char(b.yr_mo_id)=to_char(add_months(sysdate,-1),'yyyymm')
) a
where r <= 10
;
TABLE FILE SQLOUT PRINT *
END

Crashes the agent everytime, without fail.
BTW, it runs perfectly in pl/sql developer.

Anything look unusual? Any ideas?

Thanks,
--wg

This message has been edited. Last edited by: Winfred Gunter,

Instead of "select * from ", specify the column names and then give a try.

PRINT * in a TABLE FILE request will work fine.
But make sure if the columns are created properly. use this command to check

?FF SQLOUT

Hope this helps.

This message has been edited. Last edited by: Ram Prasad E,

WF seems to like 'vanilla flavor' SQL. Success with RANK() before? Maybe this is beyond WF's understanding. If rank() is used to generate a unique id -- maybe try using a standard order by and rownum <11 instead?

did you try this

quote:

ENGINE SQLORA SET DEFAULT_CONNECTION OEEBID
SQL SQLORA
PREPARE SQLOUT FOR

select * from (
select rank() over( partition by rgn_nme order by valu_lost_amt desc, acct_nme, oppy_nme) r,
acct_nme, valu_lost_amt, rgn_nme, oppy_nme, br_nme
from oppy_fact b, oppy_dim ba, rgn_dim c, br_dim br, acct_dim d
where b.oppy_dim_id=ba.oppy_dim_id(+)
and b.rgn_dim_id=c.rgn_dim_id(+)
and b.br_dim_id=br.br_dim_id(+)
and ba.acct_dim_id=d.acct_dim_id(+)
and upper(stg_nme)='LOST'
and to_char(b.yr_mo_id)=to_char(add_months(sysdate,-1),'yyyymm')
) a
where r <= 10
;
TABLE FILE SQLOUT PRINT *
END

Regards
Qalqili

Ram, Michael, Qalqili,

- Printing individual column names didn't make a difference and the agent crashes before the ?FF SQLOUT can execute.

- I use RANK in at least 4 other fex's just fine (in this same project).

- Tried PREPARE statement.... no difference.

I've run into this before but I usually always find a keyword capitalized or starting in position 1 that I can modify and make it work.... NOT THIS ONE!

Anyway, thanks for the suggestions.... I resolved it by creating a view (which I don't particularly like) and moving on.

Thanks,
--wg

Even if this one is old, I post the solution.

SQL-Passthrough using rank with order by desc does not work!
You will need to use ascending order.

Regards