CODEWITHSUNDEEP
sqloracle-database
Slater Victoroff
Slater Victoroff

Reputation: 21914

sql skipping an insert statement if no results are found

I have a number of insert statements like the ones shown below that I am trying to run in one large block. Is there anyway for me to tell the query to skip that particular insert statement and continue on down the list of one of the select statements returns null? 

    insert into V1144engine.T_edges(edge_id, version, node1_id, node2_id, edge_type_id, created_at, weight,deleted_at) VALUES(V1144ENGINE.S_PK_EDGES.NEXTVAL,0,(Select node_id from V1144engine.T_nodes where node_name = 'Writing an Equation of a Perpendicular Line' and rownum=1),(select node_id from V1144engine.T_nodes where node_name = 'slope'and node_type_id =11),5,SYSDATE,5.318,null);

    insert into V1144engine.T_edges(edge_id, version, node1_id, node2_id, edge_type_id, created_at, weight,deleted_at) VALUES(V1144ENGINE.S_PK_EDGES.NEXTVAL,0,(Select node_id from V1144engine.T_nodes where node_name = 'Writing an Equation of a Perpendicular Line' and rownum=1),(select node_id from V1144engine.T_nodes where node_name = 'vertic'and node_type_id =11),5,SYSDATE,5,null);

Upvotes: 0

Views: 349

Answers (3)

Tebbe
Tebbe

Reputation: 1372

You could duplicate the predicates of the nested SELECTs in EXISTS clauses:

INSERT INTO v1144engine.t_edges (
    edge_id
,   version
,   node1_id
,   node2_id
,   edge_type_id
,   created_at
,   weight
,   deleted_at
)
SELECT v1144engine.s_pk_edges.NEXTVAL
,      0
,     (SELECT node_id 
       FROM   v1144engine.t_nodes 
       WHERE  node_name = 'Writing an Equation of a Perpendicular Line'
       AND    ROWNUM = 1)
,     (SELECT node_id
       FROM   v1144engine.t_nodes
       WHERE  node_name = 'slope'
       AND    node_type_id = 11)
,      5
,      SYSDATE
,      5.318
,      TO_DATE(NULL)
FROM   DUAL
WHERE  EXISTS (SELECT NULL
               FROM   v1144engine.t_nodes 
               WHERE  node_name = 'Writing an Equation of a Perpendicular Line')
AND    EXISTS (SELECT NULL
               FROM   v1144engine.t_nodes
               WHERE  node_name    = 'slope' 
               AND    node_type_id = 11);

I don't know how many of these you've got, but, as an alternative, perhaps pivoting like the following would work, as more of a set-based approach?

INSERT INTO v1144engine.t_edges (
    edge_id
,   version
,   node1_id
,   node2_id
,   edge_type_id
,   created_at
,   weight
,   deleted_at
)
SELECT v1144engine.s_pk_edges.NEXTVAL
,      0
,      MAX(CASE
           WHEN node_name = 'Writing an Equation of a Perpendicular Line'
           THEN node_id
           END)
,      MAX(CASE
           WHEN node_name = 'slope'
           AND  node_type_id = 11
           THEN node_id
           END)
,      5
,      SYSDATE
,      5.318
,      TO_DATE(NULL)
FROM   v1144engine.t_nodes
WHERE  node_name IN ('Writing an Equation of a Perpendicular Line','slope')
HAVING MAX(CASE
           WHEN node_name = 'Writing an Equation of a Perpendicular Line'
           THEN node_id
           END) IS NOT NULL
AND    MAX(CASE
           WHEN node_name = 'slope'
           AND  node_type_id = 11
           THEN node_id
           END) IS NOT NULL
;

(Warning: untested code.)

Upvotes: 1

GWu
GWu

Reputation: 2787

I'd wrap it into an select ... from dual and restrict on node1|2_id IS NOT NULL:

INSERT INTO v1144engine.t_edges (edge_id
                                ,version
                                ,node1_id
                                ,node2_id
                                ,edge_type_id
                                ,created_at
                                ,weight
                                ,deleted_at
                                )
 SELECT *
       FROM (
             SELECT 
                    v1144engine.s_pk_edges.NEXTVAL edge_id
                   ,0                              version
                   , (SELECT node1_id
                        FROM v1144engine.t_nodes
                       WHERE node_name = 'Writing an Equation of a Perpendicular Line'
                         AND ROWNUM = 1)           node1_id
                   , (SELECT node_id
                        FROM v1144engine.t_nodes
                       WHERE node_name = 'slope'
                         AND node_type_id = 11)    node2_id
                   ,5       edge_type_id
                   ,SYSDATE                        created_at
                   ,5.318                          weight
                   ,NULL                           deleted_at
               FROM dual
            )
      WHERE node1_id IS NOT NULL
        AND node2_id IS NOT NULL
;

Upvotes: 1

Robbie Dee
Robbie Dee

Reputation: 1977

Why not just put the selects into a temporary table and then generate the dynamic SQL from that?

Upvotes: 0

Related Questions