1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

column has multiple date used as a param. now errors out

Discussion in 'SQL PL/SQL' started by tomrubble, May 9, 2014.

  1. tomrubble

    tomrubble Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    a value set was changed that is obsolete and now we have to use a new one. used to be DD-MON-YYYY now its YYYY/MM/DD HH24:MI:SS they dont want to change old date format in the table. oracle passes YYYY/MM/DD HH24:MI:SS when program ran

    query errors out now w/ different date formats. Any one have the solution to combine two queries into one so the rdf query can be edited. tried decodes trunc substr etc . so query needs to be able to handle multiple date types using 'YYYY/MM/DD HH24:MI:SS' as the mask.

    --30-MAY-2014 works when all this way

    SELECT * FROM xx pov
    WHERE TO_DATE (pov.attribute14, 'DD-MON-YYYY')
    BETWEEN TO_DATE ( :p_date_request_from, 'YYYY/MM/DD HH24:MI:SS')
    AND TO_DATE ( :p_date_to,'YYYY/MM/DD HH24:MI:SS')

    --works when all 2014/05/29 00:00:00
    SELECT * FROM xx pov
    WHERE pov.attribute14 BETWEEN :p_date_request_from AND :p_date_to


    thanks tom rubble
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Tom,

    provide SELECT * FROM xx pov
     
  3. tomrubble

    tomrubble Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    the querys to be combined are in the first post but here it is again

    --30-MAY-2014 works when all this way DD-MON-YYYY

    SELECT * FROM xx pov
    WHERE TO_DATE (pov.attribute14, 'DD-MON-YYYY')
    BETWEEN TO_DATE ( :p_date_request_from, 'YYYY/MM/DD HH24:MI:SS')
    AND TO_DATE ( :p_date_to,'YYYY/MM/DD HH24:MI:SS')

    --works when all 2014/05/29 00:00:00
    SELECT * FROM xx pov
    WHERE pov.attribute14 BETWEEN :p_date_request_from AND :p_date_to


    i created a test table with attribute14 I have placed this data in it

    29-MAY-2014
    30-MAY-2014
    2014/05/29 00:00:00
    2014/05/31 00:00:00

    the rdf is a simple query but the table has 380k + records. for ease of use thats why i created a table w/ the 4 records.

    thanks for the reply and help
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    I didn't get you clearly.....
     
    tomrubble likes this.
  5. tomrubble

    tomrubble Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    The rdf has existed since 2007 working and had no problem . Someone changed the value set with a new one which caused a problem. When they tried to change it back to the origninal value set they couldnt because it is now obsolete. So the new value set stores the attribute as YYYY/MM/DD 00:00:00 the obsolete v.s stores the data as DD-MON-YYYY. I was told that the old vendors attribute14 must stay at DD-MON-YYYY and the new vs will now be in YYYY/MM/DD 00:00:00 . So the table column will now hold a mixed date format.

    The query in the rdf is

    SELECT pov.vendor_name , pov.attribute14
    FROM po_vendor_sites pvs
    , po_vendors pov
    WHERE pov.vendor_id = pvs.vendor_id
    AND pov.attribute15 = 'Y'
    AND pov.attribute14 = to_char(to_date(SUBSTR( :p_date_request ,1,10),'YYYY/MM/DD HH24:MI:SS') , 'DD-MON-YYYY')

    So since there is two types of formats now the query errors out

    So i need to correct the query to look at both date formats. I did this ..............

    i created a test table with attribute14 I have placed this data in it

    -------------------------------------------------------------------------------------------------------------
    If the 4 records are in the DD-MON-YYYY as 30-MAY-2014 it works when all this way DD-MON-YYYY
    29-MAY-2014
    30-MAY-2014
    28-MAY-2014
    30-MAY-2014

    SELECT * FROM xx pov
    WHERE TO_DATE (pov.attribute14, 'DD-MON-YYYY')
    BETWEEN TO_DATE ( _date_request_from, 'YYYY/MM/DD HH24:MI:SS')
    AND TO_DATE ( _date_to,'YYYY/MM/DD HH24:MI:SS')
    -------------------------------------------------------------------------------------------------------------
    If the 4 records are in 2014/05/29 00:00:00 it works

    2014/05/30 00:00:00
    2014/05/31 00:00:00
    2014/05/29 00:00:00
    2014/05/31 00:00:00

    SELECT * FROM xx pov
    WHERE pov.attribute14 BETWEEN _date_request_from AND _date_to
    -------------------------------------------------------------------------------------------------------------

    ....... BUT ......................
    If i mix them
    29-MAY-2014
    30-MAY-2014
    2014/05/29 00:00:00
    2014/05/31 00:00:00


    I tried multiple ways w/ decodes truncs substr etc. and i get errors like this
    --ORA-01858: a non-numeric character was found where a numeric was expected
    -- ORA-01861: literal does not match format string

    So i need help w/ the query to work to look at both types of date formats without error-ing out



    Sorry for the confusion i confused myself after i re-read
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi tomrubble,

    If I understand your problem correctly: due to some manipulation you have two format of dates stored as text in the ATTRIBUTE14 field (which is of VARCHAR2 type in Oracle EBS if I'm not mistaken) and you cannot or don't wish to update the dates to 1 of the two specific formats (which could have been simpler !).

    However, if the tow dates format are 'DD-MON-YYYY' and 'YYYY/MM/DD HH24:MI:SS', then there is a way you could handle this.

    Try the following query on your XX table : [Assumption: table created using "CREATE TABLE XX (ATTRIBUTE14 VARCHAR2(30));"]

    It uses the INSTR function combined with CASE:

    Code (SQL):
    SELECT
      CASE WHEN INSTR(attribute14,'/')>0
        THEN to_date(attribute14, 'YYYY/MM/DD HH24:MI:SS')
        ELSE to_date(attribute14, 'DD-MON-YYYY')
      END
    FROM XX;
    Good Luck !
    Rajen.
     
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Create a view against the table to homogenize the two values and then run your queries against the view. The column is obviously a VARCHAR2 data type holding text-based DATE data. Your view could convert the old and new formats to a DATE as follows:

    Code (Text):
    CREATE VIEW table_name_v AS
    SELECT [other columns]...,
           DECODE(SUBSTR(attribute14, 3, 1), '-',
               TO_DATE(attribute14, 'DD-MON-YYYY'),
               TO_DATE(attribute14, 'YYYY/MM/DD HH24:MI:SS') AS attribute14
    FROM   [table_name]
     
    However, since you have been writing queries against text data, possibly you want to keep the value as text. The following example simply converts the old (DD-MON-YYYY) values into a text version of the new format:

    Code (Text):
    CREATE VIEW table_name_v AS
    SELECT [other columns]...,
           DECODE(SUBSTR(attribute14, 3, 1), '-',
               TO_CHAR(TO_DATE(attribute14, 'DD-MON-YYYY'), 'YYYY/MM/DD HH24:MI:SS'),
               attribute14) AS attribute14
    FROM   [table_name]
     
    Instead of creating a view, you could use a DECODE in all of your queries when performing a comparison against this column. However, that would get really messy over time.