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     [CASE-OPENED] JOIN DB tables result similar to FOCUS join

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE-OPENED] JOIN DB tables result similar to FOCUS join
 Login/Join
 
as2010
Member
posted
Hi,

I have a join between one focus table TA and onde DB table TB :

JOIN
fieldA in TA TO ALL filedA in TB AS JOIN0
END

Then I have:

TABLE FILE TA
TA.x
Ta.y
TB.z
WHERE TB.a = 2;
ON TABLE HOLD AS RES
END

If I print all lines of TA it shows all lines of TA even those which TB.a <> 2 putting zero ou NULL

My problem is if TA and TB being DB tables (Netezza e.g.) the query generated is:

SELECT T1.x, T1.y,T2.z
FROM TA T1 left outer join TB T2 on T1.fieldA = T2.fieldA
WHERE TB.a = 2

Which exclues the TB lines where TB.a <> 2

IN sql teh way to solve this would be putting the TB.a = 2 condition in the JOIN clause.

I would like to know if there is some way to do this in Webfocus code,so the join would produce the same results either with focus or DB tables


Best Regards.

This message has been edited. Last edited by: <Kathryn Henning>,


7706M
AIX 7.1
HTML
 
Posts: 22 | Location: Portugal | Registered: May 07, 2015Report This Post
David Briars
Master
posted Hide Post
quote:
...I would like to know if there is some way to do this in Webfocus code...

Take a look at setting SHORTPATH.

By default it is set to FOCUS.

It sounds like you want to set it to SQL in the Netezza table to Netezza table scenario.




Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
 
Posts: 822 | Registered: April 23, 2003Report This Post
as2010
Member
posted Hide Post
quote:
SHORTPATH

Thanks David, but no luck

I tried to put SET SHORTPATH = SQL

but the generated code continus to put the WHERE coindition in the where sql clause instead of join, and the "zeros" lines are not shown.

Regards.

A.S.


7706M
AIX 7.1
HTML
 
Posts: 22 | Location: Portugal | Registered: May 07, 2015Report This Post
Waz
Expert
posted Hide Post
I would also suggest changing

WHERE TB.a = 2;

WHERE TB.a EQ 2;


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
as2010
Member
posted Hide Post
I've opened a Case to try to overcome this.
I'll keep you on track of any advance.

Thanks.


7706M
AIX 7.1
HTML
 
Posts: 22 | Location: Portugal | Registered: May 07, 2015Report This Post
Dave
Master
posted Hide Post
Here are some hints... ...they might help.

I struggled with joins a few days ago. ( finally had the DB solve it, not WF )
Problem is... ...what WF calls a join... isn't.

It decides wether to do the join based on the query.
Any other SQL would do the join first...

You can force WF to do the join by selecting fields from both masters.

You have JOIN as statement. I think that's a CROSS join ( full other join ).
Try some of the join-options.

I had this ( both DB files )

LEFT JOIN x in A to x in B
WHERE A.y = B.y

Turns out the WHERE is only excuted at query level.

e.g.
TABLE FILE A
PRINT A.x
END

shows all lines from A regardless of the WHERE in the join-statement.

TABLE FILE A
PRINT A.x B.x
END

shows only lines with the WHERE applied.

____________________________________

I came around this. Very useful. ( if performance of reportingserver suffices )


You can actually write your own SQL to HOLD files.

SET ASNAMES = ON

TABLE FILE CAR
	SUM SALES
	BY	CAR
ON TABLE HOLD AS FOC_CAR
END

TABLE FILE CAR
	SUM MIN.SEATS AS LSEATS
		MAX.SEATS AS HSEATS
	BY	CAR
ON TABLE HOLD AS FOC_SEATS
END

-* join
SQL
SELECT
	FOC_CAR.CAR , SALES , LSEATS , HSEATS
FROM FOC_CAR
INNER JOIN FOC_SEATS
ON FOC_CAR.CAR = FOC_SEATS.CAR;
TABLE HOLD AS BLACKBOX
END


-* even insert works..
SQL
insert into BLACKBOX ( CAR , SALES, LSEATS, HSEATS ) VALUES ( 'TESLA' , 80000, 2 , 4 );
-*TABLE ON TABLE HOLD AS H_CAR2
END
-RUN

TABLE FILE BLACKBOX
	PRINT *
END


Good luck,
Dave


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report 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     [CASE-OPENED] JOIN DB tables result similar to FOCUS join

Copyright © 1996-2020 Information Builders