parameterized in clause

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel Fisherman - 08 Sep 2005 18:47 GMT

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.

Reply to this Message

Michel Walsh - 08 Sep 2005 21:04 GMT

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

>I would like to create a query that contains an IN clause whose elements
>are
[quoted text clipped - 12 lines]
>
> Thanks for any help you can offer.

Reply to this Message

Daniel Fisherman - 08 Sep 2005 21:11 GMT

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?

> Hi,
>
[quoted text clipped - 42 lines]
> >
> > Thanks for any help you can offer.

Reply to this Message

Michel Walsh - 08 Sep 2005 21:42 GMT

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

> 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
[quoted text clipped - 58 lines]
>> >
>> > Thanks for any help you can offer.

Reply to this Message

Daniel Fisherman - 08 Sep 2005 21:46 GMT

thanks.

> Hi,
>
[quoted text clipped - 68 lines]
> >> >
> >> > Thanks for any help you can offer.

Reply to this Message

Michel Walsh - 08 Sep 2005 21:54 GMT

Hi,

hit enter too fast...

FROM myTable INNER JOIN temp INNER JOIN myTable
ON myTable.Field1 LIKE "*" & temp.ItsFieldName & "*"

Reply to this Message

peregenem@jetemail.net - 09 Sep 2005 10:26 GMT

> 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.

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
;

Reply to this Message