on 08-09-2011 5:19 PM
I'm running Crystal Reports XI against an Oracle database. I created a command which initially used a static value for one of the where comparisons. Now I have created a parameter with static values and I want the command to make the comparison against the entered selection for the parameter. After searching the internet, I tried many variations but so far none of them work. HERe is the where clause from the command with the last thing I tried (a like statement):
WHERE U.INSTRUMENT_TYPE_CD IN ('Future EFP Unposted')
AND (U.TRADE_NUM = S.TRADE_NUM AND U.TRADE_NUM = A.TRADE_NUM AND U.TRADE_NUM = T.TRADE_NUM AND U.TRADE_NUM = X.TRADE_NUM)
AND (U.TRADE_NUM = TTU.TRADE_NUM)
AND (S.EXTERNAL_KEY = A.CASHFLOW_NUM AND S.ITEM_STATUS_IND = 1 AND S.REPLACED_ITEM_HDR_NUM IS NULL)
AND S.COST_TYPE_CD = 'Primary Settlement'
AND U.STRATEGY_NUM = O.STRATEGY_NUM
AND O.PROCESS_GROUP_CD like {?LOB}
AND O.PARENT_STRATEGY_NUM IN (1080,1078,1081,1084,1007,2343)
AND (I.IND_NAME = 'buy_sell_ind' AND U.BUY_SELL_IND = I.IND_VALUE)
AND S.LOCATION_NUM = L.LOCATION_NUM
AND U.COUNTERPART_COMPANY_NUM = C.COMPANY_NUM
AND T.BROKER_COMPANY_NUM = C1.COMPANY_NUM
AND U.TRADER_PERSON_NUM = P.PERSON_NUM
AND (X.EXCH_CONTRACT_NUM = E.EXCH_CONTRACT_NUM AND X.QUOTE_DEF_NUM = E.QUOTE_DEF_NUM)
AND U.MTM_QUOTE_DEF_NUM = Q.QUOTE_DEF_NUM
ORDER BY FUTURE_EXPIRATION_DT, RISK_PERIOD_CD, QUOTE_DEF_CD
The line in question is AND O.PROCESS_GROUP_CD like {?LOB}. I've treid equals and IN's for the like. I've tried {?@LOB}. This should be so easy. Where am I going wrong?
Any help would be appreciated.
I forgot to tell you that the error message says that there is an invalid character.
The last thing I tried was AND O.PROCESS_GROUP_CD IN ({?LOB})
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
That is almost it. I figured out that for commands the parameters have to be entered via Database Expert/Command Edit. You cannot create the parameter the normal way. That is unfortunate as the options for a paramter in command edit are sparse. When you use the parameter in the SQL you do have to use the quotes as you suggested becuase apparently the value is determined before the SQL command is evaluated.
I figured out that for commands the parameters have to be entered via Database Expert/Command Edit.
>> This is true. You do have to add the parameter to the parameter list in order to reference it in the command SQL.
You cannot create the parameter the normal way.
>> Not true. You have 2 options...
-- 1) Create the parameter in the "normal manner" then simply retype the parameter name in parameter list (making sure to use the exact same spelling).
-- 2) Create the parameter in the command's parameter list, then edit it from the design view/field explorer.
That is unfortunate as the options for a paramter in command edit are sparse.
>> The parameter list is only there to create the reference. Use the parameter designer, from the report design view to set the parameter details. (see above)
When you use the parameter in the SQL you do have to use the quotes as you suggested ...
>> Not necessarily true... CR does a VERY cool thing when it comes to passing parameters to commands. It actually does a direct substitution of the parameter value into the command SQL. So it simply depends on the syntax of the database you are using.
... because apparently the value is determined before the SQL command is evaluated.
>> Yes, and this is a good thing.
-- For example (Using SQL Server syntax), all of the following are valid
-- Exact text match
SELECT *
FROM TableName
WHERE TextColumn = '{?TextParameter}'
-- Exact numeric match
SELECT *
FROM TableName
WHERE NumberColumn = {?NumberParameter}
-- Wildcard match
SELECT *
FROM TableName
WHERE TextColumn LIKE '%{?TextParameter}%'
You can even pass entire blocks of SQL through a parameter, giving the effect of dynamic SQL without having to write dynamic SQL
For exmple... Say you want to make a parameter that allows the user to select "FirstName LastName" or "LastName, FirstName"...
Create a parameter like this...
Value 1
t.FirstName + ' ' + t.LastName AS Name
Desc 2
First then Last
Value 2
t.LastName + ', ' + t.FirstName AS Name
Desc 2
Last then First
Now write your SQL like this...
SELECT
{?NameParameter},
t.Column2,
t.Column3
FROM TableName AS t
The only limitation to using parameters in commands is your imagination. I've even passed entire select statements through commands.
HTH,
Jason
Hi,
Thanks for your posting it helps a lot, but still I am not able to pass parameters to select statement. Please refer the below screen shot and details. Could you please help?
I wrote the below query in Add Command - but getting an error - when I pass hard coded value, it is working fine.
Not working:-
SELECT SUM(XXXXX) from XXXX_YYY where ZZZZZ_TYPE in (1,3,4,6,10,12,13,21,23,30,31,2,11,20,22,32,33) and DATE > ({@Interpreted Start Date})
Working :-
SELECT SUM(XXXXX) from XXXX_YYY where ZZZZZ_TYPE in (1,3,4,6,10,12,13,21,23,30,31,2,11,20,22,32,33) and DATE > '09/17/2011'
FYI... I have already created Interpreted Start Date formula.
Thanks,
Veer Jetta
Veer,
Dates & Times present their own special pains in the back side when working with commands and parameters. The problem is that the formatted value that CR passes isn't necessarily compatible with the acceptable date formats of your RDBMS.
The best way to figure out the solution is to build a quick test report, add a table (in the Database expert, not a command) that has a data or datetime column, create a data or datetime parameter and add that parameter to the selection criteria in the Selection Expert.
Make sure the report executes and that it is in fact being filtered on the date column.
Now that you know it works, open the "Show SQL Query..." under Database in toolbar. This will give you the syntax that CR is using to successfully pass the date parameter.
HTH,
Jason
Jason's right about the format of dates. The DB is picky. I'm running ORacle 10g and it likes 'YYYYMMDD'. The other thing I noticed is that you did not enclose the parameter in quotes. You have to do that for strings and dates. Notice that in your hard-coded example you ahve the date enclosed in quotes. I'd try that first.
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.