CODEWITHSUNDEEP
sqlcrystal-reportscrystal-reports-2008
user2022883
user2022883

Reputation: 29

Converting SQL statement to SQL expression field in Crystal Reports

I have a SQL statement that pulls data I need but I can't get the syntax right in Crystal Reports.

This statement works in SQL:

SELECT
    max([meter_reading])
FROM [Forefront].[dbo].[EC_METER_HISTORY_MC]
WHERE [Meter_Number] = '1' AND [Transaction_Date] < '20130101'
GROUP BY 
    [Company_Code], [Equipment_Code], [Meter_Number]

This is what I changed it to in crystal but I can't get the right syntax.

SELECT     
   Maximum({EC_METER_HISTORY_MC.meter_reading}) 
FROM [EC_METER_HISTORY_MC] 
WHERE {EC_METER_HISTORY_MC.Meter_Number} = '1' 
AND {EC_METER_HISTORY_MC.Transaction_Date}  <  {1?Startdate}
GROUP BY {EC_METER_HISTORY_MC.Company_Code}
,{EC_METER_HISTORY_MC.Equipment_Code}
,{EC_METER_HISTORY_MC.Meter_Number}

Upvotes: 1

Views: 53082

Answers (2)

craig
craig

Reputation: 26262

You can't use parameter fields in a SQL Expression, sadly. Perhaps you can correlate the Transaction_Date to a table in the main query. Otherwise, I would suggest using a Command.

You have two options for the Command:

  1. Use a single Command object as the data source for the whole report--which involves (potentially) a fair amount of rework.
  2. Add a Command to the existing table set (in the Database 'Expert'). Link it to other tables as desired. This will perform a second SELECT and join the results in memory (WhileReadingRecords, if I'm not mistaken). The slight performance hit may we worth the benefit.

Upvotes: 1

Ryan
Ryan

Reputation: 7287

Your first step should be reading up on how SQL Expressions work in Crystal. Here is a good link to get you started.

A few of your problems include:

  1. Using a parameter field. SQL Expressions are not compatible with CR parameters and cannot be used in them.
  2. SQL Expressions can only return scalar values per row of your report. That means that your use of GROUP BY doesn't serve any purpose.
  3. Your use of curly braces means that you're referencing those fields in the main report query instead of in the subquery you're trying to create with this expression.

Here's a simplified example that would find the max meter reading of a particular meter (for Oracle since that's what I know and you didn't specify which DB you're using):

case when {EC_METER_HISTORY_MC.Meter_Number} is null then null
else (select max(Meter_Reading)
      from EC_METER_HISTORY_MC
      where Meter_Number={EC_METER_HISTORY_MC.Meter_Number} --filter by the meter number from main query
       and Transaction_Date < Current_Date) --filter by some date. CAN'T use parameter here.
end

Upvotes: 2

Related Questions