SQL & PL/SQL :: Displaying All Other Columns If Column Is A Dupe
Nov 22, 2010
I am able to get a list of all the duplicate location_code values.
select count(*), location_code
from ZZPN_PARKING_PORTFOLIO_801_V
group by location_code
having count(*) > 1
But how can I select all the other columns from ZZPN_PARKING_PORTFOLIO_801_V where the location_codes are duplicated.
select * from apps.ZZPN_PARKING_PORTFOLIO_801_V
where location_code in
(
select count(*), location_code
from apps.ZZPN_PARKING_PORTFOLIO_801_V
group by location_code
having count(*) > 1
)
order by location_code
I need to display the record when all the columns have matching records,If one of them doesn't match then it should not be displayed
The following is the example
WITH t1 as (select 159435 ky from dual) ,t3 as (select 78 id ,'Z-' rk,'SL' cd from dual union all select 78 id ,'Z+' rk,'SL' cd from dual union all select 78 id ,'Z-' rk,'SL' cd from dual union all
[code].....
In the above data bg.rk= 'Z-' but one of the record in T3 is having Z+ ,So this should not be displayed (same condition with column CD) in this example cd column in both table matches I tried like above query but i'm getting the record.
I have a view that is displaying records that are almost duplicates with the exception on one column.
Distric name id status logtime ------- --------- ---- ------ --------------- ATLANTA John 007 A 4/5/2011 8:20 ATLANTA John 007 B 4/5/2011 8:20 ATLANTA John 007 D 10/5/2012 8:20 JERSY C. Eric 111 C 8/1/2012 4:45 PLANO Jake 522 A 1/2/2012 7:22 DENVER Steve 222 C 11/24/2012 9:00 DENVER Steve 222 D 11/24/2012 9:00
I have a situation where I need to only display one status per name at a specific time.I would like to return the following:
Distric name id status logtime ------- --------- ---- ------ --------------- ATLANTA John 007 A 4/5/2011 8:20 ATLANTA John 007 D 10/5/2012 8:20 JERSY C. Eric 111 C 8/1/2012 4:45 PLANO Jake 522 A 1/2/2012 7:22 DENVER Steve 222 C 11/24/2012 9:00
So I decided to include a rank for the different statuses to identify the highest rank status and eliminate the lowest rank status records I don't want.
Select distric,name,id,status, case status when 'A' then 1 when 'B' then 2 when 'C' then 3 else 4 end statusrank, logtime from tst; [code]...
I would like to return the record with the highest statusrank when they duplicates with the exception of the status column.Is there a built in function I can use for this ? The statusrank can be returned in query. My final return would be:
Distric name id status statusrank logtime ------- --------- ---- ------ ---------- --------------- ATLANTA John 007 A 1 4/5/2011 8:20 ATLANTA John 007 D 4 10/5/2012 8:20 JERSY C. Eric 111 C 3 8/1/2012 4:45 PLANO Jake 522 A 1 1/2/2012 7:22 DENVER Steve 222 C 3 11/24/2012 9:00
I just ponder how to write SQL or PLSQ if I'd like to get more columns into the one column.
For example:
I have table T1 where are 10 records. One columns TEXT is VARCHAR2. And I need to get by SQL/PLSQL one record with the new column VARCHAR2 where will be written 3 records as I wrote.
my need is to perform merge - update when id column is matched, but one of others columns not.When id column is not matched then I perform insert.
It works fine for matched or not matched id column.
Commented code is my try to perform check for others columns, The code should not update when all columns match. It should update only when on of columns doesn't match (except id column of course, because it's key column).
begin merge into copy.table1 rr using ( select ID , DEALID , ESTIMATIONDATE , BOUNDOVERESTIMATDATE , ESTIMATIONTYPEID , MARKETAMOUNT , LIQUIDATINGAMOUNT , [code]....
I want to add some new columns in an existing table that has over 10 millions rows, but not at the end of existing columns, my requirement is such i want new columns between existing existing columns. Is it possible.....
I have limited permissions and am unable to create temp tables.So I would like to use a cursor to "create" a table of sorts then access/query it. But this "table"/cursor would have no column names so how do I refer to the columns? Is there a way to refer to a column by column number rather than column name in a query:
select column1 from tablename where column2 = 'abc'?
Is there a way in a query/update/insert to refer to a column by column number rather than column name?
declare cursor c1 is select 'abc', '8-Apr-2013', pk_id from EMPLOYEE where pk_id = '153' UNION select '1xyz', '4-10-2013', pk_id from EMPLOYEE where pk_id = '154' c1_val number;
I need to update the current column with sum of the previous column values. Following are the creation scripts
DROP TABLE TEST_LOG; CREATE TABLE TEST_LOG (
[Code]....
Above query is working fine to retrieve the previous column values.But when we are updating the SUM_PRE_COLS column with those values it's not working fine.
I tried by using the following query
UPDATE TEST_LOG T SET SUM_PRE_COLS = ( SELECT LAG(T2.KEY0, 1, 0) OVER(ORDER BY T2.KEY0) + LAG(T2.KEY1, 1, 0) OVER(ORDER BY T2.KEY0) FROM TEST_LOG T2 WHERE T2.ROWID= T.ROWID);
Given a table with some columns and data associated with that. Need to find out a column or a combination of some columns, so that the values or combination of values will be unique in the table.The table and number of columns and the columns will be dynamic.
I need to split a column into multiple columns. The data in my column is separated by a Comma (,). But the data is dynamic and I could have any number of data separated by (,).
Quote:FOR Ex: If COL1 contains CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION, ITEM_DATA_TYPE
RESULT: should be 4 columns contains the values CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION and ITEM_DATA_TYPE
Is COL1 contains CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION
RESULT: should be 3 columns contains the values CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION
trying to update a column in a table which has 3 columns of 16million rows from column in another table which has 1million rows, there is no relationship between the 2 tables.
Table A has 3 columns of 16million rows, the first two columns have 16million ID numbers, the 3rd colunm is currently NULL.
Table B has 1million Numbers, i need to somehow update column 3 in table A using the numbers in table B, it doesnt how many times each of the 1 million numbers are used but i dont want it to just update every row to the same value.
INSERT INTO TEST_TBL VALUES( 1000, 2000, 3000, 4000 ) ;
Expected result
A1 1000 A2 2000 A3 3000 A4 4000
A1, A2, A3, A4 are hard coded fixed values.
I could have done this but not a good idea in case table TEST_TBL is not a single row table but an inline query on 1,00,00,000 records with summary functions. In my table I've a summary query instead of single row table.
SELECT 'A1', COL1 FROM TEST_TBL UNION ALL SELECT 'A2', COL2 FROM TEST_TBL UNION ALL SELECT 'A3', COL3 FROM TEST_TBL UNION ALL SELECT 'A4', COL4 FROM TEST_TBL
My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.
SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty, a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':' || b.mnfst_qty FROM vw_acao_critical a JOIN vw_acao_critical b ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr [code]......
What will be the case when I need to concatenate for more number of values.
like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).
should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.
there is a drawback if a most of the tables of a database contain composite primary keys?
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.
I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.
Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.
Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.
So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.
Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.
What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.
I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.
What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.
So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.
