Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
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
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Remove Order and Group by Clause Generated by IBI from SQL
 Login/Join
 
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: 129 | Registered: July 10, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
  
TABLE FILE CAR
PRINT RETAIL_COST COUNTRY
END


WebFOCUS 8206, Unix, Windows
 
Posts: 1852 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
ahhh, gotcha, thank you


WebFOCUS 8204
 
Posts: 129 | Registered: July 10, 2018Reply With QuoteReport This Post
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: 129 | Registered: July 10, 2018Reply With QuoteReport This Post
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: 129 | Registered: July 10, 2018Reply With QuoteReport This Post
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: 1852 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
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: 129 | Registered: July 10, 2018Reply With QuoteReport This Post
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: 1852 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
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: 129 | Registered: July 10, 2018Reply With QuoteReport This Post
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: 439 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
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: 1961 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
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: 1852 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
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: 129 | Registered: July 10, 2018Reply With QuoteReport This Post
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: 958 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.