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
3 answers
Count with SQL SubQuery in From Clause Not Working
I am trying to get the number of workdays over a range of dates and assign it to a variable and I can't seem to get it. I have tried just my subquery and it works fine to get me the distinct dates but I need a count of them. Here is the code that…
Missy
- 1,286
- 23
- 52
0
votes
2 answers
scalar valued function having cte and top 1 within 'Exist' function
My query uses 'exists' function as filter and it has scalar valued function within it. The scalar valued funciton contains cte and "(select top 1 1)". When I use exists it does not filter at all. Rather when I use "where 1=(svf)" it seems work.Did…
xyz
- 762
- 7
- 24
0
votes
1 answer
Scalar subquery taking a lot of time to execute
I have the following tables:
table1 -
session_id, company_id, session_start_time
table2 -
id, session_id, message_time, message_type, message
table3 -
company_id, company_name
table1 stores the sessions done on companies. Each session has a lot…
rohits
- 3
- 3
0
votes
1 answer
TSQL: SELECT CASE WHEN THEN Subquery: Error: Subquery returns more than 1 Value
im getting the error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
in the query below
select case
when serial_number like…
Tobi89
- 199
- 2
- 4
- 11
0
votes
2 answers
SQL scalar subquery checking a row was found
Introduction
Sometimes instead of a join you can deliberately use a scalar subquery to check that not more than one row was found. For example you might have this query to look up nationality for some person rows.
select p.name,
c.iso
from…
Ed Avis
- 1,350
- 17
- 36
0
votes
2 answers
Subquery in SELECT or Subquery in JOIN?
I have a MYSQL query of this form:
SELECT
employee.name,
totalpayments.totalpaid
FROM
employee
JOIN (
SELECT
paychecks.employee_id,
SUM(paychecks.amount) totalpaid
FROM
paychecks
…
Yossi Fendel
- 1
- 1
0
votes
3 answers
select subquery inside then of case when statement? or how to do otherwise?
it's just an example. (My point is how can I do when I'm getting more then one result in the CASE statement):
SELECT
,@PARAM AS id
,Date=(
SELECT distinct CASE
WHEN P.DATE1 <= 05 and P.DATE2 <= 10
THEN 'X'
WHEN P.DATE1 > 05 and…
Data Engineer
- 23
- 6
0
votes
2 answers
Left outer join vs subquery to include departments with no employees
Lets say I have the following database model:
And the question is as follows:
List ALL department names and the total number of employees in the department. The total number of employees column should be renamed as "total_emps". Order the list from…
martinap
- 37
- 1
- 9
0
votes
1 answer
Passing Data Through A Three Tiered Scalar Subquery
I have a query, that has three tiers. That is to say, I have a main query, which has a scalar subquery, which also contains a scalar subquery.
The bottom level scalar query is returning two different values two which the mid-level subquery is…
enrique41
- 1
- 4
0
votes
1 answer
scalar subquery has an aggregate operation
My oracle version is 10.2.
It's very strange when a scalar subquery has an aggregate operation.
my table named t_test looked like this;
t_id t_name
1 1
2 1
3 2
4 2
5 3
6 3
query string looked like…
user1021531
- 487
- 1
- 7
- 16
-1
votes
1 answer
Scalar subquery WHERE clause DATA STUDIO
Data studio connector returns an error, which I do not understand.
I have done another SQL connector with the same scalar subquery but another source and it works okay.
I do not understand what is difference but if I execute that subquery from the…
Sergei Hlevnoj
- 1
- 1
-1
votes
1 answer
TABLE DOESN'T EXIST - CANNOT REFERENCE SUBQUERY TABLE FROM OTHER SUBQUERY
I am using mysql. I have a database with tables medico, paziente and visita (doctor, patient and visit respectively). I want to get the dates with the maximum number of visits in one day. So I create a subquery that returns the dates with the number…
Vito Pampinella
- 11
- 1
-1
votes
2 answers
Subquery returned more than 1 value in SQL SErver 2008
Please see my previous question that I am still stuck with. Insert statement with sub queries
I have 4 columns I need to address and I cannot drop the table to change the 4th column from an int not null to a
I was directed FROM this original…
KenSummersNJ
- 35
- 4
-2
votes
3 answers
display the avg salary and loc of each location
Write a query to display the LOC and average salary of Each location.(Scalar Sub query).
LOC is in dept table and salary is in emp table.
I have to do this with scalar subquery.
select loc,(select avg(sal) from emp)
from dept group by loc;
MAYANK RAJ
- 11
- 6