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     [CLOSED] Can I stop : Cannot be converted to Sql

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Can I stop : Cannot be converted to Sql
 Login/Join
 
Master
posted
Hello all,

When tracing the SQLAGGR we get messages like this:

14.09.26 BT (FOC2565) THE OBJECT OF IF/WHERE CANNOT BE CONVERTED TO SQL
14.09.26 BT (FOC2566) WHERE WH$$$1 CANNOT BE CONVERTED TO SQL
14.09.26 BT (FOC2583) NON-RELATIONAL OPERAND IS INVALID FOR OPERATION OR
14.09.26 BT (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
14.09.26 BT (FOC2598) FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL

**I do not need info about WHY we get these warnings nor how to change the TABLE request to avoid it.**

I'm wondering whether there is some setting to STOP the request.
Our database-appliance can do it in 10 secs. Webfocus will take more than an hour to do the same.

So when we make an report I never want Webfocus to "fix" it.

Sample 1
TABLE FILE ours
SUM TURNOVER
    CNT.DST.CUSTOMER_ID AS 'CUSTOMERS'
BY WEEK
WHERE YEAR EQ 2012 OR 2013
END

This works fine within seconds... ( rows returned from database : 104 )

Sample 2
TABLE FILE ours
SUM TURNOVER
    CNT.DST.CUSTOMER_ID AS 'CUSTOMERS'
BY WEEK
WHERE YEAR EQ (2012) OR (2013)
END

This is something different. This is an expression. According to WebFocus ( or the adaptor ) this can not be translated.

Webfocus fires this query instead ( to be able to do the cnt.dst itself )
TABLE FILE ours
SUM TURNOVER
BY CUSTOMER_ID
BY WEEK
WHERE YEAR EQ 2012 OR 2013
END

This works fine within seconds... ( rows return : 50,000,000+ )
...but then it takes the Agents a very long time to determine the cnt.dst.


Once again I do not need hints about how to change the TABLE request to avoid is. Sometimes it's not so very clear ( parameter in a parameter in a parameter etc. ). It happens by 'accident'. And I do not want to have to test every query seperately with a trace.


I'm looking for something like:

SET HOLDONWARNING = ON

or some documentation about SQL settings? Optimizer hints and such... ?


Greets,
Dave

This message has been edited. Last edited by: Dave,


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Expert
posted Hide Post
what happened to "skin a car"
When comparing a field to a constant, use IF not WHERE




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Master
posted Hide Post
Susannah,

Thanks for the reply.

But I'm not looking for an alternative. We have alternatives. I just don't want to have to trace every single query to check whether WebFocus decided it "cannot be converted to SQL".



Off topic on the "IF" statement.
We never use "IF". See help:

Tip: Unless you specifically require IF syntax (for example, to support legacy applications), we recommend using WHERE.

and

Note: The IF phrase alone cannot be used to create compound expressions by connecting simple expressions with AND and OR logical operators. Compound logic requires that the IF phrase be used with the DEFINE command, as described in Using Expressions. You can accomplish this more easily with WHERE.

Greets,
Dave


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Expert
posted Hide Post
quote:
I just don't want to have to trace every single query to check whether WebFocus decided it "cannot be converted to SQL".


You don't have to. But you'll have to live with the inefficient SQL code that WebFOCUS may generate.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Master
posted Hide Post
@Francis

And waiting for a query more then 45 min. instead of 2 seconds?

Not really and option, just because someone accidentally added parenthesis? Which in any other language would not make a difference?

WHERE YEAR EQ (2012) OR (2013)
instead of
WHERE YEAR EQ 2012 OR 2013

that's hard to live with, hope you agree.


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Expert
posted Hide Post
I would like to know why you added the parentheses ?, can this test become quite complex, or is it always a list of years ?

If its a list of YEARs, then you can use:
WHERE YEAR EQ 2012 OR 2013
or
IF YEAR EQ 2012 OR 2013
or
WHERE YEAR IN (2012,2013)

If the list is in a file, then you can use:
WHERE YEAR IN FILE {ddname}
or
IF YEAR EQ ({ddname})

I think the parenteses confused the interpreter.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
quote:
someone accidentally added parenthesis

IMHO: There are standards and syntactical rules which should be adhered to when coding. Not really room here for "accidental" code, is there? The code may be tightened-up someday where accidental coding may fail.




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Master
posted Hide Post
Waz,

as Doug quoted...

Problem is the TABLE usually looks like:

TABLE FILE ...
SUM ... ...
&BY_FIELDS
&GLOBAL_WHERES
&USER_WHERES
END


and those &...._WHERES are made up of several parts done by -INCLUDES etc.

...and again. We know how to fix it. It just took us some time to figure out that this was causing bad performance. Especially because it isn't that obvious that those parenthesis make the parser change it's mind.

Would be nice that we didn't have to find out 'the hard way' ( when in production ( bigger dataset ) -> users complain ).


but thanks for thinking along, seriously.


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Virtuoso
posted Hide Post
Perhaps you can stop such queries at the database side?

Most databases allow limiting the amount of resources a user is allowed to use. If such a query from such a user exceeds those limits, the query would be aborted.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Expert
posted Hide Post
I know it's not ideal, but if you wanted to check the SQL without actually executing it then look into SET XRETRIEVAL = OFF.

Don't forget to set it on again if you decide to run your code!

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Master
posted Hide Post
Hi Wep5622, that might actually be not such a bad idea... ...thanks.

@Tony A, Yeah that's what we use, thanks.


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Expert
posted Hide Post
quote:
Yeah that's what we use

I guessed that would be the case, but still worth mentioning for those newbies who come across this topic in a few years when they use search Smiler .... They do use search don't they? Wink

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report 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     [CLOSED] Can I stop : Cannot be converted to Sql

Copyright © 1996-2020 Information Builders