Re: WHERE CLAUSE PROPERTY IN DATA BLOCK

On Feb 27, 8:39 am, "nick048" <nicosia.gaet..._at_moonsoft.it> wrote:
> Hi,
> I need help on this problem:
> In a form data block I want to enter in the Property WHERE CLAUSE a
> clause based on certain conditions:
> 1. the first condition is ANA_TYPE := TXT_ANA_TYPE AND ANA_CODE :=
> TXT_ANA_CODE, where the two variable are initialized in Header Block.
> For this I don't have problem. The query work fine.
> 2. But I want to add an other clause, depending from an other variable
> initialized in the Header Block . For example, based on the Radio
> button field :HEADER_BLOCK.SITUATION. Depending from Value of this
> field, the AND clause to add can be:
> a) :SITUATION = 1 --> clause must be AND DOC_PAID <> 0
> b) :SITUATION = 2 --> clause must be AND DOC_PAID = 0
> c) :SITUATION = 3 --> clause must be AND DOC_INS <> 0
>
> Is possible to create a unique Where variable clause (1. plus 2.) with
> this conditions ? If Yes How?
>
> I hope...
> Best Regards
> Gaetano

Method #1:
WHERE
  ( :SITUATION = 1 AND ABS(SIGN(DOC_PAID)) = 1)   OR ( :SITUATION = 2 AND SIGN(DOC_PAID) = 0)   OR ( :SITUATION = 3 AND ABS(SIGN(DOC_INS)) = 1) Method #2:
WHERE

  DECODE( :SITUATION , 1, ABS(SIGN(DOC_PAID)),
                       2, 1 - ABS(SIGN(DOC_PAID)),
                       3, ABS(SIGN(DOC_INS)), 0) = 1

First method uses OR statements that are wrapped in (). Second method uses mathemathics to state that the embedded formula must resolve to 1. Method #2 might execute faster.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Feb 27 2007 - 10:47:19 CST