cancel
Showing results for 
Search instead for 
Did you mean: 

Parameter in command object giving error

ginger_fabricius
Explorer
0 Kudos

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!

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Answers (1)

Answers (1)

DellSC
Active Contributor
0 Kudos

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

ginger_fabricius
Explorer
0 Kudos

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 ?