I have a query which does a double right outer join, and I need it to be rewritten using the newer syntax.
The previous query had the following clause:
WHERE
MOD_INFO.MODIFICATION_ID (+)= MOD.ID
AND MOD.PEPTIDE_ID (+)= PEPTIDE.ID
Or you can think of it generically as:
T1.T2_FK (+)= T2.ID AND
T2.T3_FK (+)= T3.ID
How would this be written using the newer syntax?
i want to know the difference between Left outer join Vs. Right outer join? Its like which join is safer to use or is there any recommendations to use any join?
View 6 Replies View RelatedLets say I have three tables t1 and t2 and t3.
SELECT * FROM T1;
Id
____
1
2
3
4
SELECT * FROM T2;
Id
____
1
SELECT * FROM T3;
Id
____
1
Now when data exists in T2 and T3, I want to return only the records in T1 that match the records in T2 and T3 which is basically a normal join
select t1.id from t1, t2,t3 where t1.id = t2.id and t1.id = t3.id
However when there are no records in T2 or T3, I want to return all records in T1 i.e 1,2,3,4
One way of doing that is using the not exists clause
select * from t1 where not exists ( select null from t2 where t2.Id != t1.id) and not exists ( select null from t3 where t1.Id != t3.id)
Is there a better way of doing this in sql ?
I have an attendance table in which we have empno, time_in, time_out..etc.
I want to create a matrix report that should shows all dates of any specific required month in columns and present empno in rows and time_in in cells.
I did it with using a temporary table by filling all the required dates and outer join with attendance table, it works fine, but it takes so long, is there any other better fast way to do it?
I have two tables A and B
Table A
bill_id number,
bill_line_id number,
discountnumber,
month varchar2(12),
year varchar2(4)
Table B
bill_id number,
bill_line_id number,
total_amt number,
customer_idnumber,
sales_repvarchar2(25),
sales_begindate,
sales_enddate
For all the records in table B I am trying to get discount information from table A. Each bill_id has one or many billing_line ids.It is not mandatory that all the bill_line_id from table B should be present in table A.in other words: a.bill_id can be 100 and a.bill_line_id = 456 but in table B, for b.bill_id =100 there can be b.bill_line_id = 123 and bill_line_id = 456.
I am joining the 2 tables based on bill_id, bill_line_id, and the start and end date.I am not sure how the outer join is done...Basically for every bill_id i need all bill_line id from table A.I am not sure how to *outer join on only one column*. When i tried it did not fetch all the rows from A.
select *
from table B left outer join table A on
a.bill_line_id = b.bill_line_id
and a.month = TRIM (TO_CHAR (b.sale_begin, 'Month'))
and a.year = extract(year from b.sale_begin)
where a.bill_id = b.bill_id
This is what I need: for every bill_line_id in table B i need discount information from table A if that bill_line_id is not in table A, the discount can be null. But, the bill_line_id in A and B should be for the same bill_id a.bill_id = b.bill_id and the outer join ......
i have the following problem
select dem.NUM_PCE, memo.comment
from demand dem, dem_comment memo
where CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE) = memo.parentId (+)
and memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO)
FROM dem_comment memo
WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))
I should have a result with this request but there's nothing and the problem come from the fact that when the following condition is not met, there are no resulty even if there's an outer join
and memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO)
FROM dem_comment memo
WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))
This condition makes Oracle ignores the outer join..I have tried to add this:
select dem.NUM_PCE, memo.comment
from demand dem, dem_comment memo
where CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE) = memo.parentId (+)
and (memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO)
FROM dem_comment memo
WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))
or memo.dateCreation is null)
but there are too much records and the result is not consistent
Suppose I have two tables
Transaction
TXN_ID:integer
TXN_DATE:date
Return_Transaction
RET_TXN_ID:integer
TXN_ID:integer
RET_TXN_DATE:date
[code]....
Transaction may have returned transactions. We use outer join to join the tables using TXN_ID. We have a report that shows the following data...The report use the following basic query to check all transactions with returned transactions information...Quote:
SELECT t.txn_id,
t.txn_date,
rt.ret_txn_id
FROM TRANSACTION t,
return_transaction rt
WHERE t.txn_id = rt.txn_id (+)
Result
TXN_ID, TXN_DATE, RET_TXN_ID
100, 2010/03/10, 500
102, 2010/03/11, 501
If user want to check all transactions with no returned transactions, a where clause is appended to the query
Quote:
SELECT t.txn_id,
t.txn_date
FROM TRANSACTION t,
return_transaction rt
WHERE t.txn_id = rt.txn_id (+)
AND Nvl((SELECT 1
FROM return_transaction rt
WHERE t.txn_id = rt.txn_id),0) = 0
[code].....
I need to apply an outer join on following statement.
Select * From Abc A,Def B
Where Decode(A.Fin_Wid,10,A.Gry_Wid,A.Fin_Wid)=B.Fin_Wid;
I used following but error
Select * From Abc A,Def B
Where Decode(A.Fin_Wid,10,A.Gry_Wid,A.Fin_Wid)(+)=B.Fin_Wid;
I need to use an outer join outside a to_char
to_char(the_date_field,'YYYYWW')(+) = '200815'
This throws up the error ORA-00936 missing expression.
I know it will work if I put the (+) right after the date before the mask but it need to check the week rather than the date.
I have tried using a function based index without success.
I am trying to use Full Outer Join, here is the DB version and test case
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
create table tab1 (The_Date DATE, x_ID varchar2(4), x_CODE varchar2(10),
r_qty number(8))
create table tab2 (The_Date DATE, x_ID varchar2(4), x_CODE varchar2(10),
o_qty number(8))
tab1 can have multiple records for key fields The_Date + x_ID + x_CODE
insert into tab1
values (to_date(sysdate)-2, '001', 'ABC', 100)
insert into tab1
[code]...
tab2 should have unique record for key fields The_Date + x_ID + x_CODE
insert into tab2
values (to_date(sysdate)-2, '001', 'ABC', 1000)
insert into tab2
values (to_date(sysdate)-2, '001', 'XYZ', 1500)
[code]...
THE_DATE X_ID X_CODE R_QTY
--------- ---- ---------- ---------
17-OCT-11 001 ABC 100
17-OCT-11 001 ABC 100
17-OCT-11 001 ABC 100
17-OCT-11 001 XYZ 1000
18-OCT-11 001 ABC 200
18-OCT-1
[code]...
When I tried my SQL statement as below it is not showing what i expected, but where and what it is? this approach is correct or not
devtest@ Test.DB> select a.the_date,
2 a.x_ID,
3 a.x_code,
4 sum(a.o_qty) o_qty, sum(b.r_qty) r_qty
5 from tab2 a full outer join tab1 b
[code]///
I want output as below:
THE_DATE X_ID X_CODE O_QTY R_QTY
--------- ---- ---------- --------- ---------
17-OCT-11 001 ABC 1000 300 -- here O_QTY should be 1000
17-OCT-11 001 XYZ 1500 1000
18-OCT-11 001 ABC 500 600 -- here O_QTY should be 500
19-OCT-11 001 ABC 500
18-OCT-11 001 XYZ 2000 -- here The_Date, x_ID and x_CODE should appear
EDITED FOR:
devtest@ Test.DB> ed
Wrote file afiedt.buf
1 with data1 as (select the_date, x_ID, x_CODE, sum(o_qty) o_qty
2 from tab2
[code]...
now only the required is values of THE_DATE, X_ID and X_CODE of Last Record.Still asking about, is it correct approach ?
I am working on a query that has outer joins. The result of this query has to be stored into a table. When i execute this query alone (without INSERT command) it works absolutely fine but when i put the INSERT statement it gives me the error message "ORA-01719: outer join operator (+) not allowed in operand of OR or IN". The thing is I dont have an IN or OR in my query. I am in the middle of something
Attached is the screen shot of both the result and the error message. Query mentioned below:
<INSERT into table query comes here..>
select * from (
SELECT yy.ACCOUNTID, yy.dump_date, yy.balance, yy.Last_30days_activity,zz.GSM_Balance, nvl(yy.SUM_GSM_REV, 0) from
(
[Code]....
I'm wondering what part of this query is wrong because obviously I'm not getting the desired results.
select a.iss_id,C.ISSR_ID
from ft_t_isid a left outer join ft_t_issu b on a.INSTR_ID=b.INSTR_ID
left outer join ft_t_irid c on b.INSTR_ISSR_ID=c.INSTR_ISSR_ID
and a.ISS_ID in ('CA13606ZDD20',
'CA780086AP98',
[Code]...
I know that the problem is in the joins. I expect to get 9 rows of result but I get a lot more and they are not even what I'm looking for.
How to use outer join condition in my below query.In the table APPS_JP.GEDIS_OFFER_HEADER goh I have more recordsin the table APPS_JP.GEDIS_ORDER_BUILDS gob I have less number of records. I want all the records from
APPS_JP.GEDIS_OFFER_HEADER goh including other conditions.I have tried goh.OFFER_NO=gob.OFFER_NO(+) but same result. [code]SELECT GOH.ORIG_SYSTEM, gsp.USER_NAME, goh.ORDER_NO, goh.OMEGA_ORDER_NUMBER, goh.ORDER_TYPE, gc.CUSTOMER_ID, gc.OMEGA_CUSTOMER_NUMBER, CASE WHEN gc.PRIVATE = 'N' THEN gc.CUSTOMER_NAME ELSE ''
[code]...
i have a query that is not working properly. i am trying to join two tables and i have OR condition and outer join..i am getting error ORA-01719: outer join operator (+) not allowed in operand of OR or IN..my query is as follow
SELECT l.* FROM table1 l, table2 map
WHERE (NVL(l.id, '-99') = NVL(map.id(+), NVL(l.id, '-99'))
AND l.TYPE = 'TKE')
) OR
(NVL(l.id, '-99') = NVL(map.id(+), NVL(l.id, '-99'))
[code]....
)So basically in the query above i am saying that if type ='TKE' dont do the join on all the pos columns. if type is not TKE then do all the joins on all the pos columns etc.how can i rewrite query so that i can use OR and the outer JOIN in same query?
I have a couple of questions to ask. see below that I have provided a script to re-create my problem.
Question 1.
I have the following query that will not run when I include columns (see 1.2) in the select but will run when I use the asterix symbol (see 1.1)
*1.1 Runs with no problems*
select *
from ora full outer join txt on ora.ora_id_y = txt.txt_id_y;*1.2 Returns error.*
select txt.txt_id_y
, txt.txt_n
[code]...
Is it possible to use one query with self joins and using multiple full outer joins to accomplish what I have below which is 6 view to get to the query that I have in Question 1.
CREATE SCRIPT
drop table master;
create table master
( id varchar2 (10 char)
, txt varchar2 (1 char)
[code]...
I have an outer join.
select *
from table a, table b
where
a.id = b.id (+) and
b.status = 'N';
I am not getting any results I want where the table b is null for table a due to the last condition. I would like the result to be like the one below.
Results:
Table a ID Table b ID Table b status
1 1 'N'
2 null null
I have a basic requirement to report rows from one table where the second table do not have the same record (basically same key).I understand that we could achieve this using not in or not exists clause. But I think using outer join probably be the simplest one. However, I could not achieve what I actually wanted using outer join
Test Case:
create table tab_1 (a number(1), b varchar2(20));
insert into tab_1 values (1,'one');
insert into tab_1 values (2,'two');
insert into tab_1 values (3,'three');
create table tab_2 (a number(1), b varchar2(20));
insert into tab_2 values (1,'one');
insert into tab_2 values (2,'two');
commit;
Now, I framed the query as
select y.a ya, x.a xa, x.b xb
from tab_1 x, tab_2 y
where x.a = y.a (+);
/
YA XA XB
---- ---- ----
1 1 one
2 2 two
3 three
In this case, as there is no corresponding record in second table, the value of YA is showing as null as shown above.Hence, I changed the query as
select y.a ya, x.a xa, x.b xb
from tab_1 x, tab_2 y
where x.a = y.a (+)
and y.a(+) is null;
/
YA XA XB
---- ---- ----
1 one
2 two
3 three
I dont understand why it is behaving in that way. I am rather expecting the output to come as:
YA XA XB
---- ---- ----
3 three
I have two tables employee_master and employee_time. There could be situation that record from employee_master does not exist in employee_time.
create table employee_master(employee_id number, employee_name varchar2(100));
insert into employee_master values(1, 'employee 1');
insert into employee_master values(2, 'employee 2');
insert into employee_master values (3, 'employee 3');
create table employee_time(employee_id number, project_id number, project_type varchar2(20), time_date date, hours number)
insert into employee_time values(1, 100, 'Billable', '01-Oct-2010', 10);
insert into employee_time values(1, 200, 'Billable', '02-Oct-2010', 9);
insert into employee_time values(1, 210, 'Non Billable', '03-Oct-2010', 10);
insert into employee_time values(2, 100, 'Billable', '01-Oct-2010', 10);
insert into employee_time values(2, 200, 'Billable', '02-Oct-2010', 9);
The requirement is to show all the employees from employee_master and with total billable hours and non billable hours, if not exist, show zero.The output will be:
Employee_ID Employee_Name Total_Billable_Hours Total_Non_Billable
1 Employee1 19 10
2 Employee2 19 0
3 Employee3 0 0
The question is to write a Left outer join query or to write a PL/SQL function which can return total rows if Employee_ID is supplied to it as a parameter
Query 1:
Select Employee_ID, Employee_name, sum(Billable), sum(Non_Billable)
From
(
Select a.Employee_ID, a.employee_name,
decode(b.project_type, 'Billable', hours, 0) as Billable,
decode(b.project_type, 'Non Billable', Hours, 0) as Non_Billable
from employee_master a
left outer join employee_time b on a.Employee_ID=b.Employee_ID
)
Group by Employee_ID, Employee_Name
Query 2:
Select Employee_ID, Employee_Name, func_billable(Employee_ID) as Billable, func_non_billable(Employee_ID) as Non_Billable
From Employee_Master
Which query is good from the performance perspective? In real situation the employee_time is a very huge table.
How to use outer join condition in my below query. In the table APPS_JP.GEDIS_OFFER_HEADER goh I have more records in the table APPS_JP.GEDIS_ORDER_BUILDS gob I have less number of records.
I want all the records from APPS_JP.GEDIS_OFFER_HEADER goh including other conditions. I have tried goh.OFFER_NO=gob.OFFER_NO(+) but same result.
SELECT GOH.ORIG_SYSTEM,
gsp.USER_NAME,
goh.ORDER_NO,
goh.OMEGA_ORDER_NUMBER,
goh.ORDER_TYPE,
[code].......
I have the following query but it is taking too much time because of the LEFT OUTER JOIN on HST table which is a huge table , is there an alternative to LEFT OUTER JOIN that can be used to optimize the code:
SELECT HST.COMP_CODE,
HST.BRANCH_CODE,
HST.CURRENCY_CODE,
HST.GL_CODE,
HST.CIF_SUB_NO,
HST.SL_NO,
SUM(CV_AMOUNT) CV_AMOUNT,
[code].....
I have problems with the query below. Left join and right join is working. Well actually it's not working correct I think but that's another story wich I will leave for another posting. But with full join I am getting error ORA-00932.The columns starting with id_ are of number datatype and the ones starting with dat_ are of type date.Oracle points out the last line to be faulty. If I make that line a comment it points out the preceeding line and so on.
SQL> /
and trunc(t.dat_trans) <= to_date(:sql_dat_avst_tom,'yyyymmdd')
*
ERROR at line 24:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
select k.id_pers k_id_pers, k.dat_avst
,t.id_pers t_id_pers, t.id_trans, t.dat_trans
from (
select ka.id_pers, ka.dat_avst, ka.dat_nasta_avst
[code]...
I have 8 tables and I want full outer join on these to get the output. The tables are very small having 10 rows at max and consists of only two columns (date and value).
how to write query for this.
I've created a query so I can easily compare two sets of data for two different instruments:
select a.CalId, a.AtName, a.NRef, a.VaLoat, a.ValTime, a.ValRing,
cvs.NRef, cvs.CalId, cvs.AtName, cvs.VaLoat, cvs.Valtime, cvs.ValRing
from CalcAttribute a, CalcAttribute cvs
where a.NRef like '438815' and cvs.NRef like '438813'
and a.CalId *= cvs.CalId
and a.AtName *= cvs.AtName
union
[Code]...
This works great - however I want to add an addtional condition, basically so it only returns where the two are not equal.
I thought I should just be able to add an extra:
and a.ValLoat *<> cvs.ValLoat
and a.ValLoat <>* cvs.ValLoat
But it doesnt seem to like this (Incorrect syntax near '<'.)
What is the fundamental difference between MINUS keyword and LEFT outer join in Oracle.
I can achieve same results using either one of them.
Oracle v11.1 Standard edition. I am trying to use TRIM function around the columns which are used for outer join and it's not working. However, if I use ANSI syntax, it works. Please check the SQL below.Is there any way, I can use old syntax and TRIM and get the outer join to work?I know, I can fix/trim the data in the tables, but for some reason (need to keep the data exactly same as we received it), that is not possible here.
SQL>SQL> desc tabaName Null?
Type----------------------------------------- -------- ----------------------------COL1
VARCHAR2(20)SQL> desc tabbName Null?
Type----------------------------------------- -------- ----------------------------COL1
VARCHAR2(20)SQL> select a.col1, b.col1 from taba a, tabb b where a.col1 = b.col1 ;
no rows selectedSQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A AC CD
DSQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) (+)= trim(b.col1) ;
select a.col1, b.col1 from taba a, tabb b where trim(a.col1) (+)= trim(b.col1)
*ERROR at line 1:ORA-00920: invalid relational operator
SQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1) (+);
select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1)
*ERROR at line 1:ORA-00933: SQL command not properly ended
SQL> select a.col1, b.col1 from taba a left outer join tabb b on trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A AC CD DB
SQL> select a.col1, b.col1 from taba a right outer join tabb b on trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A AC CD D E
I am using left outer join to fetch PRSN_KEY .I need to find null values in B.PRSN_KEY. I am using below query but its giving me 0 count.
select count(*) from (
Select A.PRSN_KEY AS AKEY,B.PRSN_KEY AS BKEY from CD03955P.H_CM_EEST_EEOR A LEFT JOIN CD03955P.H_CM_EEST_EEOR B
ON
A.PRSN_KEY =B.PRSN_KEY
where
A.CAT_ID=111
AND
A.DATA_SOURCE='PEN_CO'
AND
B.CAT_ID = 1 and B.DATA_SOURCE ='PEN_EEST'
AND B.CAT_CD IN ('ACTIVE','LOA','LOAWP','LOAMLP','LOAMLN')
AND B.EFBEGDT < A.EFBEGDT
)
where BKEY IS NULL
What I am trying to do is get my report to list every room in the table even if there is nothing scheduled in the room for the selected date. I add a command to the report to force the left outer join but I keep running into errors. This is how I have it worded:
SELECT
"ROOM"."ROOM_ID",
"PATIENT_CARE_EVENT"."OR_NUM"
FROM
"ROOM"."ROOM" LEFT OUTER JOIN "PATIENT_CARE_EVENT"."PATIENT_CARE_EVENT"
ON "PATIENT_CARE_EVENT"."OR_NUM"="ROOM"."ROOM_ID" AND "PATIENT_CARE_EVENT"."PROCEDURE_DATE_DT" IN {?Start Date} TO {?End Date}
Someone else suggested that I change the IN/TO wording in the last line to BETWEEN/AND. When I do that it gives me an error stating that the table or view does not exist.
dblink:MS_LCR oracle link to sqlserver 2005
sql: select a.* from log_Board_parts@ms_lcr a , reeldata@ms_lcr b where a."sReelId"=b."sReelId"(+);
error: ora-02070 database MS_LCR does not support outer join in this context.
these are the sample data :
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);
CREATE TABLE department (
courses CourseList)
NESTED TABLE courses STORE AS courses_tab;
INSERT INTO department (courses)VALUES (CourseList('1','2','3'));
[code]....
The query returns the correct data, CourseList that are not subset of any other CourseList of the table.
I am trying to convert this not exists in a left outer join query to check if the performance is better, but I don't know how to do it.
I was making some variations of this code :
select d1.courses c_1, d2.courses c_2
from department d1,department d2
where d1.courses<>d2.courses(+);
but it is now working.
I have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.
Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......
The result of this is:
Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
I need the output to be
Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868