A scalar subquery is an SQL subquery used as a scalar expression. When the subquery result is empty it denotes NULL, when the subquery result has one column and one row it denotes the single value or NULL contained, and otherwise generates an error. It can be used almost anywhere a single column value or literal is legal, and has the usual operand characteristics: a data type, a length, an indication that it can be NULL, etc.
Questions tagged [scalar-subquery]
45 questions
0
votes
1 answer
Bigquery Scalar subquery produced more than one element
I have a scalar subquery that looks up city name based on a city code and state code. Sometimes the subquery does not return a match and this is causing an error: 'Scalar subquery produced more than one element'. I verified that there are no…
AlpsToronto
- 25
- 4
0
votes
1 answer
SQLAlchemy correlated subquery TypeError: Boolean value of this clause is not defined
I need to write a Python SQLAlchemy code converting this SQL query, but I cannot find a meaningful way to perform this query in SQL Alchemy.
SELECT *
FROM alpha AS X
WHERE X.index = (
SELECT
MAX(index)
FROM
alpha AS Y
…
linello
- 8,451
- 18
- 63
- 109
0
votes
2 answers
SparkSQL - How to make scalar subquery work without FIRST/MIN/MAX/AVG
As in What does "Correlated scalar subqueries must be Aggregated" mean?, SparkSQL complains for scalar sub-query.
So when catalyst can't make 100% sure just by looking at the SQL statement (without looking at your data) that the sub-query only…
mon
- 18,789
- 22
- 112
- 205
0
votes
1 answer
BigQuery - Scalar subquery produced more than one element -
I have this query that helps me to find separate key words within strings (very useful with utm_campaign and utm_content):
SELECT
utm_campaign,
splits[SAFE_OFFSET(0)] AS country,
splits[SAFE_OFFSET(1)] AS product,
…
Stumeister23
- 25
- 4
0
votes
2 answers
Apply subquery result to every row in outer query
I have written a subquery like so:
(
SELECT SUM(X.kMax)
FROM (
SELECT MAX(Val) AS kMax
FROM [Consumers] upc
GROUP BY upc.[Profile]
) X
)
It gets the maximum value for each profile and then…
jcoke
- 1,555
- 1
- 13
- 27
0
votes
3 answers
Why the output of a SELECT can be another SELECT?
I am rather confused about the following SQL query:
SELECT (SELECT S.name FROM student AS S
WHERE S.sid = E.sid) AS sname
FROM enrolled as E
WHERE cid='15-455';
SELECT should be followed by an output, but why here there is another SELECT? How…
Name Null
- 390
- 2
- 11
0
votes
3 answers
How to use Scalar Subquery on a single table for lob column?
I have a below query in Oracle having duplicate rows, where file_data is a BLOB column.
SELECT attachsysfilename, file_seq, version, file_size, lastupddttm, lastupdoprid, file_data
from PS_LP_EX_FILEATTCH
I want to apply distinct clause on top of…
Pranav
- 363
- 8
- 19
0
votes
1 answer
Count records from another table to include in the results of a bound table
I am using Visual Studio and have an Access database with a couple of tables that are related. They are using default drag/drop binding using WinForms to the dataset which was automatically generated as well.
I want the parent table to include a…
braX
- 11,506
- 5
- 20
- 33
0
votes
2 answers
Correlated Scalar SubQueries
I am using SparkSQL. I have the following table:
Department:
dep_id | emp_id | age
0 | 0 | 21
0 | 1 | 53
0 | 3 | 42
0 | 5 | 58
1 | 4 | 36
1 | 8 | 48
2 | 2 | 54
2 | 6 | 31
2 | 7 | 30
2 | 9 | 54
However, I am not being able to successfully execute…
marie20
- 723
- 11
- 30
0
votes
1 answer
Athena scalar subquery issue
I am working on athena .
I have a table with two columns of interest besides other columns. They are id and account architect column. Both are string type .
This is how the table looks like now .
The end product should be ;
The query that i…
user2974748
- 61
- 1
- 1
- 5
0
votes
2 answers
Mysql: Using where comparison operator in outer query against select subquery result
I am am trying to use a WHERE clause based on a scalar subquery result. The query executes correctly without the WHERE clause. With the WHERE clause I get Error Code: 1054. Unknown column 'available_services' in 'where clause'.
How can I achieve…
user12372677
- 1
- 1
0
votes
2 answers
ERROR: more than one row returned by a subquery used as an expression. POSTGRESQL / LOOKER
I am trying to do a subquery in Looker that returns the assignee name from the person table where the person ID equals the assignedtoperson ID
I have tried to do a limit 1, but that just returns the first value for person in every row
SELECT
…
Andrew
- 1
0
votes
0 answers
BigQuery - Unexpected result when UPDATE using SELECT from JOIN: zero lines matched
First question StackOverflow and just getting started with SQL and BigQuery.
On BigQuery I have a project with one table: table.
This table contains:
-some data x,
-some timestamps for each entry time,
-some group identifiers group that partition…
mcavati
- 1
- 1
0
votes
2 answers
What is the maximum number of scalars that can be used in an IN clause?
Whenever I use a small statement for example:
DELETE FROM c_ordertax WHERE (c_order_id,c_tax_id) IN ((183691598,1000862),(183691198,1000862));
It executes perfectly... but if I execute a lengthy statement to delete say 18755 records with these…
Crystal Paladin
- 579
- 5
- 26
0
votes
1 answer
error: used struct type value where scalar is required for(;*ptr;ptr++)
Can you please explain the reason behind this error.
Error code:
used struct type value where scalar is required for(;*ptr;ptr++)"for the below code?
Are there any reason why we aren't allowed struct variables in for…
Mahantesh
- 53
- 7