Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I'm new to javascript so please ignore bad programming convention.

I am trying to insert some records in tableA(say) and using insert into (select statement) methodology. This insertion is supposed to happen when an if condition returns TRUE (a==null)

Procedure is getting created successfully. But When I call the procedure even though the code is going into the if branch (checked via return text) the actual insertion is not happening.

(already tried commit as advised in a different requester's post)

Summary:

OUTPUT: code reached here so..success.

ISSUE: When I check the tableA after calling SP proc(), there is no new record inserted.

I have already checked all the SQL query outside of the SP proc and they are working fine. All the table and variable names are changed for privacy reasons.

Please find below my procedure code and expected and actual outcomes and help me understand what am I doing wrong.

<pre lang="text">


What I have tried:

create or replace procedure proc()
  returns string
  language javascript
  as 
  $$

 var a = ('select max(fw) from  tableA where fy in (select max(fy)from tableB b join tableC c on\ c.id=b.uuid);').execute;

 if (a == null)
 {
 ('insert into tableA\
  (select\
   c.fy,\
   c.fw,\
   sum(b.somenumber) Total,\
   count(distinct b.uuid) Count,\
   (Total/Count) AOV\
   from tableB b\
   join tableC c\
   on c.id=b.uuid\
   where fy in (select max(fy)from tableB b join tableC c on c.id=b.uuid)\
   group by FY,FW\
   order by FY,FW)').execute;
return 'code reached here so..success'
}
else {
  return 'nothing'
     }
  $$;

call proc();
EXPECTED: 'code reached here so..success' and Records should be inserted in tableA (as they are being inserted when I test this SQL outside of Stored Procedure proc.).

ACTUAL: only 'code reached here so..success' gets returned and NO RECORD INSERTED
Posted
Comments
Chris Copeland 29-Nov-22 7:50am    
Have you tried taking out the select query and running it separately to confirm that it does return some results? If it doesn't return anything then nothing will get inserted. If it does, and you expect the insert to have worked, it sounds more like a committing issue.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900