SQL & PL/SQL :: LEFT JOIN And NVL Isn't Working

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


ADVERTISEMENT

SQL & PL/SQL :: Use Greater And Less Than In Left Join?

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

SQL & PL/SQL :: Left Outer Join Or Function

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

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 View Related

PL/SQL :: Getting Alternative To LEFT OUTER JOIN?

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

SQL & PL/SQL :: Left Join With Fixed String Failed?

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

Forms :: How To Base A Block On Left Join

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

SQL & PL/SQL :: Difference Between MINUS And LEFT Outer Join?

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

SQL & PL/SQL :: Incorrect LEFT JOIN Results In 11gR2?

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

SQL & PL/SQL :: How To Get Count Of Null Values In Left Outer Join

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

Retrieve Auto Increment Field Value With Left Join?

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

PL/SQL :: Left Outer Join Drama - SQL Command Not Properly Ended?

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

PL/SQL :: To_char Not Working / (||) Is Working With Join Query

Mar 22, 2013

I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.

I am using following two queries:

select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))

by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.

whats the diff between to_char and || symbol?

View 1 Replies View Related

SQL & PL/SQL :: Left Outer Join Versus Right Outer Join

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

SQL & PL/SQL :: Transform NOT EXIST Subquery In LEFT OUTER JOIN With Nested Tables?

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

Self Join No Longer Working?

May 20, 2010

I have a piece of code that joined the same table onto itself twice in order to get the previous, current and future year's into columns in the same output.

Up until recently this has been working fine but the most recent data has just been uploaded into the table and now it comes up with an error.

On the second (left outer) join it now says that the column is ambiguously defined (ORA-00918). It doesn't matter which order the joins are in it is always the second join that the error pops up on.

View 4 Replies View Related

Basic Join Not Working?

Apr 20, 2008

am not very experienced with SQL. I have two tables:

Class
-----
ID (int)
Teacher (nvarchar)
Subject (nvarchar)

Student
--------
ID (int)
ClassID (int)
Name (nvarchar)

I am trying to write a SQL query that will give me the total number of students in each class. I have tried the following:

SELECT
c.ID,
c.Teacher,
c.Subject
COUNT(s.ID)
FROM
Class c LEFT OUTER JOINT ON Student s c.ID=s.ClassID

Unfortunately, that gives me an error of:Column 'Class.Teacher' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'm afraid if I place everything in a GROUP BY clause, I will get some invalid results.

View 4 Replies View Related

SQL & PL/SQL :: Full Outer Join Not Working?

May 24, 2011

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]...

View 29 Replies View Related

SQL & PL/SQL :: Join Two Tables - Condition Not Working?

Aug 16, 2010

I have two tables. i need to join the tables. The query is -

select v.c_venditore,v.s_venditore,v.t_diretto_indiretto,v.d_disattivazione,d.s_direzione from VENDITORE v,DIREZIONE d
where v.p_direzione=d.p_direzione order by v.s_venditore

In the table VENDITORE there are 2919 rows. I need to display all the rows. But the joining column p_direzione has some null values.I need to display the null also. But to join the two tables this is the only condition. How can i display all the rows.

View 3 Replies View Related

ORA-00932 - Sample Code Working Fine In 10g And Not Working Now In 11g

Apr 1, 2013

Below is the sample code working fine in 10g and not working now in 11g.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.List;
[code]....

we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class

Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

View 3 Replies View Related

SQL & PL/SQL :: Normal Join And Outer Join

Oct 19, 2013

Lets 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 ?

View 5 Replies View Related

Get Left Chars Of A Field?

Oct 15, 2008

I have many different file names within my table and I want to remove the .TXT extension from each one. I want to try this SQL but being a newbie in Oracle, I don't know how to say "Left" characters. "Left" is an invalid identifier.

Update TableName
Set File_Name = Left(File_Name, Len(File_Name)-4)
Where File_Name LIKE '%.TXT'

View 2 Replies View Related

SQL & PL/SQL :: NULL In Left Joins

Jun 11, 2013

I am observing some skewed results for Left outer join where the main table has NULL in the field we are joining against with another table.

Just wondering if there are some tricks to get over it. I am currently using NVL(tab1.col1,'X') = NVL(tab2.col3,'X') and am just wondering if there is a better way to handle this.

View 1 Replies View Related

Multiple Columns Right To Left Orientation?

Sep 15, 2009

i have downloaded ireports latest version (3.6.0) for working with jasper reports (3.5.2) and creating reports;How can I change the orientation of a 4 columns report in order to generate the columns starting from the right side.I need to generate a multiple columns report in arabic and it should be done from right to left.

View 2 Replies View Related

SQL & PL/SQL :: Left Function Equivalent In Oracle 9i

Dec 22, 2006

Is there is an equivalent to the 'LEFT' Function of SQLServer in Oracle9i?

View 10 Replies View Related

Forms :: Char Display Right To Left

Apr 1, 2011

I am using forms 6i and oracle 10g. i want to display a char from database. that means a database string 'Bangladesh' is display from by one char to one char.

and display view right to left first display "B" then "BA" then 'BAN' etc.

View 2 Replies View Related

SQL & PL/SQL :: Left Align Number Fields

Mar 11, 2010

Is there any way to align the output data of sql plus.

for eg., if i give "select custname, custid, custage from cust;" the ouput am getting is,

custname custid custage
aaa 1 23
bbbbb 2 22
cccc 3 45
dddddddd 4 21

but i need to left align the custid and custage.

my output should look like,

custname custid custage
aaa 1 23
bbbbb 2 22
cccc 3 45
dddddddd 4 21

View 7 Replies View Related

Forms :: Choose Fields From Left

Feb 16, 2013

Is there any possibility of creating a selection interface similar to our report or forms wizard where user can select single fields or multiple fields together and when he clicks on the arrow those fields data to be inserted into another table.Please refer attachment.

--this is the main table
create table batch_item (batch_no varchar2(12),item_batch varchar2(12),total_batch_qty number);

insert into batch_item ('0001','a',300);
insert into batch_item ('0002','b',200);
insert into batch_item ('0003','a',102);

--after inserting the data i should be able to move this data to corresponding batches by manually selection and pushing them to a batch.create table ct_item (item varchar2(12),item_nm varchar2(20),item_qty number);

insert into om_item values ('a','alpha',2);
insert into om_item values ('b','beta',3);
insert into om_item values ('c','gama',4);

--left side is om_item and right will have batch items where batch qty will be accumulated upon choose arrows.

View 3 Replies View Related

SQL & PL/SQL :: How To Calculate Sum Of Left Diagonal Of Table T1

Jul 13, 2011

t1

col1 col2 col3
1 10 100
2 20 200
3 30 300

How to calculate sum of left diagonal of table t1

1,20,300=321

Give me the query

View 17 Replies View Related

Left Joining Select Of Multiple Tables

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







Copyrights 2005-15 www.BigResource.com, All rights reserved