Questions tagged [inline-view]
34 questions
14
votes
3 answers
SQL - Relationship between a SubQuery and an Outer Table
Problem
I need to better understand the rules about when I can reference an outer table in a subquery and when (and why) that is an inappropriate request. I've discovered a duplication in an Oracle SQL query I'm trying to refactor but I'm running…
user2858650
4
votes
1 answer
Oracle 12c Inline View Evaluation
A long time ago in a database far, far away a developer wrote a query in which he/she was reliant on the order in which predicates were written.
For example,
select x
from a, b
where NOT REGEXP_LIKE (a.column, '[^[:digit:]]')
and a.char_column…
0909EM
- 4,761
- 3
- 29
- 40
3
votes
2 answers
Performance issue with inline view in Oracle
I have a query that looks like below and the tables A,T,S have around 1 million rows whereas P have more than 100 million rows. I've newly introduced the inline view "temp" into this query and it caused a drastic degradation in performance. The data…
Varun
- 1,014
- 8
- 23
3
votes
0 answers
How to get a subquery in FROM clause in Django ORM
I'm trying to express the following (Postgres) SQL statement using the Django ORM:
SELECT
v.id, v.min_salary, v.max_salary, v.min_weekly_hours, v.max_weekly_hours
p.min_start_date, p.max_end_date
FROM
vacancy v,
(
SELECT
…
jaap3
- 2,696
- 19
- 34
3
votes
3 answers
can't merge a union all view
I know Oracle RDMS can't merge a view that has a set operator in it. I want to know why is that.
For example, this:
SELECT u.*
FROM
(
SELECT a.a1 A,
a.a2 B
FROM tab_a a
UNION ALL
SELECT b.b1 A,
b.b2 B
FROM…
milan
- 63
- 6
3
votes
2 answers
Issue with referencing column in Oracle inline view
I have three tables as a master, child and grandchild. Simplified something like this:
CREATE TABLE TABLE1
(
ID NUMBER(10) NOT NULL,
TIME_STAMP DATE NOT NULL,
COL1 VARCHAR2(64 CHAR) NOT NULL
)
CREATE TABLE TABLE2
(
ID …
Peter Å
- 1,269
- 11
- 20
2
votes
3 answers
Oracle syntax for subselect in FROM clause?
According to Using Subqueries Oracle SQL accepts a subquery in the from-clause of a select statement, like
SELECT * FROM ( SELECT a FROM b );
However, looking at the SELECT documentation, I see no possibility to get to select/subquery in the from…
azrdev
- 225
- 1
- 13
2
votes
1 answer
Oracle 12c Subquery Factoring Inline View now has bad plan?
Update 11/2
After some additional troubleshooting, my team was able to tie this Oracle bug directly to a parameter change that was made on the 12c database the night before the query stopped working. After experiencing some performance issues from…
user2858650
2
votes
2 answers
Filter rows from oracle sql table using max value of a column
I am learning oracle SQL, and I have a table like the following:
+--------+--------+------------------------+
| name | nation | count |
+--------+--------+------------------------+
| Ruben | UK | 2 |
|…
Youssef Khloufi
- 685
- 3
- 13
- 24
2
votes
5 answers
I wish I could correlate an "inline view"
I have a Patient table:
PatientId Admitted
--------- ---------------
1 d/m/yy hh:mm:ss
2 d/m/yy hh:mm:ss
3 d/m/yy hh:mm:ss
I have a PatientMeasurement table (0 to many):
PatientId MeasurementId Recorded …
craig
- 25,664
- 27
- 119
- 205
1
vote
2 answers
Oracle SQL query is working by itself, but failing when wrapped into "select count(*) from ()"
I have the following example of the Oracle SQL query which is failing with “ORA-00979: not a GROUP BY expression”. If remove the “select count(*) from (...)” wrapper the query works as expected. The idea is to get the count of rows returned by the…
Oleksandr Onoshko
- 11
- 3
1
vote
2 answers
Using inline view in SQL
I have a database table with employees (emp) with colomns name and salary. By using an inline view query i would like to list name, salary and a new colomn with each employees % of the total salary of all employees (salary/tot_sal*100). I am having…
Amarillo
- 59
- 6
1
vote
2 answers
Linq to Entities eqiuvalent to a filtered inline view in T-SQL
I have a query from a database I need to make. I understand how to write the query in T-SQL. The real query is much more complicated, but a simple illustration of the pattern is something like this:
SELECT * FROM [dbo].[A] AS a
LEFT JOIN…
Matthew Reynolds
- 11
- 2
1
vote
1 answer
Having problems using inline view for all data
I am having some issues with using inline view to create a column where I calculate the salary/total_sal * 100. My problem is that for some reason I am not getting the value for all employees, but only for the first.
select emp.ename, emp.sal,
…
Chris
- 45
- 6
1
vote
1 answer
Oracle SQL How can I separate values from a column in two different columns?
I want to code a query to return description of some concepts and their respective price but I want to make two different columns to categorise two diferentes items categories of values. Is it possible?
SELECT b.descripcion CONCEPTO, a.cantidad,…
Young Al Capone
- 369
- 1
- 6
- 25