SQL & PL/SQL :: History Table Join

Aug 16, 2010

two history tables with each record having effective date and end date needs to join (date is in dd/mm/yyyy)

table one

effdate enddate ID Name
01/08/2010 04/08/2010 01 devendra
04/08/2010 06/08/2010 01 deven

table two

effdate enddate ID Family
01/08/2010 02/08/2010 01 X
02/08/2010 03/08/2010 01 Y
03/08/2010 05/08/2010 01 Z
05/08/2010 06/08/2010 01 W

Expected output

effdate enddate ID Name Family
01/08/2010 02/08/2010 01 devendra X
02/08/2010 03/08/2010 01 devendra Y
03/08/2010 04/08/2010 01 devendra Z
04/08/2010 05/08/2010 01 deven Z
05/08/2010 06/08/2010 01 deven W

what can be optimum sql for this?

View 3 Replies


ADVERTISEMENT

Application Express :: Insert Into History Table When Update Action Is Performed On Tabular Form View Or Table

Aug 24, 2013

My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.

So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).

View 2 Replies View Related

Create A Table Which Contain History Of Main Table

Sep 8, 2008

I have to create a table which contain history of a main table. like this:

if the main table is
========================
nametypelengthnot null
Avarchar5Y
Bvarchar5N
Cvarchar5N
Dvarchar5N
========================
[code]....

I've plan to so this by create a trigger in main_table. my problem is my main table have a lot of fields and I can't write a code to control it 1 by 1 like :

if old.A <> new.a
insert into history("A",old.A,new.a)
if old.B <> new.B
insert into history("B",old.b,new.b)
......

I decided to select column name from the data dictonary using this SQL:

SELECT column_name FROM user_tab_columns WHERE table_name = '<<Table Name>>';

and then do a loop over the resultset and use the column name I've got , like this (its just an idea, may be not a write syntax):

BEGIN
.....
FOR i IN 1..:result.COUNT LOOP
if ld.colname[i] <> :new.colname[i]
INSERT INTO history
VALUES ( colname[i], ld.colname[i], :new.colname[i]);
END LOOP;
END;

but I can't write a "old.colname". I try with " old.'colname' ", " ld.'colname' " but it won't work.how to create a history file like I've describe.

View 5 Replies View Related

SQL & PL/SQL :: Updating Fields Of History Table

Aug 25, 2011

pgit_policy is transaction table having producer code field.

pgith_policy is history table, on that table if any endorsement passed new records created with same polh_sys_id and increment on POLH_END_NO_IDX.

I am trying to update all records of the history table but its updating only higest POLH_END_NO_IDX only. i need to update all producer code.

update pgith_policy a
set a.polh_producer_code= (select b.pol_producer_code
from pgit_policy b
where b.pol_no=a.polh_no
--and b.POL_END_NO_IDX= a.POLH_END_NO_IDX and b.POL_END_SR_NO = a.POLH_END_SR_NO
and b.pol_producer_code is NOT NULL
and b.pol_class_code='10')
where a.polh_class_code='10'
and a.polh_producer_code is null
and a.polh_appr_dt between to_date('01-06-2011', 'dd-mm-yyyy') and to_date('30-06-2011', 'dd-mm-yyyy')

View 4 Replies View Related

SQL & PL/SQL :: How To Return Last 2 Rows In History Table

Nov 23, 2011

I have a history table which has a new record written to it for every time static data is updated. What I want to do is return any record that has been changed on a daily basis returning both the new record and the previous record so you can see the 2 records one below the other and compare.I have written the below but it returns all records per account:

SELECT H.Timestamp,H.AccountNo FROM History H
where exists (select H2.AccountNo,count(*) from History H2
where H2.AccountNo = H.AccountNo
group by H2.AccountNo having count(*) > 1 )
Where H.Timestamp > '20111101'
order by H.AccountNo,H.Timestamp

View 18 Replies View Related

PL/SQL :: Update / Insert History Table?

Jun 15, 2012

I've a one history table in which I'm putting approval history data.

For any transaction id you may have more than one record with approval status APPROVED,REJECTED,ERRORED,OVERLIMIT etc

Another program selects records from this table by passing transaction_id. For transaction id, it needs to check the most recent approved record exist or not ? If it's there then updating the record by adding comments to comments field of the same record and then delete all other records for the same transaction id.

