How To Display Unmatched Records In Two Tables

Feb 28, 2008

I want to display all records from table 1 (even the null values) that do not match records in Table 2. Below I am creating both tables and I am posting the result query I need.

------------------------------------
CREATE TABLE temp_table1
(
name VARCHAR2(12 BYTE),
last_name VARCHAR2(12 BYTE),
STATE VARCHAR2(2 BYTE),
BIRTH_DATE DATE
);

CREATE TABLE temp_table2
(
name VARCHAR2(12 BYTE),
last_name VARCHAR2(12 BYTE),
STATE VARCHAR2(2 BYTE),
BIRTH_DATE DATE
);

[code].....

The result query need to have 5 rows as shown below

NAME LAST_NAME STATE BIRTH_DATE
------------ ------------ ----- ---------------------
john smith MA 12/1/1979
null null AZ null
null null CT null
null null MA null
null null CT null

View 5 Replies


ADVERTISEMENT

Forms :: Display Records Of Two Tables In One Form

Mar 27, 2013

want to display records of two tables in in one form.I have 2 tables. tab1 and tab2

columns in tab1 are code, desc, part, onhand_qty AND
columns in tab2 are code, room_no, row_no, rack, shelf, qty, remarks.

Common column in both the tables is code.

i want to display all the columns of both the tables where tab1.code=tab2.code ,and tab1.onhand_qty is not equal to SUM of tab2.qty

View 1 Replies View Related

Security :: How To Retrieve Unmatched Data From A Column

May 30, 2013

i have a column that contains 1 to 100 values but it has some missed data such as it does not has 50,45,34,23 etc..

so how could i find the missed data alone in a single query..

View 4 Replies View Related

SQL & PL/SQL :: Differences Among Records Tables And Tables Of Records

Sep 16, 2010

In Oracle database pl/sql 11.2: describe the differences between :

records,
tables, and
tables of records.

View 11 Replies View Related

PL/SQL :: How To Display All Records In A Table

Oct 3, 2013

how to display all the records in a table ,i am passing the table name as in param to the procedure/function suppose if i pass emp table name it will display 14 rec, if i pass dept it will display 4 records.

View 3 Replies View Related

Query To Display Records In Particular Order

Feb 8, 2007

I need to write a query to sort the records in a particular order,

Say if I group the records by Dept number
Dept no Name
10A
10G
10f

20B
20K

30I
30M
30R
30Y

I need to write a query that will make this records listed like

Dept No Name
10A
20B
30I

10G
20K
30M

10F
20null
30R

10null
20null
30Y

View 1 Replies View Related

Forms :: Display All Records Of DB In A Table?

Dec 29, 2012

i want to display all the records of my database in a table with scrollingexample if i have 3records in my DB so a table must have 3records too !!!this is my code :

DECLARE
Cursor cur IS
SELECT numb_ph AS phone,
name_emp AS name

[code]...

but the problem 's all records of my DB are display in one record (to view another record i use the scrolling ..

View 31 Replies View Related

SQL & PL/SQL :: How To Display Data Without Duplicate Records

Nov 16, 2011

how to display the data which is shown below without duplicate records in compid and compname and all policy_id's should be there while excuting this query iam getting this data.

select distinct comp_id as compid,
comp_disp_name as company,
plcy_id as policyid,

[Code]....

output
-----------------
compid compname policy_id policy_name

19734 Save the Children 9013 GPA
19734 Save the Children 9012 GMC
20097 JMT 9486 GTL
10890 Steelco Gujarat Ltd. 9727 CAR
17330 Golden Jubilee Hotels Limited 8915 CGL
23117 NBHC 9093 GMC
17542 Heinz India 10693 Fire
19821 KSK Fabricators 10341 D&O
3769 Jones Lang Lasalle India 9199 WC
19821 KSK Fabricators 10340 WC

View 10 Replies View Related

PL/SQL :: Unable To Fetch And Display Records?

Aug 21, 2013

I am using Oracle 11g

The procedure is compiled without any errors.

The select statement retrieves records well when executed at SQL promt.

But the same records are not displayed in this procedure by fetch from cursor.

The last message in the procedure 'completed' is also displayed.

create or replace procedure disp_rec(vid IN varchar2, vfeat IN number)
is
cursor c1 is select gid, listagg(vindex, ',') within group (order by tid) idx from ridrecords where idarpt=vid and feattype=vfeat group by gid;
type vg is table of number;
type vi is table of varchar2(3500);

[Code]....

View 19 Replies View Related

Forms :: Before / While Changing - Display All Records?

Jun 22, 2011

I have created two tabs tab1 & tab2. after changed(not while changing/before changing)) into tab2 all records should be display.

I have written execute_query in when-tab-page-changed.but that is before/while changing.i want after entered in tab2 it should be display.

View 1 Replies View Related

Display All Tables In DB (with Their Definition)?

Sep 19, 2007

I've tried using Select * from ALL_TABLES but this does not return me the column info for each table.

How do I get an output of all tables in a database along with column (e.g datatypes etc)?

I'm using TOAD v8.6.1.

