CODEWITHSUNDEEP
amazon-web-servicesamazon-redshift
user11959597
user11959597

Reputation: 11

How to Abort or Exit from Redshift Query with a conditional expression?

I'm trying to abort/exit a query based on a conditional expression using CASE statement:

    drop table if exists #dups_tracker ;

    create table #dups_tracker
    (
      column1 varchar(10)
    );

    insert into #dups_tracker values ('John'),('Smith'),('Jack') ;

    with c1 as
    (select
         0 as denominator__v
        ,count(*) as dups_cnt__v
    from #dups_tracker
    )
    select
      case 
        when dups_cnt__v > 0 THEN 1/denominator__v
      else   
        1  
      end Ind__v
    from c1
    ;

Here is the Error Message :

Amazon Invalid operation: division by zero; 1 statement failed.

Upvotes: 1

Views: 1641

Answers (3)

ATD
ATD

Reputation: 1

drop table if exists temp_table;
create temp table temp_table (field_1 bool);

insert into temp_table
    select  case
                when false -- or true
                then 1
                else 1 / 0
                end as field_1;

This should compile, and fail when the condition isn't met.

Not sure why it's different from your example, though...

Edit: the above doesn't work querying against a table. Leaving it here for posterity.

Upvotes: 0

user11959597
user11959597

Reputation: 11

Here is the logic I was able to write to abort a SQL in case of positive usecase,

/* Dummy Table to Abort Dups Check process if Positive */

--Dups Table
drop table if exists #dups;
create table #dups
(
  dups_col varchar(1)
);
insert into #dups values('A');

--Dummy Table
drop table if exists #dummy ;
create table #dummy
(
dups_check decimal(1,0)
)
;

--When Table is not empty and has Dups
insert into #dummy
select 
count(*) * 10
from #dups
;

/*
[Amazon](500310) Invalid operation: Numeric data overflow (result precision)
Details: 
 -----------------------------------------------
  error:  Numeric data overflow (result precision)
  code:      1058
  context:   64 bit overflow
  query:     3246717
  location:  numeric.hpp:158
  process:   padbmaster [pid=6716]
  -----------------------------------------------;
1 statement failed.
*/


--When Table is empty and doesn't have dups
truncate #dups ;

insert into #dummy
select 
count(*) * 10
from #dups
;

Upvotes: 0

John Rotenstein
John Rotenstein

Reputation: 269826

There is no concept of aborting an SQL query. It either compiles into a query or it doesn't. If it does compile, the query runs.

The closest option would be to write a Stored Procedure, which can include IF logic. So, it could first query the contents of a table and, based on the result, decide whether it will perform another query.

Upvotes: 1

Related Questions