1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Automate a sql query and insert values into blank table

Discussion in 'SQL PL/SQL' started by Sparamanga, Jun 27, 2017.

  1. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Hi,

    I have written a query that essentially unions a new set of data with a previous set of data for a bridge examination.

    I need to do this for hundreds of different bridges. Each bridge is identified by a 'guid'.

    There are 3 tables.

    The template table contains the bridge elements and current scores assigned for the current bridge exams.
    The element table contains the historic elements associated with each bridge but no scores.
    These tables are unioned for each guid (bridge identifier) to get all thehistoric elements of each bridge (as current bridge exams may have not examined some historic elements) and then left joined back to the template table to get the current scores for each element. Any elements that have 'null' in the scores after the left joined are updated to 'ne' (not examined)

    Anyway this is just the background..

    The real question is how do i automate this..

    I know you can do a for each loop and then insert those value into the third table.. target template.

    I presume i need something along the lines of..

    for rob_rec in (select distinct guid from template)
    insert into target_template (
    MAJORELEMENTCODE,
    MAJORELEMENTNO,
    MINORELEMENTCODE,
    MINORELEMENTNO,
    ELEMENTMATERIAL,
    SEVERITYEXTENT1,
    SEVERITYEXTENT2,
    CMSEVERITYEXTENT1,
    CMSEVERITYEXTENT2,
    COMMENTS,,
    GUID
    )
    values (
    ROB_REC.MAJORELEMENTCODE
    ROB_REC.MAJORELEMENTNO
    ROB_REC.MINORELEMENTCODE
    ROB_REC.MINORELEMENTNO
    ROB_REC.ELEMENTMATERIAL
    ROB_REC.SEVERITYEXTENT1
    ROB_REC.SEVERITYEXTENT2
    ROB_REC.CMSEVERITYEXTENT1
    ROB_REC.CMSEVERITYEXTENT2
    ROB_REC.COMMENTS
    ROB_REC.GUID)

    and also to replace any hard coded guid in my below query with rrr.guid..

    My query is below..



    WITH ROB AS (

    select
    MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE, MINORELEMENTNO, ELEMENTMATERIAL

    from template where LOWER(guid)='3978559c4fa045d9e04400306e4ad01a'
    UNION
    select
    MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE, MINORELEMENTNO, ELEMENTMATERIAL
    from ELEMENT where LOWER(CARRSGUID)='3978559c4fa045d9e04400306e4ad01a'
    )
    ,ROB2 AS
    (SELECT * FROM TEMPLATE WHERE
    LOWER(guid)='3978559c4fa045d9e04400306e4ad01a')

    ,rob3 as (

    SELECT
    ROB.MAJORELEMENTCODE,
    ROB.MAJORELEMENTNO,
    ROB.MINORELEMENTCODE,
    ROB.MINORELEMENTNO,
    ROB.ELEMENTMATERIAL,
    ROB2.SEVERITYEXTENT1,
    ROB2.SEVERITYEXTENT2, ROB2.CMSEVERITYEXTENT1, ROB2.CMSEVERITYEXTENT2,
    ROB2.COMMENTS,
    ROB2.GUID
    FROM ROB
    LEFT JOIN ROB2 ON
    ROB.MAJORELEMENTCODE=ROB2.MAJORELEMENTCODE AND
    ROB.MAJORELEMENTNO=ROB2.MAJORELEMENTNO AND
    ROB.MINORELEMENTCODE=ROB2.MINORELEMENTCODE AND
    ROB.MINORELEMENTNO = ROB2.MINORELEMENTNO AND
    ROB.ELEMENTMATERIAL=ROB2.ELEMENTMATERIAL
    )
    SELECT

    MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE, MINORELEMENTNO,
    ELEMENTMATERIAL,
    case when severityextent1 is null then 'ne' else severityextent1 end
    SEVERITYEXTENT1,
    case when severityextent2 is null then 'ne' else severityextent2 end
    SEVERITYEXTENT2,
    case when cmseverityextent1 is null then 'ne' else cmseverityextent1 end
    CMSEVERITYEXTENT1,
    case when cmseverityextent2 is null then 'ne' else cmseverityextent2 end
    CMSEVERITYEXTENT2,
    COMMENTS,
    CASE WHEN GUID IS NULL THEN '3978559c4fa045d9e04400306e4ad01a' ELSE GUID END
    GUID

    FROM ROB3

    Sorry for the large amount of code.. hopefully it make sense.

    I just need to automate the process for each guid in the template table and insert the values into the target_template table.

    Any help would be greatly appreciated.

    Thanks,

    Rob.
     
  2. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    I have now tried this but its not working.. is this along the right lines?

    DECLARE

    BEGIN
    FOR RRR IN (select DISTINCT guid FROM TEMPLATE)
    LOOP

    WITH ROB AS (

    select
    MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE, MINORELEMENTNO, ELEMENTMATERIAL

    from template where LOWER(guid)=RRR.GUID
    UNION
    select
    MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE, MINORELEMENTNO, ELEMENTMATERIAL
    from ELEMENT where LOWER(CARRSGUID)=RRR.GUID
    )
    ,ROB2 AS
    (SELECT * FROM TEMPLATE WHERE
    LOWER(guid)=RRR.GUID)

    ,rob3 as (

    SELECT
    ROB.MAJORELEMENTCODE,
    ROB.MAJORELEMENTNO,
    ROB.MINORELEMENTCODE,
    ROB.MINORELEMENTNO,
    ROB.ELEMENTMATERIAL,
    ROB2.SEVERITYEXTENT1,
    ROB2.SEVERITYEXTENT2, ROB2.CMSEVERITYEXTENT1, ROB2.CMSEVERITYEXTENT2,
    ROB2.COMMENTS,
    ROB2.GUID
    FROM ROB
    LEFT JOIN ROB2 ON
    ROB.MAJORELEMENTCODE=ROB2.MAJORELEMENTCODE AND
    ROB.MAJORELEMENTNO=ROB2.MAJORELEMENTNO AND
    ROB.MINORELEMENTCODE=ROB2.MINORELEMENTCODE AND
    ROB.MINORELEMENTNO = ROB2.MINORELEMENTNO AND
    ROB.ELEMENTMATERIAL=ROB2.ELEMENTMATERIAL
    )
    ,ROB4 AS (SELECT

    MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE, MINORELEMENTNO,
    ELEMENTMATERIAL,
    case when severityextent1 is null then 'ne' else severityextent1 end
    SEVERITYEXTENT1,
    case when severityextent2 is null then 'ne' else severityextent2 end
    SEVERITYEXTENT2,
    case when cmseverityextent1 is null then 'ne' else cmseverityextent1 end
    CMSEVERITYEXTENT1,
    case when cmseverityextent2 is null then 'ne' else cmseverityextent2 end
    CMSEVERITYEXTENT2,
    COMMENTS,
    CASE WHEN GUID IS NULL THEN RRR.GUID ELSE GUID END
    GUID

    FROM ROB3
    )

    INSERT INTO TARGET_TEMPLATE (MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE,
    MINORELEMENTNO,
    ELEMENTMATERIAL,
    SEVERITYEXTENT1,
    SEVERITYEXTENT2,
    CMSEVERITYEXTENT1,
    CMSEVERITYEXTENT2,
    COMMENTS,
    GUID)

    SELECT ROB4.MAJORELEMENTCODE, ROB4.MAJORELEMENTNO, ROB4.MINORELEMENTCODE,
    ROB4.MINORELEMENTNO, ROB4.ELEMENTMATERIAL, ROB4.SEVERITYEXTENT1, ROB4.SEVERITYEXTENT2,
    ROB4.CMSEVERITYEXTENT1,
    ROB4.CMSEVERITYEXTENT2,
    ROB4.COMMENTS,
    ROB4.GUID FROM ROB4



    END LOOP;
    END;
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "it is not working" doesn't provide any information as to WHY it isn't working. Yes, you are on the right track with this but helping you fix what may be wrong is impossible without error messages, tables and sample data.

    Provide create table scripts, sample data for those tables and the error message or messages you see when you run your code.
     
  4. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    OK ill provide more info, hopefully tonight.. Thanks Zargon
     
  5. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Hi Zargon,

    To summarise what I was trying to do:

    I have 2 tables:
    Element table
    Template table

    Element table script:
    CREATE TABLE "ROB1"."ELEMENT"
    ( "CARRSGUID" VARCHAR2(128 BYTE),
    "MAJORELEMENTCODE" VARCHAR2(26 BYTE),
    "MAJORELEMENTNO" NUMBER(3,0),
    "MINORELEMENTCODE" VARCHAR2(26 BYTE),
    "MINORELEMENTNO" NUMBER(3,0),
    "ELEMENTMATERIAL" VARCHAR2(26 BYTE)
    )

    Template table script:
    CREATE TABLE "ROB1"."TEMPLATE"
    ( "MAJORELEMENTCODE" VARCHAR2(26 BYTE),
    "MAJORELEMENTNO" NUMBER(3,0),
    "MINORELEMENTCODE" VARCHAR2(26 BYTE),
    "MINORELEMENTNO" NUMBER(3,0),
    "ELEMENTMATERIAL" VARCHAR2(26 BYTE),
    "SEVERITYEXTENT1" VARCHAR2(26 BYTE),
    "SEVERITYEXTENT2" VARCHAR2(26 BYTE),
    "CMSEVERITYEXTENT1" VARCHAR2(26 BYTE),
    "CMSEVERITYEXTENT2" VARCHAR2(26 BYTE),
    "COMMENTS" VARCHAR2(1024 BYTE),
    "GUID" VARCHAR2(128 BYTE)
    )

    Element table contains all the various elements (parts) of a bridge structure. It is one large table and each row has a guid identifying that row of elements to a bridge structure.

    Template table contains all the elements for bridges recently examined. This table also contains the score given to the elements. 1 row of data = one bridge element. Again each bridge is indentified by a guid.

    Some bridges in the template table have elements that have not been examined. These are missing from the template table but are present in the Element table for that bridge (identified by guid).

    So I need to select the elements for a particular bridge (select * from element where guid='x') and union this to the elements on the template table
    (select * from template where guid='x').

    I then left join this union query to the template table to bring across the scores for the elements. If there are extra elements brought over from the element table during the union query, these will have null values for the scores after the left join.

    These are then updated to 'ne' indicating 'not examined' by using a case statement.

    This query works fine.

    The issue I have is i need to do this query for each bridge in the template table (each bridge which has recently been examined and has scores).

    So essentially i need to do it for.. select distinct guid from template

    My sql query has several instances where the guid is used as the criteria so i would like to replace those with a guid variable which is looped through
    i.e for rec in (select distinct guid from template) and replace the hardcoded guid with rec.guid.

    And each bridge the query is performed on I would then like to insert the data into a third table.. target_template. Which essentially is the same as template table, just no data in yet.

    CREATE TABLE "ROB1"."TARGET_TEMPLATE"
    ( "MAJORELEMENTCODE" VARCHAR2(26 BYTE),
    "MAJORELEMENTNO" NUMBER(3,0),
    "MINORELEMENTCODE" VARCHAR2(26 BYTE),
    "MINORELEMENTNO" NUMBER(3,0),
    "ELEMENTMATERIAL" VARCHAR2(26 BYTE),
    "SEVERITYEXTENT1" VARCHAR2(26 BYTE),
    "SEVERITYEXTENT2" VARCHAR2(26 BYTE),
    "CMSEVERITYEXTENT1" VARCHAR2(26 BYTE),
    "CMSEVERITYEXTENT2" VARCHAR2(26 BYTE),
    "COMMENTS" VARCHAR2(1024 BYTE),
    "GUID" VARCHAR2(128 BYTE)
    )

    Here is some sample data for the Template table.. Which has 2 different bridges (guids)


    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('ES',1,'WWL',1,'C','C3','B2','A1','A1',null,'3978559c53b745d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('ES',1,'WWL',2,'C','C3','B2','A1','A1',null,'3978559c53b745d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('ES',1,'ABT',1,'C','C3','B3','A1','A1',null,'3978559c53b745d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('DK',1,'BAR',1,'B','B4','A1','P7','J9',null,'3978559c53b745d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('DK',1,'FRV',1,'B','B2','A1','A1','A1',null,'3978559c53b745d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('DK',1,'FRV',2,'B','B2','A1','A1','A1',null,'3978559c53b745d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('DK',1,'SPW',1,'C','B3','A1','A1','A1',null,'3978559c53b745d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('DK',1,'SPW',2,'C','C3','B2','A1','A1',null,'3978559c53b745d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('ES',2,'WWL',1,'C','A1','A1','A1','A1',null,'3978559c53b745d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('ES',2,'WWL',2,'C','A1','A1','A1','A1',null,'3978559c53b745d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('ES',2,'ABT',1,'C','C3','B3','A1','A1',null,'3978559c53b745d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('ES',1,'ABT',1,'B','E3','D4','A1','A1',null,'3978559c5b0945d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('ES',1,'WWL',1,'B','D4','C4','A1','A1',null,'3978559c5b0945d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('ES',1,'WWL',2,'B','C4','A1','A1','A1',null,'3978559c5b0945d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('ES',2,'ABT',1,'B','E3','D4','A1','A1',null,'3978559c5b0945d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('ES',2,'WWL',1,'B','C4','A1','A1','A1',null,'3978559c5b0945d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('ES',2,'WWL',2,'B','A1','A1','M8','A1',null,'3978559c5b0945d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('DK',1,'FRV',1,'B','B5','A1','A1','A1',null,'3978559c5b0945d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('DK',1,'FRV',2,'B','A1','A1','A1','A1',null,'3978559c5b0945d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('DK',1,'BAR',1,'B','D5','C5','A1','A1',null,'3978559c5b0945d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('DK',1,'SPW',1,'B','B3','A1','A1','A1',null,'3978559c5b0945d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('DK',1,'SPW',2,'B','A1','A1','A1','A1',null,'3978559c5b0945d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('DK',1,'PPT',1,'B','D4','A1','J9','A1',null,'3978559c5b0945d9e04400306e4ad01a');
    Insert into TEMPLATE (MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL,SEVERITYEXTENT1,SEVERITYEXTENT2,CMSEVERITYEXTENT1,CMSEVERITYEXTENT2,COMMENTS,GUID) values ('DK',1,'PPT',2,'B','A1','A1','M7','J11',null,'3978559c5b0945d9e04400306e4ad01a');

    Here is some data for the element table.. the same 2 guids


    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C53B745D9E04400306E4AD01A','DK',1,'BAR',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C53B745D9E04400306E4AD01A','DK',1,'FRV',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C53B745D9E04400306E4AD01A','DK',1,'FRV',2,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C53B745D9E04400306E4AD01A','DK',1,'SPW',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C53B745D9E04400306E4AD01A','DK',1,'SPW',2,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C53B745D9E04400306E4AD01A','ES',1,'ABT',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C53B745D9E04400306E4AD01A','ES',1,'WWL',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C53B745D9E04400306E4AD01A','ES',1,'WWL',2,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C53B745D9E04400306E4AD01A','ES',2,'ABT',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C53B745D9E04400306E4AD01A','ES',2,'WWL',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C53B745D9E04400306E4AD01A','ES',2,'WWL',2,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C5B0945D9E04400306E4AD01A','DK',1,'BAR',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C5B0945D9E04400306E4AD01A','DK',1,'FRV',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C5B0945D9E04400306E4AD01A','DK',1,'FRV',2,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C5B0945D9E04400306E4AD01A','DK',1,'PPT',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C5B0945D9E04400306E4AD01A','DK',1,'PPT',2,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C5B0945D9E04400306E4AD01A','DK',1,'SPW',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C5B0945D9E04400306E4AD01A','DK',1,'SPW',2,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C5B0945D9E04400306E4AD01A','ES',1,'ABT',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C5B0945D9E04400306E4AD01A','ES',1,'WWL',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C5B0945D9E04400306E4AD01A','ES',1,'WWL',2,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C5B0945D9E04400306E4AD01A','ES',2,'ABT',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C5B0945D9E04400306E4AD01A','ES',2,'WWL',1,'B');
    Insert into ELEMENT (CARRSGUID,MAJORELEMENTCODE,MAJORELEMENTNO,MINORELEMENTCODE,MINORELEMENTNO,ELEMENTMATERIAL) values ('3978559C5B0945D9E04400306E4AD01A','ES',2,'WWL',2,'B');
     
  6. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    and here is my sql query..

    WITH ROB AS (

    select
    MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE, MINORELEMENTNO, ELEMENTMATERIAL

    from template where LOWER(guid)='3978559c4fa045d9e04400306e4ad01a'
    UNION
    select
    MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE, MINORELEMENTNO, ELEMENTMATERIAL
    from ELEMENT where LOWER(CARRSGUID)='3978559c4fa045d9e04400306e4ad01a'
    )
    ,ROB2 AS
    (SELECT * FROM TEMPLATE WHERE
    LOWER(guid)='3978559c4fa045d9e04400306e4ad01a')

    ,rob3 as (

    SELECT
    ROB.MAJORELEMENTCODE,
    ROB.MAJORELEMENTNO,
    ROB.MINORELEMENTCODE,
    ROB.MINORELEMENTNO,
    ROB.ELEMENTMATERIAL,
    ROB2.SEVERITYEXTENT1,
    ROB2.SEVERITYEXTENT2, ROB2.CMSEVERITYEXTENT1, ROB2.CMSEVERITYEXTENT2,
    ROB2.COMMENTS,
    ROB2.GUID
    FROM ROB
    LEFT JOIN ROB2 ON
    ROB.MAJORELEMENTCODE=ROB2.MAJORELEMENTCODE AND
    ROB.MAJORELEMENTNO=ROB2.MAJORELEMENTNO AND
    ROB.MINORELEMENTCODE=ROB2.MINORELEMENTCODE AND
    ROB.MINORELEMENTNO = ROB2.MINORELEMENTNO AND
    ROB.ELEMENTMATERIAL=ROB2.ELEMENTMATERIAL
    )
    SELECT

    MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE, MINORELEMENTNO,
    ELEMENTMATERIAL,
    case when severityextent1 is null then 'ne' else severityextent1 end
    SEVERITYEXTENT1,
    case when severityextent2 is null then 'ne' else severityextent2 end
    SEVERITYEXTENT2,
    case when cmseverityextent1 is null then 'ne' else cmseverityextent1 end
    CMSEVERITYEXTENT1,
    case when cmseverityextent2 is null then 'ne' else cmseverityextent2 end
    CMSEVERITYEXTENT2,
    COMMENTS,
    CASE WHEN GUID IS NULL THEN '3978559c4fa045d9e04400306e4ad01a' ELSE GUID END
    GUID

    FROM ROB3
     
  7. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    oh the guid in my query in the previous post is just one i was using at the time so will likely not be present in the 2 tables of data i provided.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I don't have a database available at the moment; I'll run through the example tomorrow and see what happens.
     
  9. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Ok thanks Zargon
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It seems to be working for me; define what you mean by 'not working'.
     
  11. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Hi Zargon,

    Yeah the query is working fine. I need to automate that query though through a for loop so that it runs that query for each guide in the template table and then dump those values into the target table.

    So something along the lines of..

    For rob_rec in (select distinct guid from template)
    Loop
    My query
    Insert values into target table
    End loop

    Where the hard coded guid within my query is replaced with rob_rec.guid.

    It was the pl SQL syntax I was struggling with.

    Thanks,

    Rob.
     
  12. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    749
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    you have hierarchical data ?


    you can to use:

    1) INSERT TARGET_TABLE WITH ... SELECT ...
    2) PL/SQL Collection with INSERT SELECT WITH ... SELECT ... by GUID

    for exapmle :

    select...
    Code (Text):



    with
    dstnct as  (select distinct lower(guid) guid  from template ),
    rob1 as
    (
    select
      d.guid,
      r.majorelementcode,
      r.majorelementno,
      r.minorelementcode,
      r.minorelementno,
      r.elementmaterial
    from dstnct d
      join template r on lower(r.guid) = d.guid
    UNION
    SELECT
      d.guid,
      e.majorelementcode,
      e.majorelementno,
      e.minorelementcode,
      e.minorelementno,
      e.elementmaterial

    from dstnct d
      join element e on  lower(e.carrsguid) = d.guid
    )

    select
      nvl(t.guid,d.guid) guid,
      d.majorelementcode,
      d.majorelementno,
      d.minorelementcode,
      d.minorelementno,
      d.elementmaterial,
      t.severityextent1,
      t.severityextent2,
      t.cmseverityextent1,
      t.cmseverityextent2,
      t.comments
    from rob1 d
      left join template t on
        d.majorelementcode=t.majorelementcode and
        d.majorelementno=t.majorelementno and
        d.minorelementcode=t.minorelementcode and
        d.minorelementno = t.minorelementno and
        d.elementmaterial=t.elementmaterial;

     
    insert....
    Code (Text):

    insert into target_template
    with
    dstnct as  (select distinct lower(guid) guid  from template ),
    rob1 as
    (
    select
      d.guid,
      r.majorelementcode,
      r.majorelementno,
      r.minorelementcode,
      r.minorelementno,
      r.elementmaterial
    from dstnct d
      join template r on lower(r.guid) = d.guid
    union
    select
      d.guid,
      e.majorelementcode,
      e.majorelementno,
      e.minorelementcode,
      e.minorelementno,
      e.elementmaterial

    from dstnct d
      join element e on  lower(e.carrsguid) = d.guid
    )


    select
      d.majorelementcode,
      d.majorelementno,
      d.minorelementcode,
      d.minorelementno,
      d.elementmaterial,
      t.severityextent1,
      t.severityextent2,
      t.cmseverityextent1,
      t.cmseverityextent2,
      t.comments,
      nvl(t.guid,d.guid) guid
    from rob1 d
      left join template t on
        d.majorelementcode=t.majorelementcode and
        d.majorelementno=t.majorelementno and
        d.minorelementcode=t.minorelementcode and
        d.minorelementno = t.minorelementno and
        d.elementmaterial=t.elementmaterial;

     
     
    Last edited: Jun 30, 2017
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The problem you're having is you have the INSERT in the wrong place in the code; this is how it should be:

    Code (SQL):
    BEGIN
    FOR RRR IN (SELECT DISTINCT guid AS guid FROM TEMPLATE)
    LOOP
    INSERT INTO TARGET_TEMPLATE (MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE,
    MINORELEMENTNO,
    ELEMENTMATERIAL,
    SEVERITYEXTENT1,
    SEVERITYEXTENT2,
    CMSEVERITYEXTENT1,
    CMSEVERITYEXTENT2,
    COMMENTS,
    GUID)
    WITH ROB AS (
    SELECT
    MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE, MINORELEMENTNO, ELEMENTMATERIAL
    FROM template WHERE LOWER(guid)=RRR.GUID
    UNION
    SELECT
    MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE, MINORELEMENTNO, ELEMENTMATERIAL
    FROM ELEMENT WHERE LOWER(CARRSGUID)=RRR.GUID
    )
    ,ROB2 AS
    (SELECT * FROM TEMPLATE WHERE
    LOWER(guid)=RRR.GUID)
    ,rob3 AS (
    SELECT
    ROB.MAJORELEMENTCODE,
    ROB.MAJORELEMENTNO,
    ROB.MINORELEMENTCODE,
    ROB.MINORELEMENTNO,
    ROB.ELEMENTMATERIAL,
    ROB2.SEVERITYEXTENT1,
    ROB2.SEVERITYEXTENT2, ROB2.CMSEVERITYEXTENT1, ROB2.CMSEVERITYEXTENT2,
    ROB2.COMMENTS,
    ROB2.GUID
    FROM ROB
    LEFT JOIN ROB2 ON
    ROB.MAJORELEMENTCODE=ROB2.MAJORELEMENTCODE AND
    ROB.MAJORELEMENTNO=ROB2.MAJORELEMENTNO AND
    ROB.MINORELEMENTCODE=ROB2.MINORELEMENTCODE AND
    ROB.MINORELEMENTNO = ROB2.MINORELEMENTNO AND
    ROB.ELEMENTMATERIAL=ROB2.ELEMENTMATERIAL
    )
    ,ROB4 AS (SELECT
    MAJORELEMENTCODE, MAJORELEMENTNO, MINORELEMENTCODE, MINORELEMENTNO,
    ELEMENTMATERIAL,
    CASE WHEN severityextent1 IS NULL THEN 'ne' ELSE severityextent1 END
    SEVERITYEXTENT1,
    CASE WHEN severityextent2 IS NULL THEN 'ne' ELSE severityextent2 END
    SEVERITYEXTENT2,
    CASE WHEN cmseverityextent1 IS NULL THEN 'ne' ELSE cmseverityextent1 END
    CMSEVERITYEXTENT1,
    CASE WHEN cmseverityextent2 IS NULL THEN 'ne' ELSE cmseverityextent2 END
    CMSEVERITYEXTENT2,
    COMMENTS,
    CASE WHEN GUID IS NULL THEN RRR.GUID ELSE GUID END
    GUID
    FROM ROB3
    )
    SELECT ROB4.MAJORELEMENTCODE, ROB4.MAJORELEMENTNO, ROB4.MINORELEMENTCODE,
    ROB4.MINORELEMENTNO, ROB4.ELEMENTMATERIAL, ROB4.SEVERITYEXTENT1, ROB4.SEVERITYEXTENT2,
    ROB4.CMSEVERITYEXTENT1,
    ROB4.CMSEVERITYEXTENT2,
    ROB4.COMMENTS,
    ROB4.GUID FROM ROB4;
    END LOOP;
    END;
    /
     
     
  14. Sparamanga

    Sparamanga Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK

    Hi Zargon, thanks! I thought it was a syntax issue.. I was using pl SQL more frequently previously but the nature of my work means I haven't used it for a while. Have been doing VBA etc so when I returned to oracle again I had some syntax issues.

    I will try the code today and let you know how I get on.

    Thanks again.