PL/SQL :: How To Compare Two Rows And Retrieve The Values
Aug 15, 2012
I have two tables which have identical schemas, one table (tbl_store) is used to hold the latest version, and the other table (tbl_store_audit) holds previous versions. When the latest record is updated, it is inserted into the tbl_store_audit table as a revision, and the updated details are used as the latest record.
For example: The latest version is held in tbl_store, however the tbl_store_audit may hold 5 records which are the past records used before changes were made - these are seen as revisions.
I want to be able to compare what has changed between each revision in the tbl_store_audit table. For example: Out of the 10 columns, the change between revision 1 and revision 2 was the size from XL to XXL. The change between revision 3 and revision 4 was the size XS to M and price 4.99 to 10.99, and so on.
Eventually i will create an APEX report that will show the user the revision number and what was changed from and to.
I seen in a previous post i need to note my oracle version: Oracle version 10.2.0.4.0
View 16 Replies
ADVERTISEMENT
Oct 28, 2013
I am searching the simplest way for ad hoc MINUS.I do:
SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
View 6 Replies
View Related
Apr 12, 2013
The Table having 3 columns
col0col1 , col2
P112
P112
P102
P113
P115
I want to retrieve the changes rows only.
Output like
col0col1 , col2
P112
P102
P113
P115
View 5 Replies
View Related
Oct 6, 2010
I have an audit table FEE_RULE_AUD with the following structure.
FEE_RULEVARCHAR2(10 BYTE)
AUDIT_DATETIMESTAMP(6)
AUDIT_ACTIONVARCHAR2(30 BYTE)
AUDIT_USERVARCHAR2(8 BYTE)
NAMEVARCHAR2(30 BYTE)
FEE_BASISCHAR(1 BYTE)
FEES_ONCHAR(1 BYTE)
SECURITYCHAR(10 BYTE)
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.
get the two rows and compare ?
View 16 Replies
View Related
Dec 4, 2009
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;
View 3 Replies
View Related
Aug 29, 2012
I have an requirement like below and would like to have SQL for that.
Source Table:
EMP_NO EMP_CODE
1 'A'
1 'D'
1 'E'
1 'F'
2 'S'
2 'A'
2 'W'
2 'Q'
3 'A'
3 'T'
3 'D'
3 'E'
4 'D'
4 'A'
I want to load only data which has EMP_CODE as A and doesn't have subsequent 'E' or 'F' in it. In the above source you can see EMP_NO 2 and 4 satisfy the condition and rest wont. So i want the output data like below.
Desired output:
EMP_NO EMP_CODE
2 'A'
4 'A'
View 4 Replies
View Related
Oct 1, 2013
create table a(sourcerow number(2), test_level number(2), dpn varchar2(1), qty number(5)); T
he insert scripts are as follows:
insert into a values(1,3,'Y',5); insert into a values(2,2,'Y',4); insert into a values(3,3,'N',3); insert into a values(4,4,'Y',3); insert into a values(5,1,'N',6); insert into a values(6,2,'N',5); insert into a values(7,2,'Y',4); insert into a values(8,3,'N',2); insert into a values(9,4,'Y',2); insert into a values(10,1,'Y',3); . SQL>select * from v$version; Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
My logic should have the output as follows without 5th column: sourcerowleveldpnqtyremark13Y522Y4This row in main table shouldn't be displayed in the query result as it has
dpn='N'44Y3*3=9In this row qty=9 will bedisplayed.
It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.this row will not be displayed as dpn='N'this row will not be displayed as dpn='N'72Y4*6=24in its previous row level value is same so it will check the previous to previous row where level is 1(parent of current row) and dpn='N' ,then it will multiply the qty of that row with current row and display the value in qty column.this row will not be displayed as dpn='N'94Y2*2=4In this row qty=4 will be displayed.It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.101Y3It will not check for the previous rows as level 1 doesn't have any parent.
View 5 Replies
View Related
Jul 10, 2012
Suppose you have the below table, same ID's occur for same month as well as different month
ID Month Value
--------------------------------------------------------------
226220 201203 100
1660 201204 200
26739 201204 1010
7750 201205 31.1
I need a query to determine the below laid result
ID Month Prior_month_value Prior_Month Value
----------------------------------------------------------------------------
1234 201203 10 201201 100
3456 201206 56.1 201204 78
View 10 Replies
View Related
Jul 23, 2012
I want to compare two tables , and delete the common rows from the first table
Here is what i have done :
Create table test1(Test1C1 Number,
Test1C2 varchar2(50));
Create table test2(Test2C1 Number,
Test2C2 varchar2(50));
Insert into test1 values(1,'testdata1');
Insert into test1 values(2,'testdata2');
Insert into test1 values(3,'testdata3');
[code].......
it deletes all the records from Table test1. What should I modify here ? or should I write a different query ?
The desired contents in table test1 will be
2 testdata2
4 testdata4
6 testdata6
8 testdata8
10 testdata10
View 5 Replies
View Related
Aug 28, 2013
How can I compare two rows from the same table and show the field and the difference?
Example:
select ename, job, mgr, hiredate, sal, deptno
from EMP t
where t.empno = 7782
or t.empno = 7788
ENAME JOB MGR HIREDATE SAL DEPTNO
1 CLARK MANAGER 7839 6/9/1981 2450.00 10
2 SCOTT ANALYST 7566 6/9/1981 3000.00 20
My report comparing fields should be:
FIELD BASE COMPARE DIFFERENCE
ENAME CLARK SCOTT --
JOB MANAGER ANALYST --
MGR 7839 7566 --
SAL 2450 3000 550 --(Show difference only for numbers)
DEPTNO 10 20 --
View 9 Replies
View Related
Dec 1, 2010
My table "xxbuyer_det" contains the below data.
buyer_id Invoice_numPriceItem
------------------------------------
148137110A
149314211B
149314315C
149314415F
148120520D
148120625E
My requirement is to select the rows which have same buyer_id more than or equals to two times (>=2). I am trying with the below query. But this query is failing(not retrieving any data) as i have used group by class and count value is 1 for every record.
SELECT BUYER_ID, INVOICE_NUM, PRICE
FROM
xxbuyer_det
GROUP BY
BUYER_ID,
INVOICE_NUM,
PRICE
HAVING COUNT(*) >=2
View 7 Replies
View Related
Jun 13, 2013
how to retrieve deleted rows in oracle?
View 7 Replies
View Related
May 10, 2012
How can I create a calculated field in one sheet to lookup a corresponding value in another sheet?
e.g. I have a person ID in one worksheet, and I want to look up the email address from a matching Person ID in another worksheet
View 1 Replies
View Related
Jun 15, 2013
I have a problem with Between clause used in where statement to compare two string variable.
Query is like this,
select item_code, item_deacrption
from itm_master, invoce_det
where im_code = item_code
AND invd_item_number BETWEEN (:startNum) AND (:endNum)
Here invd_item_number is a DB field and is of type varchar2(41), and (:startNum),(:endNum) are of same type.
now invd_item_number has one value '001003002001'
if we give :startNum = '001003001002' and :endNum = '001003004006'
:startNum and :endNum is composed of separate field values (ie, 1st 3 character shows color code, next 3 for catagory, next 3 for size etc). These codes are entered separately and are combined at run time.
it is still fetching the invd_item_number with value '001003002001'. (the last set of character(type code) in the :startNum is greater than invd_item_number's type code value. But it is smaller than the previous code (size code), that's why it is fetching).
But how can i get around this as i don't need that value to be fetched.
View 7 Replies
View Related
Jun 14, 2010
How to retrieve duplicate rows from a table, suppose i don't know any column names. Without hard-coding column names I need to find.
View 5 Replies
View Related
Mar 7, 2012
I have created a table called ct_temp and it's structure is:
create table ct_temp(id char(2),srs number(3),amt number);
Result : Table Created
I now insert the following rows into the table:
id srs amt
1 62 30000
2 65 50000
3 65 70000
4 65 80000
5 62 16000
6 65 10000
7 65 100000
8 65 10
Commit
Then I issue the following query to retrieve data based on a specific criteria (Actually I have condensed the table and data because in Production, I have large number of rows; so for simplicity I am using 8 rows with one table)
criteria : I then want to retrieve the following:
for srs = 62, all values that is greater than 10,000
Answer: select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab
where decode(srs,62,ab.amt)>10000
Works like a charm and retrives the results.
Now comes the big issue
I want to retrieve the values for srs = 62 which is greater than 10,000 and at the same time I also want to retrieve the values for srs = 65 which is less than srs = 62.
Typically I wrote the query as:
select decode(srs,62,ab.amt)temp1 ,decode(srs,65,ab.amt)temp2 from ct_temp ab
where decode(srs,62,ab.amt)>10000
and decode(srs,65,ab.amt)<decode(srs,62,ab.amt)
I should atleast get one row for srs = 65 which is id # 8 but it displays blank rows or "no rows returned".
I am actually preparing a ad-hoc report for the business analyst and stuck at this step due to which I am unable to proceed any further. I have used DECODE function because of the requirement specified by Business Analyst.
I have tried the following in the office:
using EXISTS operator = no luck
using INLINE VIEW = no luck
Is there any way around?
View 26 Replies
View Related
Nov 2, 2011
We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:
emp_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:
WITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary
)
THEN 0
ELSE 1
END
) changed_ind
FROM emp_salary
WHERE emp_id = 12345
[code]....
The cost of this query is 1677 and it is taking around 60 msec to complete. When I run this query for around 2000 employees in a loop it is taking around 3 minutes to complete.
The main bottleneck of this query is in the with clause where I am processing the entire history instead of stopping after first change.
View 7 Replies
View Related
Feb 5, 2013
Objective: I need to compile a final string by concatinating the unique values from different strings.
Here is the script to create tables and data.
Create table temp_acronyms(id number, acronym varchar2(30);
insert into temp_acronyms values(1, 'ABC');
insert into temp_acronyms values(2, 'DEC//NOFO');
insert into temp_acronyms values(3, 'CBK//FO TO USA');
insert into temp_acronyms values(4, 'DEC//NO ENTRY');
insert into temp_acronyms values(5, 'ABC//NOFO');
COMMIT;
select * from temp_acronyms;
ID ACRONYM
--- --------
1 ABC
2 DEC//NOFO
3 CBK//FO TO USA
4 DEC//NO ENTRY
5 ABC//NOFO
I need to store all the unique strings from the acronyms for id's 1,2, 3, 4 and 5 into a variable. doesn't matter even if it is through database procedure.
my final string should have the values as below
ABC//DEC//NOFO//CBK//FO TO USA//NO ENTRY
View 6 Replies
View Related
Jun 6, 2008
I am trying to retrieve all rows with the date of May 23rd, 2008. Our data is stored with the date and time.
How do I format the where clause so that the time is taken off? I am still a newbie with oracle and what I have is this:
Select Distinct Table1.*, Table2.*
from Table1, Table2
where Table1.file_name = Table2.file_name
and Table1.downloaded_date = Table2.date_loaded
and downloaded_date = '23/May/2008'
Of course, it's the last line not working because I don't know how to format it and no rows are retrieved when there are definately May 23rd rows stored in there.
View 6 Replies
View Related
Jul 25, 2013
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
View 4 Replies
View Related
Jul 19, 2010
I would like to know how can I retrieve both values below in rms forms 9i.
Both values pertains to one id.
id - 00012345 (with zeroes)
id - 12345 (w/out zeroes)
The id is use as one of earch criteria.
View 3 Replies
View Related
Oct 12, 2012
I have a table something as below
Things_t
Things Characteristic Char Value
Item 1 Colour Red
Item 1 Packaging
Item 2 Shape Square
Item 2 Brand Spunk
Now i want to reterive an item with none of its char values as Null. Using the query “ select distinct things from things_t where char value is Null ” will fetch the item 1 also together with item 2. i want to fetch a record from thing for which none of the char values are Null such as Item 2.
View 9 Replies
View Related
Jul 7, 2011
I need a generic query to generate total # of records for each table in a schema, total # of records that are not null for each column in the table, and total # of records that are null for each of those columns in those tables.
ex:
the output should look like this.
owner schema table_name total# recs in the table, column_name,
------ ------ ---------- ------------------------- -----------
# of records not null # of records null
---------------------- --------------------
View 12 Replies
View Related
Apr 23, 2008
how to retrieve values if user entered in Mixed case means (case insesitive)?
We don't have control when user enters values and it might be SMITH or Smith or smith or SmIth.
How can write the query to get this kind of mixed case data?
View -1 Replies
View Related
Jun 1, 2010
I have a table called table1 and its structure is as follows:
rtrn_id number,
entp_abn number,
litm_line_item_nbr number
country_code varchar 2(10)
I am trying to find retrieve value/row where for a particular rtrn_id , the possibility of having a country code of "CA"(for Canada) and "US" or "HK" (US or Hong Kong respectively) exists.
I framed my query the following way and it does not retrieve any result in Production and I believe there are a few values/rows in the "table1".
The query framed is:
Query 1:
SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code
FROM table1
where schd_version_yr = 2010 and prog_program_cd = '01'
and sched_nbr = '000' and (litm_line_item_nbr = '023' and country_code 'CA')
and (litm_line_item_nbr = '016' and country_code = 'CA')
The above query does not retrieve any rows
Where as if I run a query individually it retrieves tons of rows
(Individual query :
SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code
FROM table1
where schd_version_yr = 2010
and prog_program_cd = '01'
and sched_nbr = '000'
and (litm_line_item_nbr = '016' and country_code = 'CA')
-----Retrieves tons of rows as the country code = Canada for line item # 16
SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code
FROM table1
where schd_version_yr = 2010
and prog_program_cd = '01'
and sched_nbr = '000'
and (litm_line_item_nbr = '016' and country_code 'CA')
-----Retrieves tons of rows as the country code IS NOT Canada for line item # 23.
There is a possibility that there are rows that have country Code as Canada and not Canada for a particular ID whose line item numbers could be 16, 23.. is there any better way to frame my Query1 to retrieve values/rows?
View -1 Replies
View Related
Apr 19, 2010
My Form consists two Hierarchical trees. When I select a node from first tree and I press Move Right (>>) Button the selected node should move to the second tree. Similarly when I select a node from second tree and press move Left button(<<) it should move to the first tree.
I also want to know want to insert values Into New Table using node values from The Displayed Hierarchical Tree. How to retrieve values using populate_Group_from_tree and insert into table.
View 2 Replies
View Related
May 27, 2013
i have a table with a clob column and i have 150 records i want retrieve distinct values from the clob using distinct operator on clob will not work
View 1 Replies
View Related
Jun 4, 2012
CREATE TABLE emp
(
code NUMBER(4),
name VARCHAR2(15),
salary NUMBER(5),
ovt NUMBER(4),
sal_mon DATE,
dept number (3)
)
[code]....
9 rows selected.
Required Output is
CODE NAME SALARY OVT SAL_MON DEPTMerge Codes
---------- --------------- ---------- ---------- --------- ---------- -----------
1068 ALI 18500 2385 31-OCT-11 1021068,1793
1908 ASGHAR 17000 1900 31-OCT-11 1021908,1802
1960 ZAHID 16000 775 31-OCT-11 1021960,1952
1188 RAZA 9500 2910 31-OCT-11 1111188
1275 RAHIL 15300 3480 31-OCT-11 1111275,1176
Merge 02 rows of same department having sum(salary) <=20000. select those rows who have lower salary.
View 10 Replies
View Related
Sep 12, 2013
I'd like to get all rows for one particular id. I have data stored in two different tables. I'd like the values to be "denormalized" such that for each unique date I want the values(washer_val, widget_val) for each id from each table to be displayed(or null if no data for that date).
I'm not stating the exact requirements correctly
EVENT_DATEWASHER_VALWIDGET_VAL
9/12/20131, 2 1, 2
9/13/20132
9/14/2013 1
I've created two different example views("tables") of my data with a "with" statement(washers, widgets). I tried to denormalize each set of data (washers_val, widgets_val) and then get all the unique dates (unique_dates). I then join the "views" and display all possible rows with their data. This is the type of output I want BUT is there an easier way to achieve it??
with washers as
(select 1 id, trunc(sysdate) event_date, 1 val
from dual
[Code]....
View 10 Replies
View Related
Sep 3, 2010
I want to get the values and put them into html template since i want to configure mime settings. My table's name is rawticket_voip and it has 150.000rows and 20 columns so instead of COL1, COL2 what should i write there? How can i get the spesific row's and column's value and how can integrate loops in this html?
'<html>
<table border="1">
<tr bgcolor="Light Blue">
<td>COL1</td>
<td>COL2</td>
[Code]...
View 4 Replies
View Related
