Of incorrect code generation at the Insert rows step

[Shukhrat]

Hello
Faced the problem of incorrect code generation at the Insert rows step when executing a job.

Job consists of 4 steps:
1. JOIN of two oracle tables (result of work.table1)
2. JOIN of three tables, including work.table1 and two oracle tables (result of work.table2)
3. I output the result of the second step to a new table work.table3
4. Step Insert rows in oracle table

And I get the error:
ERROR: ORACLE execute error: ORA-00904: "TABLE1". "WWW_ID": invalid identifier

Is this a code generation error?

    INSERT INTO ci_common.award_missioner (
        "PLAYER_GATE_ID",
        "AWARD_ID",
        "UPDATE_DATE",
        "WWW_ID"
    )
        SELECT DISTINCT
            table2.player_gate_id   AS player_gate_id,
            10005 AS award_id,
            to_timestamp('  
11FEB2020:16:36:25.7171', 'DDMONYYYY:HH24:MI:SS.FF', 'NLS_DATE_LANGUAGE=American') AS update_date,
            table2.www_id           AS www_id
        FROM
            (
                SELECT DISTINCT
                    table1."PLAYER_GATE_ID"   AS player_gate_id,
                    10005 AS award_id,
                    to_timestamp('  
11FEB2020:16:36:25.7171', 'DDMONYYYY:HH24:MI:SS.FF', 'NLS_DATE_LANGUAGE=American') AS update_date
                    ,
                    table1."WWW_ID"           AS www_id
                FROM
                    (
                        SELECT DISTINCT
                            table1."PLAYER_GATE_ID"   AS player_gate_id,
                            table1."WWW_ID"           AS www_id
                        FROM
                            (
                                SELECT DISTINCT
                                    player_main."PLAYER_GATE_ID",
                                    player_main."WWW_ID"
                                FROM
                                    cmdm.player_main player_main
                                    LEFT JOIN ci_common.award_missioner award_missioner ON player_main."PLAYER_GATE_ID" = award_missioner
                                    ."PLAYER_GATE_ID"
                                WHERE
                                    ( award_missioner."PLAYER_GATE_ID" IS NULL )
                                    AND ( ( player_main."WWW_ID" IS NOT NULL )
                                          AND ( player_main."WWW_REG_STATUS" = 'REGISTERED' ) )
                            ) table1, cmdm.player_add_services player_add_services
                            LEFT JOIN ci_common.award_missioner_dop award_missioner_dop ON player_add_services."PLAYER_GATE_ID" =
                            award_missioner_dop."PLAYER_GATE_ID"
                        WHERE
                            ( table1."PLAYER_GATE_ID" = award_missioner_dop."PLAYER_GATE_ID" )
                            AND ( ( award_missioner_dop."PLAYER_GATE_ID" IS NULL )
                                  OR ( player_add_services."PLAYER_FRIEND_CNT" > award_missioner_dop."PLAYER_FRIEND_CNT" ) )
                            AND ( ( player_add_services."FRIEND_FLG" = 'Y' )
                                  AND ( player_add_services."PLAYER_FRIEND_CNT" >= 1 ) )
                    ) table2
            ) table3

I would be grateful for your help

[ballardw]

The error is an Oracle error message. It apparently indicates that the name of a table or variable passed to Oracle does not meet the Oracle standard naming or possibly means the referenced item doesn't exist (Not an Oracle guru by any stretch).

[Tom]

You are referencing an alias of TABLE1 , but your query only defines alias of TABLE2 and TABLE3.  

What actual table are you trying to reference?  You need to include it in the FROM clause.

[Tom]

Job consists of 4 steps:
1. JOIN of two oracle tables (result of work.table1)
2. JOIN of three tables, including work.table1 and two oracle tables (result of work.table2)
3. I output the result of the second step to a new table work.table3
4. Step Insert rows in oracle table

There is a logic/data flow problem with this design.  In step 1 you are taking data out of Oracle.  Then in step2 you either need either pull two full oracle tables into SAS or push the table generated by 1 back into Oracle.  Same problem between steps 3 and 4.  You are taking data you created in Oracle and moving it into SAS datasets just so you can move it back into Oracle.  Is there some reason the whole process is not coded as one step, coded totally to run in the Oracle database?

[Shukhrat]

Yes, there is a reason. This option already exists and works quite successfully.
But I am refactoring such jobs. To make it convenient and understandable to all users.

[Patrick]

The picture of the flow and the code you've posted don't look to be in sync. I don't see how DIS would generate such Insert code especially given that you've got a Create Table node prior to the Insert Rows transformation.

Looking into the SQL I feel the flow could consist of a single Join transformation creating an intermediary Oracle table and then the Insert transformation could load the intermediary table into the target Oracle table, and then eventually drop the intermediary table or at least truncate it (SQL Delete transformation). 

And because you're loading into one of the source tables: Make sure to set option "dbcommit=0" so that the new rows get all committed together.

The DIS flow will show you if things run in-database by adding a little O symbol to the transformations - something that's missing in the picture of the flow and what's telling us that your code is moving data between Oracle and SAS even though the code you've posted wouldn't.

N.B: The intermediary table doesn't need to be in the same Oracle schema (but should be on the same server).

[Shukhrat]

Attempt for the third time to write a message. Please do not delete, this is not spam.

Thanks for answers.
1. Why I do not use 1 step with direct access to sql.
The answer is: read better and more correct
2. It does not look like DIS code
I answer: this request is taken from the job log
3. You are referencing an alias of TABLE1, but your query only defines alias of TABLE2 and TABLE3.

What actual table are you trying to reference? You need to include it in the FROM clause.
I answer:
The table in the request is indicated, if you look carefully, please:
SELECT DISTINCT
                            table1. "PLAYER_GATE_ID" AS player_gate_id,
                            table1. "WWW_ID" AS www_id
                        FROM
                            (
                                SELECT DISTINCT
                                    player_main. "PLAYER_GATE_ID",
                                    player_main. "WWW_ID"
                                FROM
                                    cmdm.player_main player_main
                                    LEFT JOIN ci_common.award_missioner award_missioner ON player_main. "PLAYER_GATE_ID" = award_missioner
                                    . "PLAYER_GATE_ID"
                                WHERE
                                    (award_missioner. "PLAYER_GATE_ID" IS NULL)
                                    AND ((player_main. "WWW_ID" IS NOT NULL)
                                          AND (player_main. "WWW_REG_STATUS" = 'REGISTERED'))
                            ) table1

I forgot to clarify.
If you change the aliases of table1 to table2, in the step where the error occurs. That request fulfills successfully on the oracle base.

INSERT INTO ci_common.award_missioner (
        "PLAYER_GATE_ID",
        "AWARD_ID",
        "UPDATE_DATE",
        "WWW_ID"
    )
        SELECT DISTINCT
        /*table2*/    table3.player_gate_id   AS player_gate_id,
            10005 AS award_id,
            to_timestamp('  
11FEB2020:16:36:25.7171', 'DDMONYYYY:HH24:MI:SS.FF', 'NLS_DATE_LANGUAGE=American') AS update_date,
          /*table2*/   table3.www_id           AS www_id
        FROM
            (
                SELECT DISTINCT
             /*table1*/    table2."PLAYER_GATE_ID"   AS player_gate_id,
                    10005 AS award_id,
                    to_timestamp('  
11FEB2020:16:36:25.7171', 'DDMONYYYY:HH24:MI:SS.FF', 'NLS_DATE_LANGUAGE=American') AS update_date
                    ,
                 /*table1*/     table2."WWW_ID"           AS www_id
                FROM
                    (
                        SELECT DISTINCT
                            table1."PLAYER_GATE_ID"   AS player_gate_id,
                            table1."WWW_ID"           AS www_id
                        FROM
                            (
                                SELECT DISTINCT
                                    player_main."PLAYER_GATE_ID",
                                    player_main."WWW_ID"
                                FROM
                                    cmdm.player_main player_main
                                    LEFT JOIN ci_common.award_missioner award_missioner ON player_main."PLAYER_GATE_ID" = award_missioner
                                    ."PLAYER_GATE_ID"
                                WHERE
                                    ( award_missioner."PLAYER_GATE_ID" IS NULL )
                                    AND ( ( player_main."WWW_ID" IS NOT NULL )
                                          AND ( player_main."WWW_REG_STATUS" = 'REGISTERED' ) )
                            ) table1, cmdm.player_add_services player_add_services
                            LEFT JOIN ci_common.award_missioner_dop award_missioner_dop ON player_add_services."PLAYER_GATE_ID" =
                            award_missioner_dop."PLAYER_GATE_ID"
                        WHERE
                            ( table1."PLAYER_GATE_ID" = award_missioner_dop."PLAYER_GATE_ID" )
                            AND ( ( award_missioner_dop."PLAYER_GATE_ID" IS NULL )
                                  OR ( player_add_services."PLAYER_FRIEND_CNT" > award_missioner_dop."PLAYER_FRIEND_CNT" ) )
                            AND ( ( player_add_services."FRIEND_FLG" = 'Y' )
                                  AND ( player_add_services."PLAYER_FRIEND_CNT" >= 1 ) )
                    ) table2
            ) table3

[Shukhrat]

I tried to redo the diagram and received an error, already on another line of the generated code.

In the third step, I explicitly specify

LEFT JOIN ci_common.award_missioner_dop award_missioner_dop ON table2. "PLAYER_GATE_ID" = award_missioner_dop. "PLAYER_GATE_ID",

but the code is generated differently, for example:
LEFT JOIN ci_common.award_missioner_dop award_missioner_dop ON table1. "PLAYER_GATE_ID" = award_missioner_dop. "PLAYER_GATE_ID"

I can’t understand why this is happening. Can you tell me what I'm doing wrong?

[Patrick · Accepted solution]

I assume the green tables are actually all views and that's why the error only shows with the Insert node. 

I still don't understand the purpose of the Create Table node.

According to the generated SQL table AWARD_MISSIONAR is both a source and the target table. In such a scenario you really should first populate a physical intermediary table (it can be an Oracle Temporary table) and insert the rows from this intermediary table into the target table.

It's close to impossible to tell you more without actually looking into the DIS job. If you can't solve it then please attach a .spk of the job with all the dependent objects included (tables and libraries). You would ideally also provide another job/script which loads some sample data into the source tables.

[Shukhrat]

The staging table in ORACLE solved the problem.
Thanks.
But it’s still strange for me that my method of building a process works with errors.