Can some one help am facing issue while SQL query execution in outsystems.

mentioned below query there will no relationship to transation and RepotDetails table but Reportkey attribute exist and both the value will be equal.

  • Need to delete the records both table maching report key records and record date should be previous year and previous month based on posting date attribute date.


 DELETE {Transaction},{ReportDetail}
FROM
  {Transaction}
  INNER JOIN {ReportDetail} ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
    WHERE
      {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  )
 


If you want to do this in design time, you need to add this:
 Exec('com'+'mit') 


In runtime, just neet put a output variable.
If you try test the query, needs put test values in second tab.


Let me know if this helps you


Cheers

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;

Daryna Kravets wrote:

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;

Hi Daryna,

The Roberts's logic is correct. Its the same that you show, but with more conditions.

Cheers,
Miguel


Miguel Verdasca wrote:

Daryna Kravets wrote:

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;

Hi Daryna,

The Roberts's logic is correct. Its the same that you show, but with more conditions.

Cheers,
Miguel



Hi Miguel,


 Added output structure to display records,


Also tried simple delete query as below mentioned 

DELETE FROM {ReportDetail} WHERE {ReportDetail}.[ReportKey] in(SELECT {ReportDetail}.[ReportKey] FROM {ReportDetail} WHERE {ReportDetail}.[ReportKey]='nes')


On SQL test output below 


'nes' record is exisit in the database. Also executed query in server action didn't work :(

Miguel Verdasca wrote:

Daryna Kravets wrote:

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;

Hi Daryna,

The Roberts's logic is correct. Its the same that you show, but with more conditions.

Cheers,
Miguel


Hi Miguel,

Syntax 

DELETE {Transaction},{ReportDetail}

is incorrect. I tested it without contitions and got the same error.


Robert Johnston wrote:

Miguel Verdasca wrote:

Daryna Kravets wrote:

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;

Hi Daryna,

The Roberts's logic is correct. Its the same that you show, but with more conditions.

Cheers,
Miguel



Hi Miguel,


 Added output structure to display records,


Also tried simple delete query as below mentioned 

DELETE FROM {ReportDetail} WHERE {ReportDetail}.[ReportKey] in(SELECT {ReportDetail}.[ReportKey] FROM {ReportDetail} WHERE {ReportDetail}.[ReportKey]='nes')


On SQL test output below 


'nes' record is exisit in the database. Also executed query in server action didn't work :(


What is 'nes' ?

try put an input variable, and add this input variable to your sql.. and in query assign "nes" to input variable


Hi Robert,

I am checking your query and will get back to you.Just want to know reportkeyand postingdate data types?

This is an example Robert:

Abdul quadir Saifee wrote:

Hi Robert,

I am checking your query and will get back to you.Just want to know reportkeyand postingdate data types?


Hi Abdul ReportKey data type is Text and Postingdate is Date.


Thanks :)

Daryna Kravets wrote:

Miguel Verdasca wrote:

Daryna Kravets wrote:

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;

Hi Daryna,

The Roberts's logic is correct. Its the same that you show, but with more conditions.

Cheers,
Miguel


Hi Miguel,

Syntax 

DELETE {Transaction},{ReportDetail}

is incorrect. I tested it without contitions and got the same error.


Hi Daryna, I have to agree with you. I didn't see well the sql


Miguel Verdasca wrote:

This is an example Robert:


Thanks for your quick response Mguel :), I'll add input variable and check 

Daryna Kravets wrote:

Hi Robert,

DELETE statement syntax is a little different than yours:

DELETE FROM table_name WHERE condition;


Hi Daryna Kravets Didn't work :(

Hi Robert,

Your SQL syntex is incorrect PLs go through:https://stackoverflow.com/questions/1714545/delete-rows-from-multiple-tables-using-a-single-query-sql-express-2005-with-a

I have another approch for you which we generally use for archiving:

1. add new attribute "isDelete" to both tables.

2. update both tables where youre criteria meet with isDelete=true.

3. write two simple delete queries.

Robert Johnston wrote:

Miguel Verdasca wrote:

This is an example Robert:


Thanks for your quick response Mguel :), I'll add input variable and check 


Welcome! Give a feedback :)

SyntaxEditor Code Snippet

Add a new attribute "isDelete" with default valu as 0 in both of the tables and then check with this sql and please let me know if this works:

update {Transaction}
set {Transaction}.[IsDelete]=1
FROM
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
    WHERE
      {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  )
