This is a migrated thread and some comments may be shown as answers.

parameter syntax for multiple value selection

4 Answers 56 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Kevin
Top achievements
Rank 1
Kevin asked on 02 Apr 2012, 10:23 PM
Hiya.  I'm using Telerik Q3 2011 in Visual Studios 2010 on a machine running Windows 7.  I am new to Telerik and  learning the basics.  I am having trouble with the syntax for a parameter where multiple values will be allowed.  Currently I have the following in the sql query:

where a.problem_status in (:status)

This works fine if the parameter is set to single value.  However when I change the parameter to multi value and select multiple choices I get an error saying the sql query is not ended properly.

I have tried eliminating the parantheses with no luck.  I tried an = sign instead of "in" (not logical I know, but I tried it anyway), also without success.

Can someone tell me what I am doing wrong with the syntax?

Thanks!

Kevin

4 Answers, 1 is accepted

Sort by
0
Hadib Ahmabi
Top achievements
Rank 1
answered on 03 Apr 2012, 08:57 AM
Hi,
Saying (:param) and is list is like creating List< List< something> >.
it should be 
WHERE Something IN :Parameter
Double check the rest of your logic - values, data-sources, types, etc.
0
Kevin
Top achievements
Rank 1
answered on 03 Apr 2012, 02:58 PM
Hadib,

Thanks for your response - I appreciate it!  However I am still confused.  Here's my complete query:

SELECT a.numberprgn, a.open_time, a.close_time,a.assignment, a.assignee_name,  a.problem_status, a.opened_by, a.closed_by, a.category, a.subcategory, a.product_type, a.problem_type, a.brief_description, a.resolution_code, a.cause_code, a.severity_code,  a.contact_name, b.first_name, b.last_name, b.contact_phone, c.resolution, a.action 
  
from sc.probsummaryM1 a
  
inner join sc.contactsM1 b on (b.contact_name = a.contact_name)
inner join sc.ProbSummaryA1 c on (c.numberprgn = a.numberprgn)
  
where a.open_time > NEW_TIME(:startdate,'CST' ,'GMT') and
a.open_time < NEW_TIME(:enddate,'CST' ,'GMT')and
a.problem_status in :status

Looking at my last line it appears to me that this matches your description.  However It only works when I set the parameter multivalue to FALSE.  If I set it to TRUE and select more than one choice from my STATUS list, I get an Oracle error saying the  SQL command not ended properly. (ORA-00933).

Any other ideas or suggestions? I have looked at the rest of the logic.  The query works fine when multivalue is set to FALSE, so I don't think it's a data-source or type issue.

Again, thanks for the reply!  I appreciate the help!

Kevin
0
Hadib Ahmabi
Top achievements
Rank 1
answered on 05 Apr 2012, 07:49 AM
It seems that I have mislead you about the syntax :D It should be IN (:param) or at least the forums say so.
However, I read that Oracle has some trouble with multi-value param before version 9 http://connect.microsoft.com/SQLServer/feedback/details/125225/oracle-multi-value-parameter
The only other thing that comes to my mind is to try and debug see what query the server is trying to execute, it might give you a hint. 
0
Kevin
Top achievements
Rank 1
answered on 05 Apr 2012, 05:08 PM
Hadib,

Thanks for your reply.  You didn't mislead me.  I tried it both ways:  IN :status and also IN (:status), neither of which worked.  Now I am hopeful that the link you provided may be the solution.

I greatly appreciate your replies!  Thanks for trying to help!

Kevin
Tags
General Discussions
Asked by
Kevin
Top achievements
Rank 1
Answers by
Hadib Ahmabi
Top achievements
Rank 1
Kevin
Top achievements
Rank 1
Share this question
or