SQL Expression Sample Code

[LHSCNaved]

Hi All,

CRXI/SQL Server 2005


I need a sample code/syntax to create a sql expression field to calculate sum and count using Sql Server 2005.
I have tried multiple option without any luck. Any help in this regard will be appreciated.

Thanks and Regards
Naved Altaf

 

[SkipVought]

Hi,

Please post the SQL code that you have tried and explain the results.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LHSCNaved]

Thanks for the reply


SELECT count("TempDIRAD"."Encounter")
FROM "CaseCosting"."dbo"."TempDIRAD" "TempDIRAD"

and I got the Error compiling sql expressions......
native error 156 vendor code 156.....

 

[IanWaterman]

Try wrapping in ()

(SELECT count("TempDIRAD"."Encounter")
FROM "CaseCosting"."dbo"."TempDIRAD" "TempDIRAD")

Ian

 

[lbass]

Try:

(
SELECT count("Encounter")
FROM "CaseCosting"."dbo"."TempDIRAD" "TempDIRAD"
)

You must enclose in parens, and also you should not reference the table name within the summary (this is true for versions 9 - XI, I think).

-LB

 

[hilfy]

I have found that, in general, putting a full select statement in a SQL expression doesn't work. SQL expressions are added to the select list of the SQL that Crystal sends to the database. So, if your database doesn't support structures like:

Select
Field1,
Field2,
(Select Count(*)
from TableB) as countit
from TableA

Then the SQL Expression won't work.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

SQL expressions are intended to return only one value per the criteria, not a set of values. If a set of values per row is what you need, then "Add command" is the way to go.

-LB

 

[LHSCNaved]

Hi All,

Thanks Ian/Lbass this really helped. This forum is really very helpful.

Have a Nice Day.

Thanks