Results 1 to 7 of 7

Thread: a question !

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

    Unanswered: a question !

    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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    Quote 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. #4
    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
    oracle could read it differently

    instead of 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
    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
    - The_Duck
    you can lead someone to something but they will never learn anything ...

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

    Cool



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

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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