SQL & PL/SQL :: Using NVL2 To Control Joins
Jan 5, 2012
I am trying to control which tables are joined based on a null value, so I figured I could use the NVL2.
Here is the code as it stands
NVL2(TBL_TRANSACTIONS.EQUIPSVCSEQ, TBL_EQUIPANDSERVICES.LASTWORKORDERSEQ, TBL_TRANSACTIONS.WORKORDERSEQ)
= TBL_WORKORDERS.WORKORDERSEQ (+)
To explain, if first value is null, then TBL_EQUIPANDSERVICES.LastWOSeq = tbl_workorders.workorderseq (+), otherwise join the transaction.workorderseq.
When I try and execute this code, I'm given "ORA-01417: a table may be outer joined to at most one other table." I've double checked, TBL_Workorders is not joined with any other table in any select.
Now, the EquipandServices.workorderseq is joined (+) with another table.
View 5 Replies
ADVERTISEMENT
Jun 14, 2011
I have to fetch values from table conditionally.
create table tt(
userid varchar2(50),
aban8 varchar2(10)
)
insert into tt values ('1TESTX01', '111')
insert into tt values ('1TESTX02', '222')
insert into tt values ('1TESTX03', '333')
create table test1(
numval varchar2(50),
aban8 varchar2(10)
)
insert into test1 values ('12', '111')
insert into test1 values ('13', '222')
insert into test1 values ('14', '333')
The condition is to fetch userid. If a userid is provided and not null then it should return the userid provided. In this eg. '1TESTX01' If there is no userid provided then it should fetch userid from table tt with some condition...for simplicity I have not written condition but the inner query 'SELECT userid from tt' would return multiple records. And "userid IN" should be matched with the returning number of userids.
SELECT DISTINCT userid
FROM tt a, test1 b
WHERE a.aban8 = b.aban8
AND userid IN (
SELECT NVL2 ('1TESTX01', '1TESTX01',
(SELECT userid
FROM tt))
FROM DUAL);
This gives the error :-
ORA-01427: single-row subquery returns more than one row
Even running the query :-
select NVL2('1TESTX01', '1TESTX01',(select userid from tt )) from dual
gives the same error.
how to write the above query conditionally. I used case & decode too but got the same error.
View 9 Replies
View Related
Jun 17, 2008
I have a table1:
userid, name, town.
Now i want to do a self join like:
select a.name, b.name, a.town
from table1 a
inner join table1 b
on a.town = b.town
where a.first_name <> b.first_name AND
a.last_name <> b.last_name
i added the where clause to limit duplicates i would get but i still get duplicates eg. A B london, B A london etc.
View 9 Replies
View Related
Sep 30, 2011
In mys store procedure I am using a subquery with INNER JOIN. This subquery calls a user defined function which takes main query fields as parameter values. But i am facing issue for accessing main query fields. my query is like below:
SELECT
ID, Name, sub.Desc, sub.Date
FROM MainTable main
INNER JOIN
(
SELECT * FROM RMF_GetData(main.ID)
) sub
ON main.ContactID = sub.ContactID
I need data from a function in a table format based on some case conditions. Hence i need to join it with main table. But here oracle gives error as "invalid identifier" for main.ID parameter.
View 1 Replies
View Related
Mar 11, 2009
SELECT so.* FROM shipping_order so WHERE (so.submitter = 20)
OR (so.requestor_id IN (SELECT poc.objid FROM point_of_contact poc WHERE poc.ain = 20))
OR so.objid IN (SELECT ats.shipping_order_id FROM ac_to_so ats WHERE (ats.access_control_id IN (selectac. objid FROM access_control ac WHERE ac.ain = 20
OR ac.group IN ('buyers', 'managers'))))
rewrite this query to use joins. That would greatly simplify my sql query building code. The ids, objids, submitter, ain are numeric and group is a varchar.
View 1 Replies
View Related
Nov 16, 2011
I pulled in 1121 SSN's into a table and am using that table as the basis for returning data from other tables...including how many documents a user has in their folder.
My query; however, is only returning 655 rows...it is returning only those rows that have documents in their folders. I want to return ALL rows...WHETHER OR NOT THEY HAVE A DOCUMENT COUNT (count(*)). How can I get all 1,121 rows to return? I would like the output to look like:
SSN LOCATION EMP_STATUS FOL_STATUS COUNT(*)
-- For those folders containing documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER 12
-- For those folders containing 0 documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER NULL
Here is the query in it's current state:
-- Get User/Folder/Doc Count Information
SELECT b.ssn,
b.location,
b.emp_status,
c.fol_status,
COUNT (*)
[Code]....
So again, my problem here is that...not all FOLDERS contain DOCUMENTS...but I still want the folder data lised...I just need it listed with either a zero count (0), or a NULL in the COUNT(*) column.
I'm trying the various joins, but none of them seem to be working.
I've tried the old 8i (+) join as follows:
AND c.fol_id = d.doc_fol_id(+)
I've tried the inner join:
AND c.fol_id(+) = d.doc_fol_id
...and I've tried the 9i method (left outer and full outer) using the following types of notations:
folder c full outer join documend d on c.fol_id = d.doc_fol_id
...so far, no luck. I'm still having only 655 rows returned (the 655 are those folders that HAVE document count > 0. Any folder that has zero documents in the document table just aren't being returned in the query.)
View 9 Replies
View Related
May 2, 2008
why how ever way i try i cant get the joins on the tables properly.... well i know i have to work hard....if join is not proper the data i extract is also not proper.Well now i have 3 tables...
ps_operations
Name Null? Type
----------------------------------------- -------- --------
ASSB_PT_NBR_SEQ_ID NOT NULL NUMBER(8)
OPERATION_NBR NOT NULL VARCHAR2(10)
EFFECTIVE_FROM_DT NOT NULL DATE
DML_TS NOT NULL DATE
DML_USER_ID NOT NULL VARCHAR2(30)
OPERATION_DESC NOT NULL VARCHAR2(70)
HOURS_PER_PIECE_QTY NOT NULL NUMBER(9,6)
PIECES_PER_HOUR_RATE_QTY NOT NULL NUMBER(15,7)
EFFECTIVE_TO_DT DATE
EXTRACT_IND VARCHAR2(1)
[code]...
I have never worked on CPK and UK....so i dont know how to use them to join the tables,.
View 11 Replies
View Related
Feb 23, 2012
We have access to a remote Oracle database in Germany and need to insert selected data to our local Oracle database. Our problem is that we have to do several joins (7 tables) on the remote database as well as using one where clause (always the same: P.T_LIEF_LFNT_1='12803193').
Unfortunately we do not have rights to create a view on the remote database. Is there another way to send the entire query to the remote database for processing? Also, the query returns approximately 34,000 rows.
Here is our current query:
INSERT INTO PRIMUS(PARTNO,
SORT_FORMAT,
TP_WORKSPACE,
[Code].....
View 3 Replies
View Related
Oct 1, 2013
I have a query I am trying to tune. It presently takes anywhere from 15 minutes to two hours to run, depending on how many records the client has. But it needs to run several hundred times, and finish over the course of a weekend. When it runs over, we have problems.
Here's the basic structure of the query:
CODESELECT ...
FROM main_tab,
tab_a,
tab_b,
tab_c,
...
tab_z
WHERE main_tab.client_id = :1
AND main_tab.unique_id = tab_a.unique_id(+)
AND main_tab.unique_id = tab_b.unique_id(+)
AND main_tab.unique_id = tab_c.unique_id(+)
...
AND main_tab.unique_id = tab_z.unique_id(+);
All of the tables are indexed (and statistics are gathered) on the field unique_idMain_tab has an index on client_id.There is a one-to-one join (sometimes one-to-zero, thus the outer join) from the main_tab table to all the other tables.These are static tables, they're wiped and recreated - no changes, inserts, deletes.
By default, the optimizer does a full table scan and then a hash join on every single of the 26 tab_a through tab_z tables, only using the index on main_tab.
By the way I can add indexes, possibly even to the point of adding an index on some tables that would include all the fields found in the select clause on that table. But I cannot change the table structure (by, say, combining these tables together).
View 2 Replies
View Related
Mar 21, 2011
can i able to partition the table based on the column which is in another table ??
For example Table X need to be partitioned based on the column in The Table Y . and Table X and table Y has some relation.
View 2 Replies
View Related
Jul 29, 2013
i want to get SUM(salary) by combining both employee and employees table.Look my table structure below:
SQL> select * from employee;
EMPNO ENAME HIREDATE ORIG_SALARY SALARY R MGR DEPTNO
---------- --------------- --------- ----------- ---------- - ---------- ----------
1 Jason 25-JUL-96 1234 8767 E 2 10
2 John 15-JUL-97 2341 3456 W 3 20
3 Joe 25-JAN-86 4321 5654 E 3 10
4 Tom 13-SEP-06 2413 6787 W 4 20
5 Jane 17-APR-05 7654 4345 E 4 10
6 James 18-JUL-04 5679 6546 W 5 20
7 Jodd 20-JUL-03 5438 7658 E 6 10
8 Joke 01-JAN-02 8765 4543 W 20
9 Jack 29-AUG-01 7896 1232 E 10
[code]....
Above, i used separate queries to get the result of SUM(salary) by deptno.Here, I want a single query to get SUM(salary) with deptno.
deptno Sum(salary)
----------------------------
10 30056
20 27132
30 6300
40 4300
View 4 Replies
View Related
Apr 2, 2010
I'm putting together a path to select a revision of a particular novel:
SELECT e.documentname, e.Revision, e.VersionNumber
FROM Catalog, BookInCatalog
INNER JOIN NovelMaster
INNER JOIN HasNovelRevision
INNER JOIN NovelRevision e
LEFT JOIN NovelRevision s
[code]...
My goal here is to select the earliest revision from the set of Novel revision. The revision field is a string.
When I run the query for Novels that have multiple revisions I get multiple records. If there is just one record I only get one row. If there are two I get four (two for each revision). As the number of revision increases it looks like it just mushrooms from there.
One other challenge is the format of the revision- a revision sequence could look like this:
A
B
C1
C2
C
D
E1
E
So there are "intermediate" revision referred to by a number. In this case I would select revision A, but if I had:
A1
A
B1
B
I would want to select B. I am pretty sure that all the revision are stored in the db in order.Notice that the comparison operator ">" is used in e.Revision > s.Revision. I initially though it should have been "<" because we want to select the initial but the other way gives me the right order (though the wrong results).
View 12 Replies
View Related
Jul 31, 2012
When to use inline query and to use join.
View 3 Replies
View Related
Sep 29, 2010
I am working on a new project in OBIEE. I am asked to do the data modeling in the database using oracle sql developer. I have to create the joins based on the requirements. I have the tables created already. But the primary keys for few tables are not defined for few tables. PK-FK joins are also not done properly.
My questions are
(1) If I have to define the primary key for the existing tables can I do that using the alter table command or should I create the table all over again and then define it?
(2) If I have to make the changes in the existing PK-FK joins how do i go about doing that?
View 11 Replies
View Related
Feb 29, 2012
If a query can be written using where clause, what is the purpose of using joins over where clause?
View 11 Replies
View Related
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
Feb 7, 2013
I want to display first joined and last joined employee name(first_name) in each department like this
department_name | first joined employee | last joined employee
I think we have to use joins and sub query.
employee table attributes -
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID
[Code]....
View 1 Replies
View Related
Apr 11, 2011
the below merge statements has outer join.
merge into merge_st t
using (select * from merge_st1) src
on (t.id=src.id(+))
when matched then update set name=src.name;
I need to convert oracle joins to ANSI joins. I have tried below
update (select t1.name as t1_name,t2.name as t2_name
from merge_st t1 lef outer join merge_st t2
on(t1.id=t2.id))
set t1_name=t2_name;
It statements shows error like cannot modify the non key preserved tables.I have cheked these table has contains whether primary key or not.there is no constraints for these tables.Our application, constaints handle in front end. so we cannot create any constraints in oracel database.how to convert oracle joins to ansi join?
View 4 Replies
View Related
Jun 20, 2012
I have the scenario like below:
create table test_a (id number, b varchar2 (20));
create table test_b (id number, a number, b number, c number, d number, e number, f number);
insert into test_a values (1,'Manu');
insert into test_a values (2,'Tanu');
insert into test_a values (3,'Anu');
[code].....
convert the query above using joins instead of scalar queries, as scalar queries decreasing the performance.
View 7 Replies
View Related
Jun 11, 2010
We have two tables, TableA and TableB that contain list of accounts and balances.The requirement is to compare the balances of accounts in both the tables, and if there is a difference, then record that difference with account number in another table.
Both TableA and TableB contain more than 10 million rows.What is the best way to do this task in PL/SQL? A join on TableA and TableB to know the differences has become very slow due to large volume.
View 20 Replies
View Related
Aug 2, 2011
i have two tables having some fields .In table table1 there is 2 lakh rows and in table table2 there is 3 lakh rows.In both table 2 lakh rows are common.I have to find those 1 lakh rows which are distinct but the condition for this i have to use only sql joins not minus or subquery or any function.
when i doing this.select t2.id,t2.name,t2.loc from table1 t1,table2 t2 where t1.id <>t2.id;
View 8 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
Oct 25, 2011
I'm experiencing some infinite loop for my delete. I tried many way to deal with this problem but still take too much time. I will try to be clear as possible.
I have 4 implicated table in this problem.
The deletion is done depending of the pool_id given
Table 1 contain the pool_id
Table 2 the ticket_id foreign join ticket_pool_id with the pool_id
Table 3 ticket_child_id foreign join ticket_id with the ticket_id
Table 4 ticket_grand_child_id foreign ticket_child_id join with the ticket_child_id
Concerned count for each
table 1---->1
table 2---->1 200 000
table 3---->6 300 000
table 4---->6 300 000
So in fact it`s 6.3M+6.3M+1.2M+1 row to be deleted
Here`s the constraint :
-No partintionning
-Oracle version 9
-Online all the time so no downtime neither CTAS
-We cannot use cascade constraint
-The normalization is very important
Here`s what I tried:
-Bulk delete
-Delete with statement (In and Exists clause)
-temp table for each level and 1 level join
-procedure and commit each 20k
None of those worked in a decent time frame like less then one hour. The fact that we cannot base a delete on one of the column value is not working. Is there a way I'm getting desperate now
View 3 Replies
View Related
Mar 7, 2010
I have two tables A with columns a.key, a.location_code, a.status and a.first_name and table B with cols b.key, b.location_code, b.status and b.first_name.
I want to find the missing records between the two tables and as well check whether each column is populated correctly. That is if u take a record with id 1 check if loc_code is same in both the tables and if they are different, insert the key and first record column and second record column into a new table. And similarly if there is no record wiht that particular id in the second table, insert the record.
For missing records in the sense for records which are present in A but not in B, am using
Select a.key_no, a.loc_code, b.loc_code
from A,B
where a.key_no=b.key_no(+)
and b.key_no IS NULL
But the problem is I need to put some constraints on the B table like b.status='Married'and b.loc_code='CA'. When am using this condition in the above query, it's throwing me error saying cannot use outer join operator in and or or.And I could not figure out how to check for the columns being populated correctly between the two tables and at the same time check for missing ones
View 5 Replies
View Related
Sep 2, 2011
I have two large tables(rptbody and rpthead) which has over millions or even more records. Below is the table schema
describe rpthead
Name Null Type
--------------------------- -------- -------------
RPTNO NOT NULL NUMBER
RPTDATE NOT NULL DATE
RPTD_BY NOT NULL VARCHAR2(25)
PRODUCT_ID NOT NULL NUMBER
[code]...
What I want is getting all data if the referenced RPTNO belongs to a particular product_id from rptbody table, here's the sql
SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0
WHERE
(
t0.RPTNO IN
(
SELECT t1.RPTNO FROM RPTHEAD t1 where t1.PRODUCT_ID IN ('4647')
)
)
ORDER BY t0.LINENO
Since the result set is pretty large, so my application(think it as c couple of jobs, each job should be finished in a time window) can only process a subset of all data, so I need pagination so that the next job can continue the processing until all data is processed, below is the SQL with pagination
select * from (
select a.*, ROWNUM rnum from
(
SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0
WHERE
(
[code]....
As you can see each query will take 100 rows from the db. The problem for now is that the query taking too much of time(10+ mins), I know the slowness is due to "ORDER BY t0.LINENO", but it's required for pagination.
View 4 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
Dec 3, 2012
how joins work with in-line views.I have a query and its explain plan as below:
SELECT e.ename,e.deptno,d.dname FROM
dept d,
emp e
WHERE e.deptno=d.deptno
AND e.deptno=20
[code]....
I do not find any difference in both the explain plans. Both are same. In my second query, the filtered rows will be joined to dept table. And hence the baggage will reduce.But how can I verify that in-line view has worked better?
View 8 Replies
View Related
Dec 15, 2010
The following query calls the function get_meter_desc 8 times.
SELECT iu.instd_unit_id, iu.fk_cust_id, bill_cd,
iu.mfg_prod_cd, iu.mfg_prod_seq_no, ccequip.fk_mkt_cd,
eff_tmstmp, cust_ord_id, ord_dt, iu.xnac_co_cd,
iu.xnac_div_cd, smmr_wvr_cd, warr_expn_dt,
iu.auto_replen_ind, iu.ms_stat_cd,
inst_dist_id, instn_dt, iu.last_auto_dt,
iu.replen_freq_vlu, cpc_pln_cd, std_sply_ind,
emcv_total_qty, tot_actl_cpy_qty, init_emcv_tot_qty, ms_tran_cd,
[code]...
How can I replace the function call by the join in the main query?
View 19 Replies
View Related
Oct 18, 2012
I want to know how the Oracle optimizer choose joins and apply them while executing the query. So that I will insure about optimizer join before writing any query.
View 2 Replies
View Related
Sep 24, 2010
We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.
View 11 Replies
View Related
