SQL Expression

[mgallot]

Using Crystal XI r2 going to SQL Server DB.

Need to pull the maximum rate set for each employee and create totals for them so I am using an sql expression. Each employee has multiple records in the rate table.

This expression gives an 'error near Select statement'

Select hbm_persnl.'employee_code' ,
max(tbm_rate_fee.'rate'), hbm_persnl.'employee_name'
From hbm_persnl, tbm_persnl, tbm_rate_fee
where "hbm_persnl"."empl_uno" = "tbm_persnl"."empl_uno"
and "tbm_persnl"."empl_uno" = "tbm_rate_fee"."empl_uno"
Group by employee_code, employee_name

I modified it a little to try to make it work, but this is the sql that works against the database.

Select distinct employee_code 'emplcode',
max(tbm_rate_fee.rate)'ratehigh', hbm_persnl.employee_name'emplname'
From hbm_persnl, tbm_persnl, tbm_rate_fee
where hbm_persnl.empl_uno = tbm_persnl.empl_uno
and tbm_persnl.empl_uno = tbm_rate_fee.empl_uno
Group by employee_code, employee_name

Seems simple enough, any ideas why its not working?

Thanks for all help!!

 

[mgallot]

I put this code into a command instead and it works perfectly.

 

[Madawc]

Where I work, we used to use commas to specify multiple tables, but had to change to 'left outer' after an upgrade. Thus

From  hbm_persnl, tbm_persnl, tbm_rate_fee

would become

From  hbm_persnl, tbm_persnl left outer tbm_rate_fee

Does this help?

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

The reason it didn't work as a SQL expression is because it was returning a dataset, and SQL expressions can return only one value. Subqueries also must be enclosed in parentheses. In XI, you also cannot specify the table name within the summary function (corrected in CR 2008).

-LB