View 3 Replies View Related

SQL & PL/SQL :: How To Display Data From Two Tables

May 13, 2013

In SQL...I want to display the manager details and the corresponding employee details and i donot want to use joins or co-related subqueries

I am trying it to use CASE or DECODE..

Here is the Query..

SELECT *
FROM (SELECT empno,
ename,
sal,
job,
CASE
WHEN empno IN (SELECT mgr FROM emp)

[code].....

The output is as follows..

7839KING5000PRESIDENT7839 KING Manager 5000 0 10
7698BLAKE2850MANAGER 7698 BLAKE 7839 2850 0 30
7782CLARK2450MANAGER 7782 CLARK 7839 2450 0 10
7566JONES2975MANAGER 7566 JONES 7839 2975 0 20
7902FORD3000ANALYST 7902 FORD 7566 3000 0 20
7788SCOTT3000ANALYST 7788 SCOTT 7566 3000 0 20

I am able to display only the Manager details or employee details and not both at the same time..

View 13 Replies View Related

SQL & PL/SQL :: Display The Records Of Financial Year Wise?

Jul 20, 2011

i hav the following records i need to display the records of financial year wise (i.e april year-1 to mar year) here there are no records for months 9,10(september and october)

what i want is 0 to be displayed over there in the below table

s.no key id yyyymm ce1 ce2 ce3 t ihu pf mi pwd F
920110600000156431372010043424541500065457709539XXXXXXXXXXU
10201106000001565313720100534245415000009539XXXXXXXXXXU
11201106000001566313720100634245415000009539XXXXXXXXXXU
12201106000001567313720100738555415000009539XXXXXXXXXXU
13201106000001568313720100836445415000009539XXXXXXXXXXU
1420110600000157131372010110000009539XXXXXXXXXXU
15201106000001572313720101276881082200001888009539XXXXXXXXXXU
1620110600000157331372011010000009539XXXXXXXXXXU

View 2 Replies View Related

Performance Tuning :: Display All Records From TAB_ONE

Jul 8, 2010

My query formation is like below..

*Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi*

SELECT T1.COL1,T1.COL2,
T1.COL3,T2.COL2,
T3.COL1,T3.COL2,
T4.COL1,T4.COL3,
<CASE statements and calculations results - Some Amount1>,
<CASE statements and calculations results - Some Amount2>
[code]..........

First I need to display all the records from TAB_ONE which contains more than 10million records. If you see there are columns like AMOUNT_ONE,AMOUNT_TWO which has got some complex calculations and that is based on some other calculations and which in turn and goes on.... Like this I have some ten amount columns. Finally these records has to be inserted into a new table.

To get this I have written nested inline queries to calculate these AMOUNT columns but since considering the huge amount of records, it takes more than 8 hours for inserting this into a new table.

View 7 Replies View Related

SQL & PL/SQL :: Display Hierarchical Relationship Between Tables?

Mar 5, 2010

i'm trying to display the hierarchical relationship between the tables (parents-child-subchild).

[b]table structure[/b]
DEPT
|PK-DEPT_ID
|
EMP
|pk-EMP_ID
|fK emp_DEPT_fK((FK column is dept_id references dept table dept_id column))

[code]....

but by using below query i am not getting complete heirarichy.

SELECT LEVEL,
Table_Name,
Constraint_Name,
R_Constraint_Name ,
SYS_CONNECT_BY_PATH(Table_Name, '/') Path

[code]....

View 2 Replies View Related

Fetch Records In Single Select Query And Display

Mar 15, 2011

I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.

Name id dept_no
Ram 101 10
20
30
Ani 201 10
20

View 1 Replies View Related

Cursors - Display Data Gathered From 3 Different Tables?

Apr 30, 2010

I am stuck somewhere in my project, situation is that we are trying to display data gathered from 3 different tables, we used cursors for this, here is the cursor declaration.

DECLARE
procedure DocInfoCur(myWhere IN varchar2) IS

init_dept_id number;
init_dept_name varchar2(40);

rcvg_dept_id number;
rcvg_dept_name varchar2(40);

CURSORCUR_DOCINFO IS
SELECT DISTINCT I.CTN, I.DIARYNUMBER, I.INITIATINGDATE, T.TYPENAME, I.INITIATINGDEPT,
I.DOCUMENTSUBJECT, I.DESTINATIONDEPT
FROM DOCUMENTINFO I, DOCUMENTTYPE T, DEPARTMENT D

[code]...

This displays all the records out there.

Now, I have a search form where I need to filter the same data with different parameters, I have compiled the entered data in one string, now i need to join that string at the end of where clause of my cursor, how can I do that, i have tried concatenating both but received compile error, is there a way out in the current scenario???

View 1 Replies View Related

Forms :: Display All Records One By One As User Click On Next Record Button

Jul 13, 2010

I have created one tab canvased form

On first tab I am taking input (search criteria)
on that input i am trying to display all records one by one as user click on next record button....

for that i used when-tab-page-change trigger for first record display and it display correctly.but for further record what should i need to write on next_record command button so all record i can see one by one.

View 1 Replies View Related

SQL & PL/SQL :: Display Ename / Contact_no / Location Joining The Two Tables?

Apr 13, 2010

i have two tables

create table tab1(
ename varchar2(10),
empid number(3) primary key);
SQL> insert into tab1 values('Ram', 101);
1 row created.
SQL> insert into tab1 values('Hari', 102);
1 row created.
SQL> insert into tab1 values('Shyam', 103);

[code]....

but i want like this

EMPID CONTACT_NO(PHONENO, MOBILENO)LOCATION
---------- -----------------------------------------
101 80123456 Banglore
101 9986234567 Banglore
102 809863728 Banglore
102 9032456578 Banglore
103 409863728 Hyderabad

[code]....

i want to display the ename,contact_no,location joining the two tables.

View 1 Replies View Related

Forms :: Display Checkbox Selected Records In One Column Of Form / When Push Button Is Pressed

Jul 2, 2011

how to display check box selected records in one column of form when push button is pressed.

View 1 Replies View Related

SQL & PL/SQL :: How To Insert 100 Records Into Different Tables

Apr 28, 2011

i have 100 records in table1,like as we have more 15 tables without data. the issue is how can i insert above table1 data(100 records) into different 15 tables in single sql command.

View 22 Replies View Related

PL/SQL :: Get Records Count Of All Tables

Sep 26, 2012

I am trying to get the record count of all tables using dynamic query. I don't know how to put the value in placeholder. I tried the below code.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
CURSOR table_list
IS
select OBJECT_NAME from user_objects
where object_type in ('TABLE')
[code].......          

View 4 Replies View Related

SQL & PL/SQL :: Retrieving Records From Tables

Mar 27, 2010

I want to retrieve the data from ex.tables. How to get this.

table1:

Account_No, Account_sub_No
1234 1
1234 2
1234 3
2345 4
2345 5
2345 6
2345 7
2345 8
................
Account_no is the primary key

table2:

Account_sub_No, Description
1 Hello
2 Hi
3 No.1
4 great
5 people
.................
8 world
..........................
Account_sub_No is primary key.

Out put:

I want the data like Account_no who is having more than 3 Account_sub_no values.

But in my case need to join these two tables with other tables. join field is Account_no from table1. there are no other fields to join.

View 5 Replies View Related

Querying Records From Temporary Tables

Feb 2, 2011

I have a temporary table (with on commit preserve rows property) which is populated thru insert into command from a procedure. After which, i need to query the records from the populated temp table.

However, my query returns nothing. My procedure works fine cause i tried executing it to populate a regular table and it is ok. However, it shows no output in the temp table cause probably it is creating another session. How do i select the rows from the temp table after populating it from a procedure.

View 3 Replies View Related

SQL & PL/SQL :: List Records Being Deleted From All The Tables

Jan 10, 2011

I am executing a script that is deleting some parent records and the corresponding child records as I have used the "on delete cascade" with the Foreign key Constraint.

My question is that can I list the records that are being delete from all the tables i.e. both parent and child tables. Is some thing like spooling can work in this or do I have some other option with which I can see(select) all the deleted records.

View 12 Replies View Related

SQL & PL/SQL :: Select Records Where Date Is Max From Tables

Oct 6, 2011

I've got 3 tables and I need to select records where date is max from these tables.

select * from the_table_1
----------------------------------
contract_key date_1 saldo
1234 30.9.2011 12:06:50 14,6638

select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 26.9.2011 11:04:02 5,6638

select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 29.9.2011 17:39:43 2,5438

how to do that ?

View 11 Replies View Related

Delete Records From Multiple Tables

Jun 27, 2011

I have a service that executes a pl/sql function (legacy app) to delete records from multiple tables. This function works fine in development, and has worked fine in production until about a week ago. I'm not a DB guy but the DB guys are trying to say this is an application issue. That may be, be the "insuff privileges" really leads me to believe otherwise.

What is causing this type of Oracle error? Permissions between dev and prod are the same, yet it works in dev but not prod.

ORA-29876: failed in the execution of the ODCIINDEXDELETE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in textindexmethods.ODCIIndexDelete
ORA-20000: Oracle Text error:
DRG-10602: failed to queue DML change to column ABSTRACT for primary key AAAfBoAAEAABa62AAA
DRG-50857: oracle error in drexrdml
ORA-01031: insufficient privileges
[code]....

View 2 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

Forms :: Saving Records In Tables After Updating

Jun 8, 2013

I AM WORKING ON FORM I WANT TO SAVE THE RECORD IN TO TABLES TABLE1,TABLE2 AFTER UPDATING THE

TABLE1..BOTH THE TABLE HAVE SAME COLUMNS'.

WHAT IS THE TRIGGER FOR THAT

View 2 Replies View Related

SQL & PL/SQL :: Merge Two Tables Into One And Knock Out Duplicate Records?

Jul 12, 2006

I have two identical tables with same structure and have some duplicates between those two. Finally I want to combine togather and in a single table and knockout the duplicates.

SQL how can we do this in a simple way? each tables has around millions records.

View 4 Replies View Related







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