Stack Overflow
Stack Overflow

Questions tagged [scalar-subquery]

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.

45 questions
3
votes
3 answers

scalar subquery in if statement Condition in PL/SQL

I have an If Statement block similar to the below which is failing with the error - PLS-00103: Encountered the symbol "SELECT" when expecting one of the following.... Begin If (select count(*) from Table1) > 0 then …
Dinesh Manne
  • 1,824
  • 6
  • 25
  • 32
3
votes
1 answer

having an Hibernate entity with a count children column - without scalar subquery

I have an entity and I want it to have an attribute of the count of the children. Let's say I have these two tables: create table father ( id int, name ); create table child ( id int, father_id int ); I want the father entity in Hibernate…
SockworkOrange
  • 355
  • 4
  • 14
3
votes
1 answer

How to use calculated column value to another column in the same SQL Query

I am writing a query, which is using SubQuery to get some result. Instead of rewriting the SubQuery every time, i want to use the same output value of column 1 as input to another column for the further calculation. Point 1: Can we use any variable…
Arun Singh
  • 1,538
  • 4
  • 19
  • 43
2
votes
3 answers

Replace comparison to scalar subquery by inner join or left/right join

I need to write this query using inner joins or right/left joins but I don't know how to start: select * from radicados where asignado = (select estudianteid from estudiantes where usuario = (select usuarioid from usuarios where nombre…
Luis Contreras
  • 777
  • 9
  • 23
2
votes
4 answers

Alternative to an outer join to a subquery?

Apparently outer-joins to a subquery are not allowed by Oracle. For each row on table A, I'm trying find the row on table B with the same ID, and latest date. Something like this: SELECT a.*, b.date, b.val1, b.val2 FROM a, b WHERE b.id (+) = a.id …
James
  • 97
  • 2
  • 9
1
vote
1 answer

SQLAlchemy creating a scalar subquery column with comparison to a column from an outer subquery table

I'm trying to write a query that is creating a scalar subquery column that references a sibling column that is a column from a subquery table. I put together a simplified example of what I'm attempting, though how I'm actually using this is a bit…
kyleder
  • 667
  • 1
  • 7
  • 18
1
vote
1 answer

oracle scalar subquery?

I have such a table: name value1 value2 value3 --------------------------- name1 1 1 1 name2 1 1 2 name3 2 2 11 name4 2 12 2 name5 3 3 8 name6 3 3 2 what I need is such…
chance
  • 6,307
  • 13
  • 46
  • 70
1
vote
1 answer

Why would a sub query perform better than a litteral value in a WHERE clause with multiple joins?

Take to following query: SELECT * FROM FactALSAppSnapshot AS LB LEFT OUTER JOIN MLALSInfoStage AS LA ON LB.ProcessDate = LA.ProcessDate AND LB.ALSAppID = LA.ALSNumber LEFT OUTER JOIN…
Chad Harrison
  • 2,836
  • 4
  • 33
  • 50
1
vote
1 answer

Trigger After Update ,error: Scalar subquery is only allowed to return a single row

I need solution for my problem. Thanks in advance. I use Derby DB. I have one table with few columns. I created trigger after update for specific columns that I need. When I try to update columns in row I get this error. Error code 30000, SQL…
Igor Radovanovic
  • 357
  • 2
  • 6
1
vote
0 answers

Why Scalar Subquery Expressions in the following queries did not violate rules?

Oracle says scalar subqueries are not valid expressions in the following places: In WHEN conditions of CASE expressions In GROUP BY and HAVING clauses But why the following queries don't give any error.(Don't go to the purpose,I am just trying…
sql_dummy
  • 715
  • 8
  • 23
1
vote
1 answer

How to avoid multiple column in correlated sub query assignment in MySQL update

I'm attempting to assign the closest location to a community based on the community postcode and using the Haversine formula with SQL described here. I need to return a single scalar value but I can't seem to avoid having the second calculated…
tazmaniax
  • 406
  • 1
  • 6
  • 13
0
votes
0 answers

In scalar subquery getting error in column alias

Select Movie_Title , cast(replace(Box_Office_Revenue_,'$','') as signed) movieCol From sales.google_movie_data movie Join (Select round(avg(replace(Box_Office_Revenue_,"$","")),2) as col from sales.google_movie_data) avg_col On movie.movieCol >…
Anubhav Srivastava
  • 33
  • 6
0
votes
4 answers

SELECT name from table WHERE scalar subquery is true

How could I convert this: SELECT CONCAT (c.first_name, ' ', c.last_name) AS customer, SUM(CAST(p.amount as float)) AS total_amount FROM customer c INNER JOIN payment p ON c.customer_id=p.customer_id GROUP BY c.customer_id ORDER BY total_amount…
UnclePete
  • 25
  • 5
0
votes
1 answer

subquery results appends to all main query selected field

I'm writing a stored procedure to get list of IOT parameter data under a specific machine and the result of stored procedure should have a specific JSON format. the problem is, when I tried to add a parameter data under another machine I found out…
dode
  • 3
  • 3
0
votes
2 answers

Why does SQLite3 return nothing when used to find the AVG, COUNT and SUM of this database?

The database is from CS50's introduction to Computer Science course, Psets 7 | Movies. https://cs50.harvard.edu/x/2022/psets/7/movies/ When I run this command: SELECT AVG(rating) FROM ratings WHERE movie_id = (SELECT id FROM movies WHERE year =…
user19916937
1
2 3