This discussion is archived
1 2 3 4 5 Previous Next 70 Replies Latest reply: Nov 20, 2012 9:33 AM by 969952 Go to original post RSS
  • 45. Re: Load data from excel to Oracle Tables
    rp0428 Guru
    Currently Being Moderated
    >
    can you please let me know how can I load only YEAR in JOINED_YEAR table which is having all NULL values.
    >
    YOU CAN'T - how many times do I and others have to tell you that before you understand? Did you read my first reply? It was this
    >
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!

    You can use TRUNC and TO_CHAR to extract the part of the date (day, month, year) that you want to display but the parts you don't display are still stored in the DATE column whether you display them or not.

    So if you are only interested in storing a particular year value you must still store a day and a month to make it a proper date.
    >
    What part of that don't you understand?

    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!

    If this is your table
    create table temp1
    (
    no nuumber(8),
    name varchar2(20),
    joined_year date,
    joined month date
    sal number(8,2)
    )
    Then JOINED_YEAR and JOINED_MONTH will either be NULL or will contain a DATE. And the DATE they contain will contain a day, a month and a year. JOINED_MONTH will contain a day and a year and a month.

    If you want JOINED_YEAR to contain 2012 and JOINED_MONTH to contain FEBRUARY you can use this code
    UPDATE TEMP1  a
      SET  JOINED_YEAR = TO_DATE('02/01/2012', 'MM/DD/YYYY'), 
             JOINED_MONTH = TO_DATE('02/01/2012', 'MM/DD/YYYY')
      WHERE  JOINED_YEAR is NULL and a.JOINED_MONTH is NULL ;
    You can set the day to any value that is valid for the month and year.
  • 46. Re: Load data from excel to Oracle Tables
    sb92075 Guru
    Currently Being Moderated
    scroll up & actually read what odie_63 posted previously in this thread
  • 47. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    Then How can we insert only Year part into a column which is having all NULL values ?

    Please let me know. I am new to Oracle still in Learning stage.
  • 48. Re: Load data from excel to Oracle Tables
    rp0428 Guru
    Currently Being Moderated
    YOU CAN'T!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
  • 49. Re: Load data from excel to Oracle Tables
    AlbertoFaenza Expert
    Currently Being Moderated
    966949 wrote:

    Please let me know. I am new to Oracle still in Learning stage.
    From the kind of question you are posting we understood that you are in learning stage. That's why you should pay more attention at what people are writing and trying to understand it.
    Then How can we insert only Year part into a column which is having all NULL values ?
    The answer is: you cannot if you are using DATE data type.

    Date data type can only contain a date which is formed by day, month, year, hour, minutes, seconds.

    If you want a column that just hold a year or only a month, then you have to change your data type in your table from date to number (or INTEGER)
    I.e.:
    create table temp1
    (
    no nuumber(8),
    name varchar2(20),
    joined_year NUMBER,
    joined month NUMBER
    sal number(8,2)
    )
    /
    Regards.
    Al
  • 50. Re: Load data from excel to Oracle Tables
    AlbertoFaenza Expert
    Currently Being Moderated
    rp0428 wrote:
    YOU CAN'T!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    Date columns MUST CONTAIN A DATE! They cannot contain just a year or just a month or just a day. The MUST CONTAIN A DATE!
    ...
    Then how can I store a year? :D :D :D :D

    This guy is almost breaking our nerves.

    Regards.
    Al
  • 51. Re: Load data from excel to Oracle Tables
    890823 Newbie
    Currently Being Moderated
    This is a question to the regulars, since I was absent for quite a long time and even had to create a new 'handle' for the forums: For how long do we need to stay polite in such cases? ;-)
  • 52. Re: Load data from excel to Oracle Tables
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Guido,

    nobody force you to answer. As well stated in the FAQ:
    When answering a question, please be courteous; there are different levels of experience represented here. A poorly worded question is better ignored than flamed - or better yet, help the poster ask a better question.
    Though I understand that sometimes it is difficult, especially if you have a Mediterranean temper. :-)

    Regards.
    Al
  • 53. Re: Load data from excel to Oracle Tables
    890823 Newbie
    Currently Being Moderated
    Mille grazie, Alberto. So I will take a deeeeeep breath and: ignore such threads in which the op is obviously not willing to 'live by the rules'.
  • 54. Re: Load data from excel to Oracle Tables
    BluShadow Guru Moderator
    Currently Being Moderated
    966949 wrote:
    Here am posting the actual structure now.
    create table temp1
    (
    no nuumber(8),
    name varchar2(20),
    joined_year date,
    joined month date
    sal number(8,2)
    )
    That is NOT the actual structure as that is invalid in several places...
    SQL> ed
    Wrote file afiedt.buf
    
      1  create table temp1
      2  (
      3  no nuumber(8),
      4  name varchar2(20),
      5  joined_year date,
      6  joined month date
      7  sal number(8,2)
      8* )
    SQL> /
    no nuumber(8),
              *
    ERROR at line 3:
    ORA-00907: missing right parenthesis
    So, you are lying to us and not even bothering to copy and paste something that works. If you can't provide correct information, how can people help you?

    Regardless, here's an example of updating a table with a date using different datatypes... so hopefully (though I doubt) it will be clearer to you...
    SQL> create table t1 (date_val DATE
      2                  ,date_yr  DATE
      3                  ,date_mon DATE
      4                  ,date_day DATE
      5                  ,num_yr   NUMBER
      6                  ,num_mon  NUMBER
      7                  ,num_day  NUMBER
      8                  ,num_hh   NUMBER
      9                  ,num_min  NUMBER
     10                  ,num_sec  NUMBER
     11                  );
    
    Table created.
    
    SQL> insert into t1 (date_val) values (sysdate);
    
    1 row created.
    
    SQL> select * from t1;
    
    DATE_VAL             DATE_YR              DATE_MON             DATE_DAY                 NUM_YR    NUM_MON    NUM_DAY     NUM_HH    NUM_MIN    NUM_SEC
    -------------------- -------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ----------
    16-NOV-2012 09:11:55
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  update t1
      2  set date_yr = trunc(date_val,'YYYY')
      3     ,date_mon = trunc(date_val,'MM')
      4     ,date_day = trunc(date_val,'DD')
      5     ,num_yr = to_number(to_char(date_val,'YYYY'))
      6     ,num_mon = to_number(to_char(date_val,'MM'))
      7     ,num_day = to_number(to_char(date_val,'DD'))
      8     ,num_hh = to_number(to_char(date_val,'HH24'))
      9     ,num_min = to_number(to_char(date_val,'MI'))
     10*    ,num_sec = to_number(to_char(date_val,'SS'))
    SQL> /
    
    1 row updated.
    
    SQL> select * from t1;
    
    DATE_VAL             DATE_YR              DATE_MON             DATE_DAY                 NUM_YR    NUM_MON    NUM_DAY     NUM_HH    NUM_MIN    NUM_SEC
    -------------------- -------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ----------
    16-NOV-2012 09:11:55 01-JAN-2012 00:00:00 01-NOV-2012 00:00:00 16-NOV-2012 00:00:00       2012         11         16          9         11         55
    As you've been told plenty of times already... DATE columns store dates, so you can truncate a date to the current year, current month or current day etc. and they will still store a full date including the time, though the relevant components that have been truncated out of it will be zero'd or taken back to their lowest value.
    If your datatypes are NUMBER, then you can extract the numeric value from the date and store the individual values as numbers, as demonstrated.

    If you are not understanding that, then you need to go back to the manuals (http://tahiti.oracle.com) and go and learn the basic concepts of data types before you even try and touch any more code.
  • 55. Re: Load data from excel to Oracle Tables
    BluShadow Guru Moderator
    Currently Being Moderated
    966949 wrote:
    Hi am able to retirve the data by using the aboe query but the problem which I am facing is am not able to insert or update the data into the table which are having null values.

    and one more is please have a look into the below
    UPDATE TEMP
    SET JOINED_YEAR= 
    CASE JOINED_YEAR
    WHEN JOINED_MONTH  > '9 ' then   JOINED_YEAR+1
    WHEN  JOINED_MONTH   = '10' then   JOINED_YEAR+1
    WHEN JOINED_MONTH   = '11' then   JOINED_YEAR+1
    Else
    JOINED_YEAR
    END;
    but am getting the below error please have a look .

    ORA-00905: missing keyword

    Thanks.
    That is a fundamentally basic syntax error. Quite simply you have constructed your CASE statement incorrectly.

    it either has to be...
    CASE value_to_be_compared
      WHEN value_to_compare_against_1 THEN
        ... whatever ...
      WHEN value_to_compare_against_2 THEN
        ... whatever ...
      WHEN value_to_compare_against_3 THEN
        ... whatever ...
      etc.
    ELSE
      ... whatever ...
    END
    where the values to be compared against are compared on an equality comparison

    or...
    CASE
      WHEN conditional_statement_1 THEN
        ... whatever ...
      WHEN conditional_statement_2 THEN
        ... whatever ...
      WHEN conditional_statement_3 THEN
        ... whatever ...
      etc.
    ELSE
      ... whatever ...
    END
    where the conditional statements can be any conditions (or multiple conditions) that evaluate to true or false.

    In your statement you are combining the two.

    You are saying:

    Check the value of JOINED_YEAR and compare that against various conditional statements (which themselves evaluate to true or false).

    So you probably want a syntax like...
    UPDATE TEMP
      SET JOINED_YEAR= 
                     CASE
                          WHEN JOINED_MONTH > 9 then JOINED_YEAR+1
                        Else
                          JOINED_YEAR
                      END;
    (the other conditions you had were superfluous, as the first condition would deal with any of the months 10,11 or 12 and the other conditions would never have been considered)

    Also, the "greater than" operator is mathematical and should be done against numeric values, so you shouldn't have your comparison value in quotes which would make it a string.

    Again, you seem to have a fundamental lack of knowledge about the basics of datatypes (not just in PL/SQL as these concepts relate to almost all programming languages).
  • 56. Re: Load data from excel to Oracle Tables
    ascheffer Expert
    Currently Being Moderated
    UPDATE TEMP
      SET JOINED_YEAR= 
                     CASE
                          WHEN JOINED_MONTH > 9 then JOINED_YEAR+1
                        Else
                          JOINED_YEAR
                      END;
    JOINED_YEAR and JOINED_MONTH are DATE type :)
  • 57. Re: Load data from excel to Oracle Tables
    BluShadow Guru Moderator
    Currently Being Moderated
    ascheffer wrote:
    UPDATE TEMP
    SET JOINED_YEAR= 
    CASE
    WHEN JOINED_MONTH > 9 then JOINED_YEAR+1
    Else
    JOINED_YEAR
    END;
    JOINED_YEAR and JOINED_MONTH are DATE type :)
    I know, and hence why I was just providing correct syntax and followed by saying
    Also, the "greater than" operator is mathematical and should be done against numeric values
    ;)
  • 58. Re: Load data from excel to Oracle Tables
    969952 Newbie
    Currently Being Moderated
    As per the suggestion have changed the data types to NUMBER as follows. Here am giving the structure only for 3 columns.
    create table DEST 
    (
        JOINED_YEAR number,
        JOINED_MONTH number,
        JOINED_FIN_YEAR number
    );
    then I wan to update source table with the below data

    the source table is having the

    JOIN_YEAR
    JOIN_Month
    joined_FINan_year having Null values.

    so now my requirement is to update the above columns as follws.

    JOIN_YEAR = Current Year
    JOIN_Month = currentmonth -1 ( i.e. the number of this month is 11. so i want to insert like 11-1 = 10)
    update joined_finan_year =current year.

    then update Source table with these values.

    so can you please let me know the process.

    Thanks.
  • 59. Re: Load data from excel to Oracle Tables
    BluShadow Guru Moderator
    Currently Being Moderated
    966949 wrote:
    As per the suggestion have changed the data types to NUMBER as follows. Here am giving the structure only for 3 columns.
    create table DEST 
    (
    JOINED_YEAR number,
    JOINED_MONTH number,
    JOINED_FIN_YEAR number
    );
    then I wan to update source table with the below data

    the source table is having the

    JOIN_YEAR
    JOIN_Month
    joined_FINan_year having Null values.

    so now my requirement is to update the above columns as follws.
    Update? You mean insert?

    >
    JOIN_YEAR = Current Year
    JOIN_Month = currentmonth -1 ( i.e. the number of this month is 11. so i want to insert like 11-1 = 10)
    update joined_finan_year =current year.
    SQL> create table DEST
      2  (
      3      JOINED_YEAR number,
      4      JOINED_MONTH number,
      5      JOINED_FIN_YEAR number
      6  );
    
    Table created.
    
    SQL>
    SQL> insert into dest (joined_year, joined_month, joined_fin_year)
      2  select to_number(to_char(sysdate,'YYYY'))
      3        ,case when to_number(to_char(sysdate,'MM')) = 1 then 12 else to_number(to_char(sysdate,'MM'))-1 end
      4        ,to_number(to_char(sysdate,'YYYY'))
      5  from dual;
    
    1 row created.
    
    SQL> select * from dest;
    
    JOINED_YEAR JOINED_MONTH JOINED_FIN_YEAR
    ----------- ------------ ---------------
           2012           10            2012
    then update Source table with these values.
    What source table?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points