If it does not exist then delete all other records and create one with approved status?

What's the simple and best approach to do this (sql or pl/sql)?

View 2 Replies View Related

PL/SQL :: Date Format In History Table

Mar 1, 2013

Issue with Date format. I am having data in date column in History table like

'2012/02/22 11:05:20 AM'
'2012/08/15 17:00:00'

I am doing extract from this table to txt file. i want date fomrat in "YYYYMMDD HHMMSS".

i tried below query:

select to_char(to_date('1998/05/31 11:00:00 AM','yyyy/mm/dd hh24:mi:ss'),'YYYYMMDD HH24MMSS') from dual;But it is giving error like "ORA-01830: date format picture ends before converting entire input string".

I am using ORACE 11g verion.
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

View 8 Replies View Related

SQL & PL/SQL :: Data Transfer From Fact To History Table?

Aug 9, 2010

I need to transfer 6 million records from fact tables to history table .. What is the better and fast process to do that.

View 3 Replies View Related

PL/SQL :: Join Between Table With User Based Content And Base Table

Jul 30, 2012

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

View 10 Replies View Related

SQL & PL/SQL :: How To Join A Table Column Names With Data From Other Table

Jul 18, 2012

I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.

CREATE TABLE SALESHIST
(
PRODUCT VARCHAR2(30 BYTE),
Q1 VARCHAR2(30),
Q2 VARCHAR2(30),
Q3 VARCHAR2(30),
Q4 VARCHAR2(30)
)
[code]......

View 6 Replies View Related

Retrieving Max / Latest Date From A Table With A Join To One Another Table

Sep 19, 2011

I am having trouble retrieving the Max, latest date, from a table with a join to one another table and other fields from both.I was able to get the MAX service_date grouped by id. But once I tried to add more fields to the query and another table it won't work.

Here is what I have:

selectMAX(cs.service_date), cs.notes, cs.applicant_id,wr.program_code,wr.last_name,wr.first_name,wr.region_code,wr.status_cd
from cs join wr on cs.applicant_id=wr.applicant_id
where wr.status_cd='AC'
group by cs.applicant_id

View 3 Replies View Related

Join Two Table In Different Instance

Nov 1, 2010

I have two table in different instance .

IMEI in instance A

RCA_SMART_CARD in instance B

Below is the desc table :

SQL> desc RCA_SMART_CARD;
Name Null? Type
----------------------------------------- -------- ----------------------------
N_CARD_ID NOT NULL NUMBER(10)
C_CARD_SERIAL_NUMBER NOT NULL VARCHAR2(20)
C_SIM_MSISDN VARCHAR2(20)
C_SIM_IMSI VARCHAR2(20)
C_LINKED_CARD VARCHAR2(20)
N_PRO_IDENTIFIER NOT NULL NUMBER(4)
C_CARD_TYPE VARCHAR2(1)
N_SIM_STATE NUMBER(1)
N_EEPROM_SPACE_LEFT NUMBER(9)
N_VOLATILE_SPACE_LEFT NUMBER(9)
N_NONVOLATILE_SPACE_LEFT NUMBER(9)
N_CARD_OPTI NOT NULL NUMBER(15)
N_PRODUCT_ID NUMBER(10)
D_CREATION_DATE DATE
D_MODIFICATION_DATE DATE
D_STATUS_MODIFICATION_DATE DATE

SQL> desc IMEI;
Name Null? Type
----------------------------------------- -------- ----------------------------
MSISDN NOT NULL VARCHAR2(20)
IMEI NOT NULL VARCHAR2(16)
DATE_MOD NUMBER(13)
IMSI VARCHAR2(18)
ICCID VARCHAR2(20)
T_PROF RAW(20)
EXTRA_DATA VARCHAR2(100)

If I want to join two table together . I want to search the number of record in IMEI that have N_SIM_STATE =1 in RCA_SMART_CARD . The MSISDN in IMEI is equal to C_SIM_MSISDN in RCA_SMART_CARD .

View 1 Replies View Related

SQL & PL/SQL :: Multiple Join Table

May 17, 2010

oracle 10g to select column from about 8 table . I start with this statement

