Thread: where clause help
i have a record set like below: num_prods|num_open_issues|num_provisioned|num_canceled 1|0|1|0 2|0|0|2 3|0|1|1 2|0|1|1 1|0|01 2|0|0|0 3|3|0|0 3|0|0|3 3|1|0|2 3|2|0|1 2|0|2|0 Of the list above only row 3 and row 6 should be returned. Plain english definition: With a result set like above eliminate all rows that should not show up on the provision List. Provision List Definition: All rows that have products that need provisioning. Provisioning means its NOT canceled and it does NOT have an open issue. Some facts: num_open_issues + num_provisioned + num_canceled will never be more than num_prods. no individual column will ever be more than num_prods. thanks!
where num_prods > num_open_issues + num_provisioned + num_canceled if those columns are nullable (which they don't seem to be) you'd have to convert the NULLs (i.e. coalesce(num_canceled,0) ) > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ketema > Sent: Monday, April 23, 2007 4:21 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] where clause help > > i have a record set like below: > > num_prods|num_open_issues|num_provisioned|num_canceled > 1|0|1|0 > 2|0|0|2 > 3|0|1|1 > 2|0|1|1 > 1|0|01 > 2|0|0|0 > 3|3|0|0 > 3|0|0|3 > 3|1|0|2 > 3|2|0|1 > 2|0|2|0 > > Of the list above only row 3 and row 6 should be returned. > > Plain english definition: > With a result set like above eliminate all rows that should not show > up on the provision List. Provision List Definition: All rows that > have products that need provisioning. Provisioning means its NOT > canceled and it does NOT have an open issue. Some facts: > num_open_issues + num_provisioned + num_canceled will never be more > than num_prods. > no individual column will ever be more than num_prods. > > thanks! >
Man so simple! is your solution the same as: num_provisioned < num_products AND (num_open_issues + num_provisioned + num_canceled) < num_prods which is what i finally came up with....
Ketema wrote: > i have a record set like below: > > num_prods|num_open_issues|num_provisioned|num_canceled > 1|0|1|0 > 2|0|0|2 > 3|0|1|1 * > 2|0|1|1 > 1|0|0|1 > 2|0|0|0 * > 3|3|0|0 > 3|0|0|3 > 3|1|0|2 > 3|2|0|1 > 2|0|2|0 > > Of the list above only row 3 and row 6 should be returned. > > Plain english definition: > With a result set like above eliminate all rows that should not show > up on the provision List. Provision List Definition: All rows that > have products that need provisioning. Provisioning means its NOT > canceled and it does NOT have an open issue. If I understand this correctly, we start with: where num_cancelled < num_prods and num_open_issues < num_prods > Some facts: > num_open_issues + num_provisioned + num_canceled will never be more > than num_prods. > no individual column will ever be more than num_prods. > Then in addition to this, we also only retrieve records where: num_open_issues + num_provisioned + num_canceled < num_prods and num_open_issues < num_prods (already there above, no need to have it twice) and num_provisioned < num_prods and num_canceled < num_prods (already there above, no need to have it twice) giving the query: select * from table where num_open_issues < num_prods and num_provisioned < num_prods and num_canceled < num_prods and (num_open_issues + num_provisioned + num_canceled) < num_prods; With (I think) the result of: records 1,11 fail as num_provisioned is not < num_prods records 2,8 fail as num_cancelled is not < num_prods record 3 passes all constraints records 4,5,9, 10 fail as num_open_issues + num_provisioned + num_canceled is not < num_prods record 6 passes all constraints record 7 fails as num_open_issues is not < num_prods Is this what you were after? Brent Wood
Ketema <ketema@gmail.com> writes: > Man so simple! is your solution the same as: > > num_provisioned < num_products AND (num_open_issues + num_provisioned > + num_canceled) < num_prods > > which is what i finally came up with.... This can be simplified to "num_open_issues + num_provisioned + num_canceled < num_prods", without the AND and the other statement. -- Jorge Godoy <jgodoy@gmail.com>