SQL Expression that totals like records for later record selection. 1

[Renasant]

I have a SQL expression that is not returning anything like what I expect. The database contains loan data. Some loans are in relationships and others are single loans. I am trying to get totals of the relationships and select records based on the SQL expression.

My expression is supposed to total all balances within the relationship, but not total when there is no relationship or carry over when the relationship field is blank. I created a table in the database for relationships.

(SELECT SUM(`Perm_Watch_List`.`Led Bal`)
FROM `Perm Watch List`
WHERE `Perm_Watch_List`.`Relationship` IS NOT NULL AND
(`Perm_Watch_List`.`Relationship` = `Relationship`.`Relationship`))

I put the SQL field into the report as a check and its results are nowhere close to the running totals that are in the report from before I needed to select records based on these totals.

Just for information here is the select query
if isnull({Perm_Watch_List.Relationship})
then {Perm_Watch_List.Led Bal}>$500000.00
else {%Relat Balance}>$500000.00

 

[jmd0252]

I would have expected to see, a "group by", or "order by" clause in your SQL. Usually I go into the sql, and do a view,, to get my totaling correct,, and then copy the "view" statement and use that as my sql statement. Such as
SELECT TOP(100) PERCENT EQUIPMENT, SUM(CLASS_01) AS TO1, SUM(CLASS_02) AS TO2, SUM(CLASS_03) AS TO3, SUM(CLASS_07) AS TO7,
FROM dbo.EQ_EQUIPTOTALS
WHERE (GL_PERIOD = '{?BYTDPD}')
GROUP BY EQUIPMENT
This being an example from sql2005. Do not know if this helps or not.

 

[Madawc]

If you're using SQL within Crystal, why not let Crystal do the totalling? It's what it's good at.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[Renasant]

Neither Group by nor Order by did anything meaningful. The totals are still wrong. Thanks, jmd0252 that was a good suggestion.

Yes, Crystal is very good at totaling, BUT you cannot use a running total or a summary in a record selection formula. This expression will be part of the record selection formula once I get it debugged. Thanks anyway Madawc.

Anyone else have ANY suggestions?

 

[lbass]

What version of CR are you using? Syntax is different depending upon version.

Try:

(
select sum(`Led Bal`)
FROM `Perm Watch List` A
WHERE A.`Relationship` IS NOT NULL AND
A.`Relationship` = `Perm_Watch_List`.`Relationship`
)

-LB