Home » RDBMS Server » Performance Tuning » Help need .. Urgent
Help need .. Urgent [message #184795] Fri, 28 July 2006 01:48 Go to next message
juliee_jitu
Messages: 8
Registered: July 2006
Junior Member
plz help me..

how to get the list of records..

1) List of records Created but not deleted on the same day

2) List of records Deleted but not created on the same day

 
TRANS_HISTORY(
  TR_HISTORY_ID            NUMBER(19)           NOT NULL,
  TRANSACTION_ID           VARCHAR2(20 BYTE)    NOT NULL,
  CREATION_DATE            DATE,
  TRANSACTION_DATE         DATE,
  REFERENCE_ID             VARCHAR2(20 BYTE),
  STATUS                   VARCHAR2(30 BYTE),
  STAGING_REFERENCE_ID     VARCHAR2(20 BYTE),
 )

STAGE_CHECKLIST_ITEM(
  DOC_ITEM_ID                   NUMBER(19)      NOT NULL,
  DOC_DESCRIPTION               VARCHAR2(100 BYTE),
  STATUS                        VARCHAR2(30 BYTE),
  DOC_DATE                      DATE,
  EXPIRY_DATE                   DATE,
  IS_DELETED                    CHAR(1 BYTE),
  CHECKLIST_ID                  NUMBER(19),
  DOCUMENT_ID                   NUMBER(19),
  DOCUMENT_CODE                 VARCHAR2(20 BYTE),
  DOC_ITEM_REF                  NUMBER(19)      NOT NULL,
  LAST_UPDATE_DATE              DATE,
 )
 
 
Condtion 
----------
trans_history.staging_reference_id = stage_checklist_item.checklist_id

If it is Created  --> stage_checklist_item.STATUS = 'AWAITING'
If it is deleted  --> stage_checklist_item.STATUS = 'DELETED'
Re: Help need .. Urgent [message #184813 is a reply to message #184795] Fri, 28 July 2006 02:52 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
In adition to the create tables (thanks Thumbs Up) provide inserts, expected results and whatever you have already tried.

Jim
Re: Help need .. Urgent [message #184826 is a reply to message #184795] Fri, 28 July 2006 04:11 Go to previous messageGo to next message
juliee_jitu
Messages: 8
Registered: July 2006
Junior Member
In this table.
 whenever there is will be insert  into STAGE_CHECKLIST_ITEM table , same info will inserted into TRANS_HISTORY table.

if it is not correct,
 plz follow

 Table--> emp (id, name, Date, status)
 Table--> history ( id, name, date, status)

day 1  --> 
       Insert into emp values (1,'sss',sysdate,='AWAITING')
       Deleted from  emp  where id =1;

day 2  --> 
       Insert into emp values (2,'www',sysdate,='AWAITING')

day 3  --> 
       Insert into emp values (3,'3dddd',sysdate,='AWAITING')
       Deleted  same record with name =3 ;

day 4  --> 
        Insert into emp values (4,'44444',sysdate,='AWAITING')

day 5  --> 
      Deleted  same record with name = 4;
  
In the above cases ( while inserting and deleting)
    TRANS_HISTORY with keep all the  information.
 as  (onnSequenceNumber, name , sysdate, status)

The answer will  be
1) List of records Created but not deleted on the same day
   day 2 
   day 4 
2) List of records Deleted but not created on the same day
   day 5
    
Note.. I  gave scenario.  But i need the query


Re: Help need .. Urgent [message #184832 is a reply to message #184826] Fri, 28 July 2006 04:28 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
OK, here's the thing.
In your first post, you show us 2 tables trans_history and stage_item_checklist and a vague description of what you require.

In the second post you reference 2 completely different tables and an incomplete set of scripts. The point about asking for scripts is so that we can very quickly replicate your data in order to help you. Since none of your insert scripts reference the first two tables, how are we meant to replicate your situation.

Your required output makes nect to no sense, show me the actual record that you expect to be returned based on the data that you will supply.

You appear to have made no attempt whatsoever to accomplish the task yourself, a big no-no here.

Here is an example of what I would expect (for an urgently needed answer)
create table emp( emp_id number, nme varchar2(10), dpt_id number);
create table dept (dpt_id number, dptname varchar2(30));
insert into emp values(1,'bungle',10);
insert into emp values(1,'George',20);
insert into emp values(1,'Zippy', null);
insert into emp values(1,'Rod',20);
insert into emp values(1,'Jane',20);
insert into emp values(1,'Freddy',10);

insert into dept vlues(10, 'sales');
insert into dept vlues(20, 'hr');
insert into dept vlues(30, 'consultants');

expected results

Name         department
Bungle        sales
George        hr
Zippy
Rod           hr
Jane          hr
Freddy        Sales
              Consultants

what I have tried

select nme, department
from emp
left join dept
on emp.dptid = dept.dptid

and

select nme, department
from emp
right join dept
on emp.dptid = dept.dptid


try to fulfill these requirements with your next post.

Thanks

Jim
Previous Topic: How to get the CBO to recognize a FBI
Next Topic: Getting queries
Goto Forum:
  


Current Time: Sat Dec 10 11:15:36 CST 2016

Total time taken to generate the page: 0.21683 seconds