Im new with WebFocus, and having trouble getting my head ar...

[David McKinney]

Im new with WebFocus, and having trouble getting my head around certain things. For what its worth, we are on version 8.2.0.4 GEN 48

When I try to join these 3 tables with INNER JOINs, I can only get MYID from MYMAIN, and the other two give me nothing. To be clear, I get rows output as my result, with an id in MYMAIN.MYID, but nothing in the other 2 MYID fields. Plus the value of the Compute_1 field is different. Coming from a SQL background, I dont see how this can be so. For info, HTABLE1R and HTABLE2R are HOLD files.

I cant send you all the code as its on an internal isolated network.

Any clues or hints Thanks!

JOIN INNER MYID IN MYMAIN TO UNIQUE MYID IN HTABLE1R TAG H1 AS J1

JOIN INNER MYID IN MYMAIN TO UNIQUE MYID IN HTABLE2R TAG H2 AS J2

END

TABLE FILE MYMAIN

SUM COMPUTE Compute_1/A20=IF MYMAIN.MYID EQ H1.MYID THEN same ELSE different ;

BY MYMAIN.MYID

BY H1.MYID

BY H2.MYID

-RUN

[Manoj Chaurasia]

Hi David

Welcome to myibi Make sure the formats of the MYID fields match. You can put this command at the bottom of the report FF and when you run the report do a view page source ans scroll to the bottom and it will display the field formats. Or if you have master files for the hold files you can look there as well.

[David McKinney]

Hi Chuck.

Thanks for the welcome, and for the tip regarding FF. The 3 fields in question are all A6.

As Im talking about inner joins here, I would never expect to see different. Either there is a mismatch in the join, in which case the row is excluded, or there is a match, in which case the join field is by definition the same on both sides.

[David McKinney]

I ran the report with a SQL trace. I had errors relating to the DB Engine not supporting RANKing functions (which I was using in my HOLD files). So I removed any such functions.

When I had simplified as much as possible my HOLD files, I re-ran the report with a trace, and still have the same phenomenon (different) but the trace also produced the following alert.

AGGREGATION NOT DONE FOR THE FOLLOWING REASON:

NON-SQL SEGMENT IN HIERARCHY (OTHER INTERFACE PRESENT)

I suspect that this might have something to do with the behaviour Im observing

[Martin Yergeau]

Seems that MYMAIN is an SQL table where HTABLE1R and HTABLE2R are focus table (HOLD files), you better need to index them prior to JOIN them to MYMAIN

Meaning that when HOLDing them you should have something such as this for both of them.

Then try back

TABLE FILE abc

....

BY MYID

...

ON TABLE HOLD AS HTABLE1R FORMAT FOCUS INDEX MYID

END

Also try moving your COMPUTE as a DEFINE instead to be sure that each JOINed line will be tested which I think, is the best in your case.

[David McKinney]

Thanks Martin,

In fact, I already have FORMAT FOCUS INDEX MYID in both of my hold files. (Ive tried with and withoutno change.)

I was hoping that having the INDEX would make WF realise that these HOLD files are unique by MYID, but I dont see a way of saying that I want a Unique Index

I say this because my JOINS are currently UNIQUE. If I use just one HOLD table, but make the join MULTIPLE, then all works as expected (i.e. same) However if I then add back the second HOLD table also with a MULTIPLE (ALL) JOIN, I get an error that ALL SORT KEYS ARE NOT IN A SINGLE TOP-TO-BOTTOM SEGMENT PATH.

I figured that this is probable due to WF considering that I have 2 one to manys, whereas I really have 2 one to ones.

I may be barking up a few wrong trees, but its all driving me bonkers to be honest. It shouldnt be this hard.

Regarding the source data, I believe its either DB2 or Hyperstage, but Im not sure to be honest. All the masters say IBFSFILE.

[David Beagan]

I wonder if you get the same result when you run this in a fex:

SQL SELECT

MYMAIN.MYID

,H1.MYID

,H2.MYID

,CASE WHEN MYMAIN.MYID=H1.MYID THEN 'same' ELSE 'different'

END

FROM MYMAIN

,HTABLE1R H1

,HTABLE2R H2

WHERE MYMAIN.MYID=H1.MYID

AND MYMAIN.MYID=H2.MYID

END

[David McKinney]

Hi David. As soon as I start trying to use SQL I get the following error.

[iBM][CLI Driver][DB2] SQL0199N The use of the reserved word ONLY following is not valid. Expected tokens may include: FIRST NEXT.

