problem with view and case - please help

Started by Ange Michel POZZOover 25 years ago6 messagesbugsgeneral

Jump to latest

Ange Michel POZZO

poange@technologist.com

over 25 years ago

bugsgeneral

I repost my message because it seems that my previous post don't go on

i use [PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2]
rpm version of Linux Mandrake 7.02

i try this query :

[ange@ange ange]$ psql zonecommerce -h 192.0.1.84 -u -f toto.sql
Username: postgres
Password:

DROP VIEW liste_browser ;
ERROR: Rule or view 'liste_browser' not found <-------- this ok
because view does not exist at this time
CREATE VIEW browser
AS
SELECT
agent_i,
CASE
WHEN agent_i LIKE '%MSIE 2.0;%' THEN 'Internet Explorer 2.0'
WHEN agent_i LIKE '%MSIE 3.0;%' THEN 'Internet Explorer 3.0'
WHEN agent_i LIKE '%MSIE 3.0a;%' THEN 'Internet Explorer 3.0a'
WHEN agent_i LIKE '%MSIE 3.0B;%' THEN 'Internet Explorer 3.0B'
WHEN agent_i LIKE '%MSIE 3.01;%' THEN 'Internet Explorer 3.01'
WHEN agent_i LIKE '%MSIE 3.02;%' THEN 'Internet Explorer 3.02'
WHEN agent_i LIKE '%MSIE 4.0b1;%' THEN 'Internet Explorer 4.0 beta 1'
WHEN agent_i LIKE '%MSIE 4.0;%' THEN 'Internet Explorer 4.0'
WHEN agent_i LIKE '%MSIE 4.01;%' THEN 'Internet Explorer 4.01'
WHEN agent_i LIKE '%MSIE 4.5;%' THEN 'Internet Explorer 4.5'
WHEN agent_i LIKE '%MSIE 5.0b1;%' THEN 'Internet Explorer 5.0 beta 1'
WHEN agent_i LIKE '%MSIE 5.0b2;%' THEN 'Internet Explorer 5.0 beta 2'
WHEN agent_i LIKE '%MSIE 5.0;%' THEN 'Internet Explorer 5.0'
WHEN agent_i LIKE '%MSIE 5.01;%' THEN 'Internet Explorer 5.01'
WHEN agent_i LIKE '%MSIE 5.5b1;%' THEN 'Internet Explorer 5.5 beta 1'
WHEN agent_i LIKE '%MSIE 5.5;%' THEN 'Internet Explorer 5.5'
WHEN agent_i = 'Mozilla' THEN 'Netscape version inconnue'
WHEN agent_i LIKE 'Mozilla (X11; I; Linux 2.0.32 i586)%' AND agent_i NOT
LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape Linux
version non connue'
WHEN agent_i LIKE 'Mozilla/2.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape 2.0'
WHEN agent_i LIKE 'Mozilla/2.02 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape 2.02'
WHEN agent_i LIKE 'Mozilla/2.02E %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape 2.02E'
WHEN agent_i LIKE 'Mozilla/3.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape 3.0'
WHEN agent_i LIKE 'Mozilla/3.01 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape 3.01'
WHEN agent_i LIKE 'Mozilla/3.02 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/3.03 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.03 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.04 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.05 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.06 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.07 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.08 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.5 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.51 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.6 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.61 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.7 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.71 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.72 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.73 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/5.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/6.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
ELSE agent_i END AS navigateur, count (agent_i)
as total from access group by agent_i;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.
[ange@ange ange]$

what is the problem with my query ?
is it a bug ?

another problem is that after backend crash, there is a file called
browser in this database directory :

[root@ange zonecommerce]# ls -l
total 131384
-rw------- 1 postgres postgres 4 Apr 6 13:12 PG_VERSION
-rw------- 1 postgres postgres 40960 Jul 20 19:30 abonnes
-rw------- 1 postgres postgres 8192 Jul 20 19:30
abonnes_id_abonne_seq
-rw------- 1 postgres postgres 40960 Jul 20 19:30
abonnes_mail_abonne_pk
-rw------- 1 postgres postgres 16384 Jul 20 19:30 abonnes_pk
-rw------- 1 postgres postgres 78299136 Jul 21 09:47 access
-rw------- 1 postgres postgres 98304 Jul 17 15:50 access2
-rw------- 1 postgres postgres 21954560 Jul 21 09:30
access3_filename_pkey
-rw------- 1 postgres postgres 15671296 Jul 21 09:30
access3_first_pkey
-rw------- 1 postgres postgres 24576 Jul 21 08:20 appartient
-rw------- 1 postgres postgres 49152 Jul 21 08:20 appartient_pk
-rw------- 1 postgres postgres 892928 Jul 21 09:47 assoc_37_fk
-rw------- 1 postgres postgres 393216 Jul 21 09:46 bannieres
-rw------- 1 postgres postgres 8192 Jul 17 11:48
bannieres_id_banniere_seq
-rw------- 1 postgres postgres 368640 Jul 21 09:46 bannieres_pk
-rw------- 1 postgres postgres 2424832 Jul 21 09:47 boutique
-rw------- 1 postgres postgres 8192 Jul 20 19:30
boutique_id_boutique_seq
-rw------- 1 postgres postgres 876544 Jul 21 09:47 boutique_pk
-rw------- 1 postgres postgres 0 Jul 21 09:53 browser
<------------here
:

