I have a question for the SQL Server remote view gurus.
I need to create a remote view to a SQL database, and in the where clause I
would like to provide a list of values. And if the field matches one of
those values, then return the record.
Is there a way to do that?
Something like:
Local vp_List
vp_List = ["A","B","C"]
Use RemoteViewName
Remote View SQL Statement:
Select Table.* from dbo.Tablename where inlist(Tablename.Field, ?vp_List)
Author: Paul McNett
Posted: 2002-11-07 16:57:20 Link
On Thursday 07 November 2002 04:51 pm, Dan Neuman wrote:
> I have a question for the SQL Server remote view gurus.
Only if someone tells me why my combobox isn't working <g>...
> I need to create a remote view to a SQL database, and in the where clau=
se
> I would like to provide a list of values. And if the field matches one
> of those values, then return the record.
>
>
> Is there a way to do that?
>
>
> Something like:
> Local vp_List
> vp_List =3D ["A","B","C"]
> Use RemoteViewName
>
>
>
> Remote View SQL Statement:
> Select Table.* from dbo.Tablename where inlist(Tablename.Field, ?vp_Lis=
t)
try this (works with spt, not sure about remote views):
private vp_List
vp_list =3D [("A","B","C")]
* remote view:
select table.* from dbo.tablename where tablename.field in ?vp_list
--=20
Paul McNett - p@ulmcnett.com
Hollister, California, USA
Author: Dan Neuman
Posted: 2002-11-07 17:54:01 Link
Paul,
Thanks for your reply, I have already tried it, and I get the following
Error message when I try to open the view.
"Connectivity error: [Microsoft][ODBC SQL Server Driver]
[SQL Server]Line 1:Incorrect syntax near '@P1'."
Does this make sense to anyone?
Here is the actual Remote View generation script.
*********** View Definition
CREATE SQL VIEW "PCVPRJTemp" ;
REMOTE CONNECTION "pcadata" ;
AS select pcaprj.* ;
from dbo.pcaprj ;
where pcaprj.cprjid in ?vp_clist
=DBSetProp('PCVPRJTemp', 'View', 'UpdateType', 1)
=DBSetProp('PCVPRJTemp', 'View', 'WhereType', 4)
=DBSetProp('PCVPRJTemp', 'View', 'FetchMemo', .T.)
=DBSetProp('PCVPRJTemp', 'View', 'SendUpdates', .T.)
=DBSetProp('PCVPRJTemp', 'View', 'UseMemoSize', 255)
=DBSetProp('PCVPRJTemp', 'View', 'FetchSize', -1)
=DBSetProp('PCVPRJTemp', 'View', 'MaxRecords', -1)
=DBSetProp('PCVPRJTemp', 'View', 'Tables', 'dbo.pcaprj')
=DBSetProp('PCVPRJTemp', 'View', 'FetchSize', -1)
=DBSetProp('PCVPRJTemp', 'View', 'Comment', "")
=DBSetProp('PCVPRJTemp', 'View', 'BatchUpdateCount', 1)
=DBSetProp('PCVPRJTemp', 'View', 'ShareConnection', .T.)
=DBSetProp('PCVPRJTemp', 'View', 'Prepared', .F.)
=dbsetprop('pcvprjtemp', 'view', 'parameterlist', "vp_clist,'c'")
=DBSetProp('PCVPRJTemp', 'View', 'CompareMemo', .T.)
=DBSetProp('PCVPRJTemp', 'View', 'FetchAsNeeded', .F.)
**************** End of View Definition
Then I try to use the view with:
Public vp_cList
vp_cList = [("SP","SC","CA")] && Which I know are in the data.
USE PCAView!PcvPrjTemp && This is where I get the connectivity error.
***************************
Thanks for your help.
Dan Neuman
-----Original Message-----
From: profox@leafe.com [mailto:profox@leafe.com]On Behalf Of Paul McNett
Sent: Thursday, November 07, 2002 4:57 PM
To: Multiple recipients of ProFox
Subject: Re: Remote View Inlist Parameterized where clause
On Thursday 07 November 2002 04:51 pm, Dan Neuman wrote:
> I have a question for the SQL Server remote view gurus.
Only if someone tells me why my combobox isn't working <g>...
> I need to create a remote view to a SQL database, and in the where clause
> I would like to provide a list of values. And if the field matches one
> of those values, then return the record.
>
>
> Is there a way to do that?
>
>
> Something like:
> Local vp_List
> vp_List = ["A","B","C"]
> Use RemoteViewName
>
>
>
> Remote View SQL Statement:
> Select Table.* from dbo.Tablename where inlist(Tablename.Field, ?vp_List)
try this (works with spt, not sure about remote views):
private vp_List
vp_list = [("A","B","C")]
* remote view:
select table.* from dbo.tablename where tablename.field in ?vp_list
--
Paul McNett - p@ulmcnett.com
Hollister, California, USA
Author: Dan Neuman
Posted: 2002-11-07 21:14:33 Link
Mike,
Thanks for the reply.
tried that. same error message.
-----Original Message-----
From: profox@leafe.com [mailto:profox@leafe.com]On Behalf Of Mike McCann
Sent: Thursday, November 07, 2002 8:11 PM
To: Multiple recipients of ProFox
Subject: Re: Remote View Inlist Parameterized where clause
Dan Neuman <dneuman@pclv.com> wrote:
[("SP","SC","CA")]
try
[('SP','SC','CA')]
SQL Server does not like double quotes
--
Mike
|| Paul,
|| Thanks for your reply, I have already tried it, and I get the
|| following Error message when I try to open the view.
||
|| "Connectivity error: [Microsoft][ODBC SQL Server Driver]
|| [SQL Server]Line 1:Incorrect syntax near '@P1'."
||
|| Does this make sense to anyone?
||
||
|| Here is the actual Remote View generation script.
|| *********** View Definition
|| CREATE SQL VIEW "PCVPRJTemp" ;
|| REMOTE CONNECTION "pcadata" ;
|| AS select pcaprj.* ;
|| from dbo.pcaprj ;
|| where pcaprj.cprjid in ?vp_clist
||
|| =DBSetProp('PCVPRJTemp', 'View', 'UpdateType', 1)
|| =DBSetProp('PCVPRJTemp', 'View', 'WhereType', 4)
|| =DBSetProp('PCVPRJTemp', 'View', 'FetchMemo', .T.)
|| =DBSetProp('PCVPRJTemp', 'View', 'SendUpdates', .T.)
|| =DBSetProp('PCVPRJTemp', 'View', 'UseMemoSize', 255)
|| =DBSetProp('PCVPRJTemp', 'View', 'FetchSize', -1)
|| =DBSetProp('PCVPRJTemp', 'View', 'MaxRecords', -1)
|| =DBSetProp('PCVPRJTemp', 'View', 'Tables', 'dbo.pcaprj')
|| =DBSetProp('PCVPRJTemp', 'View', 'FetchSize', -1)
|| =DBSetProp('PCVPRJTemp', 'View', 'Comment', "")
|| =DBSetProp('PCVPRJTemp', 'View', 'BatchUpdateCount', 1)
|| =DBSetProp('PCVPRJTemp', 'View', 'ShareConnection', .T.)
|| =DBSetProp('PCVPRJTemp', 'View', 'Prepared', .F.)
|| =dbsetprop('pcvprjtemp', 'view', 'parameterlist', "vp_clist,'c'")
|| =DBSetProp('PCVPRJTemp', 'View', 'CompareMemo', .T.)
|| =DBSetProp('PCVPRJTemp', 'View', 'FetchAsNeeded', .F.)
|| **************** End of View Definition
||
|| Then I try to use the view with:
|| Public vp_cList
|| vp_cList = [("SP","SC","CA")] && Which I know are in the data.
|| USE PCAView!PcvPrjTemp && This is where I get the connectivity
|| error. ***************************
||
|| Thanks for your help.
|| Dan Neuman
||
|| -----Original Message-----
|| From: profox@leafe.com [mailto:profox@leafe.com]On Behalf Of Paul
|| McNett Sent: Thursday, November 07, 2002 4:57 PM
|| To: Multiple recipients of ProFox
|| Subject: Re: Remote View Inlist Parameterized where clause
||
||
|| On Thursday 07 November 2002 04:51 pm, Dan Neuman wrote:
||
||| I have a question for the SQL Server remote view gurus.
||
|| Only if someone tells me why my combobox isn't working <g>...
||
||
||| I need to create a remote view to a SQL database, and in the where
||| clause I would like to provide a list of values. And if the field
||| matches one of those values, then return the record.
|||
|||
||| Is there a way to do that?
|||
|||
||| Something like:
||| Local vp_List
||| vp_List = ["A","B","C"]
||| Use RemoteViewName
|||
|||
|||
||| Remote View SQL Statement:
||| Select Table.* from dbo.Tablename where inlist(Tablename.Field,
||| ?vp_List)
||
|| try this (works with spt, not sure about remote views):
||
|| private vp_List
|| vp_list = [("A","B","C")]
||
|| * remote view:
|| select table.* from dbo.tablename where tablename.field in ?vp_list
||
|| --
|| Paul McNett - p@ulmcnett.com
|| Hollister, California, USA
||
Author: Mike McCann
Posted: 2002-11-07 22:11:01 Link
Dan Neuman <dneuman@pclv.com> wrote:
[("SP","SC","CA")]
try
[('SP','SC','CA')]
SQL Server does not like double quotes
--
Mike
|| Paul,
|| Thanks for your reply, I have already tried it, and I get the
|| following Error message when I try to open the view.
||
|| "Connectivity error: [Microsoft][ODBC SQL Server Driver]
|| [SQL Server]Line 1:Incorrect syntax near '@P1'."
||
|| Does this make sense to anyone?
||
||
|| Here is the actual Remote View generation script.
|| *********** View Definition
|| CREATE SQL VIEW "PCVPRJTemp" ;
|| REMOTE CONNECTION "pcadata" ;
|| AS select pcaprj.* ;
|| from dbo.pcaprj ;
|| where pcaprj.cprjid in ?vp_clist
||
|| =DBSetProp('PCVPRJTemp', 'View', 'UpdateType', 1)
|| =DBSetProp('PCVPRJTemp', 'View', 'WhereType', 4)
|| =DBSetProp('PCVPRJTemp', 'View', 'FetchMemo', .T.)
|| =DBSetProp('PCVPRJTemp', 'View', 'SendUpdates', .T.)
|| =DBSetProp('PCVPRJTemp', 'View', 'UseMemoSize', 255)
|| =DBSetProp('PCVPRJTemp', 'View', 'FetchSize', -1)
|| =DBSetProp('PCVPRJTemp', 'View', 'MaxRecords', -1)
|| =DBSetProp('PCVPRJTemp', 'View', 'Tables', 'dbo.pcaprj')
|| =DBSetProp('PCVPRJTemp', 'View', 'FetchSize', -1)
|| =DBSetProp('PCVPRJTemp', 'View', 'Comment', "")
|| =DBSetProp('PCVPRJTemp', 'View', 'BatchUpdateCount', 1)
|| =DBSetProp('PCVPRJTemp', 'View', 'ShareConnection', .T.)
|| =DBSetProp('PCVPRJTemp', 'View', 'Prepared', .F.)
|| =dbsetprop('pcvprjtemp', 'view', 'parameterlist', "vp_clist,'c'")
|| =DBSetProp('PCVPRJTemp', 'View', 'CompareMemo', .T.)
|| =DBSetProp('PCVPRJTemp', 'View', 'FetchAsNeeded', .F.)
|| **************** End of View Definition
||
|| Then I try to use the view with:
|| Public vp_cList
|| vp_cList = [("SP","SC","CA")] && Which I know are in the data.
|| USE PCAView!PcvPrjTemp && This is where I get the connectivity
|| error. ***************************
||
|| Thanks for your help.
|| Dan Neuman
||
|| -----Original Message-----
|| From: profox@leafe.com [mailto:profox@leafe.com]On Behalf Of Paul
|| McNett Sent: Thursday, November 07, 2002 4:57 PM
|| To: Multiple recipients of ProFox
|| Subject: Re: Remote View Inlist Parameterized where clause
||
||
|| On Thursday 07 November 2002 04:51 pm, Dan Neuman wrote:
||
||| I have a question for the SQL Server remote view gurus.
||
|| Only if someone tells me why my combobox isn't working <g>...
||
||
||| I need to create a remote view to a SQL database, and in the where
||| clause I would like to provide a list of values. And if the field
||| matches one of those values, then return the record.
|||
|||
||| Is there a way to do that?
|||
|||
||| Something like:
||| Local vp_List
||| vp_List = ["A","B","C"]
||| Use RemoteViewName
|||
|||
|||
||| Remote View SQL Statement:
||| Select Table.* from dbo.Tablename where inlist(Tablename.Field,
||| ?vp_List)
||
|| try this (works with spt, not sure about remote views):
||
|| private vp_List
|| vp_list = [("A","B","C")]
||
|| * remote view:
|| select table.* from dbo.tablename where tablename.field in ?vp_list
||
|| --
|| Paul McNett - p@ulmcnett.com
|| Hollister, California, USA
||
Author: Mike McCann
Posted: 2002-11-08 08:20:19 Link
Dan
The only way I can make it work is like this.
pcparam = [ ( 'Berlin','London','Madrid' ) ]
? sqlexec(1,[SELECT * FROM customers WHERE city IN ] + pcparam ,[c1])
Mike
Author: Dan Neuman
Posted: 2002-11-08 09:19:15 Link
Thanks for all your input on this.
I received the answer from the VFE forum.
Another developer had the idea of using the CHARINDEX function in the Where
clause. Since my parameter field is a one character field, it works like I
need it.
Here is the solution.
Something like:
Local vp_List
vp_List = [A,B,C,] && must end with a comma
Use RemoteViewName
Remote View SQL Statement:
Select Table.*
from dbo.Tablename
where charindex( rtrim(TableName.FieldName)+',',?vp_list) > 0
Author: Chuck Urwiler
Posted: 2002-11-08 09:35:09 Link
Dan,
Unfortunately, I'm pretty sure you can't do what you're trying, since
SQL Server doesn't have an allowable syntax for this. In other words,
what SQL Server is getting is the following statement:=20
Select * from table where field in ( @p1 )
And there's no way for SQL Server to "expand" what you put into @p1,
which is the SQL Server side of the ?vp_list you're specifying. All this
will accept is a single value, no comma-delimited list acceptable here.=20
Alternatives: how many distinct values are you looking for? Could you do
this:=20
Select * from table where field =3D ?vp_p1 or field =3D ?vp_p2
Or, better yet, can you do this with data from the server? For example,
imagine that this query gets you the list (from the server) that you're
looking for:=20
Select field from othertable where somecondition =3D somevalue
(returns cursor with 'A','B','C')
Then you could send this:=20
Select * from table where field in (select field from other table where
somecondition =3D somevalue)
Instead of trying to parameterize the statement.=20
-Chuck Urwiler, MCT, MCSD, MCDBA
> -----Original Message-----
> From: Dan Neuman [mailto:dneuman@pclv.com]=20
> Sent: Thursday, November 07, 2002 7:52 PM
> To: Multiple recipients of ProFox
> Subject: Remote View Inlist Parameterized where clause
>=20
>=20
> I have a question for the SQL Server remote view gurus.
>=20
> I need to create a remote view to a SQL database, and in the=20
> where clause I
> would like to provide a list of values. And if the field=20
> matches one of
> those values, then return the record.
>=20
> Is there a way to do that?
>=20
> Something like:
> Local vp_List
> vp_List =3D ["A","B","C"]
> Use RemoteViewName
>=20
>=20
> Remote View SQL Statement:
> Select Table.* from dbo.Tablename where=20
> inlist(Tablename.Field, ?vp_List)
>=20
>=20
>=20=20
Author: Russell, Stephen
Posted: 2002-11-08 10:01:37 Link
Or you can make a SP on the server that gets a param string. Examine that
sting and parse through the ,'s execute that statement and return the
results.
Stephen Russell
Developer - Clinical Research Shared Resource
St. Jude Children's Research Hospital
Mail Stop 504
332 North Lauderdale St.
Memphis, TN 38105
(901) 495-3807 - Office
(901) 246-0159 - Cell
-----Original Message-----
From: Chuck Urwiler [mailto:chuck@eps-software.com]
Sent: Friday, November 08, 2002 9:35 AM
To: Multiple recipients of ProFox
Subject: RE: Remote View Inlist Parameterized where clause
Dan,
Unfortunately, I'm pretty sure you can't do what you're trying, since
SQL Server doesn't have an allowable syntax for this. In other words,
what SQL Server is getting is the following statement:
Select * from table where field in ( @p1 )
And there's no way for SQL Server to "expand" what you put into @p1,
which is the SQL Server side of the ?vp_list you're specifying. All this
will accept is a single value, no comma-delimited list acceptable here.
Alternatives: how many distinct values are you looking for? Could you do
this:
Select * from table where field = ?vp_p1 or field = ?vp_p2
Or, better yet, can you do this with data from the server? For example,
imagine that this query gets you the list (from the server) that you're
looking for:
Select field from othertable where somecondition = somevalue
(returns cursor with 'A','B','C')
Then you could send this:
Select * from table where field in (select field from other table where
somecondition = somevalue)
Instead of trying to parameterize the statement.
-Chuck Urwiler, MCT, MCSD, MCDBA
> -----Original Message-----
> From: Dan Neuman [mailto:dneuman@pclv.com]
> Sent: Thursday, November 07, 2002 7:52 PM
> To: Multiple recipients of ProFox
> Subject: Remote View Inlist Parameterized where clause
>
>
> I have a question for the SQL Server remote view gurus.
>
> I need to create a remote view to a SQL database, and in the
> where clause I
> would like to provide a list of values. And if the field
> matches one of
> those values, then return the record.
>
> Is there a way to do that?
>
> Something like:
> Local vp_List
> vp_List = ["A","B","C"]
> Use RemoteViewName
>
>
> Remote View SQL Statement:
> Select Table.* from dbo.Tablename where
> inlist(Tablename.Field, ?vp_List)
>
>
>
Author: Roman Suazo
Posted: 2002-11-08 14:58:09 Link
Hi!
Let me see if I can help you.
Try with $ instead of inlist.
Maybe this would work:
var='R'+chr(0)+'A'
selec * from table where field$&Var
HTH
_________________________________________________________________
Charla con tus amigos en lÃÅnea mediante MSN Messenger: