Re: ready for some testing

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 and the advanced query, neither of which work with 
> postgresql...

1)

In the advanced query. Atleast in PostgreSQL < 7.3 you need to use 'AS'.

Example:

SELECT products.name AS product FROM products;


2) 

DATE_FORMAT should be to_char instead. Also the patterns are different.
http://www.postgresql.org/docs/7.3/static/functions-formatting.html

Example:

Something like this:
Note: I usualy store dates as timestamps type instead of string.

SELECT to_char(to_date(creation, 'YYYYMMDDHH24MISS'), 'Mon/DD/YY') from bugs;

Note: I had to use single quotes here to.

3)

With the joins it seems like it doesn't want to take AS names when doing
it (Perhaps something that should be put/fixed into PostgreSQL);

So I made the query work although (without $user_options, or
$ORDER_BY_LIST). This is the query I came up with.I didn't check
$user_options or $ORDER_BY_LIST but I'll check on that later hope this
helps for now.

SELECT bid, cc, private, url, products.name as product, components.name as component,
       versions.name as version, shortdesc, severity_d.name as severity,
       priority_d.name as priority, status_d.name as status, resolve_d.name as resolution,
       u1.name as assigned, u2.name as reporter ,
       to_char(to_date(creation, 'YYYYMMDDHH24MISS'), 'Mon/DD/YY') as creation,
       to_char(to_date(modified, 'YYYYMMDDHH24MISS'), 'Mon/DD/YY') as modified
FROM bugs,products,components,versions,status_d,priority_d,severity_d, users u1, users u2
LEFT JOIN resolve_d on resolve_d.id = resolve_d.name
WHERE status_d.id = status and bugs.severity = severity_d.id and priority = priority_d.id
and bugs.product = products.pid and component = components.cid and version = versions.vid
and u1.uid = bugs.assigned and u2.uid = bugs.reporter
and ($user_options)
$ORDER_BY_LIST


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/xYHcO0EDJO5MgRYRAhUcAJ9eJFUjAKpZRvWFkA5wLrXFEBmM3QCfaSty
cZdHNw+uVaoEw9C7I2LTiO8=
=b3ru
-----END PGP SIGNATURE-----