Re: query max characters limit

From: Anders Altberg (x_pragma_at_telia.com)
Date: 10/28/04

  • Next message: Anders Altberg: "Re: sqlexec() with multiple SQL commands"
    Date: Fri, 29 Oct 2004 00:57:53 +0200
    
    

    Sa tish

    As Rick said there's a limnti to the number of elments in IN() in VFP before
    VFP9. What you can try is ORing IN clasues together
      WHERE name IN ( 'A1', 'A2',... ) OR name IN('B1','B2',... )
    As this is a VFP query there are smarter and simpler ways to create use
    multiple search values.
    Put all the values in a cursor:
     CREATE CURSOR Temp (seachvalue C(3))
     INSERT INTO Temp VALUES ('A1)
     INSERT INTO Temp VALUES ('B1')
     SELECT .. WHERE Nname IN (SELECT searchvalue FROM Temp)
    SELECT .. WHERE EXISTS (SELECT * FROM Temp WHERE searchvalue=name)
     or put them into an array aSearchValues:
     WHERE ASCAN(aSearchValues, name) >0

    -Anders

    "Satish" <Satish@discussions.microsoft.com> wrote in message
    news:32A37324-92FA-4CD2-99F2-CD348F945532@microsoft.com...
    > Hi,
    > I am trying to access data from a table like
    > "select col1,col2,col3 from table1 where name in ('A1','A2','A3',..,..,..
    > ..,'An')"
    >
    > If the number of elements in the where clause are more, i get a error
    > message and it looks like there is some limitation in the number of
    > characters in the query (with sql server i havent observed any
    limitation).
    > When I try to avoid this problem by firing individual queries, performance
    is
    > severely impacted. Any suggestions on how to get around this problem?
    >
    > I am using VFP6 and fire these queries through VFP ODBC driver from my
    VC++
    > application,
    >
    > Thanks a lot for your time & support,
    > --
    > Satish
    > Invensys


  • Next message: Anders Altberg: "Re: sqlexec() with multiple SQL commands"