Print Page | Close Window

Command object SQL statement with parameter

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7759
Printed Date: 08 May 2024 at 12:28pm


Topic: Command object SQL statement with parameter
Posted By: CRNewbie32
Subject: Command object SQL statement with parameter
Date Posted: 21 Sep 2009 at 2:27pm
I have a report that uses a SQL Server stored procedure as the data source. The first parameter they enter is their company number (we have numerous clients using Lawson and access Crystal through LBI). The next parameter is the account number. I have a SQL statement written that gets distinct account numbers by company. When I create a Command object with the SQL statement, it doesn't allow me to use the company parameter.
 

SELECT DISTINCT GLC.ACCOUNT, GLC.ACCOUNT_DESC
FROM dbo.GLMASTER AS GLM
INNER JOIN DBO.GLCHARTDTL AS GLC
ON GLM.CHART_NAME = GLC.CHART_NAME
AND GLM.ACCOUNT = GLC.ACCOUNT
WHERE GLM.COMPANY = {?Company}
ORDER BY GLC.ACCOUNT

When I do Add Command in Database Expert and add the SQL above and name the parameter and try to Add, it says "Failed to retrieve data from the datase.
Details: ADO Error Code: 0x
Source: Microsoft OLE DB Provider for SQL Server
Description: No value given for one or more required parameters
 
Does anyone have a similar situation and a solution to this problem--either regarding my approach and error or a different approach to get the same thing?
 
Any input would be greatly appreciated!


-------------
TrytoworkwithSQLServerCrystal


Replies:
Posted By: Jyothi Yepuri
Date Posted: 21 Sep 2009 at 8:50pm
Is Company column string type?
then try like this
WHERE GLM.COMPANY = '{?Company}'

Jyothi

Posted By: CRNewbie32
Date Posted: 22 Sep 2009 at 9:22am
That was very helpful.
 
I have another report where the Company is in the report's data source (not a parameter). What would be the syntax for that?
 
I've tried :
SELECT DISTINCT RIGHT('00000' + LTRIM(RTRIM(CAST(GLC1.ACCOUNT AS CHAR(5)))),5) +
  RIGHT('000' + LTRIM(RTRIM(CAST(GLC1.SUB_ACCOUNT AS CHAR(3)))),3) AS ACCOUNT2,GLC1.ACCOUNT_DESC
FROM GLCHARTDTL AS GLC1
WHERE RIGHT('00000' + LTRIM(RTRIM(CAST(ACCOUNT AS CHAR(5)))),5) +
  RIGHT('000' + LTRIM(RTRIM(CAST(SUB_ACCOUNT AS CHAR(3)))),3)  IN
(SELECT DISTINCT RIGHT('00000' + LTRIM(RTRIM(CAST(GLC.ACCOUNT AS CHAR(5)))),5) +
  RIGHT('000' + LTRIM(RTRIM(CAST(GLC.SUB_ACCOUNT AS CHAR(3)))),3)
FROM dbo.GLMASTER AS GLM
INNER JOIN DBO.GLCHARTDTL AS GLC
ON GLM.CHART_NAME = GLC.CHART_NAME
AND GLM.ACCOUNT = GLC.ACCOUNT
AND GLM.SUB_ACCOUNT = GLC.SUB_ACCOUNT
WHERE cast(GLM.COMPANY as varchar(4)) = '{COMPANY}')


-------------
TrytoworkwithSQLServerCrystal

Posted By: Jyothi Yepuri
Date Posted: 22 Sep 2009 at 4:02pm
What exactly you require do with this query? explain your requirement.

Jyothi

Posted By: CRNewbie32
Date Posted: 23 Sep 2009 at 7:09am
I'm trying to get a string combination of an account number and sub_account number in one column, and the description in a second column. I want to limit this to the chart of accounts for one specific company--the company that is in the Select Expert for the GLTRANS table which is the table for the report.

-------------
TrytoworkwithSQLServerCrystal

Posted By: Jyothi Yepuri
Date Posted: 27 Sep 2009 at 9:01pm
write the Where condition part in record selection.

Jyothi

Posted By: CRNewbie32
Date Posted: 01 Oct 2009 at 6:06am
I don't want to hard code the COMPANY field. We have 22 customers, all with different company numbers, running the same report. Once they're in the report it has their company number, so I would like this Command Object to be able to obtain it without hard-coding the company number.

-------------
TrytoworkwithSQLServerCrystal

Posted By: Jyothi Yepuri
Date Posted: 01 Oct 2009 at 3:56pm
Create a parameter for company number and use in above SQL Command.
what is the problem with above query? its not working?

Jyothi


Print Page | Close Window