parameterized in clause

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to create a query that contains an IN clause whose elements are
determined by the value of a parameter:

PARAMETERS VariableIDs ???;
SELECT a.ID, a.VariableID, a.ExecutionIndex, a.ExecutionCondition,
a.ActionTypeID, a.Parameters, a.ValueSetID
FROM VariableActions AS a WHERE a.VariableID IN ([VariableIDs])

I can't seem to get this to work. There is no type of parameter (indicated
above byt the 3 question marks) that will seem to work. Any suggestions? Is
this possible?

Thanks for any help you can offer.
 
Hi,



SELECT whatever
FROM somewhere
WHERE ( "," & parameterList & "," ) LIKE ("*," & FieldName & ",*" )



is a possible solution, under the condition that the list of parameters does
not contain space after the coma, ie:

"4,5,9"

rather than

"4, 5, 9"


Indeed, if the fieldName hold the value 5, then the where clause evaluates,
after concatenations:

",4,5,9," LIKE "*,5,*"

which is true and the record is kept. If the field hold the value, say, 33,
the expression, after concatenations, is


",4,5,9," LIKE "*,33,*"

which is false, and the record is not kept.




Hoping it may help,
Vanderghast, Access MVP
 
Thanks, I know this to be a possible solution. Unfortunately, the
performance of such a query is fairly poor, as each record in the domain must
be examined by the db engine. I'm looking specifically for use of the IN
clause, as described in my original post, which would give much better
performance. With the IN clause, the db engine runs a search on an index to
find only those records listed in the IN clause. No other records are
examined.

Any suggestions?

Michel Walsh said:
Hi,



SELECT whatever
FROM somewhere
WHERE ( "," & parameterList & "," ) LIKE ("*," & FieldName & ",*" )



is a possible solution, under the condition that the list of parameters does
not contain space after the coma, ie:

"4,5,9"

rather than

"4, 5, 9"


Indeed, if the fieldName hold the value 5, then the where clause evaluates,
after concatenations:

",4,5,9," LIKE "*,5,*"

which is true and the record is kept. If the field hold the value, say, 33,
the expression, after concatenations, is


",4,5,9," LIKE "*,33,*"

which is false, and the record is not kept.




Hoping it may help,
Vanderghast, Access MVP



Daniel Fisherman said:
I would like to create a query that contains an IN clause whose elements
are
determined by the value of a parameter:

PARAMETERS VariableIDs ???;
SELECT a.ID, a.VariableID, a.ExecutionIndex, a.ExecutionCondition,
a.ActionTypeID, a.Parameters, a.ValueSetID
FROM VariableActions AS a WHERE a.VariableID IN ([VariableIDs])

I can't seem to get this to work. There is no type of parameter
(indicated
above byt the 3 question marks) that will seem to work. Any suggestions?
Is
this possible?

Thanks for any help you can offer.
Click to expand...
Click to expand...
 
Hi,


Use a temporary table with one field, one record per word, and use a JOIN.
That would be faster than an IN clause, in theory.


.... FROM myTable INNER JOIN temp INNER JOIN myTable LIKE "*" & temp & "*"



Hoping it may help,
Vanderghast, Access MVP


Daniel Fisherman said:
Thanks, I know this to be a possible solution. Unfortunately, the
performance of such a query is fairly poor, as each record in the domain
must
be examined by the db engine. I'm looking specifically for use of the IN
clause, as described in my original post, which would give much better
performance. With the IN clause, the db engine runs a search on an index
to
find only those records listed in the IN clause. No other records are
examined.

Any suggestions?

Michel Walsh said:
Hi,



SELECT whatever
FROM somewhere
WHERE ( "," & parameterList & "," ) LIKE ("*," & FieldName & ",*" )



is a possible solution, under the condition that the list of parameters
does
not contain space after the coma, ie:

"4,5,9"

rather than

"4, 5, 9"


Indeed, if the fieldName hold the value 5, then the where clause
evaluates,
after concatenations:

",4,5,9," LIKE "*,5,*"

which is true and the record is kept. If the field hold the value, say,
33,
the expression, after concatenations, is


",4,5,9," LIKE "*,33,*"

which is false, and the record is not kept.




Hoping it may help,
Vanderghast, Access MVP



Daniel Fisherman said:
I would like to create a query that contains an IN clause whose elements
are
determined by the value of a parameter:

