MATLAB Central Newsreader - Using a variable in sql query: WHERE, IN

Using a variable in sql query: WHERE, IN

Vincent — Sun, 09 Nov 2008 18:41:02 -0500

Hey all,

My first post here. How is everyone doing? Just want to say that this is a splendid resource for finding solutions to questions. I can't be more grateful. :)

I have a little issue pertaining to writing the sql for the exec function in the database toolbox. Hopefully someone can help me.

Using the Visual Query Builder, this statement works:

SELECT * FROM mytable WHERE id IN (680)

It also works from a M-file:

curs=exec(conn,'SELECT * FROM mytable WHERE id IN (680)');

Now my question is I want 680 to be a variable, eg, G, instead of a constant. Under the Database manual, 7-37 to 7-38, there is a short explanation on how to do that with the WHERE clause using square brackets and 2 pairs of quotes.

I tried doing that with the IN operator but the M-file produces a Unexpected Matlab Expression. I figure that the pair of round brackets that guard the variable is the culprit. But not sure how so.

Does anyone here have a solution or perhaps a suggestion?

Thank you for reading.

Re: Using a variable in sql query: WHERE, IN

Nick Denman — Mon, 10 Nov 2008 01:48:02 -0500

"Vincent " <vseah@hoodiny.com> wrote in message <gf7aru$aq5$1@fred.mathworks.com>...
> Hey all,
>
> My first post here. How is everyone doing? Just want to say that this is a splendid resource for finding solutions to questions. I can't be more grateful. :)
>
> I have a little issue pertaining to writing the sql for the exec function in the database toolbox. Hopefully someone can help me.
>
> Using the Visual Query Builder, this statement works:
>
> SELECT * FROM mytable WHERE id IN (680)
>
> It also works from a M-file:
>
> curs=exec(conn,'SELECT * FROM mytable WHERE id IN (680)');
>
> Now my question is I want 680 to be a variable, eg, G, instead of a constant. Under the Database manual, 7-37 to 7-38, there is a short explanation on how to do that with the WHERE clause using square brackets and 2 pairs of quotes.
>
> I tried doing that with the IN operator but the M-file produces a Unexpected Matlab Expression. I figure that the pair of round brackets that guard the variable is the culprit. But not sure how so.
>
> Does anyone here have a solution or perhaps a suggestion?
>
> Thank you for reading.

Hi Vincent

Try the following:

G = '680';
query = ['SELECT * FROM mytable WHERE id IN (',G,')'];

Hope that helps,
Nick

Re: Using a variable in sql query: WHERE, IN

Vincent — Mon, 10 Nov 2008 16:44:02 -0500

Nick,

Thank you for the suggestion!! Much appreciated! :)

Vince

>
> Hi Vincent
>
> Try the following:
>
> G = '680';
> query = ['SELECT * FROM mytable WHERE id IN (',G,')'];
>
> Hope that helps,
> Nick

Re: Using a variable in sql query: WHERE, IN

pierca — Thu, 15 Jan 2009 10:48:12 -0500

Hi all,
It's my first post here and I don't speak english very well, hope you understand!
I have Vincent's same problem but if I try the following

> num = 'IT001E082';
> e = exec(conn,'SELECT * FROM Mytable WHERE "Pod No_" IN(',num,')' );

written in a M file Matlab says that there are Too many input arguments I think because it takes G as an argument of exec.
Does anyone here have a solution or a suggestion?

Thank you for reading.

Re: Using a variable in sql query: WHERE, IN

Steven Lord — Thu, 15 Jan 2009 14:36:29 -0500

"pierca" <pierca85@gmail.com> wrote in message
news:7302393.1232016523112.JavaMail.jakarta@nitrogen.mathforum.org...
> Hi all,
> It's my first post here and I don't speak english very well, hope you
> understand!
> I have Vincent's same problem but if I try the following
>
>> num = 'IT001E082';
>> e = exec(conn,'SELECT * FROM Mytable WHERE "Pod No_" IN(',num,')' );
>
> written in a M file Matlab says that there are Too many input arguments I
> think because it takes G as an argument of exec.
> Does anyone here have a solution or a suggestion?

You need to concatenate the strings.

x = 'world';
disp(['Hello ', x])

--
Steve Lord
slord@mathworks.com

Re: Using a variable in sql query: WHERE, IN

pierca — Thu, 15 Jan 2009 16:06:49 -0500

Thank you very much your suggestion works!!!

Re: Using a variable in sql query: WHERE, IN

Rakesh — Fri, 19 Jun 2009 20:23:02 -0400

Hello,

I have a similar problem, but I do not fully understand the solution described below. I am trying to pass a variable from a for loop into the sql query as shown below. But MATLAB gives an error saying too many input arguments. Can you suggest any solutions ?

for k = 1:40

exec(connA,'SELECT NAME FROM EMP_NAMES WHERE EMPID IN (',k,')')

Thanks.

Rakesh.

"Steven Lord" <slord@mathworks.com> wrote in message <gknhld$isp$1@fred.mathworks.com>...
>
> "pierca" <pierca85@gmail.com> wrote in message
> news:7302393.1232016523112.JavaMail.jakarta@nitrogen.mathforum.org...
> > Hi all,
> > It's my first post here and I don't speak english very well, hope you
> > understand!
> > I have Vincent's same problem but if I try the following
> >
> >> num = 'IT001E082';
> >> e = exec(conn,'SELECT * FROM Mytable WHERE "Pod No_" IN(',num,')' );
> >
> > written in a M file Matlab says that there are Too many input arguments I
> > think because it takes G as an argument of exec.
> > Does anyone here have a solution or a suggestion?
>
> You need to concatenate the strings.
>
> x = 'world';
> disp(['Hello ', x])
>
> --
> Steve Lord
> slord@mathworks.com
>