Re: Firebird and PostgreSQL at the DB Corral.

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