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.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Joins
 Login/Join
 
Gold member
posted
Does anyone know how to convert the following SQL join to a webfocus join

LEFT OUTER JOIN PRDDTA.F4105 F4105_1
ON
F4101.IMITM=F4105_1.COITM
AND F4105.COMCU<>F4105_1.COMCU
AND F4102_1.IBMCU=F4105_1.COMCU

Thanks
 
Posts: 61 | Location: New York City | Registered: July 20, 2006Report This Post
Virtuoso
posted Hide Post
I think there's got to be additional SQL somewhere as you are mentioning fields and files F4101 and F4102_1 that are not in your join. Can you give us an idea of what you are trying to accomplish with the join?


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Gold member
posted Hide Post
Ok here is the entire SQL code.

SELECT
IMLITM
IMDSC1
IMITM
F4102.IBMCU
LIPQOH
LILOCN
F4105.COLEDG
F4105.COUNCS
F4102_1.IBMCU AS IBMCU_1
F4105_1.COLEDG AS COLEDG_1
F4105_1.COUNCS AS COUNCS_1

FROM
PRDDTA.F4101 F4101
INNER JOIN PRDDTA.F4102 F4102
ON
F4102.IBITM=F4101.IMITM
LEFT OUTER JOIN PRDDTA.F41021 F41021
ON
F4101.IMITM=F41021.LIITM
AND F4102.IBMCU=F41021.LIMCU
INNER JOIN PRDDTA.F4105 F4105
ON
F4101.IMITM=F4105.COITM
AND F4102.IBMCU=F4105.COMCU
INNER JOIN PRDDTA.F4102 F4102_1
ON
F4101.IMITM=F4102_1.IBITM
AND F4102.IBMCU<>F4102_1.IBMCU
LEFT OUTER JOIN PRDDTA.F4105 F4105_1
ON
F4101.IMITM=F4105_1.COITM
AND F4105.COMCU<>F4105_1.COMCU
AND F4102_1.IBMCU=F4105_1.COMCU


This came from a product called Showcase, and we are converting Showcase code to WebFocus

Thanks
 
Posts: 61 | Location: New York City | Registered: July 20, 2006Report This Post
Expert
posted Hide Post
Why not just embed the SQL using SQL Passthru?

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Master
posted Hide Post
quote:
AND F4105.COMCU<>F4105_1.COMCU

If you can't follow Tony's suggestion and embed the SQL code then join on the equal conditions and limit the unequal conditions in a WHERE statement.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
To your original question,

JOIN
LEFT_OUTER ...
END

The ... represents the other 'normal' join syntax for the master files definitions.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Gold member
posted Hide Post
Hi all,

Give it a try with Conditional Join.
I copied this out of the HTML-Manual in TechSupport Site of IBI:

quote:

