CODEWITHSUNDEEP
sqlgoogle-bigquery
user15915737
user15915737

Reputation: 310

Can't SET variable in BigQuery | Error: Cannot coerce expression

I'm trying to set variable in order to make further operation.

But when I set my first variable on line 2 ("SET(new_user)=( "), I get an error

Query error: Cannot coerce expression ( SELECT COUNT(*) FROM...

DECLARE new_user, total_user int64;

SET(new_user)=(
    SELECT 
        COUNT(*) 
        FROM `projet.dataset.user`
        WHERE timestamp BETWEEN '2022-02-09 19:13:00' AND CURRENT_TIMESTAMP()
);


SET(total_user)=(
    SELECT 
        COUNT(*),
        FROM `projet.dataset.user`
);

The problem is coming the way I use the SET because my SELECT work without.

Upvotes: 1

Views: 5926

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Depends on what your real needs - i see few options to fix the issue

Option 1

DECLARE new_user, total_user int64;

SET new_user=(
    SELECT 
        COUNT(*) 
        FROM `projet.dataset.user`
        WHERE timestamp BETWEEN '2022-02-09 19:13:00' AND CURRENT_TIMESTAMP()
);


SET total_user =(
    SELECT 
        COUNT(*),
        FROM `projet.dataset.user`
);

Option 2

DECLARE new_user, total_user int64;

SET(new_user)=(
    SELECT AS STRUCT
        COUNT(*) 
        FROM `projet.dataset.user`
        WHERE timestamp BETWEEN '2022-02-09 19:13:00' AND CURRENT_TIMESTAMP()
);


SET(total_user)=(
    SELECT AS STRUCT 
        COUNT(*),
        FROM `projet.dataset.user`
);

Upvotes: 3

Related Questions