on 02-05-2016 7:20 PM
I am trying to add a static parameter to my command object in Crystal. I am going to use it in my where clause like this:
WHERE ARPB_TRANSACTIONS.TX_TYPE_C=1 AND (ARPB_TRANSACTIONS.POST_DATE between @StartDate and @EndDate)
and (ARPB_TRANSACTIONS.LOC_ID in {?Location ID} OR 0 in {?Location ID})
and ({BILL_AREA.FIN_SUBDIV_ID in {?Financial Subdivision ID} OR 0 in {?Financial Subdivision ID})
I get this error message:
SAP Crystal Reports
---------------------------
Failed to retrieve data from the database.
Details: 42000:[Microsoft][SQL Server Native Client 11.0]Syntax error, permission violation, or other nonspecific error
I am using Crystal 11
Please help!
Hello, Ginger,
If you could, confirm with the SQL log, but it seems to be a SQL syntax error.
From what I can see, the SQL would boil down to FIELD in STRING. It should be FIELD in SET
i.e. (note the parens)
AND ( ARPB_TRANSACTIONS.LOC_ID IN ( {?Location ID} ) )
OR ( BILL_AREA.FIN_SUBDIV_ID IN ( {?Financial Subdivision ID} ) )
OR ( 0 IN ( {?Location ID}, {?Financial Subdivision ID} ) )
In addition, why use - IN for the first 2? why not =?
Hope that helps,
- John
addendum: reformatted with = (equals) - AND corrected the logic (ORs should be grouped separately from the AND, copy/paste would fail with original)
second addendum: didn't look at YOUR parens well enough, my apologies...
and ( {?Location ID} IN ( ARPB_TRANSACTIONS.LOC_ID, 0) )
and ( {?Financial Subdivision ID} IN ( BILL_AREA.FIN_SUBDIV_ID, 0 ) )
That's syntactically and logically better
- John
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You can't use parameters in the format "@parmName". You will need to create StartDate and EndDate parameters with a data type of Date and then change the line in the Where clause to be:
(ARPB_TRANSACTIONS.POST_DATE between {?StartDate} and {?EndDate})
The rest of the where clause looks good.
-Dell
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I am still getting that error. It was working with the @startdate,@enddate. It only stopped working and giving me the error when I added the lines:
and (ARPB_TRANSACTIONS.LOC_ID in {?Location ID} OR 0 in {?Location ID})
and ({BILL_AREA.FIN_SUBDIV_ID in {?Financial Subdivision ID} OR 0 in {?Financial Subdivision ID})
Could it be the combination ?
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
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.