Now the ONLY is not anything I have written. Ive done just a most basic test

SELECT H1.MYID FROM HTABLE1R H1

As I say, Im new to Webfocus, but Im not new to SQL. (Also I havent worked with DB2 much.)

[Martin Yergeau]

Try with specifying from which connection to run against

ENGINE SQLDB2 SET DEFAULT_CONNECTION ValidConnectionName

 

SQL SQLDB2 PREPARE SQLOUT FOR

SQL SELECT

MYMAIN.MYID

,H1.MYID

,H2.MYID

,CASE WHEN MYMAIN.MYID=H1.MYID THEN 'same' ELSE 'different'

END

FROM MYMAIN

,HTABLE1R H1

,HTABLE2R H2

WHERE MYMAIN.MYID=H1.MYID

AND MYMAIN.MYID=H2.MYID

END

[David Beagan]

MartinY, Im thinking that the connection information should be picked up from the metadata because this is not SQL passthru.

David McKinney, could you try it like this without the CASE statements END on a line by itself:

SQL SELECT

MYMAIN.MYID

,H1.MYID

,H2.MYID

,CASE WHEN MYMAIN.MYID=H1.MYID THEN 'same' ELSE 'different' END

FROM MYMAIN

,HTABLE1R H1

,HTABLE2R H2

WHERE MYMAIN.MYID=H1.MYID

AND MYMAIN.MYID=H2.MYID

END

[Toby Mills]

Hey David,

If you just use this, what happens:

JOIN MYID IN MYMAIN TO MYID IN HTABLE1R AS J1

JOIN MYID IN MYMAIN TO MYID IN HTABLE2R AS J2

TABLE FILE MYMAIN

PRINT

MYMAIN.MYID

HTABLE1R.MYID

HTABLE2R.MYID

COMPUTE

Compute_1/A20=IF MYMAIN.MYID EQ HTABLE1R.MYID THEN same ELSE different ;

END

What were trying to do is:

Get away from SUM statement for now.

Also avoid sorting

Since youre going for all INNER, maybe you could change your JOIN to say:

JOIN MYID IN MYMAIN TO MYID IN HTABLE1R AS J1

JOIN HTABLE1R.MYID IN MYMAIN TO MYID IN HTABLE2R AS J2

Maybe thatll make WF think the segments are all in a row (one on top of the other).

[David McKinney]

Hi Martin, I already have a parsing error with ENGINE SQLDB2.

Parsing error: Encountered ENGINE SQLDB2 at line 1, column 1.

[David McKinney]

Sorry for being slow to respond. As I work on an isolated network, to test your suggestions I have to retype them and convert names of tables and fields. Also to give you my feedback, i have to retype also. Copy and paste is not available.

SQL really seems to be problematic here.

For example, this works

SQL

SELECT MYID FROM MYMAIN

END

But if I try this

SQL

SELECT MYID, count(1) FROM MYMAIN GROUP BY MYID HAVING COUNT(*)>1;

END

I get the following error.

[42601] [iBM] [CLI Driver] [DB2] SQL0199N The user of the reserved word ONLY following is not valid

[David McKinney]

Hi TobyIll cut to the chase

different

different

different

 

 

(This is using the code below

JOIN MYID IN MYMAIN TO MYID IN HTABLE1R AS J1

JOIN MYID IN MYMAIN TO MYID IN HTABLE2R AS J2

TABLE FILE MYMAIN

PRINT

MYMAIN.MYID

HTABLE1R.MYID

HTABLE2R.MYID

COMPUTE

Compute_1/A20=IF MYMAIN.MYID EQ HTABLE1R.MYID THEN same ELSE different ;

END)

[David McKinney]

So starting from my original code

JOIN INNER MYID IN MYMAIN TO UNIQUE MYID IN HTABLE1R TAG H1 AS J1

JOIN INNER MYID IN MYMAIN TO UNIQUE MYID IN HTABLE2R TAG H2 AS J2

END

TABLE FILE MYMAIN

SUM COMPUTE Compute_1/A20=IF MYMAIN.MYID EQ H1.MYID THEN same ELSE different ;

BY MYMAIN.MYID

BY H1.MYID

BY H2.MYID

-RUN

 

IF I include the same joins additionally in where clauses

WHERE H1.MYID EQ MYMAIN.MYID;

WHERE H2.MYID EQ MYMAIN.MYID;

