Outer Join on a filtered value

[jotek]

I am creating a report in CR 8.5 against an Oracle 8 database and have two tables which are join together using an outer join. I have an additional field that needs to be filtered in which the user provides the value. The field I am filtering on is on the outer joined table. We need all the values from the driving table to be displayed reguardless of the filtered value. Is there a way to have the filter/Select Expert have an outer join on it. Because with out it I do not get the appropriate data back. Here is the working query if done directly in Oracle.

The problem is the Values1.T$PTYP(+) field:

select Structure_Label.T$LV00,Structure_Label.T$LD00, Values.T$AMNT
from ttffst310707 Structure_Label,
ttffst300707 Values1
where Structure_Label.T$FSTM = Values1.T$FSTM(+)
and Structure_Label.T$LV00 = Values1.T$LV00(+)
and Structure_Label.T$LV00 = Values1.T$LV00(+)
and Structure_Label.T$FSTM = 'CA1300'
and Values1.T$PTYP(+) = 1
order by Structure_Label.T$LV00

I can't use the SQL Designer Query Tool since I need to pass a paramater to the query. Unless you can tell me how to pass a parameter, then I could use the query tool.

I am also trying to avoid using a view, because of a long list of maintance issue. I would like a CR solution.

 

[Ngolem]

See my post earlier " Left Outter Joins revisited..."

 

[jotek]

I had previously read this and it gave me something to try with the isnull solution. The additional problem I have is I have an additional filter which caused that solution to no longer work. I also did not see the final answer on the shared. I have not tried tried the subreport yet but I am not sure that can work given that one of the reports still will not have all the rows in it.

 

[Ngolem]

that post is abreviated I suppose

I will repeat it here

first between 2 tables you would join then on common fields and designate them left outter joins

table1---->table2

now on the right hand table where the filter is...that filtered value should be in your record selection formula and take the form:

(isnull({table2.something}) or {table2.something = value}) and
...rest of the record selection formula...

if you do this then you will bring back all values of
table 1 where

1. there has never been a value in table2
2. there is a value in table 2 that meets the filter criteria

but the problem is that you cannot retrieve records for table1 where there are records in table2 but they don't meet the criteria in the record selection formula.

If you find a way let me know PLEASE!

So you are faced with trying subreports (and this works) or setting a really low criteria to catch a whole raft of bad records, planning to eliminate them later in formulas...but not in the initial record select.

to use a subreport you do this: I will give a simple example where I move table2 out of the main report and create a subreport on it

All I want from table2 is the value of a field called "total"

I link onto the main report in the normal manner. I find that the subreport will execute once...even though there is no records returned...THIS IS A GOOD THING!

There is a single formula field in my report in the detail section...no groups...all other sections suppressed...I am looking for one value if it exists.

WhilePrintingRecords;
numbervar theTotal := 0;

if not isnull({table2.total}) then
theTotal := {table2.total};

total;

REMEMBER....the subreport will execute once even if there are no records returned...so be initializing my total to zero...I will get zero returned even if no records exist.

This is a solution...unfortunately though it calls for many,many hits to the database for a single number that may not exist so it is not very efficient...in my case it is sort of tolerable if there are less than 200 hits...but if my user goes nut and wants all possible results (there are 15,000+ records possible) then he will pay the price...

Like you I don't have the luxury of having a view created since that is under the control of others who don't deem this important enough..and I cannot use SQL Designer because these reports are to be used on the web and SQL Designer doesn't work there.

I hope this explanation clears things up a bit.

Not an easy problem....Jim

 

[Ngolem]

sorry the formula should be:

WhilePrintingRecords;
numbervar theTotal := 0;

if not isnull({table2.total}) then
theTotal := {table2.total};

theTotal;