CODEWITHSUNDEEP
oracle-databasesnowflake-cloud-data-platform
Snyder Fox
Snyder Fox

Reputation: 174

dbms_random.value() in Snowflake - Oracle to snowflake conversion

Below is the oracle sql query that I got to convert to snowflake. In here, i am blocked in creating dbms_random.value() in snowflake

select emp_id, emp_name, emp_mob,
(case when dbms_random.value() >= 0.85 then 'Y' else 'N' end) as tag
from eds_dwg.employee_data

Can someone help me on this?

Thanks

Upvotes: 1

Views: 304

Answers (2)

Gurupreet Singh Bhatia
Gurupreet Singh Bhatia

Reputation: 728

There are many ways to generate random values in Snowflake too. You can decide what kind of number/string is required.

Some of the examples i tried

--random number

--returns a pseudo-random 64-bit integer

SELECT random()

--random real number

SELECT normal(0, 1, random())

--random number b/w 1 to 10

SELECT uniform(1, 10, random()) 

or 

SELECT zipf(1, 10, random())

--random string 5 char

--Returns a random string of specified length.

SELECT randstr(5, random())

Upvotes: 0

Marcel
Marcel

Reputation: 2622

You can use Snowflake Data generation functions: https://docs.snowflake.com/en/sql-reference/functions-data-generation.html

NORMAL() returns a floating point number with a specified mean and standard deviation. Something like this with correct adaptions of the parameters could to the trick: https://docs.snowflake.com/en/sql-reference/functions/normal.html

An alternative can be using UNIFORM(): https://docs.snowflake.com/en/sql-reference/functions/uniform.html

Example from docs to generate a value between 0 and 1:

select uniform(0::float, 1::float, random()) from table(generator(rowcount => 5));

Upvotes: 1

Related Questions