I'm finding myself in a situation where I'm defining joins in the text editor and WebFOCUS is picking up DIFFERENT columns to join on than the ones I explicitly specified. The columns it picks start with the name of the column I specified.

For example, in table GERTYP I have columns:
GERTYPE
GERTYPEAANT

If I specify:

JOIN
 GERTYPE IN stuklst TO
 GERTYPE IN gertyp AS J0
 END

If I then open that join in the join-editor I find WebFOCUS actually joined on GERTYPEAANT! I explicitly told it to join on GERTYPE.

How do I turn that off?

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

Neat FOCUS/WebFOCUS feature called shortest unique truncation

Try using

SET FIELDNAME=NOTRUNC

quote:

I explicitly told it to join on GERTYP

You don't have a column called GERTYP in table GERTYP, you have GERTYPE and GERTYPEAANT. Do as JG suggests or, even better, start using the complete column names.

quote:

Originally posted by Francis Mariani:

quote:

I explicitly told it to join on GERTYP

You don't have a column called GERTYP in table GERTYP, you have GERTYPE and GERTYPEAANT. Do as JG suggests or, even better, start using the complete column names.

That was a typo, as you can see from my earlier examples. The names are confusing too, that doesn't help.

quote:

Originally posted by JG:
Neat FOCUS/WebFOCUS feature called shortest unique truncation

I don't agree with that opinion, it's not neat, it's plain dangerous!

A small typo can cause you to get subtly wrong data (from a different column), which can go unnoticed for quite a while and could have implications. For example, if you're working on a financial report involved in decisions.

It's much better to get an error that the column couldn't be found than substituting a "most likely" candidate column if no match was found and return wrong results.

Features like these tend to get deprecated, and for good reasons.

It's been part of FOCUS and WebFOCUS for a very, very long time.

The earliest reference I can find is 1998 but it predates that.

In your case this appears to be a bug because you have 2 columns

GERTYPE & GERTYPEAANT

Meaning GERTYPE is a unique truncation of GERTYPEAANT
But you have an untruncated column name matching the truncated name.

Bad Practice to be honest.

the error if there is a none unique truncation is

(FOC016) THE TRUNCATED FIELDNAME IS NOT UNIQUE : truncated name

The simplest solution here is to have, and always have unique names, this is the least dangerous option.

Are you saying it is bad practice to have column names GERTYPE & GERTYPEAANT? That people should change their database design practices because of WebFOCUS? Or are you referring to the typo in my earlier post, about which I already explained that it was just a typo in the message (and which I corrected by now)?


This "shortest unique truncation" feature doesn't seem to have much merit, while it obfuscates FOCUS code and is a big gun aimed at your feet in the case described. IMHO it should be disabled by default. That's what happens to foot-guns in other products.


For example, in Postgres (the database) there used to be (and still is) a setting named 'add_missing_from_clause'. That setting, when enabled, guesses table names that were referenced in the query but that are not present in the FROM-clause. Someone several decennia ago thought that would be a good idea.

The result was that people making a typo in a table-name or alias in a column-reference found their queries turned into a self-join resulting in an undesired Cartesian product!

Thankfully the Postgres developers had the sense to disable that option by default. I've been using Postgres for over 10 years now, and that feature's been off for as long as I can remember.

That's the sensible thing to do with this "shortest unique truncation" feature.

You should understand that changing defaults can cause many problems, and doubt that IBI will ever do that. So put the setting in your server profile.

As I said.

In your case this appears to be a bug.

You need to raise a case with IBI

quote:

The earliest reference I can find is 1998 but it predates that

The earliest reference I can remember is in PC Focus version from 1985 (or was it 1984). Very nice functon, but I do understand that newbies get a bit confused.
An other good reason to do some training and read the FM.

What I don't get is why you all consider this to be a good feature. Can you elaborate on what's good about it? It goes against everything I've learned about good design paradigms, so I'm really quite curious what your arguments in favour are.