Hi guys, I'm making a conditional join

  
JOIN PRO_COD         IN   PROMOTION TO
     PDI_PRO         IN   PDISCONT AS J4
     WHERE (PDI_IPC  EQ 'L')
     WHERE (PRO_FFV  EQ PDI_FFV)
     WHERE (PRO_DATE EQ &WK_DATE);
END

My program executes normally but in that block of code, it's shown:

UNKNOWN FOCUS COMMAND  WHERE
 BYPASSING TO END OF COMMAND

The three first letter of the fields indicates the table they belong (PRO_COD belongs to PROMOTION - PDI_IPC belongs to PDISCONT)
I'm not getting an error, but I think that my join is not being executed the right way.

Do you know how I could resolve that?

Thank you so much.

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

Try a semicolon after each WHERE statement...

quote:

Originally posted by Francis Mariani:
Try a semicolon after each WHERE statement...

I tried that but the result is the same...

Try one WHERE at a time to determine which one might be the culprit.

Try reading up on the documentation concerning conditional joins. The example in the doc is:

JOIN FILE VIDEOTRK AT MOVIECODE TAG V1 TO ALL 
     FILE MOVIES   AT RELDATE   TAG M1 AS JW1
  WHERE DATEDIF(RELDATE, TRANSDATE,'Y') GT 10;
  WHERE V1.MOVIECODE EQ M1.MOVIECODE;
END

and the AT keywword is essential for conditional joins ... it is not in your join however ...

That's something that's been bugging me ever since I ran into the conditional join feature - why is the syntax totally different from the usual join syntax?

It does seem to contain the same information, a different syntax doesn't seem to make sense. It just seems to add a (superfluous) FILE keyword and turns the order of columns and tables around.

I suppose we could just start using this syntax for unconditional joins as well (just leave out the WHERE-clauses). Would that have any drawbacks? An obvious one comes to mind: the Report Editor, but I rarely use that anyway (I don't like the results much).

GamP is right, but I did not want to use the new syntax for two reasons:

1. All the programs of my installation don't use that syntax (JOIN FILE AT...) so I thought that maybe the conditional join is performed in a different way

2. Previously of my JOIN, I declared another conditional JOIN just using one WHERE (as Francis said) and I didn't get that messagge.

But I going to try the new structure of conditional JOIN and post what I get.

Thank you

I missed the AT issue.

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.

You must use the AT.

As you guys suggested, I used this structure to perform my JOIN:

JOIN FILE PROMOTION AT PRO_COD  TAG V1  TO ALL
     FILE PDISCONT  AT PDI_PRO  TAG M1  AS J4
     WHERE (M1.PDI_IPC  EQ 'L');
     WHERE (V1.PRO_FFV  EQ M1.PDI_FFV);
     WHERE (V1.PRO_DATE EQ '&WK_DATE');
     WHERE (V1.PRC_PRO  EQ &WK_DEP_PRO);
END

Now I'm not getting the messagge.
Hope it helps someone with the same problem.

Thank you

If a single WHERE is supported, just combine the several conditions into a single (equivalent) WHERE phase (as you would for SQL) --

   WHERE (PDI_IPC  EQ 'L')
   AND (PRO_FFV  EQ PDI_FFV)
   AND (PRO_DATE EQ &WK_DATE)
   ;