Relating an SQL Expression to a group 1

[IanWaterman]

Currently building Insurance Reports using CR10 on Oracle 8

I am trying to relate some claims data to premium data. The only relationship between the Premium Transactions and the Calims transactiosn is the policy number, thus it is a many to many relationship.

I can build a subreport which will find any claims data for each policy and I can then relate this in the summary group of the policy for the premium data. I have been led to believe that I could use a SQL expression instead.

However, when I run my SQL wxpression it just brings back all Claims data and not just for a specific policy.

(Select Sum(ClaiM_amount) from GenClaim)

I can also group by policy or filter by policy, but I cannot seem to make the SQL Expression return the policy group amount when placed within the group footer of the main report where group is "GENPOLICY"."POLICY_KEY".

I have even tried
(Select Sum(ClaiM_amount) from GenClaim
where CLM_POLICY_CDE = "GENPOLICY"."POLICY_KEY")

I introduced the policy key using the table list in the SQL Exp editor, but this just resulted in a SQL error where "GENPOLICY"."POLICY_KEY" is an invalid identifier.

Can this be done or do I just go back to the slow but reliable Subreport.

Ian

 

[lbass]

This is how I would do it, although the punctuation can vary depending on datasource:

(Select Sum(AKA.`Claim_amount`) from GenClaim AKA
where AKA.`CLM_POLICY_CDE` = GenClaim.`CLM_POLICY_CDE`)

This assumes that your claims table is called "GenClaim" and that your exact field names are "Claim_amount" and "CLM_POLICY_CDE". Leave "AKA" as is, since it is an alias table name necessary to getting the group sum.

-LB

 

[kenhamady]

Maybe LBass has discovered a way around a limitation in CR, but I thought that a SQL Expression field could only do a SELECT that returned a single value, not a recurring value like a group value.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[kenhamady]

But I just tested it and it seems to work. So by using the AKA we can add correlated subqueries into the SQL and subreports aren't needed? I wonder how many people knew this. I sure didn't.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[IdoMillet]

The following approach would improve performance significantly:

1. Create a view in Oracle that returns the sum of claim_amount by policy code:
---------------------------------------------
Select Claim.CLM_POLICY_CDE, Sum(Claim.Claim_amount) from Claim
---------------------------------------------

2. In your report, join to that View. Since each policy will have a single matching record in the View, record inflation is no longer a problem.

The performance gain is due to executing the View only once instead of running the correleted query multiple times.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IdoMillet]

Step 1 should have been:
---------------------------------------------
Select Claim.CLM_POLICY_CDE, Sum(Claim.Claim_amount) from Claim GROUP BY Claim.CLM_POLICY_CDE
---------------------------------------------




CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

Ken,

I am not allowed to create views on my database, so have found SQL expressions helpful in certain instances. Initially I used the syntax above in the "Show SQL Query" where clause, but then realized I could do the same thing in the SQL Expressions editor. Certain SQL expressions can then be limited in the record selection formula.

I have found SQL expressions useful particularly in doing a topN on a summary formula, e.g., where one summary is treated as a percentage of another summary, and have also used them in certain crosstab situations, e.g., where the crosstab column needed to be based on a summary field.

-LB

 

[synapsevampire]

Ken: We've been using correlated subqueries for quite some time in CR.

The syntax varies based on the database, but it is possible.

With CR 9/10 all of this has changed since you can use real SQL, so I wouldn't put too much effort towards this.

-k

 

[kenhamady]

synapsvampire,

Many of my customers are still on v8.5 so this is really helpful.

Even in v9/v10 I have even found that there are cases where using commands cause a loss of efficiency. For instance, if you need a multiple value parameter. You can't do these in command parameters. And if you use add a regular multiple value parameter to a report based on a command the parameter won't update the SQL of the command. However, if you use a regular report with a multiple value parameter the SQL is automatically generated based on the parameter values. So I can get subqueries without having to resort to commands.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[IanWaterman]

Thank you for responses, sorry for slow response I have been a way for a couple of days.

LBASS

The code you suggest does not work, I get an Oracle error when I try to save.

(Select Sum(AKA.`Claim_amount`) from GenClaim AKA
where AKA.`CLM_POLICY_CDE` = GenClaim.`CLM_POLICY_CDE`)

The first line returns data ok, adding the second line gives me an invalid character error. It does not apear to like GenClaim.`CLM_POLICY_CDE` as GENCLAIM has now been aliased as AKA.

My group on the main report is "GENPOLICY"."POLICY_KEY".
so still not sure how this will relate policy claims to policy premiums. (Table Genpolicy holds my premium data)

Ido

I can produce a view, but I am trying to understand the benefits of SQL Expressions as sometimes I an not allowed to create views on client data.

Ian

 

[lbass]

Since in your original expression you were setting the claim policy code equal to the policy key, I assumed that summing the claim amount within the genclaim policy code would be equivalent to summing it within the policy key. The expression is set up to work within the genclaim table, not with two different tables. Is the genclaim table linked to the other table with policy code = policy key? Because then my approach should work.

If you double click on {GenClaim.CLM_POLICY_CDE} in the SQL Expressions field list, how does it appear in the formula area? This would show you the correct punctuation for your datasource. The expression does work for me as is.

-LB

 

[IanWaterman]

LB

Finally got it to work it was a hybrid of my original attempt and your use of the AKA alias.

This works
(SELECT SUM(AKA.CLM_amount) FROM GENCLAIM AKA
WHERE AKA.CLM_POLICY_CDE = "GENPOLICY"."POLICY_KEY" )

I had to remove your single speech marks '

I originally tried
(Select Sum(ClaiM_amount) from GenClaim
where CLM_POLICY_CDE = "GENPOLICY"."POLICY_KEY")

Still can not see why I have to alias table, but as it works I'll wory about that another time.

Thanks again

Ian

 

[gortuk]

Does this work with CR 10 only? I'm trying to do this in a CR 9.0 Report off an Oracle 9 DB with the folowing SQL Expression:

(select max(b.eventdate)
from cases a, caseevent b, tableevent c
where c.eventcode = 'FLG'
and a.casereference = "CASES"."CASEREFERENCE"
and a.casekey = b.casekey
and b.eventkey = c.eventkey)

(And yes, the table "CASES" is present in the report.)

I keep getting the 'invalid identifier' error message even though this should produce valid SQL. (I can demonstrate this by replacing "CASES"."CASEREFERENCE" with "test", saving the report, copying "Show SQL Query" into SQL Navigator, then replacing "test" with "CASES"."CASEREFERENCE" - the query runs exactly as I want it to).

I've tried using different Oracle ODBC drivers (CR and MS's), using OLE connection, and Oracle Native connection and I always get errors with this. Also tried every combination of quotes (" , ' and `) around the field reference. Has anyone been able to do this on CR 9?

 

[kskid]

If it's like Crystal 8.5, the "Show SQL" for CASES would look like this
...
FROM
"DBNAME"."CASES" CASES,
...

If that's the case, then change your SQL expression from

and a.casereference = "CASES"."CASEREFERENCE"

to

and a.casereference = CASES."CASEREFERENCE"


-LW