SELECT *
FROM EMP E,DEPT D
WHERE D.DEPTNO = CASE WHEN COMM IS NULL THEN E.DEPTNO ELSE E.DEPTNO(+) END;
It is giving error on executing saying
ORA-01417 - a table may be outer joined to at most one other table.
Why it is giving this error,
How can i write such a query, since it my requirement
I use a table taht i call C where the value of a field ("Type") is always a concat of values coming from more than 2 tables (A et C)
select A.Numero, B.date, B.commentaire,C.Libelle
from A, B, C
where A.codeLibelle = C.codeLibelle
and CONCAT(A.Numero, CONCAT(A.DemNumero, C.Libelle)) = B.Type (+)
when i execute this statement, i obtain
ORA-01417: a table may be outer joined to at most one other table
I have another request where it works fine and where i have concat of fields from only a single table:
select A.Numero, B.date, B.commentaire,C.Libelle
from A, B
where CONCAT(A.Numero, A.DemNumero) = B.Type (+)
In the first request Oracle seems to not accept a join with more than 2 tables
I have a schema whereby a table is not joined with other tables.
the info on that table can be gotten manually (by doing a query) and then using that info in another query. so is there a way of getting info from that table?
I have the following, simplified, query involving 3 tables:
Table 1
ID---Student
1----Terry
2----Gilbert
3 ---Egbert
4 ---Norris
Table 2
ID--Base_ID--Base_Name
1 --F100 ------St. Vincents
3 --F102 ------AB Junior High
4 --F103 ------CD Middle School
Table 3
ID--Choice_order_ID--Secondary_Base_Choice
1---Co1-------------School A
1---Co2-------------School B
1---Co3-------------School C
2---Co1-------------School B
2---Co2-------------School A
2---Co3-------------(null)
3---Co1-------------School C
3---Co2-------------School A
3---Co3-------------School B
I use the following query:
select table1.student, table2.base_name
from table 1, table2,
where table1.id = table2.id (+)
which returns the student and the base name they attend, i.e.
Student---Base_Name
Terry-----St. Vincents
Gilbert----(null)
However, I need to also list their school Secondary_Base_Choices horizontally, in the following format,
I.e.
Student---Base_Name----Base_Choice_1---Base_Choice_2---Base_Choice_3
Terry-----St. Vincents----School A----------School B-----------School C
Gilbert----(NULL)---------'NO DATA'--------'NO DATA'----------'NO DATA'
Egbert----AB Junior H----School B----------School A-----------'NO DATA'
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 have query in which having some issues with outer join.When I run the inner query, I am getting 121 rows and when i put outer query and run it, I am getting 69 rows coz the code_combination_id does not exist in the outer query table.But even when I say ( + ) , its not giving 121 rows..
SELECT DISTINCT t1.* --hr3.NAME prj_org --, p.segment1 prj_no, p.org_id --pt.task_number, pt.task_name, --paei.expenditure_type, pacdl. acct_ currency_code functional_curr from ( SELECT DISTINCT gll.ledger_id, gljeh.period_name "Fiscal Period", gll.NAME "Company Name", gljeh.NAME "Journal Name ", gljeh.je_source "Accounting Document Source", gljeb.NAME "Journal batch name", gljel. accounted_ dr,glcc.code_ combination_ id,glcc.segment1,glcc.segment2,glcc.segment3,glcc.segment4,glcc.segment5,glcc.segment6,glcc.segment7,glcc.segment8,gljel. accounted_ cr FROM gl_je_headers gljeh, gl_je_batches gljeb, gl_je_lines gljel,gl_code_combinations glcc, gl_ledgers gll WHERE gll.ledger_id =
[code]....
getting the following kind of output.
I would like to get Each year and the number of employees joined in the corresponding month for jan, feb, mar and april from emp table.
A sample output looks like below.
YEAR JAN FEB MAR APRIL
2000 4 2 1 2
2001 2 1 1 5
2002 2 4 2 6
2004 2 4 1 4
Query to find out employees who are all joined before Manager.
For Example the Table may look like.
EMPIDDesignationDateJoined
E101Programmer10-Jan-04
E102Programmer22-Mar-04
E103Analyst 14-Jan-05
E104Designer20-Dec-06
E105Tester 20-Nov-07
E106Manager 11-Oct-08
E107Programmer20-Nov-09
E108Coordinator12-Dec-10
E109DB Admin10-Feb-07
E110DB Analyst10-Aug-05
The out put must be..
EMPIDDesignationDateJoined
E101Programmer10-Jan-04
E102Programmer22-Mar-04
E103Analyst 14-Jan-05
E104Designer20-Dec-06
E105Tester 20-Nov-07
E109DB Admin10-Feb-07
I am having a table employees with columns
1.employee_id
2.department_id
3.hire_date
Display department ID, year, and Number of employees joined?
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 RelatedI was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.
So far the only option I have is to export and then import back the data.
know if there is any way to move a partitioned table between table spaces of different block size?
I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.
CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)
-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.
ST
** 9005.nc1 --WO_NO
1239401A - STRUCT
1 -REV_NO
9005 -MARK
9005 --POS
S275JR --GRADE
2 --QTY
[code]....
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
[code]...
Oracle 10g, Windows XP
There is an interface table and there is an normal transcational table..interface table is being compared with normal table and if match found the result is dumped into another normal table.
I am using two cursors one is to query the interface table and in a for loop pass the results to the second cursor..The interface table is having 5000 + rows and the transcation table is having more than 3.7 millions ..and the program is taking lots of time to execute..took almost 35-45 minutes..
create table x_interface /* INterface table */ -- 5000 + rows
( name varchar2(80), addr_line1 varchar2(35), addr_line2 varchar2(35), addr_line3 varchar2(35),
addr_line4 varchar2(35), addr_line5 varchar2(35), addr_line6 varchar2(35), suffix varchar2(35),
city varchar2(15), state varchar2(10), zcode varchar2(10))
[code]....
creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
View 3 Replies View RelatedI am trying to execute dynamic SQL in Stored Function and I don't know how to do this.
Explanation:
In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.
I need to query on this dynamic table and return the result as return result. But i am not able to do it.
Here T_web_loylty_report_table is a type.
CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......
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 RelatedWe have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.
what command is used to create a table by copying the structure of another table including constraints ?
View 2 Replies View RelatedI've 2 table with below colums
Create table rent (customer_id number(10),
doc_num varchar2(20)
);
Create table doc_id (doc_num varchar2(20));
Insert into rent(customer_id) values (1);
Insert into rent(customer_id) values (2);
Insert into rent(customer_id) values (3);
Insert into rent(customer_id) values (4);
[code]...
Now my requirement is i need to assign doc_num from doc_id table to 4 customers in rent table randomly. I mean update doc_num in rent table from doc_id table randomly. how to write update statement.
There is a requirement to make a table data in a database (eg: HR database) available in another database (eg: EMP database), instead of accessing it using database link. In EMP database(where data needs to be cloned), data will only be queried and no write operation will be done. Data in remote database (eg: HR DATABASE) will be occassionally fully truncated and reinserted. The plan is to do a similar truncate and reinsert of data (from HR database) into EMP database monthly once using dbms scheduler job. So basically data in just one table needs to be cloned in another database.
Question: For this situation, is a regular table or Materialized view the right choice to clone the table in EMP database and why? The table in HR database (remote database) is not very big.
We deleted millions of records from a table.
1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.
2.Will re org table and index improve the database performance ?
Oracle 11gI have a large table of 125 million records - t3_universe. This table never gets updated or altered once loaded, but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads - that will be updated with regard to when the lead is mailed and for other relevant information. select records from this 125 million record table to insert into the smaller table.
I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below. Then use a second query that inserts into T3_Leads from this View V_Market. This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause? My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.
CREATE VIEW V_Market asWITH got_pairs AS ( SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */ l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no , ROW_NUMBER () OVER ( PARTITION BY l.address_key ORDER BY l.hh_verification_date DESC ) AS r_num FROM t3_universe e JOIN t3_universe l ON l.address_key = e.address_key AND l.zip_code = e.zip_code AND l.p1_gender != e.p1_gender
[code]....
I want to do an import of a table from my old dump file.The same table is already there in the development box but few more columns are added to that table while testing so in the dump those columns are not available.
TABLE_EXISTS_ACTION=TRUNCATE
The new table
SQL> desc "TESTINVENTORY"."TTRANSACTION"
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
TRANSACTIONIDNOT NULL CHAR(26)
BRANCHCODE NOT NULL CHAR(3)
EXTERNALSYSTEM NOT NULL CHAR(3)
EXTRACTSYSTEM NOT NULL CHAR(3)
OWNERBRANCHCODE NOT NULL CHAR(3)
TRADEREFERENCE NOT NULL CHAR(20)
[code]...
It giving error while doing an import.
I have got two tables emp_dtl and iou_tab. i have already made entries i.e booking no, emp_cd, emp_name etc in emp_dtl snc its my master table. I want to retrieve the booking nos through lov in iou_tab which are generated in emp_dtl and corresponding info of emp_cd and emp_name should come in the respected fields in iou_tab.
View 1 Replies View RelatedI have a staging table and a target table. How do I pull in last loaded data from staging table to target table?
View 4 Replies View RelatedI stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).
i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).
Oracle version information
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]...
I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".
I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.
I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?
Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?
I have a table with a BLOB column ;
I want read data from table and insert to another table with a cursor
My code is :
procedure read_data is
cursor get_data is
select id,image from picture1;
id1 number;
pic blob;
begin
open get_data;
[code]....
when I run form , error FRM-40734 occurred
error in line " fetch .... "
I have the below data in table test_1.
select * from test_1
IDNameTotal
-----------
1A100
2B100
3C100
4D100
test_2 table contains the concatination of ID's with comma seperated. Actually in this table ID column is of datatype varchar2.
select * from test_2
ID
----
1,2,3
My requirement is to select the data from test_1 table where the id values in this table exists in test_2 table. I tried with the belowselect statement, but could not get any data.
SELECT * FROM test_1 WHERE to_char(id) IN (SELECT id FROM test_2)
create table test_1 (id number, name varchar2(100), total number)
create table test_2(id varchar2(100))
insert into test_1 values (1,'A',100)
insert into test_1 values (2,'B',100)
insert into test_1 values (3,'C',100)
insert into test_1 values (4,'D',100)
