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"

    Relevant Pages

    • Re: VFP8 wont install, wont run
      ... I would delete all the files in the Temp folder. ... I would also try starting VFP with the -A switch to avoid reading the ... It generates c0000005 on startup. ...
      (microsoft.public.fox.programmer.exchange)
    • Re: When is the Temp Directory used?
      ... > when you create cursors, run queries into cursors, and index cursors. ... > VFP by default uses the windows temp directory. ... >> INDEX ON POSTDATE TAG POSTDATE ...
      (microsoft.public.fox.programmer.exchange)
    • Re: When is the Temp Directory used?
      ... when you create cursors, run queries into cursors, and index cursors. ... VFP by default uses the windows temp directory. ... VFP will clean up it's own temp files. ...
      (microsoft.public.fox.programmer.exchange)
    • Re: When is the Temp Directory used?
      ... > The DBF directory is used when you create tables. ... > VFP by default uses the windows temp directory. ... > VFP will clean up it's own temp files. ...
      (microsoft.public.fox.programmer.exchange)
    • Re: Purchase 8.0 or want for 9.0?
      ... Along with what other says about the 2GB limitation, VFP is more able to ... > one-stop solution for application and database development. ... >> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP ...
      (microsoft.public.fox.programmer.exchange)