Referencing Potention NULL value in selection criteria

[dunlop1975]

I seem to have hit a wall resolving this what I thought to be an easy problem

I need to build a report that will summarize certain procedures (main and sub)based off of patient req#. While a surgery will always have a main procedure they do not always have subs. Also a procedure can be a main or sub.

This system I am accessing, handles these procedures by placing them in 2 different tables : Main Surgery and Sub Surgery. Because not all surgeries have sub procedures, this table will not be populated for some cases (NULL).

This is where my problem lies, as soon as I try to reference the Main and Sub procedure tables to filter for the proper procedures I get no values.

I have tried to isolate the nulls:
i.e

if not(isnull(Sub Surgery.Req#)) then
(
Sub Surgery.Procedure = <Parameter> or
Main Surgery.Procedure = <Parameter>
)
else Main Surgery.Procedure = <Parameter>

Also tried aliases, changing join types, etc..

Here is a crude layout of how the tables are linked


Request Main Surgery
------- ------------
REQ# -->(inner) REQ#
Procedure
\
\ Sub Surgery
-----------
-> (Left REQ#
Outer) Procedure


I know I could easily resolve this using a subreport but this particular report needs to be an Excel extract eventually so am trying to make it as simple as possible for the end user

 

[fisheromacse]

Have you tried linking the Sub Surgery table to the Main Surgery table on REQ#?

 

[lbass]

If you want to show all records for {request.req#}, you cannot use a selection on the subsurgery table, regardless of the join, since even if you account for nulls, you would be able to account for subsurgeries other than the selected surgeries. Also, it looks like you think there could be cases where the main surgery does not meet the criteria, but the sub does, so you wouldn't want to use an inner join there either.

The best way to handle this might be to use a command as your datasource, and set it up like this:

select 'mainsurgery' as tabletype, mainsurgery.req#, mainsurgery.procedure
from request inner join mainsurgery on
request.req# = mainsurgery.req#
where mainsurgery.procedure = {?procedure}
union all
select 'subsurgery' as tabletype, subsurgery.req#, subsurgery.procedure
from request inner join subsurgery on
request.req# = subsurgery.req#
where subsurgery.procedure = {?procedure}

If you need to show all request.req#'s, I would do this a little differently, but it sounds like that is not the case.

The command will include all surgeries of the selected type. With the command, you can distinguish main and sub surgeries by using {command.tabletype} as necessary, since the union all will merge fields that are in corressponding positions in the select clauses.

-LB

 

[dunlop1975]

thanks for the quick replies, I have been called off to deal with a slight emergency in another project so I will be putting this aside for the next few days.

I will update when I have a chance to try this out

thanks!