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] Remove Order and Group by Clause Generated by IBI from SQL

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Remove Order and Group by Clause Generated by IBI from SQL
 Login/Join
 
Brandon Andrathy
Platinum Member
posted
Hello,

Wondering if there is any way to make ORDER BY Clause not generate with SQL for fields in the BY portion of a table file.

For example:

 

TABLE FILE CAR
SUM
    RETAIL_COST
BY COUNTRY
END


This would generate SQL such as

SELECT
RETAIL_COST, COUNTRY
FROM CAR
ORDER BY COUNTRY

(obviously this isn't what generates as I can't view sql from CAR File in App studio).

Is there any way to remove the ORDER BY clause from generating?

This message has been edited. Last edited by: Brandon Andrathy,


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
BabakNYC
Virtuoso
posted Hide Post
  
TABLE FILE CAR
PRINT RETAIL_COST COUNTRY
END


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Brandon Andrathy
Platinum Member
posted Hide Post
ahhh, gotcha, thank you


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Brandon Andrathy
Platinum Member
posted Hide Post
When using PRINT, how do I get those values into a hold file? The sql generated looks great using print, but when I try to put those values into a hold file, it takes forever. Do I need to use a specific Format?


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Brandon Andrathy
Platinum Member
posted Hide Post
I just found and used SET ALL = PASS and that gave me the load times that I wanted.

Can someone explain to me what this does in combination with the Print function. If SET ALL doesn't equal Pass, does PRINT do something weird on the IBI side?


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
BabakNYC
Virtuoso
posted Hide Post
It depends on what type of HOLD file you want. The quickest is to create a temp file. That's a binary sequential file that ends up on disk. You can create that using ON TABLE HOLD AS brandon. That'll create a brandon.ftm and a brandon.mas. If you want a DBMS file you can say ON TABLE HOLD AS brandon FORMAT MSODBC or SQLORA or whatever format you want. Clearly, this will require WebFOCUS to load that answer set into the dbms and it'll take extra time. You'll have to tell us what you want to do with the output before we can decide on the optimal format.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Brandon Andrathy
Platinum Member
posted Hide Post
When I didn't have SET ALL=PASS in my code, the PRINT code was taking FOREVER to load into a hold file when I was doing:

 
TABLE FILE CAR
PRINT
  RETAIL_COST
  COUNTRY
ON TABLE HOLD AS 'TEST'
END


I was doing a join between two files before the print. When I set x retrieval to off and looked at the sql, it ran perfectly. But in IBI, it took a while. I'm trying to understand why set all = pass works with print


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
BabakNYC
Virtuoso
posted Hide Post
https://webfocusinfocenter.inf...fs6/wf8206crlang.pdf

See page 1051. You didn't mention JOIN and SET ALL in your original question. That is a significant part of the puzzle.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Brandon Andrathy
Platinum Member
posted Hide Post
Thank you Babak,

Only problem is IBI changes the sql that I would expect a tad when i use SET ALL = PASS. It's not a huge deal. But I'm curious why when I have SET ALL = OFF, my sql is perfect, but IBI takes forever to get my printed values into a hold file.

Is there any reason why putting print values in a hold file


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Frans
Guru
posted Hide Post
Writing into holdfiles is quite fast. But keep in that using PRINT instead of SUM can result in a lot more data. SUM aggregates the data on a BY field.

How many rows are you writing with PRINT? Do you have some BLOB or big character fields in your result?


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Danny-SRL
Virtuoso
posted Hide Post
If the only thing you are doing is retrieving data and HOLDing it, when you are retrieving lots of data try using TABLEF instead of TABLE.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
BabakNYC
Virtuoso
posted Hide Post
You have a lot of options:
1. If you have a more efficient SQL, you can use SQL PASSTHRU.
2. I'd turn on the SQL Trace of your request and test it in the DBMS natively to see how long the DBMS takes to return the answer set.
3. The trace in #2 will also tell you if you're turning off optimization with your join.
4. Time the request without the HOLD and see how fast it renders in HTML. Then time it with the HOLD to see how much more time it takes to load it into the hold file. Again, I'm not clear what kind of HOLD file you're trying to create and the purpose of this HOLD file. If this is something that you'll do repeatedly, you may need to come up with a better method of creating the HOLD file. If it's a one time event, you might consider using scheduler or differed.

Without the details, all you'll get from us is guesses. The best approach is to do a little digging to understand what your SET and JOIN commands are doing to the request.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Brandon Andrathy
Platinum Member
posted Hide Post
Hey All,

I appreciate all the suggestions! I will try the TABLEF method. But I woke up this morning and now the hold files are generating in the time I expected them to.

Thanks again!! Closing this


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
jgelona
Master
posted Hide Post
FYI, there are a few SET commands that allows WebFOCUS to determine if TABLEF or TABLE is more efficient.


SQLTOPTTF

The SQLTOPTTF parameter enables the SQL Translator to generate TABLEF commands instead of TABLE commands.

The syntax is:
SET SQLTOPTTF = {ON|OFF}

where:
ON
Generates TABLEF commands when possible. For example, a TABLEREF command is generated if there is no JOIN or GROUP BY command. ON is the default value. ON is the default value.
OFF
Always generates TABLE commands.


AUTOTABLEF

The AUTOTABLEF parameter avoids creating the internal matrix based on the features used in the query. Avoiding internal matrix creation reduces internal overhead costs and yields better performance.

The syntax is:
SET AUTOTABLEF = {ON|OFF}

where:
ON
Does not create an internal matrix. ON is the default value.
OFF
Creates an internal matrix.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report 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] Remove Order and Group by Clause Generated by IBI from SQL

Copyright © 1996-2020 Information Builders