update {ReportDetail}
set {ReportDetail}.[isDelete]=1
FROM
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
    WHERE
      {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  )
    
    Delete  from {Transaction} where {Transaction}.[IsDelete]=1
    Delete  from {ReportDetail} where {ReportDetail}.[isDelete]=1


Abdul quadir Saifee wrote:

Hi Robert,

Your SQL syntex is incorrect PLs go through:https://stackoverflow.com/questions/1714545/delete-rows-from-multiple-tables-using-a-single-query-sql-express-2005-with-a

I have another approch for you which we generally use for archiving:

1. add new attribute "isDelete" to both tables.

2. update both tables where youre criteria meet with isDelete=true.

3. write two simple delete queries.


Hi Abdul it is hard to add new attribute and update there are more than 2 lakhs records are exist in production 

and there is no relationship between to table.

As in metioned in the query conditon.

Both the table reportkey should macth and ReportDetail table posting date should be previous year and month only those records has to be deleted.

       {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))


Is there any other query solution :(

Robert Johnston wrote:

Abdul quadir Saifee wrote:

Hi Robert,

Your SQL syntex is incorrect PLs go through:https://stackoverflow.com/questions/1714545/delete-rows-from-multiple-tables-using-a-single-query-sql-express-2005-with-a

I have another approch for you which we generally use for archiving:

1. add new attribute "isDelete" to both tables.

2. update both tables where youre criteria meet with isDelete=true.

3. write two simple delete queries.


Hi Abdul it is hard to add new attribute and update there are more than 2 lakhs records are exist in production 

and there is no relationship between to table.

As in metioned in the query conditon.

Both the table reportkey should macth and ReportDetail table posting date should be previous year and month only those records has to be deleted.

       {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))


Is there any other query solution :(


then I need to check. :)

Robert Johnston wrote:

Abdul quadir Saifee wrote:

Hi Robert,

Your SQL syntex is incorrect PLs go through:https://stackoverflow.com/questions/1714545/delete-rows-from-multiple-tables-using-a-single-query-sql-express-2005-with-a

I have another approch for you which we generally use for archiving:

1. add new attribute "isDelete" to both tables.

2. update both tables where youre criteria meet with isDelete=true.

3. write two simple delete queries.


Hi Abdul it is hard to add new attribute and update there are more than 2 lakhs records are exist in production 

and there is no relationship between to table.

As in metioned in the query conditon.

Both the table reportkey should macth and ReportDetail table posting date should be previous year and month only those records has to be deleted.

       {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))


Is there any other query solution :(

How about something like this?

Hi Daryna,


There is high volume data in production, ForEach exection will not fail with timout error or any other impact ?


Thanks :)
Robert

try this you need to modify syntex as I havenot checked this :

SyntaxEditor Code Snippet

select  id
into #tempDeleteTransaction 
FROM
(select {Transaction}.[Id] id
from 
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
    WHERE
      {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  ))
select id
 into #tempDeleteReportDetails 
