I am working on a report which shows a summarized data from 4 different tables. I have 2 columns which shows certain amount.
The data in both the columns comes from different table.
I want to compare these 2 columns for which i placed a formula column in data model. the code goes this way:
function CF_RECV_PAYFormula return Character is V_RESULT VARCHAR2(50); begin IF :TOTAL_IOU_AMT >= :EXP_AMT THEN
[code].....
The compilation is successful. but when i am running the report i am getting an error Rep-1517 :Column 'CF_RECV_PAY' references column TOTAL_IOU_AMT and EXP_AMT has incompatible frequency.
I'm trying to collect histograms for column COL_C of table TAB_A(150K records), So an index "BAD_IDX" will *not* be used in a query when the value is not selective.
This is my query:
SELECT COL_A, COL_B , COL_C , COL_D , COL_E , COL_F FROM TAB_A WHERE COL_A = 050 AND COL_B = 13012345 AND COL_C = 0 AND COL_D = 0 AND COL_D >= '07/23/2013 00:00:00' ORDER BY COL_E ASC;
Now, I have index "BAD_IDX" on columns (COL_C, COL_E ).and the distribution of values looks like this:
select COL_C, count(*) FROM TAB_A --very not selective for 0, selective for the rest, also no histogram group by rollup(COL_C) order by 2 desc;
and the result is 20k row long (20k distincts), So I'll post just the top part of it:
Now, the problem with the query was that "COL_B = 13012345" was the most selective predicate, And an index for it did not exist, so the index "BAD_IDX" is used, and is scanning 86k records (all the "0" value records for column COL_C)!
So, I created an index
Create index GOOD_IDX on TAB_A(COL_B) compute statistics;
However, that BAD_IDX index is still being used! I've thought that maybe it's because the lack of histograms for the column COL_C.I've also understood from documentation I've read that the suitable histogram type is TOP FREQUENCY, Because although I have 20k distincts here for the column COL_C, what does the difference is the 86k records of value 0.
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).
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
I can get a list of invalid objects, know how to recompile them but I was wondering if there was a way to query the date/time of when an object went invalid.
I'm working on a 10.2.0.4.0 (Enterprise Edition) database running on SLES10.
I have 2 tables that I am going to use. These are AUDIT_LOG, and AUDIT_PROPERTIES. AUDIT_LOG tells me that who made the change and at what time. AUDIT_PROPERTIES basically tells me that what the old value was and what the new value is.
These tables operate at the field level. For example, I have a module called BUG. Now a BUG (as a module entity or record) has several fields. There are around 25 fields. But I am interested in only 3 of these fields- these are BG_STATUS (Status), Priority (BG_PRIORITY) and Severity (BG_SEVERITY). This is my requirement:
Between a given period of time (let us say the month of January), give me the count of bugs that, 1) had an "Open" Status, 2) Were of "Medium" Severity, 3) And had a "Low" priority....each of these 3 conditions must be true simultaneously, means, that to be a "qualified" bug, the bug must be Open, Medium severity and low priority at the same given time within the month.
Below are the query and an image of the data looks like.
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.
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')
I have a table CISCOWORKS that contains dumps from Ciscoworks. Now I want to make a history table CISCOWORKS_HISTORY that contains all information I no longer need actively (entries that have the same CISCOWORKS_MAC as newer entries).
The two tables are like this, exactly the same.
DROP TABLE CISCOWORKS IF EXISTS; CREATE TABLE CISCOWORKS ( CISCOWORKS_IDNUMBER(9,0), CISCOWORKS_MACVARCHAR2(20 BYTE), CISCOWORKS_SWITCHVARCHAR2(10 BYTE), CISCOWORKS_PORTVARCHAR2(10 BYTE),
[code].....
I guess I will have to check the INSERTED table and check every entry to see if there is a entry in the CISCOWORKS table with the same CISCOWORKS_MAC. If so, insert an entry in CISCOWORKS_HISTORY with the attributes of the CISCOWORKS entry, and then delete the CISCOWORKS entry. how would I check every entry separately?
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
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.
I just applied the Jan 2013 SPU and the patch succeeded with no errors; however, when I query DBA_REGISTRY_HISTORY after running the catbundle.sql, the table is not reflecting the new patch. I checked through the catbundle log files and did not see any errors. What could be wrong?
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)?
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
I have written a windows service which grants and revokes based on request. How to trace in oracle data dictionary if those privilages were granted and at what time to whom and by whom. I mean is there any table which can be queried for past privilages granted to users.
SELECT * FROM ( SELECT 1 ORDR_ID, TO_DATE('01-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 5 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL SELECT 1 ORDR_ID, TO_DATE('15-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'KPD' Colr_CD FROM DUAL UNION ALL SELECT 1 ORDR_ID, TO_DATE('16-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'ALD' Colr_CD FROM DUAL UNION ALL SELECT 2 ORDR_ID, TO_DATE('02-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 9 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL SELECT 2 ORDR_ID, TO_DATE('05-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 10 SIZE_, '' Colr_CD FROM DUAL UNION ALL
[code]....
Where every first row is the initial entry for an order and following rows are the changes done to the order with modification date
11.2.0.3 EVENTS: Null(blank) or called a 'null event' See link. Checked the docs. I don't see anythingI think a null (blank) is for an idle wait since per the V$ACTIVE_SESSION_HISTORY spec these are not record. Am I correct?What is a 'null event'. I see a paper from Oracle magician dating to 9i that says this is an 'oracle goof'. Descriptions of Wait Events
I would like to know 2 days back lock conflicts session information which user held the lock,sql text,when the lock released. any sql query or view in oracle 11g db.
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 ).
Can I extract data from our Oracle server regarding the no. of sessions per day? But the problem is that our Boss wants a previous dates from April 11-17, 2011.