All Questions

Tagged with
Filter by
Sorted by
Tagged with
-2
votes
0answers
30 views

Joining Multiple Tables causes Duplication and total price is not calculated properly [closed]

These are the created tables: CREATE TABLE hosins ( insurance_company_insurance_id NUMBER(4) NOT NULL, hospital_hospital_id NUMBER(4) NOT NULL, disscount_rate NUMBER(5,...
0
votes
1answer
41 views

How to query Last inserted row for each group in oracle 10

I have two tables about users where I want to join two tables and show the latest remarks of each user bellow is table I want to query the latest remarks for each user (1 remark per user) bellow ...
0
votes
1answer
61 views

How to improve performance for table JOINS involving millions of rows

I am dealing with a performance issue for a very simple structure involving simple queries. Consider two tables called TBL01 and TBL02. TBL01 has a master-detail relationship with TBL02. The primary ...
1
vote
3answers
88 views

View: Ignore a left join if it is not used?

I have a web service (GIS feature service) that references a view called gis_sidewalks_vw. create table gis_sidewalks ( id number(10,0), last_edited_date date ); insert into gis_sidewalks ...
1
vote
1answer
39 views

How to increase the value of a column in a table by a constant by consulting another table?

I have prepared the database of the college given as create table depts( deptcode char(3) primary key, deptname char(70) not null); create table students( rollno number(2) primary key, name char(50),...
0
votes
2answers
37 views

Joining 2 tables based on Dynamic criteria

Hoping you can help me. I have 2 Tables (Table PS & Table A). What i am trying is the following ; Depending on the ACCOUNT_NUMBER & Id_Type of table A, search the Table PS on the following ...
0
votes
1answer
33 views

How to Join two tables with the join being regexp_replace [closed]

Need your help, I have two tables “temp” and “temp 1” The temp table I am deriving a column called numbers. I am wondering how do you join table temp1 onto temp and joint on SELECT regexp_replace(...
2
votes
1answer
54 views

How can i select columns from two tables using REF

I'm new to Oracle's PL/SQL i'm using 11g. I have 2 Type Objects each one of them with has a table and third table where i keep references of this 2 objects, how can i retrieve values from objects ...
1
vote
1answer
381 views

LEFT JOIN and handling NULL Values [closed]

I have two tables. Table 1: A B ------ 1 'A' 2 'B' 3 'C' Table 2: A C ---------- 2 15 3 20 I'm using the following query: SELECT table1.A, table1.B, table2.C FROM table1 ...
0
votes
0answers
150 views

Combine 2 rows of query results into one, with all columns filled (the two rows have opposite columns filled)

(Oracle SQL) I have been trying to figure out this problem on and off for a couple of days now, and it is driving me nuts. I don't have much experience with joins, or SQL in general, but I have ...
0
votes
1answer
122 views

Oracle DB union two select statements which return multiple rows

I am trying to create a union inside a stored procedure which unions two select statements. One of the select statement returns two or more rows. Thus I get the error "exact fetch returns more than ...
1
vote
0answers
34 views

Using conditions to return specific rows

From the following table i am trying to retrieve specific rows based on a criteria +----+--------------+------+ | ID | UPDATED_DATE | TYPE | +----+--------------+------+ | 1 | 01/13 | P | |...
1
vote
2answers
31 views

Comparing duplicates and choosing the duplicate with most recent date in a join

Assume i have table A: id: name: timestamp: 1 Bob 2018-05-31 04:46:37 2 Alice 2019-01-31 07:13:56 1 Bob 2019-01-15 18:23:74 And i want to join with another table B: ...
1
vote
1answer
261 views

perform multiple conditional join on multiple tables

I have a situation where I need to get result based on conditional joins For example, SELECT CGI.SALES_BRAND_BSNSS_ID, CGI.SALES_BRAND_CD, NVL(MTRX. matrix_score_cd,'RV1') ...
0
votes
1answer
38 views

Select from one table where range is held in columns belonging to another table

I am trying to write a SELECT statement in Oracle SQL where I have a TABLE A with a "START" column and an "END" column which holds division numbers. I want TABLE B to only extract those division ...
1
vote
1answer
773 views

What is a “PARTITIONED OUTER JOIN”?

This just came up in a question on Reddit, and I'm wondering What is a PARTITIONED OUTER JOIN in Oracle? (definition) What does a simple example look like? (use) How would you write it in terms of ...
0
votes
2answers
427 views

why optimizer in Oracle database does not use index?

when I execute following queries see completely different result where is problem? here I execute a simple select query which takes a few seconds to complete: SELECT * FROM PRODUCTION....
1
vote
0answers
28 views

Index to facilitate joining a table against itself to gather the last observation

I have a discrete step timeseries table where I insert about 1M rows per "month end" with the identifier being the last day of the month (2018/02/28 or 2018/08/31). I try to keep as many months as ...
0
votes
1answer
36 views

How to get a selection from a join, where there is no result in one of the tables

I am working in an oracle database and looking to get some queries to identify any changes of address from a customer. I am creating a report from the results in a daily batch process. I will simplify ...
1
vote
0answers
40 views

Finding the different rows from two resultsets based on one of the resultsets (MINUS, NOT EXISTS)

I'm testing queries on two resultsets that have the same value in certain columns on Oracle 11g. I want to exclude the rows that share these columns from my final result based on the first table. Say ...
1
vote
1answer
4k views

How to create a view from a join with multiple duplicate column names?

I have a few tables with this sort of format myTable: id | Table1_id | Table2_id | Table3_id | value Table1: id | text | ... Table2: id | text | ... Table3: id | text | ... I want to create a ...
1
vote
3answers
179 views

How to select students that failed at least at all courses that student with id = 1 failed?

I have the following tables: STUDENT (student_id, first_name, last_name, birth_date, year , domain) PROFESSOR (professor_id, first_name, last_name, birth_date, hire_date, title, salary) COURSE (...
-1
votes
2answers
666 views

Why Toad turned my LEFT OUTER JOIN into a RIGHT OUTER JOIN

I finally figured out the answer but it might be useful for someone looking up the same question so here is the whole write up to hopefully save you the frustration. Problem I had: Toad's query ...
0
votes
1answer
464 views

Full outer join with a list in oracle

I have a table test: id number name varchar2(20) ddate varchar2(20) I want to select all row from test on specepic date and full outter join with a list of values. For example let's say this is ...
1
vote
1answer
77 views

Summary statistics: Multiple joins and group by

I have a sidewalk inspection system: SIDEWALK_INSP INSP_ID ---------- 1 2 3 SIDEWALK_DEFICIENCY DEF_ID INSP_ID DEF_LENGTH ---------- ---------- --------...
0
votes
1answer
147 views

Joining dba_users & v$database [closed]

I need to generate a report that displays the account status of all schemas, which I've got. SELECT ''''|| USERNAME ||'''' USERNAME, ''''|| ACCOUNT_STATUS||'''' ACCOUNT_STATUS, ''''|| ...
1
vote
3answers
2k views

Joining subquery from same table on 1=1 in Oracle

I have a table with zip codes, latitude and longitude. I have a working query that takes a radius and latitude and longitude and returns all the zip codes within the required radius: SELECT ...
1
vote
2answers
57 views

Database finding data [closed]

I have a table PUR# EID PID CID QTY PTIME TOTAL_PRICE ---------- --- ----- ---- --------- ----------- 100001 e01 p002 c001 1 12-AUG-17 211.65 100002 e01 p003 c001 ...
2
votes
2answers
377 views

How to count the number of times an attribute appears in an inner query?

I have this query that I am trying to write out: Which students (names) have taken more than one course from the same professor? I have a students, course, and professor table. This is the ER ...
2
votes
2answers
2k views

Update column with another table's value

I have two tables, users (with columns email, name, id) and user_details (with columns username, address, details). Currently the username in user_details is populated with a mix of ID's and Email's. ...
0
votes
3answers
4k views

Update values in one table based on contents of another

Software Name: Oracle SQL Developer Version 17.2.0.188 I have three tables: orders, order_details, and products. I wanted to run a query to update units_in_stock in the products table based on ...
-1
votes
1answer
888 views

Oracle outer join and constant values

(I posted similar question on stackoverflow.com but moving it here as there was a downvote. This is follow-up of the original question) I've multiple outer joins on constants as below in the SQL: ...
0
votes
2answers
2k views

UPDATE the records in oracle SQL with joinings and where exists query

I have the tables below and tempaddresstable account premise address Here is the link of details(id of account = account_fk of premise) (address_fk of premise = id of address) So only goal is to ...
3
votes
2answers
722 views

Explain output difference between this inner and natural join

A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. A NATURAL JOIN can be an INNER join, a LEFT OUTER join, ...
13
votes
6answers
9k views

Why can't I use null values in joins?

I have solved the query problem by using ...row_number() over (partition by... this is a more general question on why we cannot use columns with null values in joins. Why can't a null be equal to a ...
2
votes
1answer
70 views

Having issues with SQL with a join issues

I have the following code: SELECT a.oop_action_id, a.requestor, a.discussion_topic, a.action_item_desc, a.owner, a.partner, a.CP_Director, a.Chief_of_Staff, a.Date_Due, c.empl_email ...
0
votes
2answers
288 views

How to preserve key when joining to a preference table in Oracle

I have a table of customers, and a table of customer preferences, where: create table customer ( customerId number, name varchar2(200), primary key (customerId) ); create table pref ( ...
1
vote
1answer
351 views

For each field name in a list of fields, get the unique values

The Question: I have a view that is a list of fields in tables: FIELD_LIST_VW +-------------+------------+ | TABLE_NAME | FIELD_NAME | +-------------+------------+ | ENG.TABLE_1 | FIELD_1 | | ...
0
votes
1answer
338 views

Waterfall join in Oracle SQL [closed]

How can I best do a waterfall join in SQL SELECT * FROM A LEFT JOIN B ON A.col1=B.col1 AND A.col2=B.col2 but for the ones where there's no match only join on col1 and ignore col2: LEFT JOIN B ...
0
votes
1answer
4k views

How to make conditional join when containing Nulls in some columns use for join

Is there a way to make the following join more efficient? It needs to join the following: Always on dset_cob_dt and tlsn_trd_id In addition if the meas_data.tlsn_leg_id is not null it needs to join ...
3
votes
1answer
3k views

Comma-separated join (cross join)

I don't understand how this query works: 1 SELECT 2 EXTRACTVALUE(definition_xml.COLUMN_VALUE, 'SubtypeFieldInfo/DomainName') AS domain_name 3 FROM 4 SDE....
1
vote
1answer
358 views

Difference in efficiency between NVL and IS NULL

I have the below query (showing the last part of the query). In the first example I get a result (which is not exactly as desired) but the results arrive within 10min. The second version results in an ...
8
votes
1answer
247 views

How to get hierarchical data from two tables in oracle?

I have two tables table1 and table2, I need hierarchical output by joining of both of the tables. Table1 contains three categories CAT1, CAT2 and CAT3 with one Foreign key i.e. F_ID which is a ...
2
votes
1answer
49 views

Proper SQL code for retrieving all rows from three tables, one of them is like a man-in-the-middle [closed]

I'm stuck at trying to write the correct SQL code for retrieving and displaying all results from querying three tables. Simply put, I have three tables: A, B, and C. A and C are not connected to each ...
0
votes
1answer
222 views

Avoiding two LEFT JOINs?

Given these 2 tables: CREATE TABLE aaa ( x number primary key, y varchar2(10)) CREATE TABLE bbb ( x number primary key, y varchar2(10)) I insert two columns: insert into aaa values (1, '...
3
votes
3answers
722 views

join and repeat value from a column of another table

I have two table A and B Table A C1 ---- 11 232 454 65 78 87 45 87 98 99 Table B C2 ---- c1 c2 c3 c4 How can I join these table to get output like C1, C2 -------- 11 ,c1 232 ,c2 454 ,c3 65 ,c4 ...
1
vote
1answer
5k views

Joining two tables with an alias

I have the following query which casues some problems: it complains that ORA-00904: "TLEA"."TLSN_TRD_ID": invalid identifier even though TLSN_TRD_ID is clearly in the TLEA table. It is as if the ...
-1
votes
1answer
85 views

How to combine multiple OR in SQL query

I have 2 tables that I JOIN. From T1 I only need customers that match 1 value. From T2 I need to list all customers that answered 'Y' to at least one of the 12 questions. I am trying to avoid ...
0
votes
1answer
40 views

Trying to compare several similiar lists on one table to produce a concatenated result

So imagine that four friends are planning on moving into a house together. They put together a list of stuff that they all own. Obviously, there is some overlap but there is also unique things among ...
0
votes
1answer
5k views

Oracle sql combine several rows of the same table into one row

I have a table where for each given ID, three rows are generated as shown. The first row is the header row: +-------+------+-------+------+-------+----+----+----+ | ID | GRP | ITDESC| DEN | NUM ...