Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2008
    Posts
    11

    Unanswered: DDL: DB2 to Oracle conversion

    Hi guys,
    I have a db2 view that we have to create on Oracle but the DDL needs some massaging. I wast told that I have to use to_char command but I am doing something wrong.

    Could you , please, convert the line below so Oracle would accept it:

    CAST ( CASE WHEN DAY ( EFFECTIVE_DT ) > 9 THEN CAST ( DAY ( EFFECTIVE_DT ) AS CHAR ( 2 ) ) ELSE '0' || CAST ( DAY ( EFFECTIVE_DT ) AS CHAR ( 1 ) ) END AS CHAR ( 2 ) ) AS BDTD


    Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    To_char(effective_dt,'dd') bdtd
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2008
    Posts
    11
    I was also told that 'DAY' function was causing problems.
    Should I still use it here: WHEN DAY ( EFFECTIVE_DT ) > 9

    Thanks.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I was also told that 'DAY' function was causing problems.
    Who told you this?

    >Should I still use it here: WHEN DAY ( EFFECTIVE_DT ) > 9
    It is your code & you are free to do as desired or required.

    Code:
      1* select to_char(sysdate,'DD') BDTD from dual
    SQL> /
    
    BD
    --
    07
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    select to_char(sysdate,'DD') BDTD from dual
    As an alternative if you need to get the day as a numeric value (to be able to compare it using > 6) you could also use the extract() function:
    Code:
    SELECT extract(day from sysdate) from dual
    or using to_number with the anacedent's solution:
    Code:
    select to_number(to_char(sysdate, 'DD')) from dual
    For more details see the chapter "Datetime functions" in the manual:
    http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i88891

  6. #6
    Join Date
    Sep 2008
    Posts
    11
    I would really appreciate if someone could translate the whole line:



    CAST ( CASE WHEN DAY ( EFFECTIVE_DT ) > 9 THEN CAST ( DAY ( EFFECTIVE_DT ) AS CHAR ( 2 ) ) ELSE '0' || CAST ( DAY ( EFFECTIVE_DT ) AS CHAR ( 1 ) ) END AS CHAR ( 2 ) ) AS BDTD

    Thanks.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    As far as I understand, your complicated expression simply formats the day of the month with a leading zero if it's smaller than 9.

    If that is true, then simply using to_char() should do what you want
    Code:
    to_char(EFFECTIVE_DT, 'DD')
    Which you could have found out on your own by reading the manual.

  8. #8
    Join Date
    Sep 2008
    Posts
    11
    Quote Originally Posted by shammat View Post
    As far as I understand, your complicated expression simply formats the day of the month with a leading zero if it's smaller than 9.

    If that is true, then simply using to_char() should do what you want
    Code:
    to_char(EFFECTIVE_DT, 'DD')
    Which you could have found out on your own by reading the manual.
    Yes, it creates a view and formats the date field based on the value. There are similar expressions for other columns so I thought if I know how to deal with this one I will be able to do the same to the rest.


    Should I leave "AS CHAR ( 2 )" since we are telling that it would be 2 bytes by specifying "DD.

    In other DDLs I had to remove "AS" , should I do the same here: "AS BDTD"?

    How about this part:
    CAST ( DAY ( EFFECTIVE_DT ) AS CHAR ( 1 )

    Will it be "DD" or "D"?

    That is why I asked to translate the whole line. It should've taken 10 seconds for someone who knows ORACLE.
    Last edited by Bambuk; 08-11-10 at 06:53.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Bambuk View Post
    How about this part:
    CAST ( DAY ( EFFECTIVE_DT ) AS CHAR ( 1 )

    Should I leave "AS CHAR ( 1 )"
    You don't need that.

    As far as I understand this was only done in order to be able to add a leading zero if the day is lower than 10 which is done automatically by the to_char() method.
    My proposed solution (to_char(EFFECTIVE_DT, 'DD')) replaces the whole expression that you have posted
    Did you even try to run what I posted? Then you'll see if it does what you want.


    Will it be "DD" or "D"?
    Read the manual for format model of the to_char() method. 'D' and 'DD' have completely different meanings.


    In other DDLs I had to remove "AS" , should I do the same here: "AS BDTD"?
    That depends whether you rely on the column alias or not (btw: we are talking about DML not DDL)
    Only you can know if you need the column alias or not.
    If you need it, simply add it to my expression.


    It should've taken 10 seconds for someone who knows ORACLE.
    But you need to understand what you are doing (and using).
    If you need to use Oracle (or any other DBMS that is) there is no way to avoid reading the manual.

    Edit: I just saw that anacedent has already posted that solution. Sorry for not seeing that

Posting Permissions

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