Jamesx


I need help in creating a custom string for a multi-value parameter going against an Oralce 8 database.

I'm trying to build a string that looks like ('1401', 'HACT', '1504')

If I do it as an expression in a test report the results are perfect but in production it does not like it.

This is what I have based on some examples I have found but it complains it needs a )

These are the last few lines of the query

and f.shipto = a.shipto
and f.salesman in & " ('" & Join(Parameters!Salesrep.Value,"','") & "')"
GROUP BY a.company_id, a.whse#, f.salesman, .product_line, d.product_group, e.name
)
order by conoxx, whsexx, slsrepxx, sortcode




Re: Help with Oralce 8 - Multi-Value Parameters

Lisa Nicholls


Try leaving out the WHERE clause at first and build a debug expression that shows the string you've been building, in a test report, instead. You'll probably spot where it's wrong.

This may help:

http://spacefold.com/lisa/post/Writing-Dynamic-SQL-in-and-for-RDLs.aspx

My first guess, without doing this myself, is that you have a sales rep in Production with a quotation mark in his/her name (such as O'Hara).

In this case you would have to do something like this:

Code Block


Join(Replace(Parameters!Salesrep.Value,"'","''"),"','")

... but I don't know for sure that this is where the failure is occuring.

>L<







Re: Help with Oralce 8 - Multi-Value Parameters

Jamesx

I see what you are trying to do and I have read the paper "dynamic SQL" but I cannot even get a simple

="Select Company as CNO from cono" to execute. It does not like the =.

Is there some setting I have to set or do you have to do this as a stored procedure.






Re: Help with Oralce 8 - Multi-Value Parameters

Lisa Nicholls

>>It does not like the =.

What does not like the = What is the error message you get

(You do have the command type set as TEXT not stored procedure, right )

>L<






Re: Help with Oralce 8 - Multi-Value Parameters

Jamesx

I cannot get youre example to work so I am trying to setup a simple query (data tab) to test how it all works.

I have it set to "TEXT"

I cannot run from the data tab as the ! is disabled.

When I try to assign a field to a table (layout) it tells me there are no fields.

="Select company as CNO from cono"

If I can get this to work I can probably figure out how to splice my real query together to get the Join parameters to work.





Re: Help with Oralce 8 - Multi-Value Parameters

Lisa Nicholls

Yes, the ! may be disabled -- that's not the same as an error <s>. And it's not really a problem. Your dynamic additions to your base query are going to be filter conditions and stuff like that -- not your fields list.

I covered this in the walkthrough:

How do I get the the right information in my Datasets list

If you started this process with a non-dynamic query and you haven't changed your output fields list, you're already all set. If not, you can create a temporary query using literals, for example:

SELECT 10 AS OrderNo, 'Pending' As OrderStatus

... execute that query once in the Dataset window, press the little refresh button, and you should be all synched. If not, you can right-click in the Datasets window to explicitly Add fields [.. screen shot link here] by name.

In your case you can just use SELECT company as CNO from cono as your starting query. If the non-dynamic query works, then put = and quotes around it and it will work. Just because you can't always run it from that window doesn't mean it's broken.

Recap:

The Designer tells you that there are no fields because you haven't set up your Datasets list. If you do a non-dynamic query first, run that and press the refresh button (see above) the fields will be in the Dataset properly. Or (see above) just add the fields to the dataset yourself. Once you've done that you "assign fields to a table (layout)", preview, etc.

>L<






Re: Help with Oralce 8 - Multi-Value Parameters

Jamesx

Yes !!!!!!!!!!!!!!!!!!!! I finally got it to work. The quotes and double-quotes will drive you crazy.

RS and Oracle need to learn to play together better.

It might be easier to just have the DBA convert Oracle 8 to Oracle 9.

Thank you very much.





Re: Help with Oralce 8 - Multi-Value Parameters

Lisa Nicholls

>>The quotes and double-quotes will drive you crazy.

Yes <rofl>. They certainly will...

Good for you! Onward and upward,

>L<