sql select In values

The place for peer-to-peer support for VPM Enterprise 9.1, our latest framework.

sql select In values

Postby stecenko » Mon Jan 02, 2017 12:43 pm

I have this remote view in the Data Dictionary...

select * from t1T where t1T.pk in (?getIn)

in my code I have...

dimension dlaFilters [1,2]
store 'getIn' to dlaFilters [1,1]
store 'abc' to dlaFilters [1,2]
thisform.oVpmDatahandler.requeryview('t1T', @dlaFilters)
and that works.

But, these don't
store ['abc'] to dlaFilters [1,2]
store ['abc','def'] to dlaFilters [1,2]

From the query window of Sql Server manager, this works...
select * from t1T where t1T.pk in ('abc','def')

I don't need to select many records, the values string will never be more than 50 long.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: sql select In values

Postby Gerhard Schmidbauer » Tue Jan 03, 2017 4:20 am

Hi Richard,

including the literals in square brackets leads to >'abc'< insted of >abc< as value in the array. In the second case you get >'abc','def''< in dlaFilters[1,2].

HTH
Gerhard

stecenko wrote:I have this remote view in the Data Dictionary...

select * from t1T where t1T.pk in (?getIn)

in my code I have...

dimension dlaFilters [1,2]
store 'getIn' to dlaFilters [1,1]
store 'abc' to dlaFilters [1,2]
thisform.oVpmDatahandler.requeryview('t1T', @dlaFilters)
and that works.

But, these don't
store ['abc'] to dlaFilters [1,2]
store ['abc','def'] to dlaFilters [1,2]

From the query window of Sql Server manager, this works...
select * from t1T where t1T.pk in ('abc','def')

I don't need to select many records, the values string will never be more than 50 long.
Gerhard Schmidbauer
URANUS Software GmbH
Germany
Gerhard Schmidbauer
 
Posts: 152
Joined: Tue Jun 15, 2010 10:33 am
Location: Burtenbach, Bavaria, Germany

Re: sql select In values

Postby stecenko » Tue Jan 03, 2017 3:15 pm

I found a post that said this...

DECLARE @InList varchar(100)
SET @InList = ‘1,2,3,4’

SELECT *
FROM MyTable
WHERE Id IN (@InList)

doesn't work. Variables are not allowed in the value list.

So if it doesn't work in SQL, then it work work in VFP using a remote view.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: sql select In values

Postby Gerhard Schmidbauer » Wed Jan 04, 2017 5:20 am

Just tried this in VFP:

local lnList
lnList = ['abc','def']

SELECT * FROM TEST WHERE F1 IN (&lnList)

That worked for me. I did not test within VPM. But I wanted to let you some work. ;-)

HTH
Gerhard

stecenko wrote:I found a post that said this...

DECLARE @InList varchar(100)
SET @InList = ‘1,2,3,4’

SELECT *
FROM MyTable
WHERE Id IN (@InList)

doesn't work. Variables are not allowed in the value list.

So if it doesn't work in SQL, then it work work in VFP using a remote view.
Gerhard Schmidbauer
URANUS Software GmbH
Germany
Gerhard Schmidbauer
 
Posts: 152
Joined: Tue Jun 15, 2010 10:33 am
Location: Burtenbach, Bavaria, Germany

Re: sql select In values

Postby stecenko » Wed Jan 04, 2017 12:56 pm

Thanks.

But, that's a local view. The problem is in a remote view, SQL seems (or at least MS SQL 2012) doesn't like a variable in place of a value list.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: sql select In values

Postby Gerhard Schmidbauer » Thu Jan 05, 2017 3:44 am

I'm no expert in SQL Server at all. So I asked in FOXITE.COM and got:

Not possible.
Maybe possible in SQL Server 2016 with STRING_SPLIT() using T-SQL.

Sorry, that's all I "know".

Gerhard


stecenko wrote:Thanks.

But, that's a local view. The problem is in a remote view, SQL seems (or at least MS SQL 2012) doesn't like a variable in place of a value list.
Gerhard Schmidbauer
URANUS Software GmbH
Germany
Gerhard Schmidbauer
 
Posts: 152
Joined: Tue Jun 15, 2010 10:33 am
Location: Burtenbach, Bavaria, Germany

Re: sql select In values

Postby stecenko » Thu Jan 05, 2017 10:34 am

Thanks. That's confirmation. Now I won't spend any more time trying the impossible.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: sql select In values

Postby Gerhard Schmidbauer » Thu Jan 05, 2017 10:53 am

Hello again,

As already said I am noo expert for SQL Server, but Antonio Lopes is an expert an he wrote:

Code: Select all
  If you're running the SQL statement through a VFP program, the macro is expanded before
  the statement is  sent to the ODBC layer, so there is no need to change the statement:

  m.ListItems = "'abc','def'"
  m.Result = SQLEXEC(m.ODBCHandle, "SELECT * FROM Test WHERE F1 IN (&ListItems)")

  Note: you may want / need to set your values as parameters, depending on the caller
           environment.

  m.ListItems = "?'abc',?'def'"
  m.Result = SQLEXEC(m.ODBCHandle, "SELECT * FROM Test WHERE F1 IN (&ListItems)")

I don't know if this is applicable, but I wanted to let know.

BTW: Do you read Foxite.COM? There are some more answer to my question.

HTH
Gerhard

stecenko wrote:Thanks. That's confirmation. Now I won't spend any more time trying the impossible.
Gerhard Schmidbauer
 
Posts: 152
Joined: Tue Jun 15, 2010 10:33 am
Location: Burtenbach, Bavaria, Germany

Re: sql select In values

Postby stecenko » Thu Jan 05, 2017 2:00 pm

Yes, that helps. Antonio suggests using Sql Pass Through and that's what I'm trying.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia


Return to VPM Enterprise 9.1

Who is online

Users browsing this forum: No registered users and 2 guests

cron