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] SQL View replaced by Focus File

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] SQL View replaced by Focus File
 Login/Join
 
ChristianP
Platinum Member
posted
Hello,

i have a curios problem. I had to add a calculation to a sql view and created a focus file as result.
Now i wanted to replace the previous used view with the focus file. And i got strange results while joining
the focus file. I made the indexes on the joined files. But i do not get the same output amount.
Here is the origin join:

 
JOIN
 contracts.contracts.ID IN
contracts TO UNIQUE
 contracts_vol.contracts_vol.VERTRAGID
 IN contracts_vol TAG J0 AS J0
END


As result i got 450 rows.
Here is the join with the focus file:

  
JOIN
 contracts.contracts.ID IN
contracts TO UNIQUE VOL_FOC.SEG01.VERTRAGID IN VOL_FOC
 TAG J0 AS J0
 END


When i join my focus file with the sql view i got 226 rows a result.

Regards

Christian

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


WF Production Version: 7.7.02M
WF Test Version: 7.7.02M
Developer Studio: 7.7.02
HTML, EXL2K, FLEX, PDF,PPT
 
Posts: 156 | Location: Essen Germany | Registered: December 02, 2010Report This Post
njsden
Virtuoso
posted Hide Post
ChristianP, we would need to see other pieces of the puzzle such as:

- Current value of SET ALL (ON, OFF, PASS)
- Are contracts.ID and vol_foc.VERTRAGID the same data type and length?

How many records do you have in your view?

After HOLDing "FORMAT FOCUS INDEX VERTRAGID" how many records do you have in the resulting HOLD?

Minor comment, is CONTRACTS an actual database structure? why would you want to join it to a FOCUS file? in most cases, joining databases structures to external files tends to be less efficient as the database cannot do the join directly.


Could you try this? instead of joining the table to the FOCUS file, query and HOLD your results from the main table, then join HOLD to HOLD and see what changes.

Finally, it would be interesting to see the actual TABLE FILE you're attempting to use as there could be WHERE conditions in there that are being interpreted differently when using the SQL translator as opposed to FOCUS logic.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
ChristianP
Platinum Member
posted Hide Post
Hi njsden,
thanks for your help.
- Current value of SET ALL (ON, OFF, PASS) = ON
- Are contracts.ID and vol_foc.VERTRAGID the same data type and length? YES
In the view i have 6701 records, in the focus file i have also 6701 records. I have to build the focus file because i have no influence on the source structure (the provided views come from another company). In the table file are no where clauses.

Regards

Christian


WF Production Version: 7.7.02M
WF Test Version: 7.7.02M
Developer Studio: 7.7.02
HTML, EXL2K, FLEX, PDF,PPT
 
Posts: 156 | Location: Essen Germany | Registered: December 02, 2010Report This Post
njsden
Virtuoso
posted Hide Post
quote:
Current value of SET ALL (ON, OFF, PASS) = ON

That's the culprit!

SET ALL=ON gets translated as an outer join to databases, so your original JOIN to database structure is doing:

SELECT ... FROM contracts
LEFT OUTER JOIN contracts_vol ON (blah)


Regardless of the UNIQUE keyword in your JOIN, the database is returning everything from "contracts" plus any number of matching records in contracts_vol. UNIQUE is not honoured by databases; it is a FOCUS-specific functionality.


When you do it using a FOCUS file however, as you are specifying UNIQUE you probably are still getting every record from contracts but this time around only the first matching record in contracts_vol is returned.

That may explain why your SQL query returns more records than your FOCUS one.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
ChristianP
Platinum Member
posted Hide Post
Hi njsden,
with MULTIPLE i got the correct results!!
Thanks a lot!!!

Christian


WF Production Version: 7.7.02M
WF Test Version: 7.7.02M
Developer Studio: 7.7.02
HTML, EXL2K, FLEX, PDF,PPT
 
Posts: 156 | Location: Essen Germany | Registered: December 02, 2010Report This Post
njsden
Virtuoso
posted Hide Post
Great! JOIN's in WebFOCUS are very powerful and though their functionality is very well documented sometimes it's easy to assume that they behave exactly the same way across different platforms but we've already seen they don't (FOCUS vs RDBMS for instance).

I've learned that by trial-and-terror (can't remember where I heard that before) and hopefully it has sinked deep enough now Smiler

Glad it helped.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report 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] SQL View replaced by Focus File

Copyright © 1996-2020 Information Builders