I am getting this answer "WHERE clause should return type BOOL, but returns INT64 at [7:3]&quot-CodePudding

SELECT
  station_id,
  name
FROM 
  bigquery-public-data.new_york.citibike_stations
WHERE
  (
    SELECT
      start_station_id
    FROM
     bigquery-public-data.new_york.citibike_trips
    WHERE
      usertype = 'subscriber'
  )

I am new to learning SQL, so I don't know what the error is trying to tell. It my first time doing subquery within a query, so I want to say is about the space otherwise I don't know. I BigQuery to practice SQL.

CodePudding user response：

Your problem is that you are using a subquery in the WHERE section without a comparison. In the WHERE section you should have something operator something that is for example: someTableField = 'someValue' The error is trying to say that there is no logical check to return a boolean type, that is whether the comparison in the where section will be true or false.

Based on your query alone it should probably something like:

SELECT
  station_id,
  name
FROM 
  bigquery-public-data.new_york.citibike_stations
WHERE
 citibike_stations.start_station_id IN (
    SELECT
      start_station_id
    FROM
     bigquery-public-data.new_york.citibike_trips
    WHERE
      usertype = 'subscriber'
  )

In the where section above what I did was comparing whether the field start_station_id from table citibike_stations on your from section is within your subquery on the citibike_trips table.

CodePudding user response：

The where command works like if in programming. You must compare two expressions using conditional operators. Your query only performs a calculation and finally produces an output of start_station_id and does not perform any kind of comparison. To correct this, you must compare the result with another expression. For example, checking the presence of a station_id in the final result like the code below

SELECT 
  station_id, 
  name 
FROM 
  bigquery - public - data.new_york.citibike_stations 
WHERE 
  station_id in (
    SELECT 
      start_station_id 
    FROM 
      bigquery - public - data.new_york.citibike_trips 
    WHERE 
      usertype = 'subscriber'
  )

CodePudding user response：

A where clause is a condition on the select, that it should only return when some column = some value, but in your case you don't specify the value it should check on.

Perhaps you mean to say:

SELECT station_id, name
FROM bigquery-public-data.new_york.citibike_stations
WHERE station_id in
(
    SELECT start_station_id
    FROM bigquery-public-data.new_york.citibike_trips
    WHERE usertype = 'subscriber'
)

But better way at least in my opinion would be using join like:

SELECT station_id, name
FROM bigquery-public-data.new_york.citibike_stations s
join bigquery-public-data.new_york.citibike_trips t
on t.start_station_id = s.station_id
WHERE t.usertype = 'subscriber'