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
May 19, 2008, 07:33 PM
Darin Lee
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
May 19, 2008, 07:43 PM
jlsnyc
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
May 20, 2008, 02:49 AM
Tony A
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
May 20, 2008, 08:34 AM
PBrightwell
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
May 20, 2008, 01:50 PM
Leah
To your original question,
JOIN LEFT_OUTER ... END
The ... represents the other 'normal' join syntax for the master files definitions.
Leah
May 21, 2008, 04:57 AM
Roland
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.
Prod: WF 7.1.5 Test: WF 7.6.4 Unix Sun Solaris HTML, PDF, EXL2K
May 21, 2008, 08:34 AM
hammo1j
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
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
May 21, 2008, 08:45 AM
hammo1j
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
May 21, 2008, 01:16 PM
jlsnyc
quote:
ARP_ACTIVITY
Thanks a lot guys, I will try the conditional joins
May 21, 2008, 06:37 PM
Gizmo
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.