[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 781
  • Last Modified:

Crystal SQL expression using parameters

Experts,


I have to write a SQL expression to get amount summary like this
(select sum(ORTRXAMT) FROM dbo.RM20101 where DOCNUMBR = {?pDocnum}  )

it tell me syntax error or access violation. What is the problem?

my system is windows 7 and CR XI.

Please help me out.

Thanks.

lanac
0
lanac222
Asked:
lanac222
  • 3
2 Solutions
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
You can't use parameters within a SQL Expression.  SQL Expressions can only be written against actual database fields, not parameters or formulas.  That being said, if you're using that parameter to return a single document number in your primary recordset, you won't need to use a parameter anyway:

(select sum(ORTRXAMT) FROM dbo.RM20101 where DOCNUMBR = "table"."docnum"  )

Open in new window


In this scenario, the SQL expression is pulling data from RM20101 and passing in the document number from the detail record in the report to the subquery.  The quotes around the table and field for the document number indicate that it was added to the query by inserting it directly from the field tree in the SQL Expression Editor. Please note, this approach only works if you are reporting against a database that allows Crystal Reports to pass in correlated elements in a SQL Expression.  SQL Server, DB2 and even Access allow it.  Oracle does not.

~Kurt
0
 
mlmccCommented:
Are you pulling your data from RM20101?

Are you pulling data you don't want summed or in some other way limiting the data being pulled?

It would seem you could either pull the SUM in the query itself or simply use a summary function in the report to get the sum.

mlmcc
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Assuming the user isn't pulling data from RM20101 in the main report, this is a way to get an aggregate value from a related table without having to join the entire table in the main report.  It's basically a subquery at the record level.  I often use SQL Expressions and/or subqueries (if I'm writing true SQL) to "flatten" my data into a single record, for example.

~Kurt
0
 
lanac222Author Commented:
Thanks, guys.

Did you say parameter never worked in Sql expression? I remember it was working just not date value, string and number parameter is OK.

But anyway, I change the sql expression with subquery instead of parameter.
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
They never work with SQL Expression fields.  Simplest way to demonstrate is for you to expand the Field Tree in the SQL Expression Editor - what you see available is what you have to work with :)

Glad you got it working!

~Kurt
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now