Hi,
        This Query works perfectly in SQLServer 6.5.

SELECT
CctrName,CctrDescription,CctrBillable,CctrCreatDate,CctrLastModDate,CctrUser
Id,
(Select count(*) from Aggregation b where b.CctrId = a.CctrId) as
noofmeasure
FROM
 Cost_Center a
ORDER BY
CctrName ASC

Please note that I am using a Sub Query to get a value which is from
different table and I have to show it in the Report.

The problem is when I Paste the Query in Crystal Report 5.0 Show SQL Query,
It gives the error message
"ODBC error:( Microsoft) ( ODBC SQL Server Driver) (SqlServer) Line 1 :
Incorrect syntax near ')',

followed by "Sql Server Error"

When I go back to Show SQL Query, the Query appears like this:

SELECT
    Cost_Center."CctrName", Cost_Center."CctrDescription",
Cost_Center."CctrBillable", Cost_Center."CctrCreatDate",
Cost_Center."CctrLastModDate", Cost_Center."CctrUserId"
FROM
    Aggregation b
WHERE
    b.CctrId = a.CctrId) as noofmeasure FROM  Cost_Center
ORDER BY
    CctrName ASC

How do I get my Value? Please help me.

Thanks
Lalitha Jayaraman

In my experience, CR doesn't know about every SQL
construct, and subqueries like yours appear to be a case
in point.
I would be inclined to remove the subquery and allow CR to
do the counting itself, something like:

SELECT
CctrName,CctrDescription,CctrBillable,CctrCreatDate,
CctrLastModDate,CctrUser,
Id, b.CctrId, a.CctrId
FROM
Cost_Center a, Aggregation b
where
a.CctrId *= b.CctrId /* I think */
ORDER BY
CctrName ASC

and in the report have a Count(b.Cctrld)
--
Les Schmalzbach
(remove nospam- to reply).