Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE OPENED] FOCUS JOINing using LIKE of two fields - not possible?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE OPENED] FOCUS JOINing using LIKE of two fields - not possible?
 Login/Join
 
Virtuoso
posted
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,


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
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


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Virtuoso
posted Hide Post
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...


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
Wep,

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


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Master
posted Hide Post
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.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Virtuoso
posted Hide Post
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?
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
@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.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
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.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Master
posted Hide Post
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.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Platinum Member
posted Hide Post
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.


Cheers,
H.

WebFOCUS 8.1.05M
Oracle 11g - DB2
RedHat
 
Posts: 115 | Location: Brighton UK | Registered: February 19, 2005Report This Post
Virtuoso
posted Hide Post
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...


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE OPENED] FOCUS JOINing using LIKE of two fields - not possible?

Copyright © 1996-2020 Information Builders