Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    3

    Unanswered: Selecting the Column Name from on table and using it as a value in another column

    Hi Guys

    I am relatively new to Data Warehousing and Oracle. I am trying to write a maintenance script that selects data from an imported tmp_table thats come from a flat file and moves it into dimension tables.

    In the tmp_table I have column names , id, location, 2009_10, Flag and type. I have a dimension table called date_dim, which holds date_id and year.


    I want to write a statement that takes the column name, 2009_10 from the tmp_table and inserts it as a record into the column name year in the date_dim table. Is this possible?? Could anyone tell me the syntax of this statement so I can write it?

    I have been using the following statments for other columns but I cannot do that for the date_dim table.
    Code:
    INSERT INTO <table_name>
    (SELECT column
    FROM
    	source_table
    WHERE
    	column NOT IN
    	(SELECT DISTINCT
    		column
    	FROM
    		destination_table));
    If anyone has any thoughts on what I am tying to achieve or can help me I woud be very grateful. Thanks for your time looking at my issue. Regards

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Look up execute immediate. It will do what you want.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I want to write a statement that takes the column name, 2009_10 from the tmp_table and inserts it as a record into the column name year in the date_dim table.
    This challenge results directly from poor/bad design.
    The table should NOT have a column which include fixed date information.
    Oracle table should have single column such as ACTIVITY_MONTH;
    which then can store TO_DATE('2009_10','YYYY_MM'), TO_DATE('2009_11','YYYY_MM'), etc.
    The downside for existing design, is custom/unique SQL for each reporting period & no use of bind variables.
    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.

Posting Permissions

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