Need help with CRXI R2 record selection

[snejsnej]

Hi there--

I'm dealing with a two issues:

1) SQL Expression evaluation

My report has a number of SQL Expression Fields that CR XI is evaluating at design time. That is, when I save a SQL Expression, it immediately checks it for syntax errors AND runs it against the database.

However, because the SQL expression is working with an unfiltered set of data (i.e. not at run time where a user filters the data by using the dynamic params), it often returns an error stating that more than one record is being returned. That is, it isn't taking the Select Expert formula into account, which it obviously can't at design time.

Is there some sort of setting in CR XI that 'tells' it not to immediately evaluate this SQL expression at design time? If not, then there must be something wrong with how I have the filtering process set up. This brings me to my second point...


2) Select Expert formula

It looks like this:

{Complete.PatientLastName} = {?Patient Parameters - PatientLastName} and
{Complete.PatientFirstName} = {?Patient Parameters - PatientFirstName}

I'm pretty new to CR in general, nevertheless this formula doesn't appear to be a Crystal formula, i.e. it is something that SQL 2005 can understand. If that is indeed the case, what could be causing the same run time error ("Subquery returned more than 1 value...") AFTER the dynamic parameter choices are made? Based on those choices, only 1 record should be returned to CR.

Thanks

 

[synapsevampire]

Since the question is regarding SQL Expressions, please post the SQL Expression and the database being used.

Even with your filtering, you may run into problems later with John Smith sorts.

For instance with SQL Server you might use:

select TOP 1 <blah> from <blah>

Thus you'll always get just one row.

Do the equivalent for whatever database you're using.

-k

 

[snejsnej]

Thanks for your reply synapsevampire.

The John Smith problem is an issue, I will likely need to add one more dynamic parameter (for the birth date, say).

I'm using SQL Server 2005, CR XI R2, ASP.NET 2.0.

The SQL Expression:

(SELECT "OC"."OutcomeCodeName"
FROM "lu_OutcomeCode" "OC" INNER JOIN "PatientArrival" "PA"
ON "OC"."OutcomeCodeID" = "PA"."PatientArrHOBNotMetCode"
WHERE "PatientID" = "PA"."PatientID")

I had already tried something similar to your TOP 1 suggestion in limiting the result set to 1 record by using the MAX() function:

(SELECT MAX("OC"."OutcomeCodeName")
FROM "lu_OutcomeCode" "OC" INNER JOIN "PatientArrival" "PA"
ON "OC"."OutcomeCodeID" = "PA"."PatientArrHOBNotMetCode"
WHERE "PatientID" = "PA"."PatientID")

...however, the problem was that this simply returned the top record per its spelling, not necessarily the correct one. For example, in the first case above 2 records were returned: "Lung collapse", "Chylothorax". The correct record is "Chylothorax", but "Lung collapse" is returned because "L" has a higher value than "C".

The same issue applies regardless of which field I use MAX() on...

Thanks

 

[synapsevampire]

If "Lung collapse", "Chylothorax" need to be filtered, then your criteria needs to include more parameters or filters, right?

btw, a max does not always assure one row being retrurned...

A select top 1 does.

I believe that I've already given you your solution, use TOP 1, and provide adequate filtering either via parameters or coding the record selection to return the row you need.

-k

 

[snejsnej]

Appreciate it, thanks.

Would you mind clarifying a point re parameters? Specifically, I don't understand why more than one record is being returned despite the use of the dynamic parameters.

If there were 2 "John Smiths" in my db, then it would be clear; however, my test db only contains a few records that I entered myself, all with distinct first / last name combinations --> No duplicate records should be returned using the dynamic parameters I mentioned in my first post.

Thanks again.

 

[synapsevampire]

The only thing it's checking for is: SELECT MAX("OC"."OutcomeCodeName") in the SQL Expression. There must be more than one of those that are the argest OutcomeCodenaes...

Your dynamic parameters have NOTHING to do with the SQL Expression.

Anyway, please code in TOP 1. The error is them eliminated.

-k

 

[snejsnej]

I tried using TOP 1, but it didn't work.

>> Your dynamic parameters have NOTHING to do with the SQL Expression.

