Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    19

    Unanswered: How to loop through values of one table as input for 2nd table

    Hi

    I have a common table CCL that i need to join to about 200 other tables one by one and get all the matching rows.

    I queried all_tables and dumped the list of tables i need into MatchList table.

    I am expert in the SQL Server world but new to oracle so not sure how to put in Cursor or a FOR LOOP.

    I would like to join CCL with each tablename from MatchList, put the resultset into a common table.

    Any ideas which is the correct loop structure?

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    We don't have your tables, column_name, data or specific requirements.
    So constructing SQL solution is no possible.
    Realize that LOOP is not part of ANSI SQL Language.
    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
    Feb 2004
    Posts
    19
    anacedent,

    I just need the general logic, which construct, how its used. I can build the specifics.

    Thanks

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SPOOL EXAMPLE.SQL
    select 'SELECT COUNT(*) FROM ' || TABLE_NAME || ';' FROM USER_TABLES;
    SPOOL OFF
    @EXAMPLE.SQL
    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
    Feb 2004
    Posts
    19
    Ok so i think you have not understood what i was asking...maybe my bad....
    let me explain..

    I have a table CCL (POLICY_NO, AMOUNT)
    I have about 200 tables in a schema:
    LIST.C001 (POLICY_NO, AMOUNT)
    LIST.C002 (POLICY_NO, AMOUNT)
    ....where C001, C002 is a table and so on

    I queried ALL_TABLES and dumped the list of tables into ANOTHER TABLE called MatchList (TABLE_NAME, OWNER, ROWS)

    I need a FOR LOOP/CURSOR that will allow me to do this:

    INSERT INTO MatchingRecs
    SELECT * FROM
    CCL JOIN MatchList.TableName ON CCL.POLICY_NO = TableName.POLICY_NO


    The trick is obviously getting a new tablename dynamically for each run of the loop/cursor

    Thanks

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL must be static; which I showed you how to construct SQL statement containing TABLE_NAME.

    alternatively research EXECUTE IMMEDIATE to accomplish same thing.
    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.

  7. #7
    Join Date
    Feb 2004
    Posts
    19
    Thanks

    if anyone is looking for the same, here's the solution


    SET SERVEROUTPUT ON
    DECLARE
    TYPE cur_typ IS REF CURSOR;
    c cur_typ;
    query_str VARCHAR2(600);
    query_instr VARCHAR(600);
    tableNm VARCHAR(50);

    BEGIN
    query_str := 'select distinct tabs.table_NAME from all_tables tabs, all_tab_columns cols
    where TABS.TABLE_NAME = COLS.TABLE_NAME
    and tabs.owner = COLS.OWNER
    and TABS.OWNER = ''LIST''
    and (COLS.COLUMN_NAME like ''POLICY_NO''
    --OR COLS.COLUMN_NAME like ''EDIX_POL''
    )

    and TABS.TABLE_NAME like ''%_INF''
    OPEN c FOR query_str ;
    LOOP
    FETCH c INTO tableNm;
    EXIT WHEN c%NOTFOUND;

    -- process row here
    EXECUTE IMMEDIATE ' INSERT INTO FILECCL (FileName, Treaty_ID, Policy_NO)
    SELECT '''|| tableNm ||''', ccl.Treaty_ID, ccl.Policy_Number FROM
    (select distinct POLICY_NUMBER, TREATY_ID
    from LIST.CCL
    ) ccl JOIN
    (select distinct POLICY_NO
    FROM LIST.' || tableNm || ') ff
    ON CCL.POLICY_NUMBER = ff.POLICY_NO';
    --dbms_output.put_line(query_instr);

    END LOOP;
    CLOSE c;
    END;

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The solution was not straight forward because the basic design is flawed & tables not Normalized.
    Application should never have multiple tables with exact same columns.
    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.

  9. #9
    Join Date
    Feb 2004
    Posts
    19
    Lol...i wish it was a perfect and smarter world but alas!

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    below is a SQL only solution
    Code:
    spool capture.sql
    select distinct 
    'INSERT INTO FILECCL (FileName, Treaty_ID, Policy_NO)
    SELECT '|| tableNm ||', ccl.Treaty_ID, ccl.Policy_Number FROM
    (select distinct POLICY_NUMBER, TREATY_ID
    from LIST.CCL
    ) ccl JOIN 
    (select distinct POLICY_NO
    FROM LIST.' || tableNm || ') ff
    ON CCL.POLICY_NUMBER = ff.POLICY_NO'
    from all_tables tabs, all_tab_columns cols
    where TABS.TABLE_NAME = COLS.TABLE_NAME
    and tabs.owner = COLS.OWNER
    and TABS.OWNER = 'LIST'
    and (COLS.COLUMN_NAME like 'POLICY_NO' 
    --OR COLS.COLUMN_NAME like 'EDIX_POL' 
    )
    and TABS.TABLE_NAME like '%_INF';
    spool off
    @capture.sql
    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.

  11. #11
    Join Date
    Feb 2012
    Posts
    2

    @atl-choozang

    Its good but plz avoid join....

    BEGIN
    query_str :=
    'select distinct table_NAME
    from all_tab_columns cols
    where OWNER = ''LIST''
    and (COLUMN_NAME like ''POLICY_NO''
    --OR COLUMN_NAME like ''EDIX_POL'')
    and TABLE_NAME like ''%_INF''

Posting Permissions

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