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,

  
TABLE FILE CAR
PRINT RETAIL_COST COUNTRY
END

ahhh, gotcha, thank you

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?

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?

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.

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

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.

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

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?

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.

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.

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

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.