1) Sal_master
structure is voc_no varchar2(7),voc_date date
2) sal_detail
structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)
3). delivery_master
structure is voc_no varchar2(7),voc_date date;
4) delivery_detail
structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)
I want to compare these four tables i have insert 10 rows in sal_master and sal_detail tables and 5 transaction in delivery tables how to compares 10 records of sal_master,detail with delivery_master and detail if not exist in delivery_master and detail tables then display only sal_master,detail records for example
Voc_no Sale Qty Deliver Qty Remaining Qty
S000075 10 5 5 if data not found from delivery master and detail then answer must be
S000075 10 0 10
I have to compare 2 tables on a columns's value.The 2 tables have same column as Regn_no.I just need to have the result in True Or False from a Query if the particular value of Regn_no is found in both the tables.
Temporarily I using a bit different and not good query which is as follows:
select count(*) from tab1 where regn_no in (select regn_no from tab2) and regn_no = 'UP78AN7890';
i wanted to compare a date in one of my tables to sysdate. I have a table reservation and a field in it is Date Reserved From, i wanted to compare this to sysdate and returned the results
I am working in a reporting project.We have different environments.After migration of data base script from one server to another we always need to crosscheck whether newly added columns have been properly migrated or not.
any database script to address the same thing. Last but not the least we have servers with TNS entries. how we can connect to different server while present in one specific server.
Desc Table A (account) Account1_id Account2_id name, empid
Table B (Bill ) BillNo Advertiserid agencyid total vvalue
I need to pick up total value from table B where the unique combination of advertiser-Agency id is the same as the given account1_id -Account2_id combination in table A for each employee id.
In other words my output should be like
Empid | Account_id (should be same as advertiserid)| Account2_id (same as agencyid) | sum(total_value) for this adv-agency combination.....
objective: Get the total value from table B for each unique account1-account2 combination (advertiser-agency in other words) .
I am not sure if I should use a correlated subquery or how to handle the situation....Right now I am just checking the two columns separately like this:
select.......from a,b where b.advertiser_id = a.account1_id and b.agencyid = b.account2id
Is it correct to do so? I have a feeling that I am missing something if I join them separately like this.
I need to alter a table to check that the data in a column is contained in a similar column in another table.
I have a STORES table and a STORE_REP table. Both tables have a column REP_ID.
I need to add a CHECK constraint into the STORES table to make sure that the info entered into its REP_ID column matches an entry in the STORE_REP table.
Both have a NUMBER(5) data type.
Will it make any difference if the REP_ID column in the STORE_REP table was originally created with a VARCHAR2(5) data type and was later converted to NUMBER(5), while the REP_ID column in the STORES table was created as NUMBER(5) when that table was created?
Can we compare in SQL *Loader control file by using WHEN Clause.I want to load the data when in_no greater than 1300000000. While running below control file i am getting error as:
SQL*Loader-350: Syntax error at line 5. Illegal combination of non-alphanumeric characters WHEN (in_no >= '1300000000') Here is the control file.
ex:
Load Data infile * discardfile 'test_when.dsc' truncate into table test_when WHEN (in_no >= '1300000000') fields terminated by ',' (a,b,c, in_no) [code]....
I have requirement wherein i need to compare two strings (with multiple words) and it should return the %(percentage) of comparison. e.g. "oracle infotech" and "infotech oracle" are 100% match
INSERT INTO CHK VALUES(TO_DATE('25-JUN-10 04.12.57 AM', 'DD-MON-YY HH:MI:SS AM'));
COMMIT;
[Code].....
TZ_OFFS TZ_OFFS GMT_TIME ------- ------- -04:00 +00:00 25-JUN-10 04.12.57.000000000 AM
EST_TIME ----------- 25-JUN-10 12.12.57.000000000 AM EST
9 am ---- 25-JUN-10 09.00.00.000000000 AM EST
8 pm ----- 25-JUN-10 08.00.00.000000000 PM EST
OUTPUT --------- 30-JUN-11
My doubt here is that even though dt_request_datetime is between 9 am in EST timezone and 8 pm in EST timezone the query output is not according to that.
and want to check if there is an event in my EVENTS table that occurs in the same dd/mm/yyyy as the input, and can disturb the input event times. means:
input.event_start_time is between EVENTS.event_date and EVENTS.event_end_date and input.event_end_time is between EVENTS.event_date and EVENTS.event_end_date
but to compare only the hours here! (HH24:MI) because the date (dd/mm/yyyy) is checked before..
I don't know how to cut only the hours out of the date and compare them, and don't know how to write the whole function.
I've a table (RelationshipX) with two columns with following values. The table represents the relationships. When I run the following query, It will give me all the combination of relationships...however, I need to get 15 unique as defined below.
I run the following query to get below of all the combinaiton (note, I am opting out those six rows which matches to each other) select a.Col2 as Col1, b.Col2 as Col2 from RelationshipX a, RelationshipX b
where a.Col1 = 11060 and a.Col1=b.Col1 and a.Col2 <> b.Col2 order by Col1, Col2 [code]....
HOW can I modify my SQl so I get only 15 unique relationship records. (For example two UNDERLINE rows are technically same, and there are total 15 of them)???.
is it possible to compare two time values in oracle sql ie there is a column say 'tot' with values 8:29,11:35 etc(8hrs29 mins etc) can i compare this column with 03:00hrs i tried select case tot>=3 then 1 as days end from tablename;
Currently,I have this objective to meet. I need to query the database for certain results. After done so, I will need to compare the records: For example: the query return me with 10 rows of records, I then need to compare: row 1 with 2, row 2 with 3, row 3 with 4 ... row 9 with 10. The final result that I wish to have is 10 or less than 10 rows of records.
I have one approach currently. I do this within a function, hand have the variables call "previous" and "current". In a loop I will always compare previous and current which I populate through the record set using a cursor.
After I got each row of filtered result, I will then input it into a physical temporary table. After all the results are in this temporary table. I'll do a query on this table and insert the result into a cursor and then returning the cursor.
The problem is: how can I not use a temporary table. I've search through online about using nest tables. but somehow I just could not get it working.
how to replace the temp table with something else? or is there other approach that I can use to compare the row columns with other rows.
I need to compare columns of two tables in oracle 10g. If columns of both tables match with each other, then i need to create new table dynamically with matched column name and datatype. For example, table1 contains name, ID, Phone_no and address table2 contains name, Id, address, area and pincode. now , i need to create table3 which will contains name, ID,address, Phone_no, area and pincode as columns( I mean matched columns should not be repeated in table3). how to do this..
I have oracle base version and our client version. In oracle base version we have "products.fmb" file In our client version "item.fmb" file.it was created using the base version.After that many people made changes to client version. Now I want to compare those two files. But in our system there is no form builder. Is it possible to compare without FORM builder.
I have to generate a report for the audit with in the dates specified(range). I got the set of record for the specified date range by using the following query:
select * from fee_rule_aud where TO_NUMBER(TO_CHAR(TRUNC(audit_date),'YYYYMMDD'))>20090629 and TO_NUMBER(TO_CHAR(TRUNC(audit_date),'YYYYMMDD'))<=20100710 order by fee_rule,audit_date
this query returned some five records, now I have to iterate through this and compare 1st and 2nd row in first iteration (1st row will have the new value and 2nd row will have old vale). If there is any difference then insert into audit_log table which has the following structure:
Fee_rule , column_name, old_value,new_value
This process has to repeat for all the 5 rows like comparing 1st,2nd rows and 2nd,3rd rows and 3rd,4th rows and so on if it has more rows.
2114 rows I got the same result if I use NVL(o.scn,0) rather than TO_NUMBER(o.scn). I can't find out why this happens. Obviously, the ">" condition doesn't match when the difference is too small.
AFAIK, ora_rowscn is a NUMBER while my "scn" column is a NUMBER(12) (which should be sufficient to store my DB or remote DB's SCN).
We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:
CODEemp_salary ---------- emp_id NUMBER(15) NOT NULL effective_date DATE NOT NULL salary NUMBER(15) NOT NULL
Primary key - emp_id, effective_date..This table is yearly partitioned...I have to find out how long the salary is not changed for an employee from given date, and last salary. I am using below query to do this:
CODEWITH salary_tab AS (SELECT effective_date, salary, (CASE WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) = salary
[code]....
For emp_id 1, if we ran this query for 10/31/2011, then it has to compare the 10/31 salary with 10/29 and do the same until the salary mismatches. In this case, salary salary mismatch occurs on 10/20, so the stale salary period is from 10/31 to 10/21 which is 7 days.Below query will give that result:
CODE WITH salary_tab AS (SELECT effective_date, salary, (CASE WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) = salary
I've been assigned a task at work that consists of creating a trigger on a table. This table is used to store temporary query results. I'm trying to make a trigger to clean the table so old results don't accumulate.
There is a column named DATE_UPDATED that stores an Oracle Date for when the row was inserted/updated. If the row is not updated in 12 hours, I want to delete it.
Here is what I have so far.
CREATE OR REPLACE TRIGGER clean_tableName AFTER INSERT OR UPDATE ON tableName
BEGIN
END clean_tableName;
As you can see, I don't have an actual function body yet. What would be the best way to accomplish this? Should I declare a variable to store the current Date+12 hours and then just compare that in a delete statement?
I'm thinking something like:
DECLARE laterDate DATE := SYSDATE+12; // Not sure how to add 12 hours to a date. BEGIN DELETE FROM tableName WHERE DATE_UPDATED > laterDate; END clean_tableName;
But I'm not sure how to add 12 hours to the current date. Is there a way I can do this without defining a variable?
I recently discovered that there was a difference in my QA and prod environments, which I have since rectified.
SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual; DBMS_STATS.GET_PARAM('METHOD_OPT') -------------------------------------------------------------------------------- FOR ALL COLUMNS SIZE 1 SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual; DBMS_STATS.GET_PARAM('METHOD_OPT') -------------------------------------------------------------------------------- FOR ALL COLUMNS SIZE AUTO
I found a script that allows me to compare values from v$parameter
set pagesize 1000 col name format a28 col local format a20 col remote format a20 select local.name, local.value local, remote.value remote,
[code]....
Is there other SQL code or another methode out there that would find differences in my DB's such as the method_opt setting, which don't appear in v$parameter.
I am trying to set the min date if there is no gap between dates.compare previous date2 value with current date1,if they are same then my new date will be min(date1).