FROM
(select {ReportDetail}.[Id] id from
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
    WHERE
      {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  )
    )
    Delete  from {Transaction} where {Transaction}.[Id] in (select Id from  #tempDeleteTransaction)
    Delete  from {ReportDetail} where {ReportDetail}.[Id] in (select Id from #tempDeleteReportDetails)
    DROP TABLE #tempDeleteTransaction;
DROP TABLE #tempDeleteReportDetails;

Abdul quadir Saifee wrote:

try this you need to modify syntex as I havenot checked this :

SyntaxEditor Code Snippet

select  id
into #tempDeleteTransaction 
FROM
(select {Transaction}.[Id] id
from 
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
    WHERE
      {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  ))
select id
 into #tempDeleteReportDetails 
FROM
(select {ReportDetail}.[Id] id from
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
    WHERE
      {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
      AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  )
    )
    Delete  from {Transaction} where {Transaction}.[Id] in (select Id from  #tempDeleteTransaction)
    Delete  from {ReportDetail} where {ReportDetail}.[Id] in (select Id from #tempDeleteReportDetails)
    DROP TABLE #tempDeleteTransaction;
DROP TABLE #tempDeleteReportDetails;


Thanks Abdul I'll check and update you :)

Finally I have prepared and tested a query and it was a new learning for me. Please test on dev/qa before running on prod :

SyntaxEditor Code Snippet

with transactioncte
as 
(select {Transaction}.[Id] Id, {ReportDetail}.[Id] reportID
from 
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
   WHERE
    {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
     AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  ))
  select * into #temp from (select * from transactioncte) as x
  
 Delete  from {Transaction} where {Transaction}.[Id] in (select Id from  #temp)
 
 Delete  from {ReportDetail} where {ReportDetail}.[Id] in (select reportID from  #temp)
    
 
   drop table #temp
 
   
Solution

Abdul quadir Saifee wrote:

Finally I have prepared and tested a query and it was a new learning for me. Please test on dev/qa before running on prod :

SyntaxEditor Code Snippet

with transactioncte
as 
(select {Transaction}.[Id] Id, {ReportDetail}.[Id] reportID
from 
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
   WHERE
    {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
     AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  ))
  select * into #temp from (select * from transactioncte) as x
  
 Delete  from {Transaction} where {Transaction}.[Id] in (select Id from  #temp)
 
 Delete  from {ReportDetail} where {ReportDetail}.[Id] in (select reportID from  #temp)
    
 
   drop table #temp
 
   

If this help you, please mark this as Solution so others can get benefit from this.


Solution

Hi Abdul, how are you?

It would not be better to let Robert do his tests first, and then decide if any of the answers helped him, and if so, mark it as right or else he learn the answer that he implemented and that helped him.


You don't need to be constantly posting, let alone replying that makes the others that the question can help as you say, get lost with the huge scroll down they have to do!


The important thing is to help, this is not a contest of right answers.

Regards,
Miguel

Miguel Verdasca wrote:

Hi Abdul, how are you?

It would not be better to let Robert do his tests first, and then decide if any of the answers helped him, and if so, mark it as right or else he learn the answer that he implemented and that helped him.


You don't need to be constantly posting, let alone replying that makes the others that the question can help as you say, get lost with the huge scroll down they have to do!


The important thing is to help, this is not a contest of right answers.

Regards,
Miguel

Sure Miguel. I am agree with you. But as it was a new learning for me so I was bit excited. Will take care of this. Also is it better to delete previous posts as they are just increasing length of page? 


Thank you for understanding Abdul.


Robert, if you need anything else, say, test the solutions that have been presented to you here, and see if anything solves your problem. If you need help, ask, surely someone in the community has already gone through your problem and can help you, or even if they haven't, they will help you.


Continuation of good work for all.

Abdul quadir Saifee wrote:

Abdul quadir Saifee wrote:

Finally I have prepared and tested a query and it was a new learning for me. Please test on dev/qa before running on prod :

SyntaxEditor Code Snippet

with transactioncte
as 
(select {Transaction}.[Id] Id, {ReportDetail}.[Id] reportID
from 
  {Transaction} 
  INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
WHERE
  EXISTS(
    SELECT
      {Transaction}.[ReportKey]
    FROM
      {Transaction}
   WHERE
    {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
     AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  ))
  select * into #temp from (select * from transactioncte) as x
  
 Delete  from {Transaction} where {Transaction}.[Id] in (select Id from  #temp)
 
 Delete  from {ReportDetail} where {ReportDetail}.[Id] in (select reportID from  #temp)
    
 
   drop table #temp
 
   

If this help you, please mark this as Solution so others can get benefit from this.


Hi Abdul,


Applogize for delay in the response

 There is invalid attribute error in code, ID is attribute not exsist in details table.

select {Transaction}.[Id] Id, {ReportDetail}.[Id] reportID


  • Can replace with report key? like below 
 (select {Transaction}.[ReportKey] Id, {ReportDetail}.[ReportKey] reportID


  • Final query will be, please correct if query is wrong.
WITH transactioncte as (select {Transaction}.[ReportKey] Id, {ReportDetail}.[ReportKey] reportID  from {Transaction} 
INNER JOIN {ReportDetail}  ON {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]

WHERE  EXISTS(SELECT {Transaction}.[ReportKey] FROM  {Transaction} WHERE
    {Transaction}.[ReportKey] = {ReportDetail}.[ReportKey]
     AND  {ReportDetail}.[PostingDate] <= DATEADD(year, -1, DATEADD(month, -1, GETDATE()))
  ))
  select * into #temp from (select * from transactioncte) as x
  
 Delete  from {Transaction} where {Transaction}.[ReportKey] in (select Id from  #temp)
 
 Delete  from {ReportDetail} where {ReportDetail}.[ReportKey] in (select reportID from  #temp)

 drop table #temp


  • Executed above code with below error

Thank you :)

Miguel Verdasca wrote:

Robert Johnston wrote:

Miguel Verdasca wrote:

This is an example Robert:


Thanks for your quick response Mguel :), I'll add input variable and check 


Welcome! Give a feedback :)


Hi Miguel,


Simple delete query I have tested it is working in run time :), Facing issue on deleting records from two table.

like DELETE FROM {Transaction},{ReportDetail}