Linking tables

[smibarb]

I am building a simple report that uses a left outer join to link instrument table (bu_t) to result table (rslt_pat_t). I am not getting the expected results. I expect to get every instrument regardless of whether there are results assoicated with that instrument or not. I am only getting instruments that have results in the result table.

I have experienced and posted this problem before (thread149-1154885). The only response addressed this as an "SQL" and "Command Object" issue. I am only a Crystal Report user (not a programmer) and hope that a solution can be found within the CR application. I am using CR v.11. I am posting the SQL query found within CR (by using the "Show SQL query" function), but please remember this is built by the application and I do not have the knowledge to edit it outside the application.


SELECT "bu_t"."bu_serial", "rslt_pat_t"."rslt_date_time",
"rslt_pat_t"."nu_name", "rslt_pat_t"."site_abbrev", "rslt_pat_t"."pat_id",
"rslt_pat_t"."pass_flg", "rslt_pat_t"."rslt"
FROM "lfs_main"."dbo"."bu_t" "bu_t" LEFT OUTER JOIN
"lfs_main"."dbo"."rslt_pat_t" "rslt_pat_t" ON
"bu_t"."bu_sys_id"="rslt_pat_t"."bu_sys_id"
WHERE "rslt_pat_t"."rslt_date_time">={ts '2008-05-13 14:00:01'} AND
"rslt_pat_t"."site_abbrev"='UAH' AND "rslt_pat_t"."nu_name" LIKE '3c3%'
ORDER BY "bu_t"."bu_serial", "rslt_pat_t"."rslt_date_time

 

[strtech]

smibarb,

I think your problem is in the WHERE clause, which is in your Select Expert in Crystal Reports. You have the right LEFT OUTER JOIN if you are trying to get all of the instruments from the bu_t table. However, you then start to limit the records by stating the records have to have a date time >= 'date' and the abbrev = 'UAH' and the nu_name LIKE '3c3%' all of that criteria is from the rslt table. Therefore if there is no instrument record in the rslt table that has that criteria then it is being filtered out.

I would remove those criteria from the Select Expert and check your data then. You should see all of your instruments, then start to put criteria back in the Select Expert and watch your results. I hope that helps.

 

[lbass]

You should consider using a command as your datasource. This is not that complicated. You would copy the SQL below directly into the "Add command" area (above the list of tables in the database expert):

SELECT "bu_t"."bu_serial", "rslt_pat_t"."rslt_date_time",
"rslt_pat_t"."nu_name", "rslt_pat_t"."site_abbrev", "rslt_pat_t"."pat_id",
"rslt_pat_t"."pass_flg", "rslt_pat_t"."rslt"
FROM "lfs_main"."dbo"."bu_t" "bu_t" LEFT OUTER JOIN
"lfs_main"."dbo"."rslt_pat_t" "rslt_pat_t" ON
(
"bu_t"."bu_sys_id"="rslt_pat_t"."bu_sys_id" and
"rslt_pat_t"."rslt_date_time">={ts '2008-05-13 14:00:01'} AND
"rslt_pat_t"."site_abbrev"='UAH' AND
"rslt_pat_t"."nu_name" LIKE '3c3%'
)
ORDER BY "bu_t"."bu_serial", "rslt_pat_t"."rslt_date_time"

Otherwise, if you work within the Crystal framework, you have to remove ALL selection criteria that reference the right hand table, and then use conditional formulas to display only the rquired results from the right hand table, as in:

if {rslt_pat_t.rslt_date_time} >=datetime(2008,5,13,14,00,01) AND
{rslt_pat_t.site_abbrev} ='UAH' AND
{rslt_pat_t.nu_name} LIKE '3c3*' then //whatever you want to display goes here

-LB

 

[smibarb]

Thank you to both, but I am still not getting the desired results. abcmstr, removing the Select Expert criteria returns all instruments in the bu_t table, and as soon as I add a criteria from the rslt_p_t, the two meters that do not appear in this table are excluded from the report. lbass, I tried the Command Object as described. It unexpectedly returned all instruments from the bu_t table. When I go back in and look at the "Show SQL" function, Crystal has deleted sections from the SQL. That is, you advised me to copy the following:

SELECT "bu_t"."bu_serial", "rslt_pat_t"."rslt_date_time",
"rslt_pat_t"."nu_name", "rslt_pat_t"."site_abbrev", "rslt_pat_t"."pat_id",
"rslt_pat_t"."pass_flg", "rslt_pat_t"."rslt"
FROM "lfs_main"."dbo"."bu_t" "bu_t" LEFT OUTER JOIN
"lfs_main"."dbo"."rslt_pat_t" "rslt_pat_t" ON
(
"bu_t"."bu_sys_id"="rslt_pat_t"."bu_sys_id" and
"rslt_pat_t"."rslt_date_time">={ts '2008-05-13 14:00:01'} AND
"rslt_pat_t"."site_abbrev"='UAH' AND
"rslt_pat_t"."nu_name" LIKE '3c3%'
)
ORDER BY "bu_t"."bu_serial", "rslt_pat_t"."rslt_date_time"

which I did, but the program deletes sections and ends up with:

SELECT "bu_t"."bu_serial", "rslt_pat_t"."rslt_date_time",
"rslt_pat_t"."nu_name", "rslt_pat_t"."site_abbrev", "rslt_pat_t"."pat_id",
"rslt_pat_t"."pass_flg", "rslt_pat_t"."rslt"
FROM "lfs_main"."dbo"."bu_t" "bu_t" LEFT OUTER JOIN
"lfs_main"."dbo"."rslt_pat_t" "rslt_pat_t" ON
"bu_t"."bu_sys_id"="rslt_pat_t"."bu_sys_id"
ORDER BY "bu_t"."bu_serial", "rslt_pat_t"."rslt_date_time"

I did this twice with the same result. So then I tried your second selection - but still did not get the desired results.

To summarize what I am trying to accomplish...there are thousands of instruments in the bu_t. Only 18 are assigned to site UAH, unit 3C3 (these being fields in the rslt_pat_t). I am trying to determine utilization of those 18 meters by counting the number of results since a certain date. Two meters have not been used at all in this given time period - I want to capture this on a report. Any other suggestions?

Thanks again.