For XYZ reason I need a query to explicitly fail (return error code to connection) if some condition is met (on Snowflake). Can someone recommend an approach?
Some illustration in pseudo-code:
IF 0= ( SELECT COUNT(*) FROM XYZ) THEN FAIL
-
By Fail do you mean - throw an error ?Srinika Pinnaduwage– Srinika Pinnaduwage2020-05-06 20:04:18 +00:00Commented May 6, 2020 at 20:04
Add a comment
|
2 Answers
I like Simeon's approach, but you may want a custom error message if this is running in a long script. Throwing an error in a JavaScript UDF will allow custom (if untidy) error messages:
create or replace function RAISE_ERROR(MESSAGE string)
returns string
language javascript
as
$$
throw "-->" + MESSAGE + "<--";
$$;
select
case (select count(*) from XYZ)
when 0 then raise_error('My custom error.')
else 'There are rows in the table'
end
;
If there are no rows in XYZ, it will generate an error message that reads:
JavaScript execution error: Uncaught --> My custom error <--. in RAISE_ERROR at ' throw MESSAGE;' position 4 stackstrace: RAISE_ERROR line: 2
It's not the tidiest of error messages, but it will allow you to embed a custom error message if you need help identifying the error. The arrows should help direct people to the real error message thrown in the stack.
Comments
SELECT IFF(true, 1::number, (1/0)::number);
then:
IFF(TRUE, 1::NUMBER, (1/0)::NUMBER)
1
where-as
SELECT IFF(false, 1::number, (1/0)::number);
gives:
Division by zero
