1 2 3 4 5 Previous Next 70 Replies Latest reply: Nov 20, 2012 11:33 AM by 969952 Go to original post RSS
      • 45. Re: Load data from excel to Oracle Tables
        rp0428
        >
        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
          scroll up & actually read what odie_63 posted previously in this thread
          • 47. Re: Load data from excel to Oracle Tables
            969952
            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
              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
                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
                  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
                    Guido S
                    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
                      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
                        Guido S
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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?