For the life of me I can't figure this out. I need to do a join between two tables (both RDBMS tables) where I join field A on table 1 to Field B OR field C on table 2. This should be equality checks.

Basically I want the join to be WHERE (T1.A = T2.B) OR (T1.A = T2.C) and I need to handle it within WebFOCUS.

This message has been edited. Last edited by: DW Marker,

How about using CONDIONAL JOIN?

If I was building this, I would probably do it like this:

-Join A to B
-Table file that pulls what I need from A & B and holds it.
-Clear Joins
-Join A to C
-Table file that pulls what I need from A & C and appends it to the hold file from the first step
-table file on the combined hold file that generates my output

That's what I'd do.

Cheers,

Joey

I haven't ever had the need to append to a hold file. Can you let me know what the steps are to do that?

Sure! What you do is start with a FILEDEF, which would look something like:

FILEDEF {HOLDFILE NAME} {physical file name.ftm}

Then you do a table file and hold it as the same name as in your filedef:

TABLE FILE CAR
SUM
SALES
BY BODYTYPE
ON TABLE HOLD AS {HOLDFILE NAME}
END

At this point you have a temporary file sitting around, ready for the next step.

Do another filedef, with the same syntax, but with "(APPEND" at the end. Note that there is no close bracket:

FILEDEF {HOLDFILE NAME} {physical file name.ftm} (APPEND

TABLE FILE CAR
SUM
SALES
BY BODYTYPE
ON TABLE HOLD AS
END

Now if you do a table file on the hold file name, it will contain both sets of data from your first and second queries.

Cheers,

Joey

Using what Joey suggests is the pure WebFOCUS method of achieving what you require, but I would suggest that this is a time when SQL passthru is ideal -

SQL SQL...
  select column1
       , column2 etc. etc.
    from table1 T1
       , table2 T2
   where T1.A = T2.B
      or T1.A = T2.C
;
TABLE FILE SQLOUT
PRINT *
END

If your experience is SQL then why not use your strengths and those of WebFOCUS?

T

My only question regarding using SQL passthru is does that bypass the security in the DBA Security files? If so that is a no go for us as that is how it was determined we would handle security for our 500 external clients as opposed to setting up database views for each of them.

Passthru dos not use the master so yes it would bypass the DBA

Have you looked at the CONDITIONAL JOIN syntax? It should do what you want.

You can write SQL that uses the WebFOCUS metadata (Master, Access) which I assume will not bypass DBA security in the Master. If you set the SQL Engine, the SQL is "passthru", if you do not set the SQL engine, the SQL uses the WebFOCUS metadata.

Though, I think Ginny's suggestion best fits what you want to do.

This message has been edited. Last edited by: Francis Mariani,

Thanks everyone for their direction. The conditonal join did what I needed.