Re: Firebird and PostgreSQL at the DB Corral.
От | Robert Treat |
---|---|
Тема | Re: Firebird and PostgreSQL at the DB Corral. |
Дата | |
Msg-id | 1072291603.4485.48.camel@camel обсуждение исходный текст |
Ответ на | Re: Firebird and PostgreSQL at the DB Corral. (Paul Ganainm <paulsnewsgroups@hotmail.com>) |
Список | pgsql-general |
On Sun, 2003-12-21 at 18:49, Paul Ganainm wrote: > spam_eater@gmx.net says... > > > What then is a derived table, or is a derived table just a synonym for > > > inline view? > > > I'm not sure what the "official" name for this is. I have heard both. So > > from my point of view a derived table and an inline view are the same. > > OK - I'm fine with the idea that there can be more than one name for the > same thing, and that different geographies and/or cultures can mean that > people use synonyms. > > I've recently started looking at Oracle as well - boy oh boy, you'd want > to know your FTLA's with that system! > > > > What's wrong with SELECT COUNT(col1) FROM table? > > > Nothing. But my statement was just an example to show the syntax. > > > But sometimes when things get more complicated it *is* very handy, and I > > have used it now and then, and wouldn't want to miss it :-) > > OK, fine. Can you show me an example of where your construct (inline > view and/or derived table) behaves differently from and is better than > the syntax that I used? > > Paul... select distinct * FROM ( select ss.* from ( ( SELECT s.site_id, s.name, r.name AS region_name, e.active, coalesce(max,0) AS status, match_type FROM prod1 a, host h, entity_profile_1 e, site s, region r, ( SELECT 'Site name' AS match_type, site_id AS search_id FROM site WHERE name ilike '%abc%' UNION SELECT 'prod1 license' AS match_type, findsite(prod1_id) AS search_id FROM prod1 WHERE prod1_license ilike 'abc%' UNION SELECT 'prod2 license' AS match_type, findsite(prod2_id) AS search_id FROM prod2 WHERE prod2_license ilike 'abc%' UNION SELECT 'prod3 license' AS match_type, findsite(prod3_id) AS search_id FROM prod3 WHERE prod3_license ilike '%abc%' ) AS sr LEFT JOIN ( SELECT findsite(entity_id) AS error_id, max(status_id) FROM current ce GROUP BY findsite(entity_id) ) AS errors ON (error_id = search_id) WHERE search_id = s.site_id AND s.region_id = r.region_id AND search_id = e.entity_id AND a.host_id = h.host_id AND h.site_id = s.site_id ) ) AS ss ORDER BY UPPER(ss.name) ASC, region_name asc ) as matches; Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
В списке pgsql-general по дате отправления: