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:
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);
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?
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:
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.
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.
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:
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';
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
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).
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
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
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.
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?
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(+);
When we are trying to create number data type column of a table with precision greater than actual value,it's accepting the definition of the table . But we are unable to insert any values into the table.how internally it stores the value
SQL> drop table precision_test; Table dropped SQL> create table precision_test(name number(2,5)); Table created SQL> insert into precision_test values (1); insert into precision_test values (1) [code]....
I have two sql queries. They run the one after another.
Query 1: select * from capital where member_status = 'MEMBER' AND rownum <= 25 order by price desc
Query 2: select * from capital where member_status = 'MEMBER' AND rownum > 26 order by price desc
Question is, in the query 2 I want records greater than row number 25. In query 2, I don't want the records that were fetched in Query 1. Is there any way to do this without using rownum?
Select to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a1, to_char(to_date('10-02-2006 01:30:00 PM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a2, Case when to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') >
[code]...
from the above query i was expecting value '2' but its returning '1'. As I am using TO_CHAR its trying to compare characters. Is there a way, to compare times alone like less than, greater than?
I have an Oracle application that deals with a 29 hour clock. so the days begin at 5am instead of midnight. I am trying to get data from a table where time is up to certain number of hours, but when it reached the 00 clock my whole query returns no records, even though there is plenty of records before the midnight hour, because the data is saved as lets say 2530 instead of 0130
I am using this
SELECT * FROM WORK.WORK_UNIT WHERE (to_char (current_garage) = :entry_blk.curr_garage OR 'all' = :entry_blk.curr_garage) AND route >= 0 AND run >= 0 AND ((package_id > 0 and work_code = 99) OR (package_id is null and work_code = 1)) and Nvl (Emp_No, 0) = 0 AND work_date = :entry_blk.p_work_date AND on_time <= TO_CHAR(SYSDATE + :Entry_Blk.up_to_hour/24, 'HH24MI')
since oracle deals with a 24hour clock, my code doesn't seem to work if there is data for after midnight (00 hour). I am using :Entry_Blk.up_to_hour/24 to determine up to how many hours I want to see data, i.e.
on_time <= TO_CHAR(SYSDATE + 2/24, 'HH24MI') will give me data up to 2 hours
I have the below query which is doing FTS and is very expensive causing load to timeout.
I did my analysis and found that table is having large number of records and hence FTS is taking long time causing timeout from app side.
I proposed to have this table partitioned but this is still pending with business and they in meantime want some solution other solution to fix this issue.
ADD_REWARD_sp. Given the identifier of a project, add a new reward for the project. The procedure should return a unique identifier for the reward. The month and year indicated should be greater than or equal to the current month, or an error message should be generated. The pledge amount should be greater than zero. The number of backers, if not NULL, should be greater than 1. If the project is not found, generate an error message.
This is the procedure head:
create or replace PROCEDURE ADD_REWARD_sp( p_proj_id IN NUMBER, p_pledgeAmt IN NUMBER, p_rewardDesc IN VARCHAR2,
[code]...
Basically, what I am struggling with is how to check if th month and year is greater than or equal to current month. I suppose it would be easy with just checking SYSDATE, but in this case I need to use both month and year.
I am trying to do a simple query where I need to return the rows from a table and treat each rown according to some rules.The query works fine, and returns all the rows, usually I have 2 rows returned. WHen I add to the query where ROWNUM = 1, I get the first row returned, but when I use when ROWNUM =2 OR ROWNUM >1, I always get null rows retured, even if I have rows in the database. Here is my query:
SELECT on_time INTO on_time2 FROM work.work_unit WHERE work_code = 1 AND emp_no = :entry_blk.p_emp_no AND work_date = :entry_blk.p_work_date WHERE ROWNUM = 2; --RETURN NULL
I changed it to the following format, but still I get the same results, only I get data when I say when rownum = 1, i get back the first record in the query
SELECT on_time INTO on_time2 FROM (SELECT on_time FROM work.work_unit WHERE work_code = 1 AND emp_no = :entry_blk.p_emp_no AND work_date = :entry_blk.p_work_date) WHERE ROWNUM = 2;
I can't move forward in my form until I figure out why this is not returning records
I've to create a table which has 650 fields and the total length of CREATE TABLE statement got to be more than 4000 characters.I've to create the table by inserting the CREATE TABLE statment in a variable (V1) then by using EXECUTE IMMEDIATE V1 Since VARCHAR2 only supports upto 4000 characters length string, how can I create such table??
DECLARE V1 VARCHAR2(4000); BEGIN V1 := -- CREATE TALBE STATEMENT WITH LENGTH MORE THAN 4000 EXECUTE IMMEDIATE V1; END;
Quote:got the error -- PL/SQL: numeric or value error: character string buffer too small
For Just learning purpose This is an example found in text book but while i try to execute it fails..I am trying to set Boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false
DELARE v_Hire_date date :='12-Dec-2005'; v_five_years BOOLEAN; BEGIN IF [code].....
value of "Shared Pool Free %" in v$sysmetric is larger than 100% SQL> select value, group_id from v$sysmetric where metric_name = 'Shared Pool Free %'; VALUE GROUP_ID 186.45107 2 186.4685382 3
SQL> show parameter shared_pool
NAME TYPE VALUE -------------- ------------ -- shared_pool_reserved_size big integer 832149913 shared_pool_size big integer 0
SQL> show sga
Total System Global Area 1.0155E+11 bytes Fixed Size 2163880 bytes Variable Size 8.1068E+10 bytes Database Buffers 2.0401E+10 bytes Redo Buffers 79310848 bytes
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'
