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;
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]....
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?
I need to use Recursive Subquery Factoring for a project of mine, specifically I need to use some code I have found in a function.
[URL]
The code I have so far is this:
create or replace
function recursive
(
pattern in raw
, solution in raw
) return number as
[code]....
But when trying to compile it in Oracle SQL Developer, I get the following two errors:
Error(9,1): PL/SQL: SQL Statement ignored
Error(10,30): PL/SQL: ORA-00907: missing right parenthesis
The errors refer to these two lines:
with x( s, ind ) as
( select sud, instr( sud, '' '' )
I have looked over and over the code again, and I do not see any missing parenthesis. Furthermore I tried using with in a smaller test function:
create or replace function test
(
p in number
)
return number as
v number;
begin
with t(a) as
[code]....
Which compiles just fine, so I'm not sure why that line is being ignored in this function. The whole idea here is to replace the sudoku string in the function with a variable that I built from the two parameters.
I got a two tables, table the_table_1 consist of customers and the other one the_table_2 consist of channels.
And I need to get list of channels by contract which are an assigned to customers.
The select below give me a only assigned channels but I need to get a contract_key too from the_table_1.
select o.channel_name from the_table_2 o
minus
select o.channel_name from the_table_1 a, the_table_2 o
where a.contract_key=237092201
and a.offer_channel_key=o.OFFER_CHANNEL_KEY
And the select under (after minus) gives a list of non assigned channels by contract_key:
select o.channel_name from the_table_1 a, the_table_2 o
where a.contract_key=237092201
and a.offer_channel_key=o.OFFER_CHANNEL_KEY
how to rewrite a select to get required result?
I am looking for an explanation for having the following query:
SELECT PK_SERIAL_NUMBER FROM TABLE1 MINUS (SELECT FK_SERIAL_NUMBER FROM TABLE2);
Which normally returns the values of SERIAL_NUMBER that are not passed to the child table TABLE2
and the query:
SELECT PK_SERIAL_NUMBER FROM TABLE1 WHERE PK_SERIAL_NUMBER
NOT IN
(
SELECT FK_SERIAL_NUMBER FROM TABLE2
);
which returns 0 rows!
I have a two tables with same column name , I wanted to find different record in table1 when compared with table2
create table table1(col1 number,col2 number,col3 number,col4 number,col5 number);
create table table2(col1 number,col2 number,col3 number,col4 number,col5 number);
insert into table1 values(1,2,NULL,NULL,NULL);
insert into table2 values(1,2,NULL,NULL,NULL);
commit;
select col1 from (select col1,col2,col3,col4,col5 from table1 minus select col1,col2,col3,col4,col5 from table2);
no rows selected
how come i get no rows selected when col3,col4,col5 is having null values but NULL could be anything so
NULL-NULL cannot be equal to zero how is it possible
where difference lies between these operators or clause, & whether there is any "except" operator in oracle, i know other three are used bt dont think oracle uses "except" too..as far as i have seen "NOT IN", "MINUS" , "NOT EXISTS" are exactly similar?
View 4 Replies View RelatedI wish to delete records found with function MINUS from file1, which is a result of records not found in file2.
Both codes are taken way too long.
ps. none of the columms are unique, and some records have no value in some columns
DELETE
FROM file1 y
WHERE EXIST (SELECT *
FROM (
SELECT a.col1, a.col2, a.col3, a.col4
FROM file1 a
[Code]....
I have 2 questions for this SQL Statement for tuning?
select * from a where id not in (select a_id from b)
1- How do i change this query with A MINUS OPERATOR
2- Shall i use a Hint for this query?
Explain plan is:
Execution Plan
--------------------------------------------------------------------------------
Plan hash value: 31652112322
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 299 | 21528 | 9 (12)| 00:00:01
|
|* 1 | HASH JOIN RIGHT ANTI| | 299 | 21528 | 9 (12)| 00:00:01
|
|* 2 | TABLE ACCESS FULL | b | 1 | 23 | 4 (0)| 00:00:01
|
| 3 | TABLE ACCESS FULL | a | 300 | 14700 | 4 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
How do i optimize it best?
What is the fundamental difference between MINUS keyword and LEFT outer join in Oracle.
I can achieve same results using either one of them.
I am using the below query to show the difference of data between two tables using minus operator.
teh result is correct, but is there a way can it show with a flag with new rows and updated rows something like "N" for new row and "U" for updated row.
select CURRENT_STG_GLACCTS.TABLE_NAME,
CURRENT_STG_GLACCTS.ACTIVE,
CURRENT_STG_GLACCTS.BUSINESSUNITID,
CURRENT_STG_GLACCTS.COST_CENTER,
CURRENT_STG_GLACCTS.GLACCT,
[code].......
create a procedure so that I could get minus query of 2 table as a result.
I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.
so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.
select c1,c2,c3,c4 from table1 --(fetched from src_table_list)
minus
select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)
create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..
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.
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.
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.
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.)
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,.
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].....
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).
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.
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.
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
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).
When to use inline query and to use join.
View 3 Replies View RelatedI 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?
If a query can be written using where clause, what is the purpose of using joins over where clause?
View 11 Replies View RelatedI 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.
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?
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.
