Using an if-else statement in big-query-CodePudding

I need to know how to implement this. i have written a sql statement in big-query of this format:

with a( select * from tab1),
b as (select * from tab2),
select <field.a>,<field.b> from a inner join b on a.<field>=b.<field>

The above statement works a fine and returns me a number of records. Now I want to introduce a if statement which checks for the presence of a value in the logintab table then only execute the above sql statement.

So I do this:

if EXISTS (select * from logintab  WHERE
         emailid="[email protected]")
then
 with a( select * from tab1),
    b as (select * from tab2),
    select <field.a>,<field.b>,<...> from a join b on a.<field>=b.<field> order by endtime desc;
 end if;

but when I execute the above statement in bigquery console I'm just getting true or false as response instead of the results which I got without the if condition.

What can I try next?

CodePudding user response：

This is one way to achieve what you want to do.

with a as( select * from `dataset.tab1`),
b as(select * from `dataset.tab2`)
select a.field,b.field from a inner join b on a.id=b.id
where (select 1 from `dataset.logintab` where emailid="[email protected]")=1

The If expression only returns a true or a false according to the documentation. That is why it only returns you false or true, and not the complete result.

CodePudding user response：

The false you're getting is likely the result of exists(select * from logintab WHERE emailid="[email protected]"). If the result was true, the other query would have been executed, and you'd see its result as well.

You can check this by adding an else statement, e.g.

if EXISTS (select * from logintab  WHERE
         emailid="[email protected]")
then
 with a( select * from tab1),
    b as (select * from tab2),
    select <field.a>,<field.b>,<...> from a join b on a.<field>=b.<field> order by endtime desc;
else
    select "No logintab found";
end if;