Select From Main If There Is No Record In History
Aug 9, 2010
I have 3 main tables as projects, tasks, clients. Then I have a history table for each. I created a trigger on update/delete that takes the old records and inserts into the history.
Now, I need to retrieve the history but I have no clue how to do so. I need to join these 3 history tables with:
1. select from projects_history with projectID = 1
2. if no records, then go back to projects and get projectID = 1
3. select from task_history with taskID = 1
4. if no records, then go back to tasks with taskID = 1
5. select from client_history with clientID = 1
6. if no records, then go back to client with clientID = 1
where client.taskID = task.taskId and task.projectID = project.projectID.
View 1 Replies
ADVERTISEMENT
Sep 13, 2011
I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.
No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.
Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.
View 4 Replies
View Related
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
May 1, 2012
I developed a custom form upon which I developed a query find block to query the data on the main form. I have WHO columns in the table and referencing them in the main form. But Some reason when I query the data by pressing the find button on the query block it is retreiving the data properly, but record history is not enabling.
In the query find block I have 3 text fields, 1 check box and 2 date fields.
View 1 Replies
View Related
May 30, 2012
Based on ACCOUNT_NUMBER column we have to check the GUID data in test table.
for example ACCOUNT_NUMBER =11 as duplicate it has 2 values then only, we need to check the corresponding GUID if any occurence ("9f680174-cb87-4f71-887a-92" and "9f680174-cb87-4f71-887a-91"),
we should select,if not leave it.
CREATE TABLE test
(
GUID VARCHAR2(32 BYTE),
ACCOUNT_NUMBER NUMBER(30),
INDIVIDUAL_ID NUMBER(13)
)
[Code]...
it tried as like as below it's working.
select t2.* from (select distinct t1.guid
from test t1,( select ACCOUNT_NUMBER from test
group by ACCOUNT_NUMBER having count(ACCOUNT_NUMBER
[Code]...
View 15 Replies
View Related
Nov 13, 2006
I am unable to select only record for 07Nov06. if i use between i get records e.g. :07 November ,2006-0941
Select
TO_CHAR(session_START_time,'dd month,yyyy-hh24mi') LOG_IN_TIME,
TO_CHAR(session_END_time,'dd month,yyyy-hh24mi') LOG_OUT_TIME
from SESSION_LOG
where SESSION_LOG.SESSION_START_TIME
between '06Nov06' AND '07Nov06';
View 2 Replies
View Related
Nov 7, 2011
I have a UNION query having 3 parts, 1st gets date, 2nd data and 3rd displays the formatted data count :WHERE clause of 2nd and 3rd queries are same.
Problem is that I an getting different record counts when I select the overall count of records given by the whole UNION query and when I run to count the records given by each query individually.First count. Here I am selecting the overall count of records given by the query :
select count(1)
from (
SELECT SUBSTR ( '0'
|| TO_CHAR (SYSDATE, 'MM/DD/YYYY')
|| TO_CHAR (SYSDATE, 'HH:MI:SS')
|| LPAD (' ', 180)
[code]...
This count is : 1751525 Second count. Now when I run to count the records given by each query individually, here is the result
select count(1) a
from (
SELECT SUBSTR ( '0'|| TO_CHAR (SYSDATE, 'MM/DD/YYYY') || TO_CHAR (SYSDATE, 'HH:MI:SS')
|| LPAD (' ', 180)|| chr(13), 1, 200 ) dtl_record from dual
select count(1) b from (
SELECT '1'
[code]...
why there is difference of 1 (1751526 - 1751526) in the count results.
View 1 Replies
View Related
Sep 17, 2013
I have a table of below structure:
CREATE TABLE Emp_addrs
(
EMP_ID NUMBER(15) NOT NULL,
ADDRESS_ID NUMBER(15) NOT NULL,
SITE_USE_ID NUMBER(15) NOT NULL,
SITE_USE_STATUS VARCHAR2(1 BYTE) NOT NULL,
SITE_USE_CODE VARCHAR2(30 BYTE) NOT NULL)
Insert Script code :
insert into Emp_addrs values ( '1207' , '1846', '2342','A');
insert into Emp_addrs values ( '1207' , '1846', '2343','I');
insert into Emp_addrs values ( '61618' , '165200', '261449','A');
[Code]...
A combination of emp_id & address_id can have multiple site_use_id's. I want to select the max(site_use_id) where site_use_status ='A'.
Now Site_use_status can have either = 'I' or 'A' value.
For a combination of emp_id and address_id , there could be cases when there is no record with site_use_status ='A'. In such cases I need to select the max(site_use_id) (and obviously site_use_status ='I').
Just to clear my requirements, out of the above records I want the following records:
'1207' , '1846', '2342','A'
'61618' , '165200', '261449','A'
'1003' , '1004', '1007','A'
'1005' , '1010', '2002','I'
'2005' , '2010', '3002','A'
View 9 Replies
View Related
Aug 2, 2013
I have a sql query where I need to select only records with an 18 month gap between max(date) and previous date( no dates between max(date)and 18 month gap date), when I run the below query it should only select supid 130, not 120 (even though 120 does contain an 18 month gap date it also has a date that is less then the 18 month gap( '25-NOV-2012','DD-MON-YYYY'). how would get the query to look back 18 months for the next date and evaluate the month_between.
. example:
create table supply(supID number(8), supply varchar2(20), supdate Date,supamount number(13,2));
insert into supply values(100,'Tapes',to_date('01-AUG-2013','DD-MON-YYYY'),50.00);
insert into supply values(100,'TV',to_date('01-APR-2013','DD-MON-YYYY'),250.00);
insert into supply values(100,'Discs',to_date('25-DEC-2012','DD-MON-YYYY'),25.00);
insert into supply values(120,'Tablets',to_date('25-AUG-2013','DD-MON-YYYY'),15.00);
[Code]....
and p.supid in(select s.supid from supply s where months_between
(s.supdate,p.supdate)<-18) SUPID SUPPLY SUPDATE SUPAMOUNT---------- -------------------- --------- ---------- 120 Tablets 25-AUG-13 15 130 Discs 25-JUL-13 75
View 9 Replies
View Related
May 24, 2011
Is there any way that I can check what are the elements present in a pl sql record type by querying in table?
For example if I want to check what are elements present in oe_order_pub.header_rec_type and I don't want to open the package, then in which table I should query? Is it possible?
View 2 Replies
View Related
Mar 20, 2013
We are trying insert records from a select query in to temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem
Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.
View 3 Replies
View Related
Mar 12, 2013
I am trying to run an Oracle report with a query that has an embeded sql. this sql is returning more than 1 row, and the report is failing.
I need to pick the latest record entered that this sql return.
I tried rownnum and it works but only i can get the rown num I specify, not the latest record. I try to order, but I am getting an error back.
select w.emp_no, (select t.timestamp
from tob.work_unit t
where t.work_date = to_date('20130312', 'YYYYMMDD')
and rownum = 1
order by t.timestamp desc)
,w. spare_type
from work.work_unit w
where w.work_date = to_date('20130312', 'YYYYMMDD')
I am getting missing right parenthesis at the order by keyword My report is much complex than this, but I am tring to see if I can get the row that I want.
View 10 Replies
View Related
Jun 6, 2012
I'm looking for a solution to select the first row that is not currently locked in a table and insert a record to another table that reference that first row. this is my scenario:
create table ticket
(
id number(10) not null,,
ticket_type number(1) not null,,
is_sold number(1) not null,
CONSTRAINT ticket_pk PRIMARY KEY (id)
);
[Code]...
id ticket_type is_sold
------------ -------------------- -----
10000004 1 1
10000005 2 1
10000006 1 0
10000007 1 0
10000008 2 0
10000009 2 0
SQL> select * from customer_ticket;
cust_id cust_name ticket_id
------------ -------------------- ----------
1 John 10000004
2 Sara 10000005
my goal is finding the first free ticket ( not sold ) in the ticket table and insert buyer information of that ticket in customer_ticket table. at last I will mark that ticket as a sold one in ticket table with update.
Problem is that the first transaction locks the the first row in ticket table and the second transaction running the same query goes to wait untill the first transaction commit or rollback. However when first transaction finish successfully, second transaction select duplicate id from ticket table that was selected by the first transaction!
I tried to solve problem with "skip locked" and "nowait" options with select for update, but they didn't work.
View 13 Replies
View Related
Sep 21, 2011
I want to know like How we can select the latest updated record from xyz table. that record has STATUS column. I also want to check if the status is RED or GREEN query should return if the status is red then 1 and if the status is GREEN then it should return 0
View 8 Replies
View Related
May 28, 2013
I want to write a simple stored procedure and I want to keep it as simple as possible (no loop, the least amount of parameters ...etc.)
Basically, the procedure receives a Table Of Record as input parameters and needs to merge it with existing table, the table of record is of the rowtype of the existing table.
I have difficulties to merge these data. Below is what I tried
CREATE or REPLACE PACKAGE BIZ_xxx_MERGE
IS
TYPE xxx_ACTIVITE_Type IS TABLE OF MyTbl%RowType INDEX BY BINARY_INTEGER;
PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
);
END BIZ_xxx_MERGE;
CREATE OR REPLACE PACKAGE BODY BIZ_xxx_MERGE AS
PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
) AS
BEGIN
MERGE INTO MyTbl
[code].........
View 10 Replies
View Related
Apr 23, 2010
how to select 1st record from duplicate vales in a table.
If we created one table with out primary key column In form in search block have uwi value and top_depth value when i enter uwi and top_depth value then when i click search button then it will display all values in master block.
but here duplicate values r there.
SQL> select rownum,uwi,top_depth,base_depth,test_start_date from well_pre_header;
ROWNUM UWI TOP_DEPTH BASE_DEPTH TEST_STAR
---------- ---------------- ---------- ---------- ---------
1 100 453.05 458.08 09-SEP-10
2 100 200 288 23-AUG-00
3 1001 200 289 25-AUG-01
4 1001 200 201 24-MAY-87
if uwi = 1001 and top_depth=200 and i will click search button it should be display 3 record & when i click next button then it will show 4th record.
View 3 Replies
View Related
Jun 26, 2008
I am using Pro*C/C++ Release 10.2.0.2 in HP-UX. But Pro*C/C++ application was written long back during oracle release 8. Now we are facing a problem like EXEC SELECT query is not fetching the records even though record is present in DATABASE.
This is not happening every time. This problem starts happening only after heavy use of the unix process.
For every request, unix process will fetch the record and updates the same at the end and process goes to wait mode to get the request again. Let say after 50 request, process is returned with no rows found error.
It started working fine only after restarting the process and problem starts again after 50th or 60th request. This problem we are facing only after upgrade to 10g.
View 1 Replies
View Related
Jun 10, 2013
I turned on ARCHIVELOG in my main database, anticipating GoldenGate replications and standing up a physical standby database. However, during the process I forgot to create a new SPFILE for my database, meaning that the next time the database cycled, the archive log destination would revert to the Oracle default (rather than the mount point I had set up to receive them) $ORACLE_BASE/fast_recovery_area
. I discovered this because my database hung..... In looking at the ALERT LOG, I discovered that the logs were going to fast_recovery_area, and it had run out of room. OK, so I did an alter system set db_recovery_file_dest_size= 5G scope=both; at which point in time I was notified that no active SPFILE existed! I moved out all of the logfiles that were in the fast_recovery_area, but the database seemed to still think that it was full.
I've managed to get everything fixed and put back together, but my remaining question is: After I freed up all sorts of space in the fast_recovery_area, why did/does the database still think that area is full?
View 3 Replies
View Related
May 3, 2010
I want basics of snapshot...creation,deletion,check...etc and what main difference in snapshot and view..?
View 2 Replies
View Related
Sep 6, 2012
how can i put my query inside my main query.
select *
from (select pa_request_id
,max(status) status
,max(approved_amount) approved_amount
,min(level_id) level_id
,max(req_amount) req_amount
from target_aggregation_attendee
group by pa_request_id)
where status = 'Approved')
My Main Query:
select
TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername,
TARGET_EMPLOYEE.GE_ID as requesterGEID,
TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName,
TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg,
TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg,
TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
[Code]....
View 4 Replies
View Related
Jul 25, 2012
on clicking select.show,while running the form data(DATE and APPLICANT_NO) is being accesed from local database but after uploading the form the data is not coming from main database,even if the data is present in the main database.
View 1 Replies
View Related
Nov 10, 2012
I am using oracle 11g database.
unforunatly i delete the data from main table. and i operated alter stmt.
now how do i retrieve the data..??
View 5 Replies
View Related
Oct 8, 2010
I have test in the main section of the layout in forms. I want to print the footer on the first page only. If I go to properties and select print object on first page, it still prints the footer in all pages. How can I force the footer on the first page only.
View 1 Replies
View Related
Dec 3, 2010
Application developed in form builder 6i.Now I want to create main menu for calling forms and reports.
Should I use buttons or drop down menu?
View 2 Replies
View Related
Feb 10, 2012
i have generate a report using report wizard but i want to know how join main section and head section , what ever you do in report wizard impact in main section ,i have done some head section ,when i run report, head section run first and main section in next page .. how to join both sections in single page output..
View 2 Replies
View Related
Oct 1, 2013
I have a main procedure in oracle which invokes many procedures inside it. These internal procedures also calls functions and procedures inside it.This continues to many levels.
For ex:
Proc A
call c
call d
end............
[code]...
This loop goes on and on . I want to find the names of all procedures invoked at run time when main api is executed. Is it possible to find all of them using toad ? Is there any tool for doing this ?
View 16 Replies
View Related
Dec 14, 2012
i'm trying to install Oracle Apex Listener but when i try to open the apex.war file is giving me an error:
$ java -jar apex.war
Failed to load Main-class manifest attribute from apex
i see in another topic that if i create a txt file inserting the MainClass: Main-Class that my error will disappear.
i'm using VM with OEL 5
View 4 Replies
View Related
Feb 29, 2012
I am looking at a performance issue at the moment and trying to replicate on a test system. I am initially looking at the impact of upto-date statistics on the main schema's objects.
For this I wanted to:
first run the batch with whatever stats were present in the database Flashback the db to before the batch . Gather stats Re-run the batch with updated stats and compare results.
However, I inadvertently ran the stats job before running the load the first time! I have the SCN from when the environment was set up like production (ie before the stats were run) so am I correct in saying that if I flashback to this point then the stats will be "old" and I can just run the batch then? I know I can verify this when I Flashback the database by looking at LAST_ANALYZED on tables etc but it would be good to know this before hand as it's a 12 hour batch.
View 1 Replies
View Related
Aug 17, 2012
I need to refresh my main page after closing the popup page displayed by skillbuilder plugin
apex 4.1.0/Oracle 11g Ent.
View 9 Replies
View Related
Jul 13, 2010
I have a procedure which mainly run queries on a Table which has nearly 9.5 million recodes. This procedures takes nearly 15 min to complete execution on our main database. I exported and imported the schema to our backup database and the same procedure just took 3 seconds to complete.
I tried to analyze the table in our main database and tried to execute the procedure again but did not show any improvements. ANALYZE TABLE DN_ACTIONS COMPUTE STATISTICS;
I am not sure computing the statistics for all the tables in the schema will work. I also checked there is enough disk space where oracle data files are stored. I am also turning on the sql trace to see what sql statements in the procedure is taking longer time.
View 8 Replies
View Related