PARAMETERS VariableIDs ???;
SELECT a.ID, a.VariableID, a.ExecutionIndex, a.ExecutionCondition,
a.ActionTypeID, a.Parameters, a.ValueSetID
FROM VariableActions AS a WHERE a.VariableID IN ([VariableIDs])

I can't seem to get this to work. There is no type of parameter
(indicated
above byt the 3 question marks) that will seem to work. Any
suggestions?
Is
this possible?

Thanks for any help you can offer.
Click to expand...
Click to expand...
Click to expand...
 
thanks.

Michel Walsh said:
Hi,


Use a temporary table with one field, one record per word, and use a JOIN.
That would be faster than an IN clause, in theory.


.... FROM myTable INNER JOIN temp INNER JOIN myTable LIKE "*" & temp & "*"



Hoping it may help,
Vanderghast, Access MVP


Daniel Fisherman said:
Thanks, I know this to be a possible solution. Unfortunately, the
performance of such a query is fairly poor, as each record in the domain
must
be examined by the db engine. I'm looking specifically for use of the IN
clause, as described in my original post, which would give much better
performance. With the IN clause, the db engine runs a search on an index
to
find only those records listed in the IN clause. No other records are
examined.

Any suggestions?

Michel Walsh said:
Hi,



SELECT whatever
FROM somewhere
WHERE ( "," & parameterList & "," ) LIKE ("*," & FieldName & ",*" )



is a possible solution, under the condition that the list of parameters
does
not contain space after the coma, ie:

"4,5,9"

rather than

"4, 5, 9"


Indeed, if the fieldName hold the value 5, then the where clause
evaluates,
after concatenations:

",4,5,9," LIKE "*,5,*"

which is true and the record is kept. If the field hold the value, say,
33,
the expression, after concatenations, is


",4,5,9," LIKE "*,33,*"

which is false, and the record is not kept.




Hoping it may help,
Vanderghast, Access MVP



message I would like to create a query that contains an IN clause whose elements
are
determined by the value of a parameter:

PARAMETERS VariableIDs ???;
SELECT a.ID, a.VariableID, a.ExecutionIndex, a.ExecutionCondition,
a.ActionTypeID, a.Parameters, a.ValueSetID
FROM VariableActions AS a WHERE a.VariableID IN ([VariableIDs])

I can't seem to get this to work. There is no type of parameter
(indicated
above byt the 3 question marks) that will seem to work. Any
suggestions?
Is
this possible?

Thanks for any help you can offer.
Click to expand...
Click to expand...
Click to expand...
 
Hi,

hit enter too fast...

FROM myTable INNER JOIN temp INNER JOIN myTable
ON myTable.Field1 LIKE "*" & temp.ItsFieldName & "*"
 
Daniel said:
Thanks, I know this to be a possible solution. Unfortunately, the
performance of such a query is fairly poor, as each record in the domain must
be examined by the db engine.
Click to expand...

For a more 'relational' approach, create a procedure (lowercase) to
parse your parameter into individual values into a table, then your
query is a simple join.

For an even more 'relational' approach, you can do the parsing using
SQL e.g. in a PROCEDURE (uppercase) a.k.a. 'parameter Query'. Here's an
example by my pal Celko, translated into Access/Jet (it may be best to
construct the Sequence table in Excel <g>)

CREATE TABLE InputStrings (
keycol VARCHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL)
;
INSERT INTO InputStrings
VALUES ('first', '12,34,567,896')
;
INSERT INTO InputStrings
VALUES ('second', '312,534,997,896')
;
INSERT INTO InputStrings
VALUES ('Third', '667,841')
;
INSERT INTO InputStrings
VALUES ('Forth', '523,842,225,227,458,369')
;
CREATE TABLE Parmlist (
keycol VARCHAR(10) NOT NULL,
parm INTEGER NOT NULL)
;
CREATE TABLE Sequence (seq INTEGER NOT NULL)
;
INSERT INTO Sequence VALUES (1)
;
INSERT INTO Sequence VALUES (2)
;
INSERT INTO Sequence VALUES (3)
;
....
INSERT INTO Sequence VALUES (999)
;
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CLNG(MID$(I1.input_string, S1.seq, MIN(S2.seq) - S1.seq - 1))
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE MID$(',' & I1.input_string & ',', S1.seq, 1) =
','
AND MID$(',' & I1.input_string & ',', S2.seq, 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq
;
 
Back
Top