[AccessD] HELP!! fastest way to execute sql

Thanks for the responses.

I did a len() on some of the records I have locally...and some of the fields 
are over 8000.  How big of a field can you have in SQL Server...and can you 
still search with wild cards?

My dba's are looking for an informix solution...so I will pass along the 
suggestion below.

I found on IBMs site that you cannot do any comparison condition on a TEXT 
field other than IS NULL and IS NOT NULL.(I now hate informix)

ALL ideas are graciously accepted at this point(no matter how out there)...

Thanks again for everything,

Mark A. Matte


>From: "Robert L. Stewart" <rl_stewart at highstream.net>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: accessd at databaseadvisors.com
>CC: markamatte at hotmail.com
>Subject: Re: [AccessD] HELP!! fastest way to execute sql
>Date: Fri, 14 Jul 2006 13:44:12 -0400
>
>Mark,
>
>Yes, you can do a wild card in SQL Server and Oracle.  Those are the two 
>that I
>have the experience in.  Except for the specific wildcard character, the 
>syntax
>that I sent you before would work on both of them.
>
>How big can your memo field be?  With SQL Server, you can do a varchar of 
>8000.
>Would it be bigger than that?  There is a CAST function in SQL Server that 
>would
>allow me to change the type of the data to varchar(8000) so I could use it 
>if
>there was not going to be more than that in it.  Assuming there would be 
>more,
>see below.
>
>The error you are getting seems to be a limitation of Informix.  You might 
>have
>to get the DBAs to create a stored proc/view for you that breaks the memo 
>field
>up into smaller chunks so you can search each chunk.
>
>Then you would use something like this:
>
>SELECT case_id, DescChunk1, DescChunk2, DescChunk3, DescChunk4
>FROM   vinformix_ps_rc_case_note
>WHERE  (DescChunk1 Like "%night%")
>        OR (DescChunk2 Like "%night%")
>        OR (DescChunk3 Like "%night%")
>        OR (DescChunk4 Like "%night%")
>
>
>The view would be something like this:
>
>CREATE VIEW vinformix_ps_rc_case_note
>AS
>SELECT case_id,
>        substring(rc_descrlong,1,4000) AS DescChunk1,
>        substring(rc_descrlong,4001,4000) AS DescChunk1,
>        substring(rc_descrlong,8001,4000) AS DescChunk1,
>        substring(rc_descrlong,12001,4000) AS DescChunk1,
>FROM informix_ps_rc_case_note
>
>The stored proc would use the view and get parameters for each chunk.
>
>CREATE PROCEDURE uspGetData
>       @Word varchar(30)
>AS
>SELECT case_id, DescChunk1, DescChunk2, DescChunk3, DescChunk4
>FROM   vinformix_ps_rc_case_note
>WHERE  (DescChunk1 Like "%" + @Word + & "%")
>        OR (DescChunk2 Like "%" + @Word + & "%")
>        OR (DescChunk3 Like "%" + @Word + & "%")
>        OR (DescChunk4 Like "%" + @Word + & "%")
>
>You would need to do as many "chunks" as it would take to get all of the 
>data.
>
>You would call the stored proc from an Access pass through query like this:
>
>EXE uspGetData "night"
>
>You would just replace the word night each time.
>
>Robert L. Stewart
>The Dyson Group International
>Software for the Non-profit Enterprise
>Expanding your Sphere of Knowledge
>
>
>Quoting accessd-request at databaseadvisors.com:
>
> > Date: Fri, 14 Jul 2006 16:02:27 +0000
> > From: "Mark A Matte" <markamatte at hotmail.com>
> > Subject: Re: [AccessD] HELP!! fastest way to execute sql
> > To: accessd at databaseadvisors.com
> > Message-ID: <BAY121-F61272AFF17CEF49950501D26F0 at phx.gbl>
> > Content-Type: text/plain; format=flowed
> >
> > This problem just seems to grow.  I have written the passthough 
>query...but
> > Informix says I cannot use a wild card on this type of
> > field(TEXT,LONGCHAR,BLOB,MEMO).  I have contacted the DBA...and they are
> > lost to(but trying to look)...so...2 questions:
> > 1.  Anyone know how to search TEXT fields on informix with a wildcard?
> > 2.  Can you do this on an SQL or MYSQL db?
> >
> > Desperately begging,
> >
> > Mark A. Matte
> >
> > P.S...The error on the passthrough surprised me because I know I have 
>done
> > this type of search on informix before...so I called the DBA and had 
>them
> > watch what SQL was actually executed.  If they run the same SQL on the
> > server that I use in the Passthrough...they get the same error I 
>do...but if
> > I just use a select query against a linked table...I get
> > results(unfortunately this takes a very long time...forever)...now I 
>know
> > why.  Using a select query to linked table:
> > SELECT informix_ps_rc_case_note.case_id,
> > informix_ps_rc_case_note.rc_descrlong
> > FROM informix_ps_rc_case_note
> > WHERE (((informix_ps_rc_case_note.rc_descrlong) Like "*night*"));
> >
> > When I execute this in A97 or A2K...this is what hits the server:
> > SELECT informix_ps_rc_case_note.case_id,
> > informix_ps_rc_case_note.rc_descrlong
> > FROM informix_ps_rc_case_note;
> > Either access or informix ignores my where clause...but access returns 
>the
> > correct records.
> >
> > Any ideas, thoughts, comments, suggestions?
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com