Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] sql script crashes agent everytime, without fail

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Solved] sql script crashes agent everytime, without fail
 Login/Join
 
Gold member
posted
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,


WF 8009m, Clustered vm Windows2008r2 reporting servers;
Web interface: tomcat;
Output: EXCEL, HTML, PDF; dbms: Oracle 10, db2 on mvs, mssql
 
Posts: 81 | Location: Monroe LA | Registered: January 07, 2005Report This Post
Master
posted Hide Post
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,
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Report This Post
Member
posted Hide Post
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?


7.6.10, Winddows X64
all output
 
Posts: 5 | Registered: March 01, 2010Report This Post
Platinum Member
posted Hide Post
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


WF 7.7.0.3HF3 / WinXP- WF-Client & Apache / DevStd 7.7.0.3HF3 win XP.
 
Posts: 118 | Registered: February 08, 2006Report This Post
Gold member
posted Hide Post
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


WF 8009m, Clustered vm Windows2008r2 reporting servers;
Web interface: tomcat;
Output: EXCEL, HTML, PDF; dbms: Oracle 10, db2 on mvs, mssql
 
Posts: 81 | Location: Monroe LA | Registered: January 07, 2005Report This Post
Silver Member
posted Hide Post
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


WF 7.6.6 (MRE,BID, DevStudio, partly RC) on Windows 2003 /Apache/Tomcat
Output: HTML,Excel,PDF,PPT
Adapters: SQL Server, DB2, Oracle
 
Posts: 38 | Location: Austria, Linz | Registered: June 19, 2009Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] sql script crashes agent everytime, without fail

Copyright © 1996-2020 Information Builders