3 Replies Latest reply: Nov 23, 2012 1:14 AM by 959404 RSS

    to_date function not fetching the desired result.....

    959404
      Hi Gurus,

      I get varied results from the below 2 queries, I am pessimistic about using to_char instead prefer using to_date, but query doesn't seem to fetch the complete data while using the to_date but with to_char it works. Please can someone make this to_date used query below work for me.


      Main table structure:
      Column Name     ID     Pk     Null?     Data Type     Default     Histogram     Encryption Alg     Salt
      
      MINISTRY_CODE     1          Y     VARCHAR2 (15 Byte)          Yes          
      BILL_MONTH     2          Y     DATE          Yes          
      CUBIC     3          Y     NUMBER          Yes          
      GALLONS     4          Y     NUMBER          Yes          
      AMOUNTS     5          Y     NUMBER          Yes          
      MTR_TYPE     6          Y     CHAR (1 Byte)          Yes          
      1st query:
      SELECT ministry_code,
      DECODE(mtr_type, 'C','Billed Cubic',
      'G','Billed Gallons',
      'A','Billed OMR', 
      'R','Collected',
      'U','Total Unpaid Bills') mtr_type,
      SUM(CASE 
      WHEN mtr_type NOT IN ('C','G','A','R','U') THEN 0 WHEN to_char(bill_month,'MMYYYY') = '012011' AND mtr_type = 'C' THEN cubic 
      WHEN mtr_type NOT IN ('C','G','A','R','U') THEN 0 WHEN to_char(bill_month,'MMYYYY') = '012011' AND mtr_type = 'G' THEN (gallons)
      WHEN mtr_type NOT IN ('C','G','A','R','U') THEN 0 WHEN to_char(bill_month,'MMYYYY') = '012011' AND mtr_type = 'A' THEN amounts 
      WHEN mtr_type NOT IN ('C','G','A','R','U') THEN 0 WHEN to_char(bill_month,'MMYYYY') = '022011' AND mtr_type = 'R' THEN amounts 
      WHEN mtr_type NOT IN ('C','G','A','R','U') THEN 0 WHEN to_char(bill_month,'MMYYYY') <= '022011' AND mtr_type = 'U' THEN amounts ELSE 0 END) January
      FROM tmp_paew_month_gafu1 WHERE ministry_code IN 
      (SELECT DISTINCT ministry_code FROM tmp_paew_month_gafu GROUP BY ministry_code) 
      GROUP BY ministry_code,mtr_type
      ORDER BY 1,3 DESC
      SAMPLE OUTPUT:
      MINISTRY_CODE MTR_TYPE JANUARY

      001 Billed Gallons 5326252
      001 Billed Cubic 24210.2363636364
      001 Total Unpaid Bills 16402.5
      001 Billed OMR 13287.36
      001 Collected 10410.508
      002 Total Unpaid Bills 12089.99
      002 Billed Cubic 0
      002 Billed OMR 0
      002 Billed Gallons 0
      002 Collected 0
      003 Total Unpaid Bills 27418.711



      2nd query:
      SELECT ministry_code,
      DECODE(mtr_type, 'C','Billed Cubic',
      'G','Billed Gallons',
      'A','Billed OMR', 
      'R','Collected',
      'U','Total Unpaid Bills') mtr_type,
      SUM(CASE 
      WHEN mtr_type NOT IN ('C','G','A','R','U') THEN 0 WHEN to_char(bill_month) = to_date('012011','MMYYYY') AND mtr_type = 'C' THEN cubic 
      WHEN mtr_type NOT IN ('C','G','A','R','U') THEN 0 WHEN to_date(bill_month) = to_date('012011','MMYYYY') AND mtr_type = 'G' THEN (gallons)
      WHEN mtr_type NOT IN ('C','G','A','R','U') THEN 0 WHEN to_date(bill_month) = to_date('012011','MMYYYY') AND mtr_type = 'A' THEN amounts 
      WHEN mtr_type NOT IN ('C','G','A','R','U') THEN 0 WHEN to_date(bill_month) = to_date('022011','MMYYYY') AND mtr_type = 'R' THEN amounts 
      WHEN mtr_type NOT IN ('C','G','A','R','U') THEN 0 WHEN to_date(bill_month) <= to_date('022011','MMYYYY') AND mtr_type = 'U' THEN amounts ELSE 0 END) January
      FROM tmp_paew_month_gafu1 WHERE ministry_code IN 
      (SELECT DISTINCT ministry_code FROM tmp_paew_month_gafu GROUP BY ministry_code) 
      GROUP BY ministry_code,mtr_type
      ORDER BY 1,3 DESC
      Sample OUTPUT:
      MINISTRY_CODE MTR_TYPE JANUARY

      001 Total Unpaid Bills 106062.002
      001 Billed OMR 0
      001 Billed Cubic 0
      001 Billed Gallons 0
      001 Collected 0
      002 Total Unpaid Bills 42023.243
      002 Collected 0
      002 Billed Gallons 0
      002 Billed Cubic 0
      002 Billed OMR 0
      003 Total Unpaid Bills 133260.435
        • 1. Re: to_date function not fetching the desired result.....
          myOra_help
          In 2nd query you have used to_char(bill_month) = to_date('012011','MMYYYY') in which you compare character value with date. Here Oracle done implicit conversion.
          You must use same data type on both side and avoide any impilicit conversion.

          Your first query looks better use it,
          • 2. Re: to_date function not fetching the desired result.....
            hm
            What is the sense of to_date(bill_month) in the second query?
            bill_month is already a date. to_date function will expect a character parameter and convert it in a date value.

            So what will happen? First the bill_month will be implicitely converted to a character value.
            Second this character value will be converted back to a date. You gave no format string. So both conversions will be made will your setting of nls_date_format.

            I can only guess what you really want. Perhaps you want to trunc the bill_month with trunc(bill_month,'MM').

            Please give us

            - table create script
            - insert statements for test data
            - required output for these test data
            - description of your requirements

            Edited by: hm on 22.11.2012 23:16
            • 3. Re: to_date function not fetching the desired result.....
              959404
              Sorry, I to_char at one place in 2nd query for testing.....
              I found the data inconsistent (different while using to_char and to_date) whiile using the to_char, hence I wanted to use the second query with to_date function.