Thread: problem with view and case - please help
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/
******************************************************************************
> 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
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 #
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
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 -- ****************************************************************************** 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 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/ ******************************************************************************
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.