Re: WHERE CLAUSE PROPERTY IN DATA BLOCK

On Feb 28, 1:37 am, "nick048" <nicosia.gaet..._at_moonsoft.it> wrote:
> On 27 Feb, 17:47, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > 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

You might try changing this:
  AND ANA_TYPE := TXT_ANA_TYPE AND ANA_CODE := TXT_ANA_CODE To this:
  AND ANA_TYPE = TXT_ANA_TYPE AND ANA_CODE = TXT_ANA_CODE Are TXT_ANA_TYPE and TXT_ANA_CODE columns in one of the tables?

If :FROM_DATE and :TO_DATE must be specified, the where condition would look like this:
  AND DOC_DATE BETWEEN NVL( :FROM_DATE , DOC_DATE-1) AND NVL( :TO_DATE , DOC_DATE+1) Reading over your request again, I don't think that the above will satisfy what you are attempting to accomplish - You do not want this restriction if either of the inputs is null. I would be tempted to handle such a situation in the program code, rather than in a SQL statement.

The additional where clause might look like this:   AND DECODE( :FROM_DATE , NULL , 1,

        DECODE( :TO_DATE , NULL , 1,
          (DECODE(SIGN(DOC_DATE - :FROM_DATE ),-1,0,1)
          * DECODE(SIGN( :TO_DATE - DOC_DATE),-1,0,1))) = 1

Note that in the above, you lose any advantage that an index on the DOC_DATE column might provide. The above is executed like this:   If :FROM_DATE is Null Then
    True
  Else
    If :TO_DATE is Null Then
      True
    Else

      If (DOC_DATE >= :FROM_DATE) AND (DOC_DATE <= :TO_DATE) Then
        True
      Else
        False

The above will be difficult to maintain in SQL code, should you need to make a change to it later, while the following pseudocode in the program code would be easier to follow:
  If (:FROM_DATE is Null) and (:TO_DATE is Null) Then     Add to the SQL statement: AND DOC_DATE BETWEEN :FROM_DATE AND :TO_DATE Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Feb 28 2007 - 06:21:40 CST