I get the equivalent of the INNER JOIN I was looking for.

But this seems like it should be superflousthe INNER JOIN should have taken care of this.

What I note is that it seems to be equating blanks / nulls / missing with my join keys.

So it will output

A A (blank)

A (blank) A

A (blank) (blank)

but it will not output A A B for example.

Could this be linked to a server setting default somewhere

[David McKinney]

This discussion seems relevant to me. But if that is so, how could this be news to more experienced developers

[Alban Hertroys]

What about:

JOIN INNER MYID IN MYMAIN TAG M TO UNIQUE MYID IN HTABLE1R TAG H1 AS J1

JOIN INNER M.MYID IN MYMAIN TO UNIQUE MYID IN HTABLE2R TAG H2 AS J2

END

TABLE FILE MYMAIN

SUM COMPUTE Compute_1/A20=IF M.MYID EQ H1.MYID THEN same ELSE different ;

BY M.MYID

BY H1.MYID

BY H2.MYID

-RUN

 

Im a bit suspicious of the unqualified JOIN column there.

Alternatively, theres a default setting in WF that tries to guess what column name you meant if its doesnt match any actual columns in the request, which IMO is a terrible idea.

If you didnt already, Id suggest adding this to your edasprof:

SET FIELDNAME = NOTRUNC

[Martin Yergeau]

JOINs may also be affected by :

SET ALL = <OFF / ON / PASS>

SET SHORTPATH = <FOCUS / SQL>

[David McKinney]

Hi Alban, the additional qualifiying didnt help (identical output). Regarding edasprof, I dont have access to that.

[David McKinney]

JOINs may also be affected by :

SET ALL = <OFF / ON / PASS>

SET SHORTPATH = <FOCUS / SQL>

 

Im not sure what to do with thisor where Ill research it, but do you have in mind a particular setting, at a particular place

[David McKinney]

SET ALL is of course only for focus managed joins. It has no bearing on sql and requests processed in its entirety by the host dbms. SET ALL=PASS has meaning for all kinds of databases, provided focus does manage the join.

You could leave the SET ALL=OFF in the profile. If you need all parent records, you and your programmers could either specify SET ALL=ON or use the JOIN LEFT_OUTER syntax. Both have the same results.

Leaving it OFF ensures that the ALL parameter is used where it is really needed, and not accidentally produce the right results.

 

Im thinking I need to have SET ALL = OFF Unfortunately I just tried this and it doesnt help.

[Toby Mills]

Hi David

A couple of thoughts.

First, I notice your sample code doesnt have the word END at the end. Im guessing this is because you have some other code beneath the BYs but didnt want to bother hand typing all that in.

Do you have some WHERE tests down there that we dont see

Next thing - generally speaking, Ive seen FOCUS/WebFOCUS have problems with JOINs when you dont mention a field in each file somewhere in the request. In your case, youve got the H1 in the COMPUTE and both of the H1 and H2 mentioned in the BY field. The reason I asked you to just PRINT them out was to make sure those segments were activated. Your most recent observation that including those fieldnames in a WHERE statement made it work - still leads me to believe that the activation might not be happening.

Using PRINT instead of SUM usually helps you see the values and will often give a hint as to whats happening. When you just did the PRINT without sorting, was your Compute_1 field ever set the way you thought it should be Did the output show the H1 or H2 column was always blank (or nodata) when you thought it should have been filled in

Did it look like this:

Mymain H1 H2 Compute_1

bob bob bob same

Or was it like:

Mymain H1 H2 Compute_1

bob . . different

If you have WHERE tests in there, thatd be important for us to see.

Because youre using mixed sorts of databases, WebFOCUS actually is probably working pretty hard to pull those together.

To make sure Im tracking with you, these are the files youre using in the JOINs and the type of data:

MYMAIN - DB2 table.

HTABLE1R - FOCUS database with an index on MYID

HTABLE2R - FOCUS database with an index on MYID.

And all of your columns have the exact same format for MYID right Like all of them are A10s for example.

Is that all correct

Interesting case. Im pretty sure were just not seeing something right in front of us.

[David McKinney]

Hi Toby (and all)thanks for your suggestions. I should have a consultant from IBI coming to have a look. Unfortunately Ive spent far too much time on this, and so have put all my investigations on hold until the consultant comes. If he manages to resolve the issue, Ill post details, I promise.

[Toby Mills]

Thanks for the update David.

Good luck!

Toby