cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Bind Parameter Error

jopav613
Explorer
0 Kudos

crystaql-report-sql-bind-parameter-error-screensho.jpg

Hi. I'm trying to create a command for use in Crystal Reports 2016 and I'm getting this error and don't know what the cause is. Does anyone know a fix for this? I searched the forum and couldn't find anyone with a similar problem.

To give you some background, I'm creating this command as I'm joining my database to another external DB and it's slowing down memory hogging my CR report like a turtle. I read that by copying the sql into a command, I can drastically increase the performance and eliminate the big lag.


Thanks!

Accepted Solutions (0)

Answers (1)

Answers (1)

DellSC
Active Contributor
0 Kudos

Please take a look at my blog post about how to use commands in Crystal here.

If this doesn't help you solve the issue, would you please post the command that you're using? There's a problem with the SQL that's causing this issue.

-Dell

jopav613
Explorer
0 Kudos

Sorry for not including the command originally. I basically copy/pasted from another report from within CR into the CR report I'm trying to create.I looked through the link you provided and though it was really helpful, I still can't figure out why this is erroring out.

Here it is:

tov SELECT `itemsuev31`.`ITEM`, `itemsuev31`.`DESCRIPTION`, `itemsuev31`.`MP`, `itemsuev31`.`InnerPack`, `itemsuev31`.`CATEGORY`, `itemsuev31`.`DEPARTMENT`, `itemsuev31`.`Incoming`, `itemsuev31`.`PRICEA`, `itemsuev31`.`PRICE2P`, `itemsuev31`.`PRICE3P`, `itemsuev31`.`PRICE4P`, `itemsuev31`.`AVAILABILITY`, `itemsuev31`.`PROPOSAL`, `itemsuev31`.`WaterQtynumber1`, `itemsuev31`.`WATERDATE1`, `itemsuev31`.`WaterQty2`, `itemsuev31`.`WATERDATEnum2`, `itemsuev31`.`WaterQty3`, `itemsuev31`.`WATERDATE3`, `itemsuev31`.`QTYONORDER`, `itemsuev31`.`MATERIAL`, `itemsuev31`.`USAGE`, `itemsuev31`.`PATTERN`, `itemsuev31`.`PODATE1`, `itemsuev31`.`POQty1`, `itemsuev31`.`PODATE2`, `itemsuev31`.`POQty2`, `itemsuev31`.`PODATE3`, `itemsuev31`.`POQty3`, `itemsuev31`.`origin`, `itemsuev31`.`Qty_WH_JC`, `itemsuev31`.`Qty_WH_LB`, `itemsuev31`.`Qty_WH_CA`, `items1`.`generalDesc`, `itemsuev31`.`PRICE1P`, `items1`.`onorder` FROM `tov`.`itemsuev3` `itemsuev31` INNER JOIN `tov`.`items` `items1` ON `itemsuev31`.`ITEM`=`items1`.`itemnum` WHERE `itemsuev31`.`USAGE`='BEVERAGE DISPENSERS' EXTERNAL JOIN itemsuev31.ITEM={?Mysql: locations1.itemnum} Mysql SELECT `locations1`.`comment`, `locations1`.`itemnum` FROM `HEANDB`.`locations` `locations1` WHERE `locations1`.`itemnum`={?tov: itemsuev31.ITEM}

DellSC
Active Contributor
0 Kudos

Are both database schemas on the same database server or are they on different servers?

If they're on different database servers you won't be able to use a command and you'll have to let Crystal do it's thing, even though it's slow.

If they're on the same server, you're going to have to change the syntax of the query - what you copied is very specific to Crystal when linking to multiple databases and won't work in a command. Here's what your query should look like if the two schemas are on the same database server:

SELECT 
  itemsuev3.ITEM, 
  itemsuev3.DESCRIPTION, 
  itemsuev3.MP, 
  itemsuev3.InnerPack, 
  itemsuev3.CATEGORY, 
  itemsuev3.DEPARTMENT, 
  itemsuev3.Incoming, 
  itemsuev3.PRICEA, 
  itemsuev3.PRICE2P, 
  itemsuev3.PRICE3P, 
  itemsuev3.PRICE4P, 
  itemsuev3.AVAILABILITY, 
  itemsuev3.PROPOSAL, 
  itemsuev3.WaterQtynumber1, 
  itemsuev3.WATERDATE1, 
  itemsuev3.WaterQty2, 
  itemsuev3.WATERDATEnum2, 
  itemsuev3.WaterQty3, 
  itemsuev3.WATERDATE3, 
  itemsuev3.QTYONORDER, 
  itemsuev3.MATERIAL, 
  itemsuev3.USAGE, 
  itemsuev3.PATTERN, 
  itemsuev3.PODATE1, 
  itemsuev3.POQty1, 
  itemsuev3.PODATE2, 
  itemsuev3.POQty2, 
  itemsuev3.PODATE3, 
  itemsuev3.POQty3, 
  itemsuev3.origin, 
  itemsuev3.Qty_WH_JC, 
  itemsuev3.Qty_WH_LB, 
  itemsuev3.Qty_WH_CA, 
  items.generalDesc, 
  itemsuev3.PRICE1P, 
  items.onorder,
  locations1.comment  
FROM tov.items items
  INNER JOIN tov.itemsuev3 itemsuev3 
    ON items.itemnum = itemsuev3.ITEM
      AND itemsuev3.USAGE = 'BEVERAGE DISPENSERS'
  INNER JOIN HEANDB.locations locations 
    ON itemsuev3.ITEM = locations.itemnum

Note that I did some tweaking on the order of the tables in the from clause. I specifically swapped items and itemsuev3 so that I could move the filter on the USAGE field out of the Where clause and into the Join. This will help make the query run faster.

If it's possible that there are records that don't have a match in the locations table and you want to show the data whether or not there's a comment, change the Inner Join on locations to a Left Join.

-Dell

jopav613
Explorer
0 Kudos

Dell,

Firstly I really appreciate your help with this and your time as I am fairly new with SQL.

I first just ran the code as you wrote it and I got this error screenshot-error-before-adding-a-comma-to-the-end.jpg

I then noticed that the comma was missing from the location1 line and put one in as I thought that was causing it to error and then I got the next error screenshot-error-after-adding-a-comma-to-the-end-o.jpg

??

Thanks.

DellSC
Active Contributor
0 Kudos

Change "locations1" to "locations" and try again. That should fix it.

-Dell