Re: WHERE CLAUSE PROPERTY IN DATA BLOCK

From: nick048 <nicosia.gaetano_at_moonsoft.it>
Date: 27 Feb 2007 22:37:37 -0800
Message-ID: <1172644657.790201.106040@a75g2000cwd.googlegroups.com>

On 27 Feb, 17:47, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> 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.- Nascondi testo tra virgolette -
>
> - Mostra testo tra virgolette -

Hi Charles

Thank You for Reply.

I ask to You still a little help.
Following Your suggestion, I have written therefore my WHERE CLAUSE (for example with the Method #2):

WHERE
    DECODE( :SITUATION , 1, ABS(SIGN(DOC_PAID)),

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

    AND ANA_TYPE := TXT_ANA_TYPE AND ANA_CODE := TXT_ANA_CODE Is this statement correct ?

How can alter the WHERE CLAUSE, if I need to add another filter (FROM_DATE - TO_DATE) with the condition that, if FROM_DATE is not null AND TO_DATE is not null, then all the records with DOC_DATE in the date range are selected ?

In other words with the condition
if FROM_DATE is not null AND TO_DATE is not null then

     DOC_DATE >= :FROM_DATE OR DOC_DATE <= TO_DATE else

    ALL records found

I hope ...

Best Regards
Gaetano Received on Wed Feb 28 2007 - 00:37:37 CST