I have two FOCUS files that I'd like to JOIN like this:

JOIN CLEAR *
JOIN LEFT_OUTER
	FILE BOM_EXPLOSION AT FUNCTION TAG b TO UNIQUE
	FILE BOM_FUNCTIONS AT FUNCTION_MASK TAG f AS J0

	WHERE b.BREADCRUMBS LIKE f.FUNCTION_MASK;
END

The left-hand FOCUS file is the result of a complicated hierarchical SQL query (360+ lines of SQL) that I'd really prefer not to duplicate. This side builds a breadcrumbs string according to the hierarchical level of a node, for example:

Omega.Romeo.Delta.Epsilon.Romeo

The right-hand FOCUS file contains patterns with LIKE wildcards (% and _) to put sort-of a template over the left-hand side results. For example:

%Romeo.%Epsilon%

Hence, for this example the comparison boils down to:

WHERE 'Omega.Romeo.Delta.Epsilon.Romeo' LIKE '%Romeo.%Epsilon%'

Both values in the above are from their respective fields.

However, WebFOCUS complains with:

(FOC36251)  SYNTAX ERROR IN LIKE OPERATOR

I've also tried this with a Cartesian product on dummy fields and the same WHERE-clause in the TABLE request, with the same error as above...

I don't see any possibility to omit the LIKE comparison between two fields here, which FOCUS just plain refuses to do.

There is the option to HOLD the results to temporary tables in one of the RDBMSes available to me and then do the JOIN there - SQL allows JOINing two fields on a LIKE expression.

I hope that there are better solutions than that.
Does anyone have ideas how to solve this?

I'm starting to think that perhaps I need to write a custom subroutine for this (in C, most likely) that takes 2 string parameters and returns a boolean (or rather, a long integer) result. It's going to be interesting writing a LIKE parser though!

EDIT: In the end we had to fall back to HOLDing the files in temporary tables in one of our RDBMSes just to be able to perform this JOIN.

The custom function didn't work out, because it returns its result as a parameter to the function, which is inaccessible to expressions in a conditional JOIN's WHERE clause; That would go something like: WHERE MYFUNCTION(TABLE_A_COL1, TABLE_B_COL2, RESULT) EQ ...ehrm?
It's probably possible to first create a Carthesian product, add a COMPUTE with the LIKE-function result and reduce the results in a WHERE TOTAL, but that's a lot of hoops to jump through!

Creating a couple of stored procedures (we have a few variations, heavily parameterised) that produce these results is another option, but Oracle stored procedure calling conventions are so outlandish that I decided to postpone that a bit. It's been ages (well, >15 years) since I had anything to do with Oracle SP's and in this case we're not in charge of that database...

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

Hi Wep ;-)

To make the join work like you want it to...
...just type SQL !

Works like a charm.
You can even do inserts and deletes.

e.g.

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

TABLE FILE CAR
	SUM MIN.SEATS AS MIN_SEATS
		MAX.SEATS AS MAX_SEATS
	BY	CAR
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS FOC_SEATS
END

-* join
SQL
SELECT
	FOC_CAR.CAR , SALES , MIN_SEATS , MAX_SEATS
FROM FOC_CAR
INNER JOIN FOC_SEATS
ON FOC_CAR.CAR = FOC_SEATS.CAR;
TABLE HOLD AS BLACKBOX
END


-* insert
SQL
insert into BLACKBOX ( CAR , SALES, MIN_SEATS, MAX_SEATS ) VALUES ( 'TESLA' , 80000, 2 , 4 );
END
-RUN

TABLE FILE BLACKBOX
	PRINT *
END

Yeah, I would if the SQL would be more maintainable, but one side of this join is the result of a pretty darn huge SQL query that gets used in several places.

I could copy that, but then I would have to maintain another copy of that code - I'd rather not.

Still, that is one of the remaining options.

Silly that with the FOCUS LIKE-operator the pattern absolutely has to be a constant...

Wep,

you could use a .fex to generate that one-side of the join and include it when needed?

what if you were to hold it as SQL? ON TABLE PCHOLD FORMAT SQL...

Does that make a difference? I've found that the sql generates differently (It may just be that its formatted better) when I hold like that. Maybe it will create the appropriate SQL that way.

Or do you need to use CONTAINS instead of like?

