Getting info from 2 tables depending on value 2

[FatEric]

Hi guys,

I'm currently making a report that has three tables in the database design. The first table contains a date (for a whole year), a name, a number, ...
The other two tables contain other values then name, number, ... In one of these tables you have all the dates from the beginning of the year up to today. In the second you find all dates from tomorrow till the end of the year.

So i want to show all dates of this year, and if the date is less or equal than today, show fields from the first table, else show fields from the second table.

So I linked the main table to the other two tables with left joins. But when I run the report, nothing is showed.

Can somebody help me solve this problem?

Many thanks in advance.

FatEric

 

[Madawc]

Have you been doing selections on the other two tables? This destroys the effect of 'Left Outer' because the selection will hit a null and then stop.

If you include the possibility of a null in your selections that should fix it.(Use Isnull). Or else be less selective and choose the details once you have them.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

As long as you linked FROM the main table TO each of the other tables using a left join, and you use NO selection criteria on the two left joined tables, this should work.

-LB

 

[FatEric]

Hi both,

I indeed was doing some selections in the two other tables (not in the main table). And I have to do so. In the main table are only dates, name, number. In the other to are for each date a code. What I was trying to do was to display only some codes.

So for example. Main table has these values:
22/06/2008 NAME NUMBER
23/06/2008 NAME NUMBER
24/06/2008 NAME NUMBER

extra table 1:
...
22/06/2008: CODE A
23/06/2008: CODE B

extra table 2:
24/06/2008: CODE C
...

So I only want to show in the report the dates with for example Code A and Code C, but not those with Code B. Therefor I made a selection on the Code (with IN("A", "C")).

Is there a solution do to this?

Thanks in advance,
FatEric

 

[Madawc]

You could suppress the detail lines with Code B. Or do an IsNull test first, before attempting the A-or-C test.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[FatEric]

Thanks Madawc,

I thought of that one too, but didn't know how if it was possible. I have tried it and made the supress formula dependent. So that one worked. But then I also had to edit the running total fields (because they where still showing the total of all details. That has worked too.

Thanks very much both of you guys.

Stars earned for sure.

FatEric

 

[lbass]

If you have a CR version where you can use a command, there is a way to implement selection on a left-joined table in the From clause (instead of the where clause) that allows you to maintain the left join while selecting on the right hand table. If you are interested in this method, please copy your SQL from database->show SQL query into this thread (assuming all of your criteria are passing from your record selection formula to the query).

-LB

 

[FatEric]

Hi lbass,

I currently using CR9 (license from the company I work for). I'm not using any selection criteria's at the moment. I suppressed the detail lines I don't won't to see with a formula. Beneath you find the SQL:

SELECT DISTINCT "TEMP"."TPGUDT", "TEMP"."TPPENR", "TEMP"."TPNAM1", "BWEGPF"."BWBWSL", "BWEGPF"."BWKGBW", "VDISPF"."VDBWSL"
FROM {oj ("ADMIN"."GISOSEK"."TEMP" "TEMP" LEFT OUTER JOIN "ADMIN"."PZW8DTA"."BWEGPF" "BWEGPF" ON (("TEMP"."TPFINR"="BWEGPF"."BWFINR") AND ("TEMP"."TPPENR"="BWEGPF"."BWPENR")) AND ("TEMP"."TPGUDT"="BWEGPF"."BWBWDT")) LEFT OUTER JOIN "ADMIN"."PZW8DTA"."VDISPF" "VDISPF" ON (("TEMP"."TPPENR"="VDISPF"."VDSUCH") AND ("TEMP"."TPFINR"="VDISPF"."VDFINR")) AND ("TEMP"."TPGUDT"="VDISPF"."VDGVDT")}
ORDER BY "TEMP"."TPPENR"

Thanks,
FatEric

 

[lbass]

What does the SQL look like with the criteria built in to the record selection?

-LB

 

[FatEric]

Hi lbass,

I have managed to make the file. I have now joined the three tables (one main and two secondary tables) together. In my record selection I now have but the selection criteria. There was the problem. I forgot to but the ELSE TRUE criteria for the second secondary table.

The sql now stands is as follows:

SELECT DISTINCT "TEMP"."TPGUDT", "TEMP"."TPPENR", "TEMP"."TPNAM1",
"BWEGPF"."BWBWSL", "BWEGPF"."BWKGBW", "VDISPF"."VDBWSL", "BWEGPF"."BWAKKZ"
FROM {oj ("ADMIN"."GISOSEK"."TEMP" "TEMP" LEFT OUTER JOIN
"ADMIN"."PZW8DTA"."BWEGPF" "BWEGPF" ON (("TEMP"."TPFINR"="BWEGPF"."BWFINR")
AND ("TEMP"."TPPENR"="BWEGPF"."BWPENR")) AND
("TEMP"."TPGUDT"="BWEGPF"."BWBWDT")) LEFT OUTER JOIN
"ADMIN"."PZW8DTA"."VDISPF" "VDISPF" ON (("TEMP"."TPPENR"="VDISPF"."VDSUCH")
AND ("TEMP"."TPFINR"="VDISPF"."VDFINR")) AND
("TEMP"."TPGUDT"="VDISPF"."VDGVDT")} ORDER BY "TEMP"."TPPENR"

This on is working for me!

Thanks very much for all your help. I will need it in other reports to I guess...

Cheers, FatEric