NetTalk Central

NetTalk Web Server => Web Server - Ask For Help => Topic started by: broche on March 24, 2014, 09:44:05 AM

Title: Postgres and Searches
Post by: broche on March 24, 2014, 09:44:05 AM

Clarion 8
Nettalk 8
Postgres 9....

Simple table for Staff members with default sort column of staffname
Data is mixed case, eg Roche, Brian
Have a locator on the table
Set as Starts with works great, type in 'Roc' and it finds the record.
Set as Contains and it starts OK when I type 'R' then filters all records out so none show.
I have the column set as-is, do not have case sensitive on (Either way does not work)
Same applies to Search
I know this is a case sensitive thing but why works on Starts with and not on the others?

Brian

Title: Re: Postgres and Searches
Post by: Bruce on March 24, 2014, 12:19:48 PM

Turn on "send filter and order to debugview" and see what it says as you type the locator.

Title: Re: Postgres and Searches
Post by: broche on March 25, 2014, 12:55:16 PM

OK - Did that, the results below

City - is Caps first letter and lower case the rest 'Brookfield'

Entered 'br'
Contains
Case: As-Is
[4540] [st] [netTalk][thread=3] Browsestaff :: Filter: (sta:activeflag='Y') AND ( INSTRING('BR',UPPER(sta:city),1,1) <> 0)
Located fine on the first letter 'B' then cleared table on the 'r'

Turned on Case Sensitive Checkbox
Same Result as above
[5592] [st] [netTalk][thread=3] Browsestaff :: Filter: (sta:activeflag='Y') AND ( INSTRING('BR',UPPER(sta:city),1,1) <> 0)

Changed setting to Upper
Same
Turned off case sensitive
same

Turned on Case Sensitive Sorting
Contains
As-Is
Case Sensitive Locating
[4844] [st] [netTalk][thread=3] Browsestaff :: Filter: (sta:activeflag='Y') AND ( INSTRING('BR',UPPER(sta:city),1,1) <> 0)

Same reult each time always the UPPER on sta:city even though the data is 'Brookfield'

Hope this helps.

Brian

Title: Re: Postgres and Searches
Post by: broche on March 25, 2014, 12:59:42 PM

Uisng Starts With works
[2560] [st] [netTalk][thread=3] Browsestaff :: Order: UPPER(sta:city),sta:staffsysid
[2560] [st] [netTalk][thread=3] Browsestaff :: Filter: (sta:activeflag='Y') AND ( UPPER(sta:city) >= 'BR' AND  UPPER(sta:city) <= 'BRþ')
[2560] [st] [netTalk][thread=3] Browsestaff :: Order: UPPER(sta:city),sta:staffsysid
[2560] [st] [netTalk][thread=3] Browsestaff :: Filter: (sta:activeflag='Y') AND ( UPPER(sta:city) >= 'BR' AND  UPPER(sta:city) <= 'BRþ')

The only difference I see is the odd chr at the end of the SQL statement 'þ'

Title: Re: Postgres and Searches
Post by: Bruce on March 25, 2014, 10:22:56 PM

the other difference is the use of Instring versus not instring. Maybe Postgress doesn't like the Instring, or upper inside the instring.

Title: Re: Postgres and Searches
Post by: broche on March 26, 2014, 08:52:58 AM

Is there anything I can do about that?

Title: Re: Postgres and Searches
Post by: Bruce on March 26, 2014, 11:47:43 PM

I'm not the Postgres expert - maybe someone else can jump in here?

cheers
Bruce