 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [CLOSED] OPERATION IF-THEN-ELSE CANNOT BE CONVERTED TO SQL
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] OPERATION IF-THEN-ELSE CANNOT BE CONVERTED TO SQL Read-Only TopicGo | Search | Notify | Tools |
| [CLOSED] OPERATION IF-THEN-ELSE CANNOT BE CONVERTED TO SQL | Login/Join |
| Gold member |
I have construction like DEFINE TABLE LLL VALU=IF AA EQ 'Y' THEN AMOUNT1 ELSE 0; END TABLE FILE LLL SUM VALU BY COUNTRY END I am getting -RUN 12.00.37 BT (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON: 12.00.37 BT (FOC2565) THE OBJECT VALU OF SUM CANNOT BE CONVERTED TO SQL 12.00.37 BT (FOC2566) DEFINE VALU CANNOT BE CONVERTED TO SQL 12.00.37 BT (FOC2577) OPERATION IF-THEN-ELSE CANNOT BE CONVERTED TO SQL and all records are fetched to client. How to avoid this fetch? Is it problem related to ODBC adaper that I use?This message has been edited. Last edited by: <Kathryn Henning>, Wf7704/WF8,Win64/32 | ||
|
| Virtuoso |
Try it with SQL SQLODBC SET OPTIFTHENELSE ON | |||
|
| Gold member |
Thank you for help! I have tried, but it does not help. (I have seen in topic before that in 7.7+ it is ON by default) Wf7704/WF8,Win64/32 | |||
|
| Gold member |
I see that this situation is very similar ro http://forums.informationbuild...951034762#3951034762 where nobody help to solve, but one good trick was demonstrated. for myself, I can use same trick (but it will make troubles - in real life I use 5 defines). So I decided to write SQL manually and use SQL SQLODBC PREPARE HOLD1 FOR select .... END techniques.... At least I hope that I will be sure what I will getThis message has been edited. Last edited by: Piter, Wf7704/WF8,Win64/32 | |||
|
| Virtuoso |
Yeah, some adapters are quite simplistic in their capabilities to convert FOCUS code to SQL. I've run into this same issue several times using the DB2/400 adapter... In such cases we too fall back to plain SQL. A word of warning though: WF will use different field formats for the results from such a query than it used to create your masters. That is relevant when you try to join your results to a master file in the same database, as although the actual fields are the same size, WebFOCUS thinks they're not and refuses to join. I opened a case for that, but IBI has no intention to fix their mistake. 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 : | |||
|
| Master |
@Piter we found that sometimes the inablity to convert to SQL is caused by the Format of the field. VALUE/P12 = ... didn't translate VALUE/D20 = ... did g'luck _____________________ WF: 8.0.0.9 > going 8.2.0.5 | |||
|
| Master |
Piter, why not use: TABLE FILE LLL SUM AMOUNT1 BY COUNTRY WHERE AA EQ 'Y' END In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux. | |||
|
| Gold member |
Thank you all for help and attention. Wep5622, I have noticed an issue with result of SQL. How you create a master? you have it just defined somewhere or what you are doing? In one place I have solved it using more SQL have done SQL union instead of WF MORE, but there will be places where union will not help. Dave - I will try your trich. May be you know what will be with VALUE/D20.4? Will it be translated or not?
I cannot use this as in real example I have over 5 defines Wf7704/WF8,Win64/32 | |||
|
| Master |
@Piter I don't know. I guess it depends on your DBMS, adapter and settings. _____________________ WF: 8.0.0.9 > going 8.2.0.5 | |||
|
| Virtuoso |
We don't usually explicitly create a master for the SQL; we just use the result of SQLORA PREPARE SQLOUT FOR ... etc. and then run ?FF SQLOUT to check the resulting field definitions. If you compare those to a master created using 'Create synonym' (from the webconsole, for example) on the same table in that same database, you'll find that the data types (especially for VARCHAR fields) don't match in size. Of course, the actual field definitions in the table and in the fields from the SQL query are exactly the same. The fun bit is that when you try to join those SQL results to a similar table, FOCUS refuses to perform the join because the field formats are different. They are not! It's just FOCUS being ridiculous. 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 : | |||
|
| Expert |
I would always recommend that you actually supply a format in a define and not let the default be used. Also, not sure what the parser is like for ODBC (as I don't use it that often) but for Oracle I find adding defines to the synonym works great. e.g. If I want a substring depending upon a certain column value then I would code something like this in the synonym - DEFINE D_SUBSTRING/A6 MISSING ON = IF COLUMN1 EQ 'H' THEN SQL.SUBSTR(LONG_STRING, 5, 6) ELSE MISSING; $ This would be interpretted as something like .... (CASE (COLUMN1) WHEN 'H' THEN SUBSTR(LONG_STRING,5,6) ELSE NULL END) This works with much of Oracle SQL syntax and is well worth investigating. T
| |||||||||
|
| Gold member |
I have tried with D20, I10, P20 I6 - aggregation not passed into db (at least when adapter is ODBC) Wf7704/WF8,Win64/32 | |||
|
| Expert |
You say that you use the ODBC adapter, but what is the actual data source? T
| |||||||||
|
| Gold member |
This time on development machine it is Sybase ASA (SAP) SQL Anywhere. Later it will be Sybase IQ, but it may be other db in future Wf7704/WF8,Win64/32 | |||
|
| Expert |
Using the ODBC adapter is OK but it does have very limited SQL. I would always recommend using the correct adapter for the source you are using, as the adapter is optimised to use the SQL of the source. I know that some companies tend to just "stick with" the ODBC adapter but that can cause problems due to limited SQL able to be used. Of course, obtaining and installing the adapter has a licence implication but ultimately it is always worth it (imho). T
| |||||||||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] OPERATION IF-THEN-ELSE CANNOT BE CONVERTED TO SQL
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] OPERATION IF-THEN-ELSE CANNOT BE CONVERTED TO SQLCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |