Text Reports query

On Wed, 26 Nov 2003 11:26:06 -0700
Vincent Danen <[email protected]> wrote:

> Basically, we have two really convoluted SQL queries for 
> the text reports


For text reports query:

1)

instead of IFNULL PostgreSQL has COALESCE.
http://www.postgresql.org/docs/7.3/static/functions-conditional.html

SELECT COALESCE(name, 'UNRESOLVED') AS resolve_info FROM resolve_d;

Note: Again single quotes.


2)

Change to single quotes again for case.

3)
Changed order of from to work with left join.

Query:

SELECT bid, u2.name as assigned_user, severity_d.name as severity_name, status_d.name as status_name,                              creation, modified, shortdesc, priority_d.name as priority_name, products.name as product_name,
       users.name as reporter_user, versions.name as version_name, components.name as component_name,                              COALESCE(resolve_d.name,'UNRESOLVED') as resolve_info, cc,
       case private when 0 then 'Public' when 1 then 'Private' else 'Unknown' end, url                                      FROM priority_d, users, users as u2, severity_d, status_d, versions,
     products, components, bugs                                                                                             LEFT JOIN resolve_d ON (resolve_d.id = bugs.resolution)
WHERE (bugs.priority = priority_d.id) and (bugs.reporter = users.uid) and (bugs.assigned = u2.uid)                                and (bugs.severity = severity_d.id) and (bugs.status = status_d.id) and (bugs.version = versions.vid)
      and (bugs.product = products.pid) and (bugs.component = components.cid)                                               ORDER BY bid;


Note: Again someone needs to check that it is what is wanted. Sometimes I
screw up when dealing with joins.


Russell Valentine

_______________________________________________
Anthill-workers mailing list
Anthill-workers-+mQDA36h6dWw5LPnMra/[email protected]
http://vmlinuz.ca/mailman/listinfo/anthill-workers

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/xY3sO0EDJO5MgRYRAl68AJ9qRsLe/T0Tc88/NF5aATDls3AZiwCcDKKY
DY4sD14e8rzJ8sdzTrMvSls=
=vv3b
-----END PGP SIGNATURE-----