Linking Databases when one Database is Empty 1

[kcoleman26]

CR 2008

I have a report that pulls from two databases lets
just say Table1 and Table2 they are linked using two fields. Everything seems to work but only if the
Table2 database has info in it.

If a certain field in Table2 is blank and it meets
all the other criteria then I want to see the record
so I make a change.

What is happening is if there is not any info in
Table2 it will just not show up on my report.
The report works if any of the other fields in Table2
have info in it. So 99% of the time the report is effective. I am just trying to get to that 100% mark.


My Select Formula
not ({GIP.RSTAGI} in ["L", "O", "X"]) and
{GIP.VIPGI} in ["C", "M", "S", "V"] and
{TIP.AMODTI} = ""

My SQL Query SELECT "GIP"."LNAMGI", "GIP"."VIPGI", "GIP"."ARDTGI",
"GIP"."RSTAGI", "GIP"."FNAMGI", "GIP"."FIRMGI",
"GIP"."CONVGI", "GIP"."ACTPGI", "GIP"."RMNOGI",
"GIP"."REF1GI", "TIP"."AMODTI", "TIP"."ETATI",
"TIP"."ETDTI", "GIP"."DPDTGI"
FROM "ATHENA"."LMDTA"."GIP" "GIP" INNER JOIN "ATHENA"."LMDTA"."TIP" "TIP" ON ("GIP"."KYDTGI"="TIP"."KYDTTI") AND ("GIP"."SEQGI"="TIP"."SEQTI")
WHERE NOT ("GIP"."RSTAGI"='L' OR "GIP"."RSTAGI"='O' OR "GIP"."RSTAGI"='X') AND ("GIP"."VIPGI"='C' OR "GIP"."VIPGI"='M' OR "GIP"."VIPGI"='S' OR "GIP"."VIPGI"='V') AND "TIP"."AMODTI"=''

Thanks,
KC

 

[lbass]

Change the join to a left outer join, and change the record selection formula to:

isnull({TIP.AMODTI}) and
not ({GIP.RSTAGI} in ["L", "O", "X"]) and
{GIP.VIPGI} in ["C", "M", "S", "V"]

-LB

 

[kcoleman26]

lbass

It worked beautifully...

Thank you
KC