using null date values 3

[robhc]

I have two tables joined together with a left outer join and am trying to use isnull(obsdate) to find records in the left table that are missing a corresponding record in the right table, with Obsdate being a date/time field in the right table. I am choosing "default value for nulls" in the formula editor.

I am not finding any records. Any suggestions?

CR - 11 user

 

[IanWaterman]

If you replace nulls with default value Isnull will not return any data.

Also make sure that in report options

Convert Database Null values to Default is unchecked.

Ian

 

[CrystalizeCanada]

Hi,

Using an outer join and then filtering for isnull({yourfield}) is the correct way to isolate your mismatched records (if any exist).

The reason it may not be working is that in your report options (under File Menu) you may have the option 'convert database null values to default selected'. You want Nulls in this scenario since you are searching for Nulls - you don't want them converted to anything else.

Let me know if this is the problem

Thanks
Gordon BOCP
Crystalize

 

[robhc]

thank you both for your reply.

It sounds like both of you are saying to choose "exceptions for nulls" instead of "default value for null" in the formula editor. I changed it back and unchecked "convert NULL values to default".

It is now finding those right table records that have a blank date field but not any missing records (in left table but not in right). It seems I have an additional problem with my joinder or my select criteria.

The join is "not enforced" and again it is a left outer join linking. Basically, I am trying to find adult female patients who have or haven't had a Mammogram. If there was a mammogram, it would be in Table B along with a lot of other different clinical observations. Including or excluding a "isnull", I am still missing the records where table a is true but there is no record in B. Here is my query. Observation #71 is Mammograms.

the join is
Table A table B
person --------------->person
adult female observation ID#
observation date

If I omit the null, the selection criteria would be
{Table A.adult female} = "Y" AND
(not ({table B.obsID#}<71 or {table B.obsID#}>71)

Sorry - I know I might need a new thread on this, but perhaps the null is still needed but in a different form than I was using.


CR - 11 user

 

[lbass]

You cannot add criteria that references tableB and still maintain the left join. There are two ways of handling this. You could remove the criteria and just check for females using the Table A field, and then create a formula in the field explorer like this:

if isnull({table B.obsID#}) or
{table B.obsID#} <> 71 then
0 else 1

Then insert a group on the patient and go to report->selection formula->GROUP and enter:

sum({@hadmamm},{table.patient}) > 0

The other approach is to use a command as your datasource, where you can add your criteria into the from (not the where) clause.

-LB

 

[robhc]

thanks LBass.
No wonder. I thought the outer join would be handled like a subreport.

The problem with not limiting the observations in the record selection criteria is that there will be millions of observations on these patients that I will have to initially pull into the report (since can't limit by date either). I will give it a shot though and let it run. I might try to figure out the second idea too but that is pretty advanced for me.

I thought about using a subreport but I also need to use a calculated mammo "due date" for those who had a mammo in another selection criteria.



CR - 11 user

 

[lbass]

The command approach is not that complex and would save lots of report running time. Are you working with a lot of tables? You could set up a dummy report that uses all the relevant tables, with the necessary fields placed in the report header section, and then copy the resulting SQL query from database->show SQL query into a new report, using "Add command" instead of tables. Then add the selection criteria into the from clause. If you want to try this, you could paste the query here, and I could help you with adding the criteria. You can add parameters, too, maybe for dates?

-LB

 

[robhc]

Thanks for your encouragement and generous offer. I'll give it a try tomorrow...

CR - 11 user