1. Registered User
Join Date
Aug 2005
Location
Kuala Lumpur, MY
Posts
108

hie all im doing a sum by dates but it returns me different answers everytime i change the order
anyone mind tellin me whats the difference or the logic

1st:

Code:
```select distinct NVL(SUM(total_p),0)
into r_CodeP
from VW_A01_PROD_SCR_TO_WAREHOUSE
where to_date(date_generated, 'dd/mm/RRRR') <= to_date(p_ToDateGen, 'dd/mm/RRRR')
or to_date(end_date_p, 'dd/mm/RRRR') > to_date(p_ToDateGen, 'dd/mm/RRRR')
and to_char(end_date_p, 'dd/mm/RRRR') = '31/12/9999'
and denomination = p_Val;```
2nd:
Code:
```select distinct NVL(SUM(total_p),0)
into r_CodeP
from VW_A01_PROD_SCR_TO_WAREHOUSE
where to_date(date_generated, 'dd/mm/RRRR') <= to_date(p_ToDateGen, 'dd/mm/RRRR')
and to_char(end_date_p, 'dd/mm/RRRR') = '31/12/9999')
or (to_date(end_date_p, 'dd/mm/RRRR') > to_date(p_ToDateGen, 'dd/mm/RRRR')
and denomination = p_Val;```

3rd:
Code:
```select distinct NVL(SUM(total_p),0)
into r_CodeP
from VW_A01_PROD_SCR_TO_WAREHOUSE
where to_date(date_generated, 'dd/mm/RRRR') <= to_date(p_ToDateGen, 'dd/mm/RRRR')
and (to_char(end_date_p, 'dd/mm/RRRR') = '31/12/9999')
or (to_date(end_date_p, 'dd/mm/RRRR') > to_date(p_ToDateGen, 'dd/mm/RRRR'))
and denomination = p_Val;```

Note that the 2nd and the third is the same but there is a parentisis enclosed only...
any way anyone could explain the flow to me ?
Last edited by shatishr; 04-25-06 at 23:08.

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
by using a text editor and replacing the actual conditions with single characters, it appears your three WHERE clause look like this --

... where W or X and Y and Z

... where W and Y) or (X and Z

... where W and (Y) or (X) and Z

notice that the 2nd query parentheses are not properly matched

now here is a suggestion to help you understand -- ANDs take precedence over ORs

therefore this --

... where W or X and Y and Z

is actually equivalent to this --

... where W or ( X and Y and Z )

does that help at all?

3. Registered User
Join Date
Aug 2005
Location
Kuala Lumpur, MY
Posts
108
Originally Posted by r937
by using a text editor and replacing the actual conditions with single characters, it appears your three WHERE clause look like this --

... where W or X and Y and Z

... where W and Y) or (X and Z

... where W and (Y) or (X) and Z

notice that the 2nd query parentheses are not properly matched

now here is a suggestion to help you understand -- ANDs take precedence over ORs

therefore this --

... where W or X and Y and Z

is actually equivalent to this --

... where W or ( X and Y and Z )

does that help at all?

sorry rudy, i dont really get it.... i have 3 dates condition
but you have 4
where W and (Y) or (X) and Z

Are you taking the denomination into count ? could u please mark each line with the alphabets u used ? thanks

4. Registered User
Join Date
Jul 2003
Posts
2,296
you need to encapsulate your conditions better.
depending on where you put your parenthesis and your OR and AND conditions

PHP Code:
``` select distinct NVL(SUM(total_p),0)     into r_CodeP     from VW_A01_PROD_SCR_TO_WAREHOUSE     where to_date(date_generated, 'dd/mm/RRRR') <= to_date(p_ToDateGen, 'dd/mm/RRRR')       or to_date(end_date_p, 'dd/mm/RRRR') > to_date(p_ToDateGen, 'dd/mm/RRRR')       and to_char(end_date_p, 'dd/mm/RRRR') = '31/12/9999'       and denomination = p_Val;  ```
You need to figure out where you want oracle to search for data
possibly like this:
PHP Code:
``` select distinct NVL(SUM(total_p),0)     into r_CodeP     from VW_A01_PROD_SCR_TO_WAREHOUSE     where        (        ( to_date(date_generated, 'dd/mm/RRRR') <= to_date(p_ToDateGen, 'dd/mm/RRRR') )       or        ( to_date(end_date_p, 'dd/mm/RRRR') > to_date(p_ToDateGen, 'dd/mm/RRRR') )       )       and to_char(end_date_p, 'dd/mm/RRRR') = '31/12/9999'       and denomination = p_Val;  ```

5. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713

Also "distinct NVL(SUM(total_p),0)" has no logic, A SUM() with or without "group by" columns has only one value per group!

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by shatishr
sorry rudy, i dont really get it.... i have 3 dates condition
but you have 4
there may be 3 date conditions, but you have 4 conditions altogether which are being ANDed and/or ORed together

by the way, your job (finding date ranges) is tough enough without the added complexity of actual dates like 9999-12-31

that's what i call a "weenie" date, and i see it mostly used by people who are trying to avoid NULL in an end date column -- i hope this wasn't your idea

7. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,520
And's are evaluated BEFORE or's, so they way your query would be evaluated is

select distinct NVL(SUM(total_p),0)
into r_CodeP
from VW_A01_PROD_SCR_TO_WAREHOUSE
where to_date(date_generated, 'dd/mm/RRRR') <= to_date(p_ToDateGen, 'dd/mm/RRRR')
or (to_date(end_date_p, 'dd/mm/RRRR') > to_date(p_ToDateGen, 'dd/mm/RRRR')
and to_char(end_date_p, 'dd/mm/RRRR') = '31/12/9999'
and denomination = p_Val);

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•