I need joining the below 2 tables. I had used "between and " for Joining the table by using Inner Bound and Outer bound Key Columns and i use (current date - due_dt) for day calculation
My requirement is to show all the time buckets if at all no days falls in the particular time bucket.as like Outer join on Time bucket table
i am facing a very challenging situation and i am not able to write a query. consider the following data
SELECT 16 num, 17 num2 , 3 num3, 22 num4, 10 num5 FROM dual UNION ALL SELECT 9 num, 15 num2 , 21 num3, 2 num4, 24 num5 FROM dual UNION ALL
[Code]....
[URL]..... i am trying to write a query that given two values it will give you summation of the values within a particular bucket. for example, please see attachments, highlight in yellow.
if value 15 and 19 is given, i want to find the total sum of all values yellow (capture.jpg).
if value 8 and 21 is given, i want to find the total sum of all values in yellow (capture2.jpg)
if value 8 and 14 is given, i want to find the total sum all of values in yellow (capture3.jpg)
basically, when given two values, i draw a square or rectangle from starting number(first number) to end number and sum up all the values
write a query for such scenario. i heard it can be done with analytic functions but dont know how to use it. im using oracle 10g and 11g
(CASE WHEN DATEa=DATEb THEN 0 WHEN DATEa> DATEb THEN NETWORKDAYS(DATEb, DATEa) WHEN DATEa < DATEb THEN NETWORKDAYS(DATEa, DATEb) WHEN STATUS='PENDING' THEN NULL ELSE NULL END) AS RESULTa,
Now what I need to be able to do is place those results in buckets, similar to this:
(CASE WHEN RESULTa < 0 THEN '<0' WHEN RESULTa between -1 AND 6 THEN '<=5' WHEN RESULTa >5 THEN '>5' ELSE '' END) AS BUCKETa
I understand that I can't call an alias from a previous case expresson to get these desired results and how I could combine the two statements to get the desired bucket.
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?
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].....
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)
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 (
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 ''
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)
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:
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.
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.
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 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
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).
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 '<'.)
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.