select A.a, B.b, C.c, D.d, E.e, F.f, G.g, H.h
from A
full outer join B on(A.a=B.b)
full outer join C on(B.b=C.c)
full outer join D on(C.c=D.d_
.
.
..
.
.
.

View 12 Replies View Related

SQL & PL/SQL :: Table Join Query

Nov 10, 2010

I have the following 2 tables.

A) Docversion
CREATE TABLE DOCVERSION
("OBJECT_ID" VARCHAR2(250 BYTE),
"OBJECT_CLASS_ID" VARCHAR2(250 BYTE),
);

[Code]..

Join column : object_id and parent_id.

The object id in docversion will have multiple values for element value in listofstring table.

ordinal value represents teh sequence of element value

Eg:

data for docversion:

1 23
2 34

data for LISTOFSTRING:

1 11 0 100
1 11 1 109
1 11 2 119
2 22 0 A
2 22 1 B

ouptut:

I want the output as follows

docversion.objectid,listofstring.elementvalue
1 100,109,119
2 A,B

View 14 Replies View Related

SQL & PL/SQL :: Multiple Join To Same Table

Apr 12, 2010

I have a table that contains 15 columns. Say each columns contains name of fruits. And another master table which contains names of valid fruits. Now I have to create another table from these tables such that for each column if the fruit name is present in the master table then fruit-name is populated else "invalid" is populated. I know using left join for 15 times to the same master table can work. But I found this very clumsy. Is there any other way of doing it.This is just a sample case. In real scenario I have a table of 800 million records and master table of 30000 records.

View 7 Replies View Related

SQL & PL/SQL :: Join Between Table And View

Mar 18, 2011

I have create a select statment using more then 10 table which is returning only two columns as per my requirement.

From this select statement I have create a View?

In order to proceed further first I would like to as If I can join a table and a view?

View 3 Replies View Related

SQL & PL/SQL :: Join Two Table In Different Instance?

Nov 1, 2010

I have two table in different instance .

IMEI in instance A

RCA_SMART_CARD in instance B

Below is the desc table :
SQL> desc RCA_SMART_CARD;
Name Null? Type
----------------------------------------- -------- ----------------------------
N_CARD_ID NOT NULL NUMBER(10)
C_CARD_SERIAL_NUMBER NOT NULL VARCHAR2(20)
C_SIM_MSISDN VARCHAR2(20)
C_SIM_IMSI VARCHAR2(20)
C_LINKED_CARD VARCHAR2(20)
N_PRO_IDENTIFIER NOT NULL NUMBER(4)
C_CARD_TYPE VARCHAR2(1)
N_SIM_STATE NUMBER(1)
N_EEPROM_SPACE_LEFT NUMBER(9)
N_VOLATILE_SPACE_LEFT NUMBER(9)
N_NONVOLATILE_SPACE_LEFT NUMBER(9)
N_CARD_OPTI NOT NULL NUMBER(15)
N_PRODUCT_ID NUMBER(10)
D_CREATION_DATE DATE
D_MODIFICATION_DATE DATE
D_STATUS_MODIFICATION_DATE DATE

SQL> desc IMEI;
Name Null? Type
----------------------------------------- -------- ----------------------------
MSISDN NOT NULL VARCHAR2(20)
IMEI NOT NULL VARCHAR2(16)
DATE_MOD NUMBER(13)
IMSI VARCHAR2(18)
ICCID VARCHAR2(20)
T_PROF RAW(20)
EXTRA_DATA VARCHAR2(100)

If I want to join two table together. I want to search the number of record in IMEI that have N_SIM_STATE =1 in RCA_SMART_CARD . The MSISDN in IMEI is equal to C_SIM_MSISDN in RCA_SMART_CARD .

How can I do and what is the sql statment ??

View 5 Replies View Related

SQL & PL/SQL :: Mechanism Of Self Join Table

Jun 11, 2010

Explain the mechanism of this sql statement. I tried to understand how it goes but I really got confused.

select e.last_name, m.last_name mgr
from employees e join employees m
on (e.manager_id = m.employee_id)

The first line seems clear to me that will bring the names of employees and second column will bring names of managers. Second line is clear as well, but the third line couldn't understand. in my mind it should be like that

m.manager_id = e.employee_id

I tired this but doesn't work so what happened exactly in that full statement, I mean how it will match. I just want to understand the mechanism.

