Author |
Message |
CRNewbie32
Newbie
Joined: 14 Oct 2008
Location: United States
Online Status: Offline
Posts: 22
|
Topic: Command object SQL statement with parameter 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
|
IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 21 Sep 2009 at 8:50pm |
Is Company column string type?
then try like this
WHERE GLM.COMPANY = '{?Company}'
Jyothi
|
IP Logged |
|
CRNewbie32
Newbie
Joined: 14 Oct 2008
Location: United States
Online Status: Offline
Posts: 22
|
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
|
IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 22 Sep 2009 at 4:02pm |
What exactly you require do with this query? explain your requirement.
Jyothi
|
IP Logged |
|
CRNewbie32
Newbie
Joined: 14 Oct 2008
Location: United States
Online Status: Offline
Posts: 22
|
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
|
IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 27 Sep 2009 at 9:01pm |
write the Where condition part in record selection.
Jyothi
|
IP Logged |
|
CRNewbie32
Newbie
Joined: 14 Oct 2008
Location: United States
Online Status: Offline
Posts: 22
|
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
|
IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
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
|
IP Logged |
|
|