Long Shot... but sometimes you get surprised.

Why would

JOIN LEFT_OUTER
FILE BOM_EXPLOSION AT FUNCTION TAG b TO UNIQUE
FILE BOM_FUNCTIONS AT FUNCTION_MASK TAG f AS J0

WHERE b.BREADCRUMBS LIKE f.FUNCTION_MASK;
END


yield a "unique" match from the second file for each row of the first?

@eric; Doesn't FORMAT SQL just write it to a database somewhere?

That is indeed one of the options I'm looking into, but I want to be absolutely certain that those TABLE requests will be written to TEMPORARY tables that are only visible to the current user's (HTTP) session, or we'll get conflicts.

That might still cause issues if the same user opens multiple browser tabs with the same report on different part-no's, so perhaps I even need to generate HOLD names for both tables then...

Still, that may be easier to achieve than writing my own like function, partially because the documentation for doing the latter is lacking a lot of important details.

Oh, and CONTAINS doesn't do wildcard patterns I think, or does it?

We have patterns like '%Capstrip.%Composite%', which contains a wildcard in between pattern sections...

@j.gross; Now that you mention it, the reason for this whole exercise is to group BOM-items together, so they won't be unique! Not that that changes the problem in any way.

It's a shame that LIKE apparently can't be used this way, as it would have solved the problem nicely.

Thanks for the suggestions so far, keep them coming.

quote:

Oh, and CONTAINS doesn't do wildcard patterns I think, or does it?

Its been a while since I've had to use a contains... but the FOCUS wildcard is $*. Doesn't that work with contains? maybe I'm stretching... Actually... I think you could just use EQ. You might need to do a string replace for all of your '%' to '$*' and '_' to '$'. But I believe the following should work:

 
WHERE FIELDA EQ '$*Romeo.$*Epsilon$*'

Not sure if that translates to SQL though... might still be Out of Luck...

quote:

Doesn't FORMAT SQL just write it to a database somewhere?

Format sql just creates a SQL file to execute against the database later. I've started using it lately to create subqueries. It works really well. It's greatly improved the response time of a number of queries for me. Write it to foccache, then you can see the SQL generated.

quote:

Originally posted by Wep5622:
Yeah, I would if the SQL would be more maintainable, but one side of this join is the result of a pretty darn huge SQL query that gets used in several places.

I could copy that, but then I would have to maintain another copy of that code - I'd rather not.

Still, that is one of the remaining options.

Silly that with the FOCUS LIKE-operator the pattern absolutely has to be a constant...

Also, you may have the option of creating the query as a view. If you are dealing with a BOM, you might be better off creating a hierarchical join anyways. You could Parse you're period delimited field into Parent/Child fields.

Or, just take that portion of the query that you don't want to copy because it would be difficult to maintain in multiple places and put it in a view. Then you can use it anywhere you like and only need to maintain it in one place.

No more ideas on the like - What about having that megasql as a stored procedure? At least that way it'd be maintained in only one place.

quote:

Originally posted by eric.woerle:

quote:

Oh, and CONTAINS doesn't do wildcard patterns I think, or does it?

Its been a while since I've had to use a contains... but the FOCUS wildcard is $*. Doesn't that work with contains? maybe I'm stretching... Actually... I think you could just use EQ. You might need to do a string replace for all of your '%' to '$*' and '_' to '$'. But I believe the following should work:

 
WHERE FIELDA EQ '$*Romeo.$*Epsilon$*'

I just found this in the documentation, which explains why my tests failed to match:

quote:

mask
Is an alphanumeric or text character string you supply. The wildcard characters that you can use in the mask are the dollar sign ($) and the combination $*. The dollar sign indicates that any character in that position is acceptable. The $* combination allows any sequence of zero or more characters. The $* is shorthand for writing a sequence of dollar signs to fill the end of the mask without specifying a specific length. This combination can only be used at the end of the mask.

SET EQTEST = WILDCARD

TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY IS '$*A$*'
   OR COUNTRY EQ '$*A$*'
   OR COUNTRY CONTAINS '$*A$*';
END

 0 NUMBER OF RECORDS IN TABLE=        0  LINES=      0

That's a shame, because yours was a very promising suggestion towards solving this problem.

On to try out other options...