General discussion

Locked

SQL quyer in VB reports

By vbbeginner ·
i am developing datareports with data environment and backend as oracle 9i.
i have to use the query like 'select * from det where name in (parval)'
but im not able to pass multiple values to parval like parval=23,34,45 or parval='23','34','45'.it is not accepting.
but the same query runs well from oracle 9i sql prompt.
do anyone have an idea of how to pass multiple values to a datareport command parameter?
thanks in advance

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by jesse.smedley In reply to SQL quyer in VB reports

Try parval In('23','34','45')

Collapse -

by Realvdude In reply to SQL quyer in VB reports

parval = "'23','34','45'"
qry = "select * from det where name in (" & parval & ")"

If parval is derived from an array you will want to create a string builder routine to correct format the sql syntax, like:

parval = ""
Dim cnt
For cnt = 0 To UBound(MyArray)
parval = parval + "'" + MyArray(cnt) + "',"
Next
If Len(parval) > 1 Then parval = Left(parval, Len(parval) - 1)

Obivously if your using something like Listbox for the parameter selection or an array that may not have a value for each element, you will need to conditionally add to the parval variable.

Collapse -

by Realvdude In reply to

Sorry, I noticed the double and single quotes are hard to distinguish. Substitulting dblQ for " and sngQ for ' here are the two lines:
parval = dblQdblQ
parval = parval + dblQsngQdblQ + MyArracy(cnt) + dblQsngQ,dblQ

Hope that helps.

Collapse -

String Parser for Subquery

by gsquared In reply to SQL quyer in VB reports

What I've had to do in similar circumstances is build an SQL function that parses an input parameter into a subtable and then use that in the where statement.

I'm not sure how to do this in Oracle. All my code is T-SQL. But here's how I do it in T-SQL:

create function fString_Parser2
(@List_in varchar(8000),
@Delim_in varchar(10))
returns @Strings table
(StrValue varchar(8000))
as
/*
***** Takes a varchar string and turns it into a table of
***** selectable substrings
*/
begin
--Parses List
while charindex(@delim_in, @list_in) > 0
begin
insert into @Strings (strvalue)
select replace(ltrim(rtrim(substring(@list_in,1, charindex(@delim_in,@list_in)+len(@delim_in)-1))), @delim_in, '')

set @list_in = rtrim(ltrim(substring(@list_in, charindex(@delim_in,@list_in) + len(@delim_in), len(@list_in))))
End
--Inserts final value
insert into @strings (strvalue)
select replace(ltrim(rtrim(@list_in)), @delim_in, '')
Return
end
go
create proc pSelect
(@Names_in varchar(8000),
@Delim_in varchar(10))
as
select *
from det
where name in
(select strval
from dbo.fstring_parser2(@names_in, @delim_in))

Again, I don't know if that translates well to Oracle's implementation of SQL, but it works nicely in T-SQL.

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums