[AccessD] HELP!! fastest way to execute sql

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?