Re: Text Reports query

On Nov 26, 2003, at 22:38, Russell Valentine 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.

Got it.

> 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.

Ok, I changed textreport.php to reflect this.  The patch is too big to 
post, but I'm going to go and check it here right away and we'll see 
what happens.

---
MandrakeSoft Security; http://www.mandrakesecure.net/
Online Security Resource Book; http://linsec.ca/
"lynx -source http://linsec.ca/vdanen.asc | gpg --import"
{FE6F2AFD : 88D8 0D23 8D4B 3407 5BD7  66F9 2043 D0E5 FE6F 2AFD}

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