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.
am learning the Oracle PL/SQL language using CBT Nuggets tutorials and practicing what I learn. I seem to be getting confused with the ROUND function...For example, what am I doing wrong in this statement:
select avg(salary) from employees, ROUND (avg(salary) , 2;
Basically I am trying to round the result to two decimal places but get the error ORA - 00933 - SQL command not properly ended?
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?
My requirement with one table which contains 20 columns. Now I want to convert 18 column into rows. I tried with the following query but its throwing sql error is given below.
select * from tmpl_pop_age_range_col_lvl UNPIVOT ( quantity FOR product IN (COUNT_0_TO_4 as 'a', COUNT_5_TO_9 as 'b', COUNT_10_TO_14 as 'c', COUNT_15_TO_19 as 'd', COUNT_20_TO_24 as 'e', COUNT_25_TO_29 as 'f',
[code].....
ERROR at line 4: ORA-00933: SQL command not properly ended
After creating this procedure...we r not able to execute it...
SQL> ed Wrote file afiedt.buf 1 create or replace procedure dyn_pro1 2 (tablename varchar2,colname varchar2, prodid number) is 3 cmd varchar2(100); 4 begin 5 cmd := 'delete ' || tablename || ' where'|| colname|| '=' ||prodid; 6 execute immediate cmd ;--using College_id; 7* end; SQL> /
Procedure created.
SQL> exec dyn_pro1('stu_4','COLLEGE_ID',200); BEGIN dyn_pro1('stu_4','COLLEGE_ID',200); END; * ERROR at line 1: ORA-00933: SQL command not properly ended ORA-06512: at "APPS.DYN_PRO1", line 6 ORA-06512: at line 1
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.
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 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
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(+);
I am trying to understand "ordered" hint. I want to use it in my sql where I am using some left outer joins. I believe if I use this ordered hint it will be much faster.
But the problem is I am not able to understand the concept of this hint. As i did alot of search on this eveyone says it will be , even I got some similar references where it worked But I am getting contradicting explanation for on this.
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?
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 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 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?
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 ......
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
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].....