OK, but then how are the multiple results filtered in CR XI?
The following query (I took out MAX because it was wrong) returns ALL records that have an OutcomeCodeName to CR XI-- in which case I assume I can delete the WHERE clause. So where does the dynamic parameter filtering come in?

(SELECT "OC"."OutcomeCodeName"
FROM "lu_OutcomeCode" "OC" INNER JOIN "PatientArrival" "PA"
ON "OC"."OutcomeCodeID" = "PA"."PatientArrHOBNotMetCode"
WHERE "PatientID" = "PA"."PatientID")

Again, appreciate your help.

 

[synapsevampire]

Not sure, try posting what this select staement is for. I don't want to go back and reread the thread each time.

Is this the SQL Expression? Where's the TOP 1???

Where or how did you use the Top 1? Since you don't want to post it I can't help.

Don't know where the dynamic parameter comes in, check what data source you pointed to.

-k

 

[lbass]

You need to explain how you are planning to use the SQL expression in your report. You seem to be trying to connect a dynamic parameter with the use of a SQL expression.

-LB

 

[snejsnej]

Sorry for the late reply:

@synapsevampire: Here's the expression:

(SELECT TOP 1 (OC.OutcomeCodeName) FROM lu_outcomecode OC
INNER JOIN PatientArrival PA
ON OC.OutcomeCodeID = PA.PatientArrHOBNotMetCode)

The statement works fine except when "PA.PatientArrHOBNotMetCode" contains a "0" (zero), in which case the statement retrieves the value from the next record that contains a non-zero number. I have a large number of fields like "PA.PatientArrHOBNotMetCode" (obviously with slightly different names, like "PA.PatientArrCTNotMetCode").

I could probably take care of the 'zero' issue by incorporating this SQL statement in a CR Formula (if {%SQLstatement} not = 0 then... else), but would prefer to take care of it directly in the SQL if possible.

Thanks

 

[snejsnej]

@lbass: Thanks for your input.
Does my last post give you some insight as to what I want to do?

 

[lbass]

No, you didn't really answer my question. Also, is this really being created in the SQL expression area or is it a command object?

-LB

 

[snejsnej]

lbass: this is being created in the SQL expression area.

The purpose of the SQL expression is to return a single result - the OutcomeCodeName - for the patient whose last and first names were selected from dynamic parameter drop down lists.

The problem that I'm running into is that the expression is returning _all_ records wherever an OutcomeCodeName exists (in this case 3 of 7 records).

I tried using the TOP 1 function. It reduces the number of records to 1 of course, but it's not always the right one-- the one I need in this case is the 2nd record.

I'm quite new at working with CR in general and I made the assumption that using dynamic parameters would narrow the scope of the SQL Expressions when the query the database, but that's obviously not the case.

Any idea how I can do this?
Thanks-
Jens

 

[lbass]

What determines which record you want to see? Is it the most recent? Or?

-LB

 

[snejsnej]

The record to be displayed is determined by the dynamic parameter drop down lists.

 

[lbass]

I'm not talking about the patient, but about the outcome record that would be displayed by the SQL expression.

If you want to show the most recent you could use a SQL expression with this format:

(
select top 1 A.`Outcome` from table A where
A.`PatientID` = table.`PatientID` and
A.`Date` =
(
select max(A.`Date`) from table A where
A.`PatientID` = table.`PatientID`
)
)

-LB

 

[snejsnej]

Thanks LB--
Apologies if I'm still misinterpreting your question... The record to be displayed is determined solely by the PatientID.
However, because the user can't relate to the PatientID (simply the PK of the PatientInfo table in SQL), the user selects from the drop down lists listing the patients' names.
Jens

 

[lbass]

Yes, you are still misinterpreting (or I am). The issue with the SQL expression is that there is more than one record returned per patient ID--so in that case how do you know which record you want?

-LB

 

[snejsnej]

The problem is that I can't know which record I want until the user makes a choice from the drop down list at runtime. So for SQL to be able to pull the right record, it seems to me that this parameter needs to be given dynamically to SQL after the patient's last / first names are chosen by the user. The PatientID is the best parameter for this.

Jens

 

[lbass]

Once you see what values are returned, how do you know which outcome record you want? What are your criteria to select among multiple records per patient ID?

-LB