SQL & PL/SQL :: Time Bucket Outer
Aug 16, 2012
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
Loan_Account_summary
SNo Due_dt AMT
-----------------------------
102-08-201250000
203-05-201245000
309-05-201230000
415-09-201225000
531-05-201215000
608-06-201235788
719-10-201255000
Time Bucket
TB_IDTB_DESCReserve%Innner Bound outer Bound
------------------------------------------------------------------
1011-72.25 1 7
1028-302.5 8 30
10331-603 31 60
10461-901.75 61 90
10591-1201.5 91 120
106121-1801.25 121 180
107180+0 181 99999
Expected Output
TB_DESC Reserve AMT
----------------------------
1-7500000
8-30154822
31-600
61-90750000
91-1200
120-1800
180+65842
View 2 Replies
ADVERTISEMENT
Jun 13, 2012
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
View 4 Replies
View Related
May 19, 2011
I have a case expression as follows:
(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.
View 1 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
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
Apr 10, 2010
How to give table1 column to subquery with table2 :
(select t1.*, sq.*
from table1 t1,
(select a,b,c from table2 where col1= t1.col1) sq
where ...
View 4 Replies
View Related
Jan 7, 2011
I have two tables A and B
Table A
bill_id number,
bill_line_id number,
discountnumber,
month varchar2(12),
year varchar2(4)
Table B
bill_id number,
bill_line_id number,
total_amt number,
customer_idnumber,
sales_repvarchar2(25),
sales_begindate,
sales_enddate
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 ......
View 2 Replies
View Related
Jul 9, 2013
BEGIN
FOR i IN 1 .. 10
LOOP
[Code]....
Assume the above is my code; there is inner and outer block.
Currently if inner block is failed, the outer block also going to exception block
But, my requirement is if inner block is failed it should not go to outer block, still the loop should continue and print 1...10 rows
View 9 Replies
View Related
Aug 17, 2012
i have the following problem
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
View 7 Replies
View Related
Mar 10, 2010
Suppose I have two tables
Transaction
TXN_ID:integer
TXN_DATE:date
Return_Transaction
RET_TXN_ID:integer
TXN_ID:integer
RET_TXN_DATE:date
[code]....
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].....
View 4 Replies
View Related
Sep 19, 2012
I need to apply an outer join on following statement.
Select * From Abc A,Def B
Where Decode(A.Fin_Wid,10,A.Gry_Wid,A.Fin_Wid)=B.Fin_Wid;
I used following but error
Select * From Abc A,Def B
Where Decode(A.Fin_Wid,10,A.Gry_Wid,A.Fin_Wid)(+)=B.Fin_Wid;
View 1 Replies
View Related
Apr 1, 2008
I need to use an outer join outside a to_char
to_char(the_date_field,'YYYYWW')(+) = '200815'
This throws up the error ORA-00936 missing expression.
I know it will work if I put the (+) right after the date before the mask but it need to check the week rather than the date.
I have tried using a function based index without success.
View 7 Replies
View Related
Oct 12, 2011
I have a query which does a double right outer join, and I need it to be rewritten using the newer syntax.
The previous query had the following clause:
WHERE
MOD_INFO.MODIFICATION_ID (+)= MOD.ID
AND MOD.PEPTIDE_ID (+)= PEPTIDE.ID
Or you can think of it generically as:
T1.T2_FK (+)= T2.ID AND
T2.T3_FK (+)= T3.ID
How would this be written using the newer syntax?
View 6 Replies
View Related
Oct 19, 2011
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)
[code]...
THE_DATE X_ID X_CODE R_QTY
--------- ---- ---------- ---------
17-OCT-11 001 ABC 100
17-OCT-11 001 ABC 100
17-OCT-11 001 ABC 100
17-OCT-11 001 XYZ 1000
18-OCT-11 001 ABC 200
18-OCT-1
[code]...
When I tried my SQL statement as below it is not showing what i expected, but where and what it is? this approach is correct or not
devtest@ Test.DB> select a.the_date,
2 a.x_ID,
3 a.x_code,
4 sum(a.o_qty) o_qty, sum(b.r_qty) r_qty
5 from tab2 a full outer join tab1 b
[code]///
I want output as below:
THE_DATE X_ID X_CODE O_QTY R_QTY
--------- ---- ---------- --------- ---------
17-OCT-11 001 ABC 1000 300 -- here O_QTY should be 1000
17-OCT-11 001 XYZ 1500 1000
18-OCT-11 001 ABC 500 600 -- here O_QTY should be 500
19-OCT-11 001 ABC 500
18-OCT-11 001 XYZ 2000 -- here The_Date, x_ID and x_CODE should appear
EDITED FOR:
devtest@ Test.DB> ed
Wrote file afiedt.buf
1 with data1 as (select the_date, x_ID, x_CODE, sum(o_qty) o_qty
2 from tab2
[code]...
now only the required is values of THE_DATE, X_ID and X_CODE of Last Record.Still asking about, is it correct approach ?
View 3 Replies
View Related
Aug 13, 2011
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
(
[Code]....
View 7 Replies
View Related
Jan 23, 2013
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.
View 19 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 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 ''
[code]...
View 4 Replies
View Related
Jun 14, 2012
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?
View 1 Replies
View Related
Aug 21, 2012
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)
[code]...
View 15 Replies
View Related
Jun 20, 2012
I have an outer join.
select *
from table a, table b
where
a.id = b.id (+) and
b.status = 'N';
I am not getting any results I want where the table b is null for table a due to the last condition. I would like the result to be like the one below.
Results:
Table a ID Table b ID Table b status
1 1 'N'
2 null null
View 4 Replies
View Related
Aug 30, 2010
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:
YA XA XB
---- ---- ----
3 three
View 5 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
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
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
Apr 10, 2013
I have a select query that was working with no problems. The results are used to insert data into a temp table.
Recently, it would not complete executing. The explain plan shows a cartesian. But, there could be problems with using nested loops on the outer join.
Interestingly, when I copy production code and rename the temp table and rename the view, it works.
CREATE TABLE "CT"
( "TN" VARCHAR2(30) NOT NULL ENABLE,
"COL_NAME" VARCHAR2(30) NOT NULL ENABLE,
"CDE" VARCHAR2(5) NOT NULL ENABLE,
"CDE_DESC" VARCHAR2(80) NOT NULL ENABLE,
[Code]....
View 1 Replies
View Related
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
May 27, 2010
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).
how to write query for this.
View 8 Replies
View Related
Aug 20, 2010
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 '<'.)
View 3 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
Nov 7, 2011
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.
View 5 Replies
View Related