JOIN [LEFT_OUTER|INNER] FILE hostfile AT hfld1 [TAG tag1]
[WITH hfld2]
TO {UNIQUE|MULTIPLE}
FILE crfile AT crfld [TAG tag2] [AS joinname]
[WHERE expression1;
[WHERE expression2;
...]
END



where:

INNER

Specifies an inner join. If you do not specify the type of join in the JOIN command, the ALL parameter setting determines the type of join to perform.


LEFT OUTER

Specifies a left outer join. If you do not specify the type of join in the JOIN command, the ALL parameter setting determines the type of join to perform.


hostfile

Is the host Master File.


AT

Links the correct parent segment or host to the correct child or cross-referenced segment. The field values used as the AT parameter are not used to cause the link. They are simply used as segment references.


hfld1

Is the field name in the host Master File whose segment will be joined to the cross-referenced data source. The field name must be at the lowest level segment in its data source that is referenced.


tag1

Is the optional tag name that is used as a unique qualifier for fields and aliases in the host data source.


hfld2

Is a data source field with which to associate a DEFINE-based conditional JOIN. For a DEFINE-based conditional join, the KEEPDEFINES setting must be ON, and you must create the virtual fields before issuing the JOIN command.


MULTIPLE

Specifies a one-to-many relationship between from_file and to_file. Note that ALL is a synonym for MULTIPLE.


UNIQUE

Specifies a one-to-one relationship between from_file and to_file. Note that ONE is a synonym for UNIQUE.


Note: Unique returns only one instance and, if there is no matching instance in the cross-referenced file, it supplies default values (blank for alphanumeric fields and zero for numeric fields).


crfile

Is the cross-referenced Master File.


crfld

Is the join field name in the cross-referenced Master File. It can be any field in the segment.


tag2

Is the optional tag name that is used as a unique qualifier for fields and aliases in the cross-referenced data source.


joinname

Is the name associated with the joined structure.


expression1, expression2

Are any expressions that are acceptable in a DEFINE FILE command. All fields used in the expressions must lie on a single path.


Note: Single line JOIN syntax is not supported. The END command is required.


Here the link for more information.

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


Roland

Prod: WF 7.1.5
Test: WF 7.6.4
Unix Sun Solaris
HTML, PDF, EXL2K
 
Posts: 54 | Location: Switzerland | Registered: May 13, 2003Report This Post
Master
posted Hide Post
Last time I looked the LEFT_OUTER conditional join did not work correctly in wf 713 but it seems to have been corrected

-INCLUDE SQLTRACE 
-RUN
JOIN LEFT_OUTER FILE ACTIVITY AT ACT_ACTIVITY_SEQ 
TO MULTIPLE FILE ACTIVITY_RESOURCE_PROJ AT ARP_ACTIVITY_SEQ AS J1
WHERE ARP_ACTIVITY_SEQ LT ACT_ACTIVITY_SEQ ; 
END
-RUN
TABLE FILE ACTIVITY
PRINT 
ACT_ACTIVITY_SEQ ARP_ACTIVITY_SEQ
IF ACT_ACTIVITY_SEQ EQ 100047260 OR 100047261
-* IF READLIMIT EQ 99
-* IF RECORDLIMIT EQ 99
END


Correctly produces the result.

PAGE 1 
 
Activity
Seq       Activity
          seq  
100047260 . 
100047261 100047260 


With activity seq 100047260 which is the lowest item in the list a missing value is returned as is required for the left outer join. (This did not work before IIRC)

However the code generated for the join is perhaps unneccesarily cautious. Instead of passing the left outer join to Oracle (as it does with equijoin) it generates a second stored procedures called under the control of webfocus from the results of the first query.


Use SET TRACEON=<name>/<lvl>/CLIENT to send traces to the client
 Name     Level Description                       Set  Comp.ID
 STMTRACE  1    SQL/MDX Generated Statement Trace  Y     AE
 SQLTRANS  3    SQLTRANS Memory Dump               Y     BL
 SQLAGGR   1    SQL Aggregation Trace              Y     BR
 13.33.16 BR  (FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
 13.33.16 BR  (FOC2513) OUTER JOIN (ALL=ON or ALL=PASS) CANNOT BE PASSED
 13.33.16 BR  (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 13.33.16 BR  (FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED
 13.33.16 AE    SELECT T1."ACTIVITY_SEQ" FROM IFSAPP.ACTIVITY T1 WHERE
 13.33.16 AE   (T1."ACTIVITY_SEQ" IN(100047260, 100047261));
 13.33.16 AE    SELECT T2."ACTIVITY_SEQ" FROM IFSAPP.ACTIVITY_RESOURCE_PROJ T2
 13.33.16 AE   WHERE (T2."ACTIVITY_SEQ" < :0001);
 ...RETRIEVAL KILLED
 0 NUMBER OF RECORDS IN TABLE=        0  LINES=      0

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



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Master
posted Hide Post
PS Remember that a

WHERE TOFIELD EQ MISSING

test will incorrectly disable any LEFT_OUTER join (changes to INNER), both the simple and conditional so be careful.

IB say they may or may not fix this bug!



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Gold member
posted Hide Post
quote:
ARP_ACTIVITY

Thanks a lot guys, I will try the conditional joins
 
Posts: 61 | Location: New York City | Registered: July 20, 2006Report This Post
Gold member
posted Hide Post
Keep in mind that with the following
select *
from tb1
  left outer tb2
    on tb2.idx = tb2.idx and tb2.value = 'somedata'

that the filtering of "somedata" happens before the outer join takes place. In 7.6.5, this may be possible to recreate (I havn't done this yet, but I remember something about this being a new feature in 7.6.5)

You can come close to recreating this with the following.
SQL engine SET SQLOUTER ON
SQL engine SET OPTIMIZATION SQL
SET ALL=PASS
JOIN TB1.IDX IN TB2 TO ALL TB2.IDX IN TB2
TABLE FILE TB1
PRINT IDX VALUE
WHERE TB2.IDX IS MISSING OR TB2.VALUE EQ 'somedata'
END


This would create something similar to the following SQL
select t1.idx, t2.value
  from tb1 t1
    left outer join tb2 t2
      on t1.idx = t2.idx
where t2.idx is null
   or t2.avalue = 'somedata'


The issue with this is that the filtering of somedata takes place AFTER the outer join has taken place, so if something did exist in tb2, but was not "somedata", it would then be excluded from the report rather than just shown as missing.



Windows: WF 7.6.2: SQL Server 2008 R2
 
Posts: 86 | Location: Chicago | Registered: August 03, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders