SQL & PL/SQL :: Joining A Query Instead Of Using Left Join?
Jan 20, 2011
joining this query instead of using the left join. Reason is want to show the score column in a different place and also do not want to show the second IPS column that is used in the joined query.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
select * from
(
select i.ips,
p.project_name,
p.project_segment,p.location,p.project_exec_model,
p.project_exec_model||' - '||p.project_config pmodel,
one.score schedule,two.score cost,three.score execution,four.score commercial,
nvl(one.score,0)+nvl(two.score,0)+nvl(three.score,0)+nvl(four.score,0) as total,
[code]....
View 6 Replies
ADVERTISEMENT
Nov 22, 2010
i am trying to left join a selection of two or more tables. what i have found, and solved part of my problem, is that oracle left joins only the last table in the select statement ...
i.e : select * from A, B left join C on C.id = A.id wouldn't work because left join applies to B and not A.
but as my queries grow i need to make something as follows :
select * from A, B
left join C on (C.ID_A = A.ID and C.ID_B = B.ID)
[... evantually more left joins as the preceding one may go here]
this query works for DB2 but Oracle claims that "A"."ID" is an invalid identifier, while the B.ID is recognized since it's the last table stated before the "LEFT JOIN" keyword.
View 7 Replies
View Related
Apr 9, 2013
I am creating a query where I am trying to take phone call lengths and put them into buckets of length ranges 0:00 - 0:59, 1:00 - 1:59 etc. Even if there are no calls in the call table I need to return the range with a zero (hence the left join and nvl). When I do this the left join acts like an equal join, I suspect there is some reason left joins only work if there is an equal condition in the join (instead of >= and < that I use, or similarly I could use BETWEEN). I also have a question about performance (below).
The create table script for the lookup is like this:
CREATE TABLE DURATION_RANGES
(
RANGE_TEXT varchar2(20),
RANGE_LBOUND decimal(22),
RANGE_UBOUND decimal(22)
)
Sample inserts are:
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0,59);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('01:00 - 01:59',60,119);
etc.
The query is:
select
r.range_text as duration_range,
nvl(count(*),0) as calls,
nvl(SUM(call_duration),0) as total_duration
from
[code]...
As I say, it is not returning all ranges in the duration_ranges table, so acting like an inner join. I realize one solution would be to populate duration ranges with every value possible (instead of ranges) so join is an equal join, but that would make the duration_range table larger.
My questions:
1. Is it possible to get the left join to work with the duration range values as they currently are?
2. Even if 1 is possible, would it be better performance to have exact values (but a larger lookup table) and do an equals join instead of >=, < or BETWEEN? Performance now is not bad.
What I mean is (with only one time value and not lbound and ubound:
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',1);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',2);
View 4 Replies
View Related
Nov 29, 2011
Using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
Here's a simplified version of the query I am having problems with:
SELECT
assoc.association_id
FROM mentor_initiative mi
LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1
LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND
NVL(assoc_prog.program_id, -1) = NVL(assoc.program_id, -1)
Note that there is no program with program id -1. So the assoc_prog left join will come up with nothing. I was thinking that since assoc_prog.program_id will be null, the second assoc left join would pick the row where assoc.program_id is null. However, the second left join doesn't join to any row.
In this query, it does join to an assoc row (I changed assoc_prog.program_id to NULL)
SELECT
assoc.association_id
FROM mentor_initiative mi
LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1
LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND NVL(NULL, -1) = NVL(assoc.program_id, -1)
I was thinking it would join to an assoc row in the first query though. How can I change the first query to have the desired effect of left joining to a row where assoc.program_id is null if assoc_prog.program_id is null?
View 7 Replies
View Related
Oct 13, 2010
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.
View 2 Replies
View Related
Sep 21, 2012
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].....
View 10 Replies
View Related
Jun 17, 2010
I have the following 2 SQLs ; one return 1 row, another one is no row returned.
select v.value
from v$parameter v
where v.name = 'cpu_count';
return value "1"
select o.value
from v$osstat o
where o.stat_name = 'NUM_CPU_CORES';
No row returned.
combine the above two in to 1 SQL, and return 1 , null or 1, 1. I assume we can get it with left join for the condition "o.stat_name (+) = 'NUM_CPU_CORES'" , but no row returned for the following SQL. How could we get the result for 1, null for this case?
select v.value, o.value -- or NVL(o.value, 1)
from v$parameter v, v$osstat o
where v.name = 'cpu_count'
and o.stat_name (+) = 'NUM_CPU_CORES';
no row returned
View 5 Replies
View Related
Feb 5, 2012
I am trying to develop an application of cars. The car have marques example :
TOYOTA,HUNDAI,CHEVROLET
each mark have families example: TOYOTA have Hilux, yaris corola, CHEVROLET have opra,...etc. Each family have a lot of models example: hilux have h2kn-clim,.. etc. And finally there are some options witch are generally in all cars example Radio-k7,air-conditioner ... etc.
option 1..n-----------------1..n model the relation call(opt_mod)
i did develop the block of marques (master) and the block of families (detail) in a form 1. i did develop the bock of models(master) in form 2 and the is no problem. but i want to add to form 2 the block of (opt_mod) but the user did tell me that he want to to see all options with check boxes .
As a solution of this problem i want to build a block on LEFT JOIN between table :option and table :opt_mod
View 4 Replies
View Related
Jul 27, 2012
What is the fundamental difference between MINUS keyword and LEFT outer join in Oracle.
I can achieve same results using either one of them.
View 8 Replies
View Related
Jul 20, 2011
We just upgraded to 11g and have run into incorrect results for some of our LEFT JOINs. If the table, view, subquery, or WITH clause that is being LEFT JOINed to contains any constants, the results are not correct.
For example, a test (nonsensical) view such as the following is created:
create or replace view fyvtst1 as
select spriden_pidm as fyvtst1_pidm,
'Sch' as fyvtst1_test
from spriden
where spriden_last_name like 'Sch%' ;
When I run the following query, I get correct results; that is, only those with "Sch" starting their last name are listed.
select spriden_pidm, spriden_last_name, fyvtst1_pidm, fyvtst1_test
from spriden
join fyvtst1 on fyvtst1_pidm = spriden_pidm ;
However, when I change the JOIN to a LEFT JOIN, the last column contains "Sch" for all rows, instead of NULL:
select spriden_pidm, spriden_last_name, fyvtst1_pidm, fyvtst1_test
from spriden
left join fyvtst1 on fyvtst1_pidm = spriden_pidm ;
We've discovered other quirky things related to this. A WITH clause with similar logic as the above view, when LEFT JOINed to a table will also cause the constant to appear in each row, instead of NULL (and only the value where there is a join). But when additional columns are added to the WITH, it behaves correctly.
This is easy enough to rewrite - but we have WITHs and views containing constants in numerous places, and cannot hope to track down every single one successfully before the incorrect results are used.
Finally, the NO_QUERY_TRANSFORMATION hint will force the query to work correctly. Unfortunately, it has a huge negative performance impact (one query ran for an hour, vs. 1 second in 10g).
View 10 Replies
View Related
Oct 16, 2012
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
View 8 Replies
View Related
May 27, 2013
I have already done auto increment by making sequence and trigger. but now the problem is when i am trying to retrieve data from that table it returns all data perfectly but the primary key that is my auto increment value shows blank.I am facing this problem with join query, there 4 table left joined in my query. But when I remove join from my query then it shows that value.
But i need that value in my join query.So, what is the problem and what can I do?And other thing is when I apply this query in Oracle SQL Developer, it works perfect.
My Query:
return $this->db->query("select * from TBL_EMPLOYEE_BASIC left join TBL_EMPLOYEE_DETAILS on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_DETAILS.EMPL_ID left join TBL_EMPLOYEE_EDUCATION on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EDUCATION.EMPL_ID left join TBL_EMPLOYEE_EXPERIENCE on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EXPERIENCE.EMPL_ID where
[code]...
View 2 Replies
View Related
Oct 16, 2012
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.
View 18 Replies
View Related
Aug 14, 2009
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 Related
Mar 22, 2012
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.
View 3 Replies
View Related
Jun 19, 2012
I have the following four tables with the following structures Table A
ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE
Table B
ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE
when these two tables were joined like the following:
Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)
Now i have to join the next table C with table B
Table C structure is
ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer
I have the common column such as ColD2 and colB4 to get the Col D3
how do i join the existing query + join between table b and table c?
View 4 Replies
View Related
Apr 10, 2013
I am new to writing queries for an oracle database and I was giving a bit of challenge. Here's the situation. I have 3 tables I am using. 2 of the tables are being used to transpose people's names from rows to columns by account number (there are multiple people associated with each account). The last table is a pretty straight forward query. I can run each query by itself and I get the results I want. But when I try to compile the two together, I start getting a variety of errors. Below is the two queries:
Query 1 (returns about 1,500 rows):
SELECT DISTINCT CAST (EIS_DW.ACCTCOMMONLOAN.ACCOUNT as VARCHAR(20)) as ACCOUNT_NUM,
EIS_DW.ACCTCOMMONLOAN.ACCOUNT_STATUS,
EIS_DW.ACCTCOMMONLOAN.MINOR_DESCRIPTION,
EIS_DW.ACCTCOMMONLOAN.OWNER_NAME,
[code]......
Query 2 (returns about 570 rows):
SELECT ACCTNBR,
max(DECODE (rn , 1, FULLNAME)) GUARANTOR_1,
max(DECODE (rn , 2, FULLNAME)) GUARANTOR_2,
max(DECODE (rn , 3, FULLNAME)) GUARANTOR_3,
max(DECODE (rn , 4, FULLNAME)) GUARANTOR_4,
[code]....
Ideally, I want to join these two queries on ACCOUNT and ACCTNBR. I have tried working my first query into my second query, but the best I get with that, is the 570 or so accounts, not all the accounts.
View 4 Replies
View Related
Nov 13, 2011
The existing format of the records are
MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Cno
------ ---------- ---------- ---------- ---------- ---
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN 102
41958 1102 CHEN ZHEN Sub CHEN HONGJIAN 103
41960 2007 CHEN ZHEN Base CHEN ZHEN 104
41961 2007 CHEN ZHEN Sub CHEN HONGJIAN 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107
This should be modified as below.
MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Indicator Sub Name baseCno SubCno
------ ---------- ---------- ---------- ---------- ---------- -------- ------ ------
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 102 103
41960 2007 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 104 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107
[Code]..
Throught this query, I get a extra record with null value in the Sub_name and Sub_Cno.update this query using a self join.
View 5 Replies
View Related
Nov 2, 2012
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?
View 7 Replies
View Related
Nov 10, 2010
I have the following 2 tables.
A) Docversion
CREATE TABLE DOCVERSION
("OBJECT_ID" VARCHAR2(250 BYTE),
"OBJECT_CLASS_ID" VARCHAR2(250 BYTE),
);
[Code]..
Join column : object_id and parent_id.
The object id in docversion will have multiple values for element value in listofstring table.
ordinal value represents teh sequence of element value
Eg:
data for docversion:
1 23
2 34
data for LISTOFSTRING:
1 11 0 100
1 11 1 109
1 11 2 119
2 22 0 A
2 22 1 B
ouptut:
I want the output as follows
docversion.objectid,listofstring.elementvalue
1 100,109,119
2 A,B
View 14 Replies
View Related
Jun 26, 2012
How to update single table column using join query
Example:
Update table1 t1,table2 t2
set t1.column2 = 'Y'
where t1.column1 = t2.column1
View 8 Replies
View Related
Oct 26, 2011
I have come across a join in a query which has the (+) symbol at the end of it.
View 3 Replies
View Related
Oct 11, 2013
I have been told to create a query which should give out the same output as the UI of an exception management application . However , many referenced tables have one to many relationship . I created a query using a function which was giving the required output . But the performance of the query was not good as for each row . Is there a method to create a select using joins to show one to many relationship in a table .
View 4 Replies
View Related
Feb 27, 2008
In my Oracle 9i Schema, I have two tables:
Table One
pocOne pocTwo
2 3
2 4
1 2
Table Two
TableTwoId Name
1 Jones
2 Smith
3 Edwards
4 Camden
My SQL to fetch all records with Smith works great:
select Name from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo)
where Name = 'Smith'
Now I need to create an alias for the Name field. Here is my attempt:
select myAliasName from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo),
(select Name as myAliasName from TableTwo)
where myAliasName = 'Smith'
This attempt pulls up all the records instead of just Smith records. How I can create an alias for the Name field in my above query?
View 3 Replies
View Related
Sep 10, 2012
I want to convert join to subquery. how cna i do this.
FROM
TABLE_1 A
INNER JOIN TABLE_2 B
ON ( A.COL_1 = B.COL_1)
[Code]....
View 6 Replies
View Related
May 19, 2011
TABLE - 1
CIDDOB(DATE)
12312-MAR-63
58918-JAN-78
658927-DEC-43
46515-FEB-80
TABLE - 2
DIDDOB_INFO(VARCHAR2)
34425 Mar 1967
123 12 MAR 1963;25 FEB 1974;25 AUG 1978
46515 FEB 1980
I want to join the columns DOB from table -1 and DOB_INFO from table 2 but the datatype of DOB is DATE and DOB_INFO is VARCHAR. TO_DATE function is not working here.
View 13 Replies
View Related
Dec 2, 2010
the following statement gives each customer owns how many promotions.
Q:) how do i retrieve a customer who has max. promotions?
SELECT C.CUSTOMER_ID,COUNT(P.PROMOTION_ID)
FROM PROMOTIONS P,CUSTOMERS C
WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
=====================================================
CUSTOMER_ID COUNT(P.PROMOTION_ID)
-------------------------------------
001 | 5
002 | 8
003 | 4
004 | 6
005 | 5
006 | 3
View 7 Replies
View Related
Dec 14, 2010
In SQL, almost all the thing which are possible with join is possible with sub-query also and vice-a-versa.
So when should I use sub-query and when should I go for join?
View 9 Replies
View Related
Jun 27, 2013
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].......
View 10 Replies
View Related
May 13, 2013
I saw a strange plan for one query in TESTING DB today. Although 2 tables are involved i dont see any join , NL/HJ/SMJ !! i am not facing any performance issue but curious to know what type of optimization oracle is doing here.
Query text and plan :
SELECT FIRST_NAME
FROM CAMPA.TABLE_A
WHERE NAME_ID =
(SELECT NAME_ID
FROM CAMPA.TABLE_B
WHERE ban=:b1);
[code]....
View 7 Replies
View Related
