Find Missing Data From Parent Table?

Aug 8, 2013

SELECT c.table_name CHILD_TABLE, p.table_name PARENT_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND c.table_name = UPPER('ODS_TSAF_MES_PC');

and output is

child table parent table
ODS_TSAF_MES_PC ODS_TSAF_MES_PCTYP
ODS_TSAF_MES_PC ODS_TSAF_MES_PC
ODS_TSAF_MES_PC ODS_TSAF_MES_PCSTAT

i tried

SELECT A.piecestatus from ods_TSAF_MES_PCSTAT A WHERE NOT EXISTS
(SELECT * FROM ODS_TSAF_MES_PC B WHERE B.piecestatus = A.piecestatus);

and i found one piecestatus values is 'I' but i am not getting where it is related to the table and in which row it is getting affected?

View 6 Replies


ADVERTISEMENT

PL/SQL :: Oracle Sql To Find Missing Data?

Aug 10, 2012

Database version

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

I have this query

select dept_id,qc_subtype_id,equip_code,drive_id
from
(select distinct dept_id,
decode(qc_subtype_id,

[code]...

View 3 Replies View Related

PL/SQL :: How To Update Parent And Child Tables While Updating Parent Table

Jun 13, 2012

I have a parent table EMPLOYEE which includes columns (sysid, serviceno,employeename...) sysid is Primary key, serviceno is Unique key and I have child table DEPENDENT includes columns (sysid,employee_sysid,name,dob...) here again SYSID is primary key for DEPENDENTS table, employee_sysid is Foreign key of EMPLOYEE table.

Now I want to change SYSID (using sequence) in EMPLOYEE table which need to be update in DEPENDENTS table as well

Note: I have 10000 records in EMPLOYEE table as well as I have 5 more child tables which need to update new SYSID.

View 5 Replies View Related

XML DB :: How To Select Parent / Child Related Data From XMLTYPE Table

Jan 9, 2013

select * from V$VERSION

BANNER
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 Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

I have loaded into an Oracle table defined as XMLTYPE. I'm okay with PL-SQL and stored procedures, where I will ultimately do this processing once I get a handle on XML DB querying from an XMLTYPE table.

Based on reading the oracle documentation about XML DB, and more importantly, reading dozens of posts to this forum,I have been successful in loading the XML files into a SQL XMLTYPE table and doing simple queries against that table to retrieve some of its data so that I can then insert that data into other target oracle relational tables.

how to select parent/child data from XMLTYPE tables. I am able to follow the forum examples and can replicate the methods shown on the many example XML contents shown on this forum, but not against the XML that I have to process. I am wondering if my struggle is caused by my lack of knowledge, or by ill-formed XML content supplied to me by the educational vendor.The XML content has structured the XML content nodes in such a way that I do not seem to be able to apply the parent/child sql methods.I have been able to use for other XML examples I have tested against.

My XML file shown below represents High School Transcript data, for which I need to be able to parse out into my own oracle relational tables for that student, his personal info, and his course info, etc. i.e., for our example, which courses he has taken for which High School grade levels. The vendor-supplied XML seems to put the Courses and the High School grade level in "parallel nodes," instead of parent/child nodes, so I am struggling to be able to use SQL to differentiate which course the student took in NinthGrade versus TenthGrade.

-- WHat I would like to determine from a select statement:

LASTNAME GradeLevel COURSETITLE
=============================
Smith NinthGrade PHYS ED 101
Smith TenthGrade CALCULUS 201
Smith TenthGrade ZOOLOGY 202

(The data has been simpliied and masked, but is true to the content and is queryable).

select * from V$VERSION

BANNER
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 Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Table Is Missing In User_segments Data Dictionary Table

May 20, 2012

In my database I found some anomalies like

SQL> select SEGMENT_NAME, SEGMENT_TYPE, BLOCKS, EXTENTS, BYTES/1024
2 from user_segments
3 where SEGMENT_TYPE='TABLE'
4 ORDER BY SEGMENT_NAMe;
[code]....

The countries table is missing in user_segments data dictionary view.But I can queries the countries using select statement.

SQL> SELECT * FROM COUNTRIES;

CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ----------
AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
[code]...

why the country table is missing in user_segments data dictionary table.

View 8 Replies View Related

SQL & PL/SQL :: Joins - Find Missing Records Between Two Tables?

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

SQL & PL/SQL :: Query To Find Missing Dates Between Two Columns In Given Month?

Sep 26, 2012

I need query to find the missing dates between two columns in given month.

CREATE TABLE emp_shift (
empno NUMBER(4),
fr_date DATE,
TO_DATE DATE,
shift VARCHAR2(1));
CREATE TABLE emp (
empno NUMBER(4)
);

[code].....

Required output is

MISSING_DATES EMPNO
---------------------- ----------
09-SEP-12 TO 11-SEP-12 7499
23-SEP-12 TO 26-SEP-12 7499
01-sep-12 TO 30-SEP-12 7521
01-sep-12 TO 30-SEP-12 7788

View 8 Replies View Related

SQL & PL/SQL :: Find Missing Date For A Year Excluding Saturday And Sunday

Feb 3, 2011

I given the table name,column name,datatype and sample record in the table. I have given the sample record for 01-jan-2008 to 8-Jan-2008, but in the real thing it will be for 30 years.

My Requirement:

For each class_no (202,203..), I need the missing date excluding weekends (sat, sun), I have provided the sample output below.

Table Name : ABC

Column Name : Class_no Data Type : Number
Column Name : Class_DateData Type : Date

Sample Record in the Table :

Class_noClass_Date
202 1-Jan-08
202 2-Jan-08
202 7-Jan-08
202 8-Jan-08
203 1-Jan-08
203 2-Jan-08
203 3-Jan-08
203 7-Jan-08
203 8-Jan-08

OUTPUT:

Class_noClass_Date
202 3-Jan-08
202 4-Jan-08
203 4-Jan-08

View 5 Replies View Related

SQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child

Aug 13, 2012

We 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.

View 3 Replies View Related

SQL & PL/SQL :: How To Find Parent / Host Procedure Name Within Procedure

Sep 6, 2013

I have the following requirement.Say I have the following procedure created.create or replace procedure myproc (p1 varchar2, p2 varchar2) is begin

find_the_proc_name();
end;

In the above myproc, using find_the_proc_name() i should print the parent procedure name (or function) calling along with the parameters passed.

View 5 Replies View Related

SQL & PL/SQL :: Parent Table Relationship

Feb 6, 2012

Would there be any problem / error when we import the dump file (which has parent table) on target database ?

View 11 Replies View Related

Data Dictionary - Find All References Of A Table?

Sep 14, 2011

I wanna to DROP a table called EMPLOYEES, but when I execute the DROP TABLE EMPLOYEES, I get a ERROR saying that I cant do it because this table do reference to another table(s).

I tried to use the DBA_CONS_COLUMNS and DBA_CONSTRAINTS data tables, but its not enough to find it.

How can I find these references?

View 4 Replies View Related

SQL & PL/SQL :: Join 2 Tables To Get Parent Child Data

Mar 24, 2012

I have 2 tables, ITEM_T is the base table and RELATIVE_T is the relation table which establish the relationship between parent and child.

I want to give ZZZ child as input and I want get output in below format. I tried various method, but I am not able to get the below result.

Result:

ChildParent_1Parent_2
ZZZYYY XXX
ITEM_T
PK_IDITEM_ID
PK_1XXX

[Code]...

Expected Result:

When give input as ZZZ:

ChildParent_1Parent_2
ZZZ YYY XXX

View 7 Replies View Related

SQL & PL/SQL :: Find Maximum Iteration In Table And Change Data

Jul 27, 2011

i have inner query result like

Emp status
----- ----------
ram a
ram a
ram b
ram a

i want write a query to find a maximum occurrence of status and update in status column.

result should be

Emp status
----- ----------
ram a
ram a
ram a
ram a

i tried lot of thing but not to go.

View 2 Replies View Related

SQL & PL/SQL :: Logic To Find Table Names And Data In Oracle?

Feb 1, 2013

I have a schema DEF and I have a column_name CREATE_DATE.

I wanted to write a procedure which will give me list of tables whose CREATE_DATE data is prior to year 2009.

View 5 Replies View Related

SQL & PL/SQL :: Way Of Getting Only Parent And Child Record From Table

Jul 27, 2010

i have a table with data as follows:

select genres.* from genres

data is as follows

INTPRODUCTIDVCHGENRENAME INTGENREPAGEIDINTPARENTIDINTGENREID
14430015Biography 157 0100
14430015Classics & Poetry 173 0116
14430015Literature & Anthologies 175 173118

now when i give

select level,genres.* from genres connect by prior INTGENREPAGEID= INTPARENTID

i get

LEVELINTPRODUCTIDVCHGENRENAME INTGENREPAGEIDINTPARENTIDINTGENREID
114430015Biography 157 0100
114430015Classics & Poetry 173 0116
214430015Literature & Anthologies 175 173118
114430015Literature & Anthologies 175 173118

i need to find the parent and child from the table in this case the parent is Classics & Poetry and child is Literature & Anthologies..the way of getting only the parent and child record from this table.

View 5 Replies View Related

SQL & PL/SQL :: Parent And Child Table - Delete Row

May 18, 2010

I have a parent table and child table. I want a row to be deleted from the parent table which is referenced by a child row. Is there a way to achieve this. I dont have permission to re create the table or alter the table using delete cascade option. Is there a way to do it in sql.

SQL> create table t1(a number primary key, b number);
SQL> create table t2(c number, d number references t1(a));
SQL> insert into t1 values(1,2);
SQL> insert into t1 values(2,3);
SQL> insert into t1 values(3,4);
SQL> insert into t2 values(10,3);
SQL> insert into t2 values(20,2);
SQL> delete from t1 where a=2;
delete from t1 where a=2
*

ERROR at line 1:
ORA-02292: integrity constraint (CISBATCH.SYS_C00763501) violated - child
record found

View 11 Replies View Related

SQL & PL/SQL :: How To Delete Parent Table Record

Jul 10, 2012

how to delete Parent table records without affecting to child table dependent records?..

View 5 Replies View Related

SQL & PL/SQL :: Update Parent Child Table?

May 26, 2011

i want to update primary key of 1 master and 2 child table is it possible if i do it simultaniously if not

View 9 Replies View Related

SQL & PL/SQL :: Delete Records From Child And Parent Table

Jan 2, 2013

I want to delete records from parent table which are less than 2 years. Before deleting records from parent table we have to delete records from child table. How can we delete those records. I don't want to use ON DELETE CASCADE.

MASS_MASTER --parent table.
MASS_CHILD --child table.

The below query is used to delete records from parent table.

DELETE FROM mass_master WHERE last_date<=ADD_MONTHS(sysdate,-24);

The child table MASS_CHILD is not having last_date column. provide me the query to delete same records from child table.

View 21 Replies View Related

SQL & PL/SQL :: Use Index To Minimize Lock On Parent Table?

Mar 27, 2013

I want to know that what oracle server do to minimize the lock on parent table, when we use Index on foreign key column ?

View 2 Replies View Related

SQL & PL/SQL :: Finding Child And Parent Rows In Table?

Jun 6, 2011

I have table emp that contains empno, empname, mgr .what i want is a general procedure that will take empno as input and will give all the child rows and parent for entered empno.

for ex

E
A-->B-->C-->D
F-->G
H

When i will pass d as node it will return c,b,a,e,f,g,h

View 3 Replies View Related

SQL & PL/SQL :: Multiple Of Same Constraint Keys In Parent Table?

Jun 9, 2011

I used

SELECT USER_CONS_COLUMNS.CONSTRAINT_NAME,
COLUMN_NAME,
CONSTRAINT_TYPE,

[Code]...

to create a parent key table sort of thing, but for some reason there are rows that display multiple times

CONSTRAINT_NAME COLUMN_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME DELETE_RU
-------------------- -------------------- --------------- ------------------------------ ---------
SYS_C00135225 PATIENT_STATUS C
SYS_C00135226 BALANCE C
SYS_C00135227 PATIENT_STATUS C
PK_PATIENT PATIENT_NUMBER C

[Code] ..........

is it something to do with the join command

View 4 Replies View Related

Server Utilities :: How To Get Parent Table Column Reference

Sep 24, 2013

I have csv file which having parent and child related data.i need to load parent data in to parent table and get parent reference id and store in child table with child data. i am not able to find how to get parenet refernce id using control file. In my csv file i have 2 parent name rows. I need to create one A record in parent table and get that parent primary(P_ID) for 'A' record and put into child table for c_name test1 and test2 records.

mydata.csv
---------------
P_Name C_Name
------ ------
A, test1;
A, test2;
B, test3;
B, test4;
C, test5;
C, test6;
D, test7;
D, test8;

1. table parent (P_ID, P_Name)

2. table child (C_ID, C_Name, P_ID)

i need data bello way.

Parent Table data:

P_ID P_Name
----- ------
1A
2B
3C
4D

Child Table data:

C_IDC_NameP_ID
--------------
1test11
2test21
3test32
4test42
5test53
6test53
7test64
8test64

My controll file
-------------------
LOAD DATA
INFILE 'mydata.data'
APPEND
INTO TABLE parent
when (1:1) = 'p'
fields terminated by ',' optionally enclosed by '"' trailing nullcols

[code].....

able to load parent data and generate P_ID, but i am not able to get P_ID for child records.

View 6 Replies View Related

SQL & PL/SQL :: Missing Data

Nov 8, 2012

i ahave a table with below

01-oct-12,ARTACT001,Memory
04-oct-12,ARTACT001,Memory
01-oct-12,ARTACT001,OS
05-oct-12,ARTBRIO,CPU

Required output as below which dates are not available my table

02-oct-12,ARTACT001,Memory
03-oct-12,ARTACT001,Memory
01-oct-12,ARTACT001,OS
02-oct-12,ARTACT001,OS

[Code]...

View 5 Replies View Related

PL/SQL :: Closest Date Missing Data

Jul 19, 2012

My requirement is to find the closest date from col B(a.p_date) to the dates in col A(d.p_date). I got the following which works great:

SELECT
d.p_no_s,
d.p_date,
MIN(a.p_date),
MIN(a.p_date)-d.p_date||' Number of Days' NUM_OF_DAYS

[Code]...

Output:

p_no_s p_date MIN(a.p_date) MIN(a.p_date)-d.p
-------------------- ---------- ---------- -------------------------------------------------------

Z1575560 15/06/2008 29/07/2008 44 Number of Days
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008 27/12/2008 36 Number of Days
Z1575560 17/12/2008 27/12/2008 10 Number of Days

Problem:

For 1st and 2nd row,

I am getting 29/07/2008 as a.p_date for both 15/06/2008 and 15/07/2008 which is wrong in my scenario. This is because data is missing in the second table for row 1 (similarly for row 3). So What I want is :

Z1575560 15/06/2008
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008
Z1575560 17/12/2008 27/12/2008 10 Number of Days

Say for eg: a person is in city A. He is departing on (15/06/2008 ) and arriving on 29/07/2008 and again departing on 15/07/2008 which is not possible.

It should be departing on (15/06/2008 ) and arriving between 15/06/2008 and 15/07/2008 (missing data hence null is required here) and departing on 15/07/2008 and arriving on 29/07/2008 .

let me know how to achieve this.

View 4 Replies View Related

SQL & PL/SQL :: UTL_SMTP.DATA / Why Body Of Email Is Missing

Jul 23, 2012

I have a procedure called SANDEEP_INC105657, which calls another procedure called "Send_Prodcontrol_Mail_Pr". This procedure should be sending out an email with the following message:

From: <sandeep.tanjore@xxx.xx.ca>
Subject: Remit Advice Slips Report (TR000336).
To: sandeep.tanjore@xxx.xx.ca
Report TR000336 - Remit Advice Slips.

Total number of records that needs slips printed are: 1

However what's happening is we had a database change from 10g to 11g and ever since this has happened the email is sent with "From", "Subject" and "To" but the body(content of the email : Total number of records that needs slips printed are: 1) is missing.The code in Send_prodcontrol_Mail_pr is as follows:

CREATE OR REPLACE PROCEDURE Send_Prodcontrol_Mail_Pr( v_mailhost IN VARCHAR2,
v_sender IN VARCHAR2,
v_recipient IN VARCHAR2,
v_subject IN VARCHAR2,
v_message1 IN VARCHAR2,
v_message2 IN VARCHAR2,
v_message3 IN VARCHAR2)
[code]....

Any reason why the body of the email is missing? I tried the following:

replaced utl_smtp.data(mail_conn,mesg); with utl_smtp.write_data(mail_conn, utl_tcp.CRLF||mesg);

It sends out the email as required but it repeats the "From" two times with no "TO", and "Subject"... however then embeds the whole "from" "to" and the body in an email.

View 4 Replies View Related

SQL & PL/SQL :: Data Cleansing - Avoid Null Or Missing Values?

Jul 20, 2010

As data completeness, data consistency, data integrity are some of features of good quality data. What is best approach and best practice to deal with missing or null values in database? I am facing such type of situation. In a main table, there are several columns have null or missing values.

Get rid of null or missing values. Should I consult source documents from where data was fed into database to achieve maximum data completeness?

View 14 Replies View Related

Data Guard :: Redo Archive Logs Missing

Apr 23, 2013

While Configuring Data Guard for ORacle 10g (10.2.0.4) 64 bits on Windows 2007 Server 64 bits.I got few questions

1. What is the Default mode of Standby Database?

2. Should we Always Start Physical Standby Database to Recover Missing Redo Archive Log?

SQL> startup mount;
ORACLE instance started.

Total System Global Area 591396864 bytes
Fixed Size 2067496 bytes
Variable Size 163578840 bytes
Database Buffers 419430400 bytes
Redo Buffers 6320128 bytes
Database mounted.

SQL> alter database recover managed standby database disconnect from session;
Database altered.

3. When there are missing Redo Log Archives e.g.

----On Standby Database--------

SQL> SELECT RESETLOGS_ID,SEQUENCE#,STATUS,ARCHIVED FROM V$ARCHIVED_LOG
2 ORDER BY RESETLOGS_ID,SEQUENCE#;

RESETLOGS_ID SEQUENCE# S ARC
------------ ---------- - ---
812980008 15 A YES
812980008 16 A YES
812980008 17 A YES
812980008 18 A YES
[code]....

65 rows selected. Log 8, 9, 10, 11, 12, 13, 14, 15 are missing.

How to Apply / Recover These Logs on Standby Database?

View 11 Replies View Related

Server Utilities :: Missing Objects During Data Pump Import

May 18, 2011

I did the datapump export and import from one schema to a new schema in the same database. I had to use different tablespace. I used the following parameters in the parfiles :

export parfile
directory
dumpfile
logfile
parallel

import parfile
directory
dumpfile
logfile
parallel
remap_schema
remap_tablespace

Tell me whether I need to use different parameters than the one I used? Can I use both remap_schema and remap_tablespace at a time?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved