on 04-24-2018 6:22 PM
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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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}
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
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.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.