View 4 Replies View Related

SQL & PL/SQL :: Distinct On Multiple Table Inner Join

Apr 28, 2010

I am little confused applying DISTINCT on a Multiple table Inner join.

Table: Role
=========================
role_id email
1 xxx@abc.com
2 yyy@abc.com
3 zzz@abc.com

Table: notification_role
===========================
id role_id process_id
1 1 p1
2 1 p2
3 1 p3
4 1 p1
5 2 p2
6 2 p2
7 2 p3
8 2 p3
9 3 p4

Table: process
================
process_id proces_name
p1 process1
p2 process2
p3 process3
p4 process4

Expected Result
====================
role.role_id role_email process_process_id process_name
1 xxx@abc.com p1 process1
1 xxx@abc.com p2 process2
1 xxx@abc.com p3 process3
2 yyy@abc.com p2 process2
2 yyy@abc.com p3 process3
3 zzz@abc.com p4 process4

QUERY::

select distinct c.process_id a.role_id,a.email_address,c.process_name
from role a, notification_role b, process c
where a.role_id=b.role_id and b.process_id = c.process_id

View 3 Replies View Related

SQL & PL/SQL :: Join 2 Tables And Get Data In Table 3

Mar 1, 2010

I have 2 tables as shown below. I have to join those tables and get data as in table 3. Condition is I have to get sum of scores for each student where category is 1 and active is Y.

Table1:
col1col2category
A 10 1
A 10 2
B 10 1
B 20 2
C 10 1
D 20 1
J 30 1

Table2:
colAcolBActive
A10Y
A20N
B30Y
B40N
Z35Y

Table3:
STUDENTSCORE
A20
B40
C10
D20
J30
Z35

query to show student name and sum of his score where category is 1 and active is Y. I am using Oracle 8i.

View 8 Replies View Related

PL/SQL :: How To Join Tables And Get Ppl Not In The List From Other Table

Nov 2, 2013

I have 2 tables Table a(girlscoutid, item, quarter)Table b(girlscoutid, fname, lname) I want to get the names of ppl who did not sell any item/s for the

4th quarter  
girlscoutid item quarter7771
drinks 4QS9000
tickets 4QW7771
cookies 2QS5085books 3QF3010tickets3QW5085
cookies1QF lscoutid
fnamelname7771 jenn richochet9000 laura wilkins5085sally mae3010dora mckenzie 

This is what I have, however, I get duplicates and  also 7771 shows up on the list even though she sold an item on the 4Q.

  SELECT fname, lname, a.girlscoutid
FROM a,b
WHERE a.girlscoutid = b.girlscoutidAND term NOT LIKE '4Q_';

View 4 Replies View Related

SQL & PL/SQL :: Outer Table Join With Not Equal Condition

Aug 20, 2010

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 '<'.)

View 3 Replies View Related

Query Optimization On Join With A View On Huge Table?

Jun 22, 2011

I have this table

create table ACTIONARI_ARH
(
actionar_id NUMBER(10) not null,
id VARCHAR2(20) not null,
id_2 VARCHAR2(20),
tip VARCHAR2(1),
nume VARCHAR2(100),
prenume VARCHAR2(100),
adresa VARCHAR2(200),

[code]....

and this view

CREATE OR REPLACE VIEW ACTIONARI AS
SELECT "ACTIONAR_ID","ID","ID_2","TIP","NUME","PRENUME","ADRESA","LOCALITATE","JUDET","TARA","CERT_DECES","DATA_REGISTRU" Data_operare,"USER_MODIF","DATA_MODIF","REZIDENT"
FROM (
select

[code]....

The table has about 30 milion records and holds persons names, addresses, personal id (id), and internal id(actionar_id) and date when a new adress has been added.

The view is about getting only the most recent info for one person (actionar_id).

if i run a

a) select * from actionari a where a.actionar_id = 'nnnnnnn', result is returned immediatly, oracle uses index and does not do a full table scan.

b) select * from actionari a where a.actionar_id in ('nnnnnnn','mmmmmm','ooooooo'), result is returned immediatly, oracle uses index and does not do a full table scan.

my problem when i use this view in a join.let's assume i have another table with no more than 500 records, something like

create table SMALL_TABLE
(
actionar_id NUMBER(10) not null,
......
);

