All Questions
34 questions
0
votes
2
answers
939
views
How to retrieve missing values of an id from a list?
have a data structure that is value/tag inside a table on Oracle sql
Lets say I have this structore
exTable
ID
TAG
VALUE
1
1
x
1
4
y
2
2
x
2
3
y
2
4
z
I want to retrieve the missing tags for a ...
0
votes
1
answer
462
views
select subquery condition in where statement
I am getting this error:
ORA-06550: line 43, column 16: ORA-00904: "STOCK_BALANCE": invalid identifier
...for the following query:
select PARTY_ID,
PARTY_NAME,
IT_ID,
IT_ID ||...
- 11
0
votes
1
answer
51
views
How do I create a table with count of instances of an id before an event date that is individual to that id?
I am very new to SQL and have a relatively complex query to write, any help would be appreciated.
I have data with the following columns:
ORDER_ID
CUST_ID
ORDER_DATE
RETURNED(1/0)
I need a table ...
- 1
2
votes
1
answer
3k
views
Updating a table with duplicate id's while avoiding " single-row subquery returns more than one row" [closed]
I'm currently making SQL that'll update data from 1 table, based on data from another table for my company
update table1
set column1 = (SELECT color
FROM table2
Where ...
- 47
0
votes
2
answers
424
views
Is it possible to have both a subquery and a function in a WITH clause?
In Oracle 12c, we can include subqueries in the WITH clause:
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc....
- 1,497
1
vote
1
answer
511
views
Update value with nested subqueries
I am working in Oracle and trying to update a value in tableA to = 'Y' based on info from two other tables (B and C)
I have tried something like this which does work if I have only the first select ...
- 13
2
votes
5
answers
13k
views
IN clause too slow
Ive been searching for about 1 hour and i have not found anything.
I have this problem:
When I do this query:
select order_id
from or_order@smartflex
where order_id in ( select distinct numeroot ...
- 21
0
votes
2
answers
904
views
Optimization by replacing subquery with named subquery having INLINE hint
Let's have these two tables:
create table table_x(
x_id varchar2(100) primary key
);
create table table_y(
x_id varchar2(100) references table_x(x_id),
stream varchar2(10),
val_a number,
...
- 131
0
votes
1
answer
570
views
Performing join on 3 tables & retrieving selected columns
I need to write a query which fetches data from 3 tables & retrieve selected columns out of it.
The query below works fine. I am interested to include 1 more column in my SELECT statement which is ...
- 3
1
vote
2
answers
70
views
How can I use all the selected values of a column, one by one, in another query?
Use column values returned from the statement below:
SELECT DISTINCT(column_name) FROM Table1;
into the following query:
SELECT dif_col_1,
dif_col_2
FROM Table2
WHERE key_in_table = (...
- 11
5
votes
1
answer
5k
views
Why does adding ROWNUM to a query improve performance?
I have two queries:
1) This query has a ROWNUM column (takes 20 seconds to execute):
SELECT
ROWNUM
,ROAD_ID
,VERTEX_INDEX
,SDE.ST_X(ST_POINT) AS X
,SDE.ST_Y(ST_POINT) AS Y
...
- 1,497
1
vote
1
answer
4k
views
Sub-Query group-by count(*) not returning 0
Prefix: I am working on an application that is tracking employee training time and I need to get the total number of employees that have reached 75 total hours of training. After researching for some ...
- 99
2
votes
0
answers
303
views
Oracle 12c: sql query return less result when it's used in a subquery
I recently encountered a weird situation where i get different result from the same query by only changing its scope.
What i mean by changing the scope , is putting the query inside a subquery:
I ...
- 21
2
votes
1
answer
47
views
Outputting the results of two select queries on to a single table in Oracle SQL
So after constructing a database for an "online auction website" I need to write a query that takes the average price of two different types of products and displays them in a table with the name of ...
- 23
35
votes
3
answers
6k
views
Why does this query work?
I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c.
Why does this query not return an exception?
select * from table_a where name in (select name from table_b);
From ...
- 523
0
votes
1
answer
407
views
ORA-00932 on subquery join from subquery join
The only guess I have is that perhaps the size of the subquery is considered a CLOB? I have no null values for the fields selected AFAIK.
SELECT DISTINCT
kbq_ent.qid AS kbq_ent_qid,
kbq_ent....
- 103
0
votes
2
answers
190
views
Does subqueries "short-circuit" based on other conditions?
I have a slightly costly case when exists (...) then 1 else 0 end sub-query in the select clause. The sub-query is only really needed if another column in the main query has a specific value (can ...
- 113
-1
votes
1
answer
102
views
Alter - Subpartition a partitioned table
I have a table which is partitioned by RANGE on DATE key
CREATE TABLE T1
DOC_ID VARCHAR NOT NULL.
STATE VARCHAR ...
DOC_DATE
...
PARTITION BY RANGE (DOC_DATE)(
PARTITION feb16 VALUES LESS ...
- 179
1
vote
2
answers
2k
views
How do I change all Oracle profiles PASSWORD_LIFE_TIME to UNLIMITED?
This query:
select distinct profile from dba_profiles;
Returns 3 rows:
default
default1
default2.
I want to change the PASSWORD_LIFE_TIME for all these profiles to UNLIMITED with a single query or ...
- 193
0
votes
2
answers
129
views
SELECT fields from table having some conditions
I have two tables
1. Named as t1
2. Named as t2
example of the tables are :
t1 :
id name
1 one
2 two
3 three
t2 :
id name
2 five
I need to select all the data from the ...
- 103
1
vote
1
answer
67
views
Return the lowest value
How do I go about getting the course number (CNO) of all the lowest enrolled by department?
For example if math had coursenum 123 and the 0 enrolled, and anoher coursenum 555 with 6 enrolled, it ...
- 111
2
votes
1
answer
49
views
Problem to select not in
I want to display the values that are between 0 and 10 and dont exist in the current table
For example :
Field
1
2
3
5
9
8
Result expected:
7
4
The problem is that the column field is a varchar, ...
- 697
0
votes
1
answer
2k
views
Lookup multiple ids in separate table with 2 subqueries
I have a query from a timesheets tables that I am using to select a submitter, approver, and a date.
SELECT PRTIMESHEET.POSTED_TIME,
PRTIMESHEET.PRRESOURCEID as "SUBMITTER",
PRTIMESHEET....
- 145
0
votes
1
answer
211
views
Passing filter params to a sub query
I'd like to create a view from the query below, but the query contains a sub query which I need to apply a filter to (the place is marked with a comment). I'm wondering if it's possible somehow to ...
- 103
-2
votes
1
answer
1k
views
Single-row subquery returns more than one row
I try to run the follwoing query but it gives me 'Single-row subquery returns more than one row' error.
BEGIN
FOR C2 IN
(SELECT S.bom_header,
S.sap_cage_cd,
I....
- 1
1
vote
1
answer
2k
views
Oracle: summing column value with subquery?
Are there some restrictions in Oracle that disallows to have a WHERE clause that compares value of column+(subquery count(*))? If the subquery table is empty, count(*) doesn't yield to zero in this ...
- 13
8
votes
2
answers
124
views
Changes access method for non-correlated subquery
Oracle 11g R2
Unfortunately our application has per row security "features".
We have a query that looks about like this:
Bad, slow:
SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
...
- 1,569
3
votes
1
answer
6k
views
Oracle 10g: Optimizing an update query that uses a subquery
I have the following query within a stored procedure, and I've been trying to improve its performance to no avail.
UPDATE TBL_REPORTING REP
SET (REP.ISSUER_ID, REP.ISSUER_NAME
) = ( ...
- 31
3
votes
1
answer
4k
views
Filter on a window function without writing an outer SELECT statement
Since window functions cannot be included in the WHERE clause of the inner SELECT, is there another method that could be used to write this query without the outer SELECT statement? I'm using Oracle. ...
- 131
0
votes
2
answers
26k
views
Use an Alias in Where Clause Subquery
I need to show some fields from another table in Oracle. Here is my query:
SELECT
ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE,
ANGGARAN.SIMPEG_PEGAWAI.NAMA,
ANGGARAN.SIMPEG_PEGAWAI.NIP,
ANGGARAN....
- 213
2
votes
1
answer
3k
views
How to speed up a query with a lot of subqueries
I have a query which uses a series of functions to return the status of each document for a loan.
SELECT loan_number,
borrower_name,
get_application_status(loan_number, ...
- 123
3
votes
5
answers
1k
views
Query optimization [Oracle]
The Query:
select JOIN_RESULT.batch_id as BATCH_ID,
JOIN_RESULT.unique_doc_id as DOCUMENT_ID,
JOIN_RESULT.brand as BRAND,
JOIN_RESULT.message_date_time as UPLOAD_DATE,
...
- 145
5
votes
3
answers
2k
views
What could be the reason for disallowing a sub query in the values clause?
For example
SQL> create table dates(d date);
Table created.
SQL> insert into dates select sysdate from dual;
1 row created.
SQL> select * from dates;
D
---------
28-MAY-11
SQL> ...
- 3,361
4
votes
3
answers
17k
views
How to convert a Top 1 subquery using outer table alias to Oracle?
I have the following SQL Server query
select
(select top 1 b2 from BB b where b.b1 = a.a1 order by b2) calc,
a1,
a2
from AA a
where a2 = 2;
which I can rewrite using analytic functions
...
- 12.3k