Hi,

I have a join between one focus table TA and onde DB table TB :

JOIN
fieldA in TA TO ALL filedA in TB AS JOIN0
END

Then I have:

TABLE FILE TA
TA.x
Ta.y
TB.z
WHERE TB.a = 2;
ON TABLE HOLD AS RES
END

If I print all lines of TA it shows all lines of TA even those which TB.a <> 2 putting zero ou NULL

My problem is if TA and TB being DB tables (Netezza e.g.) the query generated is:

SELECT T1.x, T1.y,T2.z
FROM TA T1 left outer join TB T2 on T1.fieldA = T2.fieldA
WHERE TB.a = 2

Which exclues the TB lines where TB.a <> 2

IN sql teh way to solve this would be putting the TB.a = 2 condition in the JOIN clause.

I would like to know if there is some way to do this in Webfocus code,so the join would produce the same results either with focus or DB tables


Best Regards.

This message has been edited. Last edited by: <Kathryn Henning>,

quote:

...I would like to know if there is some way to do this in Webfocus code...

Take a look at setting SHORTPATH.

By default it is set to FOCUS.

It sounds like you want to set it to SQL in the Netezza table to Netezza table scenario.

quote:

SHORTPATH

Thanks David, but no luck

I tried to put SET SHORTPATH = SQL

but the generated code continus to put the WHERE coindition in the where sql clause instead of join, and the "zeros" lines are not shown.

Regards.

A.S.

I would also suggest changing

WHERE TB.a = 2;

WHERE TB.a EQ 2;

I've opened a Case to try to overcome this.
I'll keep you on track of any advance.

Thanks.

Here are some hints... ...they might help.

I struggled with joins a few days ago. ( finally had the DB solve it, not WF )
Problem is... ...what WF calls a join... isn't.

It decides wether to do the join based on the query.
Any other SQL would do the join first...

You can force WF to do the join by selecting fields from both masters.

You have JOIN as statement. I think that's a CROSS join ( full other join ).
Try some of the join-options.

I had this ( both DB files )

LEFT JOIN x in A to x in B
WHERE A.y = B.y

Turns out the WHERE is only excuted at query level.

e.g.
TABLE FILE A
PRINT A.x
END

shows all lines from A regardless of the WHERE in the join-statement.

TABLE FILE A
PRINT A.x B.x
END

shows only lines with the WHERE applied.

____________________________________

I came around this. Very useful. ( if performance of reportingserver suffices )


You can actually write your own SQL to HOLD files.

SET ASNAMES = ON

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

TABLE FILE CAR
	SUM MIN.SEATS AS LSEATS
		MAX.SEATS AS HSEATS
	BY	CAR
ON TABLE HOLD AS FOC_SEATS
END

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


-* even insert works..
SQL
insert into BLACKBOX ( CAR , SALES, LSEATS, HSEATS ) VALUES ( 'TESLA' , 80000, 2 , 4 );
-*TABLE ON TABLE HOLD AS H_CAR2
END
-RUN

TABLE FILE BLACKBOX
	PRINT *
END

Good luck,
Dave