Can't Access Correct Data

[MikeCopeland]

Using CR11...
I have having great difficulty accessing specific data in a query that has many tables. No matter how I try to qualify the SQL selection, either I get too much data or I omit data I want. Here is the data:
IdNo Id Key Date1 Date2 Link
213298 288268 828117 <NULL> 12/16/2007 288335
213298 288335 829863 <NULL> 12/31/2007 0 <<-- I want this row
213298 287732 808403 <NULL> 8/13/2007 287840
213298 287978 813644 <NULL> 10/08/2007 288268
In this data set, the only link to this table is "IdNo", yet I must obtain the "Key" value of 829863 (the 2nd row shown above). There are many other rows in this table, but these (which have "Date1" = <NULL>) are the pertinent data.
If I qualify the SQL selection on "Link = 0", I get many other rows that happen to equal 0 in that column; if I select "isNull(Date1) and Link = 0", I get no data for this data set. Sorting the data on Id or Key (either Ascending or Descending) further exacerbates the problem by getting too much or too little data.
Bottom line is that I want to obtain/use the Key that's in the 2nd row here (to link to s subsequent table), and I can't find any way to select, sort, or qualify the data from this table to get it. Any thoughts? TIA

 

[lbass]

Are you sure the datefield is null? Is it of date datatype? If so, you could try:

(
isnull({table.date1}) or
{table.date1} = date(0,0,0)
) and
{table.link} = 0

You should also check to make sure you don't have "convert nulls to default values" in file->report options.

-LB

 

[MikeCopeland]

The Date field is of DateTime data type. When I view it in SQL via a Select statement, it displays as <NULL>. I do not have that option set in this query report. Adding the "date(0,0,0)" clause doesn't change anything.
I know this won't help much, but here is my Select clause:
if(({Clients.ClientLastName} <> 'TEST')
and ({@inRange})
////and ({@okayISP})
////and ({@isEnrollDate})
and ({@isProgressDate})
and (isNull({ClientProgressNote.VoidDate}))
and ({ClientProgressNote.ServiceTypeKey} in [50, 52])
and ({ClientISPDetail.GoalNo} in [2])
and ({ClientISPMethods.LengthKey} <> 9)
and ({ClientEnrollment.RBHA} in ["01", "06", "10"])
and ({@isTherapist})
and (((isNull({ClientISP.HistoryChangeDate})) or
({ClientISP.HistoryChangeDate} = date(0,0,0)) ) and
({ClientISP.ReviewISPKey} = 0)) ) then true
else false
The difficulty started occurring when I added the logic that starts with
"(((isNull({ClientISP.HistoryChangeDate}))" through the "then" clause. No matter how I "tweak" it, I either get far too much data (duplicates of many rows) or missing data. When I remove the logic, I get enormous amounts of duplicated data, but I never get information from the row I want (I get many instances of the links from the other 3).
<sigh...>

 

[lbass]

You have way too many parens in unnecessary places, which I think is confusing things. Try:

isNull({ClientProgressNote.VoidDate}) and
isNull({ClientISP.HistoryChangeDate})and
{ClientISP.ReviewISPKey} = 0 and
{Clients.ClientLastName} <> 'TEST' and
{@inRange} and
{@isProgressDate} and
{ClientProgressNote.ServiceTypeKey} in [50, 52] and
{ClientISPDetail.GoalNo} in [2] and
{ClientISPMethods.LengthKey} <> 9 and
{ClientEnrollment.RBHA} in ["01", "06", "10"] and
{@isTherapist}

You should always place the null checks first, and especially since we don't know what is in your nested formulas, this is probably a good idea.

If this still doesn't work, you should identify what your table joins are.

-LB