Обсуждение: 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>