and if i run

select *
from SMALL_TABLE s
join actionari a
on a.actionar_id = s.actionar_id;

it takes like forever to process, forever means 1~3 minutes.by looking at the execution plan, oracle does a full table scan, creates the view for all unique 7milion persons, and only then joins the result with the actionar_is's in the small table and returns the desired 500 record result.i am using oracle 10g.

View 2 Replies View Related

SQL & PL/SQL :: Delete Table1 Rows Where 3 Fields To Join With Another Table

Aug 13, 2011

I need to delete all the registers where the table 1 does join with table 2 in 3 fields... for example:

delete taba1 t1
where t1.campo1 in ( select distinct(tr.campo1)
from tabla1 tr,
tabla2 t2
where t2.error = 0
tr.campo1 = t2.campo1
and tr.campo2 = t2.campo2

[Code]...

View 4 Replies View Related

Join Two Oracle Tables For Fast Fetch Of Data From Table?

Jul 2, 2012

join two oracle tables for fast fetch of data from table.

View 1 Replies View Related

SQL & PL/SQL :: Filtering Dataset Based On Records Returned By Table Join

Nov 10, 2010

I need to work on this requirement.

There are FOUR tables ( T1 , T11 & T2, T22) ALL store order information.

One of four conditions are possible for each Supply Reorder Number:

•Both table queries return no records

oPopulate all the output fields with nulls

•T1 returns a record, but T2 returns no records

oPopulate output fields with values from the join of T1 and T11.

•T1 returns no records, but T2 returns one record

oPopulate output fields with values from the join of T2 and T22.

•T1 returns a record, and T2 returns a record

oIf the latest order is in T1, then populate output fields with values from the join of T1 and T11.

oIf order dates are equal from both join results, then populate output fields with values from the join of T1 and T11 .

oIf the latest order is in T2, then populate output fields with values from the join of T2 and T22.

How do we filter the dataset based on result of table join ?

View 1 Replies View Related

SQL & PL/SQL :: Update Table With Join / ORA-00933 / Command Not Properly Ended

Jan 18, 2013

I'm unable to get the below update SQL to run in Oracle, it's giving me th below error

ORA-00933: SQL command not properly ended.

UPDATE
PDR.PH_Family_Match_by_Chassis a
SET a.Launched = 'Y'
INNER JOIN
PDR.domCHASSIS
ON
a.chassis_id = PDR.domCHASSIS.chassis_id

[code]....

View 8 Replies View Related

SQL & PL/SQL :: Stored Procedure With Multiple Table Join With Aggregate Functions

Jun 15, 2013

I have 2 tables order_items and items.

Order_items Items
Item_id Item_id
Quantity Price

In normal sql statement: select sum(order_items.quantity*items.price) sales_price
from order_items,items
where order.item_id=items.item_id;

I have to put this logic in either a stored procedure or Function just to calculate sum(order_items.quantity*items.price) and store the aggregated value as Sales_price in DB. Then we have to call this from Informatica Stored procedure Transformation where we will have only one output port as Sales_price,this is to load into summary table. All the aggregate calculations and joining of 2 tables should be done on DB side and only one output should be populated when we execute this procedure.

View 2 Replies View Related

SQL & PL/SQL :: Normal Join And Outer Join

Oct 19, 2013

Lets say I have three tables t1 and t2 and t3.

SELECT * FROM T1;

Id
____
1
2
3
4

SELECT * FROM T2;

Id
____
1

SELECT * FROM T3;

Id
____
1

Now when data exists in T2 and T3, I want to return only the records in T1 that match the records in T2 and T3 which is basically a normal join

select t1.id from t1, t2,t3 where t1.id = t2.id and t1.id = t3.id

However when there are no records in T2 or T3, I want to return all records in T1 i.e 1,2,3,4

One way of doing that is using the not exists clause

select * from t1 where not exists ( select null from t2 where t2.Id != t1.id) and not exists ( select null from t3 where t1.Id != t3.id)

Is there a better way of doing this in sql ?

View 5 Replies View Related

How To Find SQL Text History

May 27, 2011

I have SQL ID , sql with bind variables ("SYS_B_005")from AWR report which is taken last week.I need find the full exact sql text with bind variable value (sql ran 1 week before).

View 4 Replies View Related







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