Page 1 of 1
SQL problem with parameter
Posted: Thu Feb 01, 2007 3:13 pm
by jing
Hi,
I have a sql query
Code: Select all
SELECT si.stockcode, si.stockgroup, sg.groupname, si.latestcost, si.description, si.bincode, si.sellprice1 as sellprice, si.x_prev_sellprice, si.x_sellprice_changed, sl.qty as quantity, si.STOCKCODE as barcode FROM stock_items si LEFT OUTER JOIN stock_groups sg ON si.stockgroup = sg.groupno LEFT OUTER JOIN stock_loc_info sl ON si.stockcode = sl.stockcode WHERE sl.location = 6 AND si.stockcode IN ( @stockCode) ORDER BY si.description
where @stockCode is a parameter based on a variable "StockCode" of type string.
I set the default value of "StockCode" to be @,UTP.5 as they are 2 valid stock codes - this doesn't work in the preview. If I set the default value of "StockCode" to @ (just 1 stock code), the report looks fine in the preview.
So, I guess I would like to know how to pass in parameter values when the parameter represents a string of different stock codes ?
thanks
SQL problem with parameter
Posted: Fri Feb 02, 2007 1:33 am
by Edward
Please define parameters for the DataSource according the following flash tutorial.
http://www.stimulsoft.com/livedemos/Rep ... eters.html
Also you can see the example Master-Detail in the group SQL from the Demo.exe Application which is provided with the standard delivery.
Thank you.
SQL problem with parameter
Posted: Sun Feb 04, 2007 2:38 pm
by jing
Hi, Thanks for your reply.
I think we have misunderstand each other.
I have no problem with creating parameters while on the report designer. I have created a parameter called @StockCode dynamically through code and I am planning to change the value of @StockCode dynamically as well, so the user doesn't need to worry about this.
I think the problem is to do with the SQL query
Code: Select all
SELECT si.stockcode, si.stockgroup, sg.groupname, si.latestcost, si.description, si.bincode, si.sellprice1 as sellprice, si.x_prev_sellprice, si.x_sellprice_changed, sl.qty as quantity, si.STOCKCODE as barcode FROM stock_items si LEFT OUTER JOIN stock_groups sg ON si.stockgroup = sg.groupno LEFT OUTER JOIN stock_loc_info sl ON si.stockcode = sl.stockcode WHERE sl.location = 6 AND si.stockcode IN ( @StockCode) ORDER BY si.description
The value of @StockCode can not contain more than 1 stock code !!
If @StockCode = "@" (where @ is a valid stock code), the report works fine
However,
If @StockCode = "@, UTP.5" (where UTP.5 is also a valid stock code), the report doesn't work - there is no output ??
Can you help me with this please?
thanks
SQL problem with parameter
Posted: Sun Feb 04, 2007 6:00 pm
by jing
Hi,
I have found a way around this.
Instead of @StockCode, I put {StockCode} - this reference to a variable called StockCode.
so if the variable StockCode has '@', 'UTP.5' as its values, then the query executes correctly.
I wonder why it doesn't work with parameter?
SQL problem with parameter
Posted: Mon Feb 05, 2007 4:17 am
by Edward
In that case also may be used the unnamed parameters. To use it please change the name of the parameter to the ? symbol in the SQL text.
Code: Select all
SELECT si.stockcode, si.stockgroup, sg.groupname, si.latestcost, si.description, si.bincode, si.sellprice1 as sellprice, si.x_prev_sellprice, si.x_sellprice_changed, sl.qty as quantity, si.STOCKCODE as barcode FROM stock_items si LEFT OUTER JOIN stock_groups sg ON si.stockgroup = sg.groupno LEFT OUTER JOIN stock_loc_info sl ON si.stockcode = sl.stockcode WHERE sl.location = 6 AND si.stockcode IN (?) ORDER BY si.description
For more detail about unnamed parameters please see the following flash tutorial
http://www.stimulsoft.com/livedemos/Rep ... ers_2.html
Thank you.