HI,
Please see the SQL query below:

--******************************

SELECT a.*,b.*
FROM a
inner join b
on (case when isnull(a.r_key,'') = '' and b.r_key <> '' then 1 when isnull(a.r_key,'') <> '' and a.r_key = b.r_key then 1 else 0 end) = 1
and (case when isnull(a.c_key,'') = '' and b.c_key <> '' then 1 when isnull(a.c_key,'') <> '' and a.c_key = b.c_key then 1 else 0 end) = 1
and (case when isnull(a.m_key,'') = '' and b.m_key <> '' then 1 when isnull(a.m_key,'') <> '' and a.m_key = b.m_key then 1 else 0 end) = 1

--*******************************


The scenario is master file "a" has 3 fields r_key, m_key, c_key. Each of these fields could have NULL or a value.
I want to join it to table b so that if there is a value in table a then join to table b otherwise take all the values from table b.
I want to create a schema with the join like the SQL above in webfocus.
The query above gives me the the expected result but not sure how to convert it into webfocus language when joining 2 master files to create a schema.

SEGMENT=a, SEGTYPE=KU, PARENT=b, CRFILE=b, CRINCLUDE=ALL, CRJOINTYPE=INNER,
JOIN_WHERE=(a.R_KEY EQ b.R_KEY) AND (a.C_KEY EQ b.C_KEY) AND ((IF a.M_KEY EQ '' AND b.M_KEY NE '' THEN 1 ELSE IF a.M_KEY <> '' and a.M_KEY= b.M_KEY THEN 1 ELSE 0) EQ 1) ;, $

The statement above gives me an error.
Is it possible to do something like that or is there another approach that gives me the result as the SQL query.


Thanks,

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

This look like you need to perform a MATCH FILE using AFTER MATCH NEW option

You need to create a define field in the master for your case statement. Then in your JOIN_WHERE, instead of referencing your case statement, you reference your define field.

But, assuming that all you did was remove the field names and this is your actual statement, then it seems like you have over engineered this and can be done with a simpler join. I would construct it like this and call it a day

JOIN_WHERE =( (A.R_KEY IS MISSING AND B.R_KEY IS MISSING) OR (A.R_KEY = B.R_KEY) ) AND ( (A.C_KEY IS MISSING AND B.C_KEY IS MISSING) OR (A.C_KEY = B.C_KEY) ) AND ( (A.M_KEY IS MISSING AND B.M_KEY IS MISSING) OR (A.M_KEY = B.M_KEY) )  

No need to play around with conditional joins. Didn't even need to add the complication in SQL. Not sure why you did.

Thanks Eric.
I had started with the Define fields but the SQL conversion for some reason was using the wrong fields. I am going to look into in more detail and open a CASE if need be.

you are right .. as usual I was over complicating it .
here is the statement I used (modified your suggestion a bit) and it works beautifully. thanks

JOIN_WHERE =
( (A.R_KEY IS MISSING AND B.R_KEY IS NOT MISSING) OR (A.R_KEY = B.R_KEY) )
AND ( (A.C_KEY IS MISSING AND B.C_KEY IS NOT MISSING) OR (A.C_KEY = B.C_KEY) )
AND ( (A.M_KEY IS MISSING AND B.M_KEY IS NOT MISSING) OR (A.M_KEY = B.M_KEY) )