and i must delete the file to create a table or a view with this name
because a DROP on this view don't work and i can't create the view

any help is welcome

thanks

Ange

--
******************************************************************************
POZZO Ange Michel
mail : ange@alpinfo.fr
Administrateur - Dï¿½veloppeur
ALPINFO
617 Rue Denis Papin
73290 La Motte Servolex
Savoie - France
tel : 04 79 26 06 28
fax : 04 79 25 68 36

Zonecommerce, l'annuaire franï¿½ais du commerce ï¿½lectronique

- Plusieurs centaines de magasin rï¿½fï¿½rencï¿½ pour tous vos achats
sur internet, tous avec paiement sï¿½curisï¿½ en ligne
- Des promotions proposï¿½es par les boutiques
- Vente au enchï¿½res, forum de discussion
- Des actualitï¿½s, sports et loisirs, cinï¿½ma, horoscope ...
- Les iddï¿½es d'olivia, le site du mois, l'interview
- Des liens, des conseils ...

http://www.zonecommerce.com/

******************************************************************************

Volker Paul

vpaul@dohle.com

over 25 years ago

In reply to: Ange Michel POZZO (#1)

bugsgeneral

Re: problem with view and case - please help

CREATE VIEW browser
AS
SELECT
agent_i,
CASE
WHEN agent_i LIKE '%MSIE 2.0;%' THEN 'Internet Explorer 2.0'
...
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/5.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/6.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
ELSE agent_i END AS navigateur, count (agent_i)
as total from access group by agent_i;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.

Query limit exceeded? At least that CASE statement looks rather clumsy,
suggest using a function instead.

V.Paul

'JanWieck@t-online.de'

JanWieck@t-online.de

over 25 years ago

In reply to: Ange Michel POZZO (#1)

bugsgeneral

Re: [BUGS] problem with view and case - please help

Ange Michel POZZO wrote:

I repost my message because it seems that my previous post don't go on

i use [PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2]
rpm version of Linux Mandrake 7.02

That's definitely the problem. I cannot recreate it with
current CVS sources.

The view generates a rewrite rule of ~40K. So v6.5.2 wouldn't
be able to store it anyway. 7.0 should, because it seems
extremely good compressable (octet length of 2.7K).

So give our latest release a try.

i try this query :

[ange@ange ange]$ psql zonecommerce -h 192.0.1.84 -u -f toto.sql
Username: postgres
Password:

DROP VIEW liste_browser ;
ERROR: Rule or view 'liste_browser' not found <-------- this ok
because view does not exist at this time
CREATE VIEW browser
AS
SELECT
agent_i,
CASE
WHEN agent_i LIKE '%MSIE 2.0;%' THEN 'Internet Explorer 2.0'
WHEN agent_i LIKE '%MSIE 3.0;%' THEN 'Internet Explorer 3.0'
WHEN agent_i LIKE '%MSIE 3.0a;%' THEN 'Internet Explorer 3.0a'
WHEN agent_i LIKE '%MSIE 3.0B;%' THEN 'Internet Explorer 3.0B'
WHEN agent_i LIKE '%MSIE 3.01;%' THEN 'Internet Explorer 3.01'
WHEN agent_i LIKE '%MSIE 3.02;%' THEN 'Internet Explorer 3.02'
WHEN agent_i LIKE '%MSIE 4.0b1;%' THEN 'Internet Explorer 4.0 beta 1'
[...]

OTOH, the previous suggestion of using a function seems more
appropriate. PL/Tcl for example has very powerful regular
expression capabilities, that could simplify it alot.

Jan

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Tom Lane

tgl@sss.pgh.pa.us

over 25 years ago

In reply to: Ange Michel POZZO (#1)

bugsgeneral

Re: problem with view and case - please help

Ange Michel POZZO <poange@technologist.com> writes:

CREATE VIEW browser
AS
SELECT
agent_i,
CASE
< massive CASE expression >
ELSE agent_i END AS navigateur, count (agent_i)
as total from access group by agent_i;
pqReadData() -- backend closed the channel unexpectedly.

Not sure why you are seeing a crash instead of a complaint, but
there's no chance of making such a huge VIEW expression work in
6.5 --- the rule text won't fit in 8K. Try it in 7.0.2 (which
has still got the 8K limit, but at least it compresses the text).

BTW I tend to agree with the other comment that this seems a clumsy
way to go about it. I'd think about making a table containing a
pattern column and a browser-name column and doing the view as a
join. Might be a little tricky to ensure you get only one match,
however ...

regards, tom lane

Ange Michel POZZO

poange@technologist.com

over 25 years ago

In reply to: Ange Michel POZZO (#1)

bugsgeneral

Re: [SQL] problem with view and case - please help

the idea of a funtion is a good idea, thanks a lot !

i am a newbie to sql, after some try, i have made a function like this :

create function browser(text) returns text
AS
'SELECT
CASE
WHEN $1 LIKE \'%MSIE 2.0;%\' THEN \'Internet Explorer 2.0\'
WHEN $1 LIKE \'%MSIE 3.0;%\' THEN \'Internet Explorer 3.0\'
WHEN $1 LIKE \'%MSIE 3.0a;%\' THEN \'Internet Explorer 3.0a\'
WHEN $1 LIKE \'%MSIE 3.0B;%\' THEN \'Internet Explorer 3.0B\'
WHEN $1 LIKE \'%MSIE 3.01;%\' THEN \'Internet Explorer 3.01\'
WHEN $1 LIKE \'%MSIE 3.02;%\' THEN \'Internet Explorer 3.02\'
WHEN $1 LIKE \'%MSIE 4.0b1;%\' THEN \'Internet Explorer 4.0 beta 1\'
WHEN $1 LIKE \'%MSIE 4.0;%\' THEN \'Internet Explorer 4.0\'
WHEN $1 LIKE \'%MSIE 4.01;%\' THEN \'Internet Explorer 4.01\'
WHEN $1 LIKE \'%MSIE 4.5;%\' THEN \'Internet Explorer 4.5\'
WHEN $1 LIKE \'%MSIE 5.0b1;%\' THEN \'Internet Explorer 5.0 beta 1\'
WHEN $1 LIKE \'%MSIE 5.0b2;%\' THEN \'Internet Explorer 5.0 beta 2\'
WHEN $1 LIKE \'%MSIE 5.0;%\' THEN \'Internet Explorer 5.0\'
WHEN $1 LIKE \'%MSIE 5.01;%\' THEN \'Internet Explorer 5.01\'
WHEN $1 LIKE \'%MSIE 5.5b1;%\' THEN \'Internet Explorer 5.5 beta 1\'
WHEN $1 LIKE \'%MSIE 5.5;%\' THEN \'Internet Explorer 5.5\'
WHEN $1 = \'Mozilla\' THEN \'Netscape version inconnue\'
WHEN $1 LIKE \'Mozilla (X11; I; Linux 2.0.32 i586)%\' AND $1 NOT LIKE
\'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape Linux
version non connue\'
WHEN $1 LIKE \'Mozilla/2.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape 2.0\'
WHEN $1 LIKE \'Mozilla/2.02 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape 2.02\'
WHEN $1 LIKE \'Mozilla/2.02E %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape 2.02E\'
WHEN $1 LIKE \'Mozilla/3.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape 3.0\'
WHEN $1 LIKE \'Mozilla/3.01 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape 3.01\'
WHEN $1 LIKE \'Mozilla/3.02 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/3.03 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.03 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.04 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.05 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.06 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.07 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.08 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.5 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.51 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.6 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.61 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.7 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.71 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.72 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.73 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/5.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/6.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
ELSE $1
END'
language 'SQL';

and now i can get this query to work :

select browser(agent_i) as navigateur,count( browser(agent_i)) as total
from access group by navigateur order by total asc;

wich is the result i search !

thanks a lot to everyone

Ange

Volker Paul a ï¿½crit :

CREATE VIEW browser
AS
SELECT
agent_i,
CASE
WHEN agent_i LIKE '%MSIE 2.0;%' THEN 'Internet Explorer 2.0'
...
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/5.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/6.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
ELSE agent_i END AS navigateur, count (agent_i)
as total from access group by agent_i;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.

Query limit exceeded? At least that CASE statement looks rather clumsy,
suggest using a function instead.

V.Paul

Zonecommerce, l'annuaire franï¿½ais du commerce ï¿½lectronique

- Plusieurs centaines de magasins rï¿½fï¿½rencï¿½s pour tous vos achats
sur internet, tous avec paiement sï¿½curisï¿½ en ligne
- Des promotions proposï¿½es par les boutiques
- Vente aux enchï¿½res, un forum de discussion
- Des actualitï¿½es, sports et loisirs, cinï¿½ma, horoscope ...
- Les idï¿½es d'olivia, le site du mois, l'interview
- Des liens, des conseils ...

http://www.zonecommerce.com/

******************************************************************************

Continuing Technical Education

CTE@Dal.Ca

over 25 years ago

In reply to: Ange Michel POZZO (#1)

bugsgeneral

Re: problem with view and case - please help

Instead of:

WHEN $1 LIKE \'Mozilla/2.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape 2.0\'
WHEN $1 LIKE \'Mozilla/2.02 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape 2.02\'
WHEN $1 LIKE \'Mozilla/2.02E %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape 2.02E\'
WHEN $1 LIKE \'Mozilla/3.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape 3.0\'
WHEN $1 LIKE \'Mozilla/3.01 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape 3.01\'
WHEN $1 LIKE \'Mozilla/3.02 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/3.03 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.03 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.04 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.05 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.06 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.07 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.08 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.5 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.51 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.6 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.61 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.7 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.71 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.72 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/4.73 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/5.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'
WHEN $1 LIKE \'Mozilla/6.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'

Why not use:

WHEN $1 LIKE \'Mozilla/%\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT
LIKE \'%compatible%\' THEN \'Netscape\'

It should be true based on the rest of your function.

Cheers.