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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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
 
Winfred Gunter
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
Ram Prasad E
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,


WebFOCUS 8.1.05
Windows
http://ibiwebfocus.wordpress.com
https://www.facebook.com/groups/ibi.webfocus/
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Report This Post
Michael Bee
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
Qalqili
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
Winfred Gunter
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
Computix
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