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

--
******************************************************************************
POZZO Ange Michel
mail : ange(at)alpinfo(dot)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/

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