pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ange Michel POZZO <poange@technologist.com>
To: Volker Paul <vpaul@dohle.com>
Cc: pgsql-sql@postgresql.org, pgsql-general@postgresql.org, psgsql-bugs@postgresql.org, Jerome Alet <alet@unice.fr>
Subject: Re: [SQL] problem with view and case - please help
Date: Fri, 21 Jul 2000 16:41:06 +0200
Message-ID: <39786102.6DE9B33D@technologist.com> (raw)
References: <39780EEE.BBCB6406@technologist.com>
	<3978139A.3F12604F@dohle.com>

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/

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



view thread (6+ messages)  latest in thread

Message-ID: <39786102.6DE9B33D@technologist.com>
Permalink:  ../39786102.6DE9B33D@technologist.com/
Also on:    postgresql.org/message-id/39786102.6DE9B33D@technologist.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: poange@technologist.com, vpaul@dohle.com, alet@unice.fr
  Subject: Re: [SQL] problem with view and case - please help
  In-Reply-To: <39786102.6DE9B33D@technologist.com>
  References: <39780EEE.BBCB6406@technologist.com> <3978139A.3F12604F@dohle.com> <39786102.6DE9B33D@technologist.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox