How to run SQL like EVAL in BigQuery

Question

In BigQuery, I have a query and its result is like:

myQueryValue
select * from 'some path'

I'd like to use it directly in new query.

SELECT someValue
FROM
(
    select * from 'some path' <- How can I replace this to myQueryValue?
)

How can I use the result value of some queries like EVAL?

----------------EDITED AT 14th Oct.----------------

Thanks for all answer but I need to explain more what I want.

If I have a 'queryTable' like

col
'select * from tableA'

The result of 'select * from tableA' is

foo
bar

When I only know about 'queryTable', how can I get the this result?

foo
bar

I'd like to refer 'queryTable', and get the final result of its.

Answer 1

You can use sub queries, its a query inside the from clause.

Here is an example code:

SELECT * FROM (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500)

A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

There are a few rules that subqueries must follow −

Subqueries must be enclosed within parentheses.

A subquery can have only one column in the SELECT clause, unless multiple > >columns are in the main query for the subquery to compare its selected > >columns.

An ORDER BY command cannot be used in a subquery, although the main query >can use an ORDER BY. The GROUP BY command can be used to perform the same >function as the ORDER BY in a subquery.

Subqueries that return more than one row can only be used with multiple >value operators such as the IN operator.

The SELECT list cannot include any references to values that evaluate to a >BLOB, ARRAY, CLOB, or NCLOB.

A subquery cannot be immediately enclosed in a set function.

The BETWEEN operator cannot be used with a subquery. However, the BETWEEN >operator can be used within the subquery.

click here for more information about sub queries.

Answer 2

Below is example of how easy to achieve this

DECLARE myQueryValue STRING;
SET myQueryValue = "select * from your_table";

EXECUTE IMMEDIATE '''
SELECT someValue
FROM ( ''' || myQueryValue || ''' )''';

Answer 3

As you didn't provide additional information I'm going to elaborate my comment.

In comment I've proposed that you can use Declare with Set. Good differences between those two are presented in this stackoverflow thread.

DECLARE does not initialize the variable. When you declare it you declare the variable name, the type, and a default value, which could be an expression.

SET is for initializing the variable you declared previously, and you cannot SET the variable until you DECLARE it.

One of the examples has been provided in @Mikhail Berlyant answer in this thread.

However, more detailed information with more examples are mentioned in GCP Set Reference.

Sets a variable to have the value of the provided expression, or sets multiple variables at the same time based on the result of multiple expressions.

The SET statement may appear anywhere within the body of a script.

This is the easiest way to achieve this.

Another common way you could do this is to use SubQuery/Nested Query, it's also well described in the GCP BigQuery Reference.

In GCP doc you can also find example which uses Set, Declare and subquery:

DECLARE target_word STRING DEFAULT 'methinks';
DECLARE corpus_count, word_count INT64;

SET (corpus_count, word_count) = (
  SELECT AS STRUCT COUNT(DISTINCT corpus), SUM(word_count)
  FROM `bigquery-public-data`.samples.shakespeare
  WHERE LOWER(word) = target_word
);

SELECT
  FORMAT('Found %d occurrences of "%s" across %d Shakespeare works',
         word_count, target_word, corpus_count) AS result;

Output:

Found 151 occurrences of "methinks" across 38 Shakespeare works