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

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?

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

Why not just embed the SQL using SQL Passthru?

T

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.

To your original question,

JOIN
LEFT_OUTER ...
END

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

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,

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,

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!

quote:

ARP_ACTIVITY

Thanks a lot guys, I will try the conditional joins

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.