Print Page | Close Window

SQL Expression Syntax

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21416
Printed Date: 04 May 2024 at 12:46pm


Topic: SQL Expression Syntax
Posted By: scottselph
Subject: SQL Expression Syntax
Date Posted: 07 Apr 2015 at 9:03am
I have a Crystal Report connected to a SQL database via an ODBC SQL Server connection. There is a field from this database that I'm trying to retrieve using the SQL Expression below. It does not appear to work as it causes the report to "timeout" and hang up with the status bar continually displaying "accessing database". I'm not sure if the syntax is correct. It is a very convoluted path as there are multiple table that have to link in order to pull the correct piece of data.

I'm dropping this in a group footer. Have been stuck on this issue for days, and it's the last piece I need to complete my report. Any assistance would be GREATLY appreciated.

(
SELECT
MIN(SalesOrderDeliveries.omdDeliveryDate)
FROM dbo.Jobs
INNER JOIN dbo.SalesOrderJobLinks
ON "Jobs"."jmpJobID" = "SalesOrderJobLinks"."omjJobID"
INNER JOIN dbo.SalesOrders
ON "SalesOrderJobLinks"."omjSalesOrderID" = "SalesOrders"."ompSalesOrderID"
INNER JOIN dbo.SalesOrderLines
ON "SalesOrders"."ompSalesOrderID" = "SalesOrderLines"."omlSalesOrderID"
INNER JOIN dbo.SalesOrderDeliveries
ON "SalesOrderLines"."omlSalesOrderID" = "SalesOrderDeliveries"."omdSalesOrderID"
WHERE "SalesOrderDeliveries"."omdShippedComplete" = 0
)


Replies:
Posted By: DBlank
Date Posted: 08 Apr 2015 at 3:51am
i would not use the SQL expression for this.
Not sure which direction I would recommend yet though.
This would depend on what youa re trying to do and why you are not using the existing data set for that value.
Why is it in the group footer? Are you expecting a unique group result?
are you doing anything with the result or just displaying it?
Is this derviable from the main data set?
What is your current data source? A stored proc? CAn you use an update or cross join in that to get the values at the same time as teh rest of the data set?


Print Page | Close Window