SQL & PL/SQL :: Inner Query Before Applying Dense_rank

Oct 20, 2012

I have the following query. The problem is that in case of dense_rank it gives wrong result when there is multi sort involved,So lets say if following data i get from inner query before applying dense_rank

Seq_uid Status Salary Pre_sort_col
1 A 4 A
1 A 3 A
2 B 5 B
3 A 0 A

After dense_rank when ordering by pre_sort_col desc and seq_uid desc the result set is

Seq_uid Status Salary ROW_NUM pre_sort_col
3 A 0 1 A
1 A 4 3 A
1 A 3 3 A
2 B 5 2 B

which is wrong as seq_uid 3 shld nt come first. I cant have salary in dense rank as these are dynamic columns,if on 3 columns multi sort is selected,the first column will come in pre_sort_col, SEq_uid has to be used as to distinguish from other records which get same pre_sort_col

SELECT c.ROW_NUM, c.RECORD_TOTAL, VW.*
FROM (SELECT distinct seq_uid,ROW_NUM,RECORD_TOTAL
FROM (SELECT dense_rank(order by pre_sort_col desc,seq_uid desc)row_num,a.*
FROM (SELECT COUNT(DISTINCT V1.seq_uid) OVER() RECORD_TOTAL,
[code]....

View 2 Replies


ADVERTISEMENT

XE :: Applying (User Groups)?

Aug 1, 2013

I am able to assign a user to a user group using the User Admin in Apex.I don't know how I would be able to assign a role (that I know how to define that for an individual user).The only thing I can see is a name for User Group and a Description! My requirement is to define a group of people to be assigned to one group/role, so that every change to that role can be automatically be applied to each user in that group

View 1 Replies View Related

Archive Not Applying On Physical Standby

Sep 3, 2010

ORACLE VERSION: 11.2
ENVIRONMENT: physical standby database
MODE: maximum performance mode.

SQL> select * from v$archive_gap;

no rows selected

SQL> select sequence#, applied from v$archived_log where applied='NO';

SEQUENCE# APPLIED
---------- ---------
10929 NO
10930 NO
10931 NO
10932 NO
10933 NO
10934 NO
10935 NO
10936 NO
10937 NO
11073 NO
11074 NO

SEQUENCE# APPLIED
---------- ---------
11075 NO
11076 NO
11077 NO
11078 NO
11079 NO
11080 NO
11081 NO
11082 NO
11083 NO
11084 NO
11085 NO

SEQUENCE# APPLIED
---------- ---------
11086 NO
11087 NO
11088 NO
11089 NO
11091 NO
11092 NO

What would be the cause

View 1 Replies View Related

Forms :: Applying Different Background Colour?

Apr 21, 2011

I have got a form in tabular format. each record contains some specific colour or it may not contain.

i want to chage the backgroud colour of that record based on what so ever user wants. for example, user want first record should be of RED back groud, second green, third blue etc.

colours are available in RGB format.

when i try to set_item_property(backgroundcolor) on post-query or on when new item instance, it makes all record of same colour.

note:- if a put another text/display item and make it number of record displayed property = 1 , then it works, because only one record colour is seen at a time.

I can not use multiple visual attributes because number of colours are up to user feedback.

how can it be applied in tabular format?

View 3 Replies View Related

Replication :: Applying DB Structure Changes Without Downtime?

Mar 16, 2011

We are trying eliminate/minimize the downtime for our application. As part of new code deployments sometimes we need to modify DB Structure also. As it is taking time to backup current DB and apply new DDL, the application is down.

Is there a way to eliminate the downtime, if I can leverage Data Guard, Golden Gate or RAC concepts?

View 3 Replies View Related

Server Administration :: Applying Patch (10.2.0.1.0 To 10.2.0.4.0)

May 19, 2011

I installed Oracle 10.2.0.1.0 in RH Linux 4.5. After that applied patch for 10.2.0.4.0. Everything went smoothly, but at the last steps I unknowingly clicked next button. I dont know whether some scripts is to be run as I did during installation of 10.1.0.4.0. If it is to be done I didn't do that.

How can I proceed now? If some scripts to be run where is the location? Will it cause any issue if I running it after completing the update?

View 1 Replies View Related

Upgrade :: Applying Patch Set Update 11.2.0.3.7 On 11.2.0.3.0

Aug 18, 2013

Database = 11.2.0.3.0

Operating System : Solaris 11 sparc 64.

applying PSU patch : 16619892

I am trying to apply patch 16619892 on 11.2.0.3.0 to upgrade it to 11.2.0.3.7.

I hope this is a straight forword stuff i beleive, but i am getting the following errors and log details.

$ cd /noracle/patch/16619892/
xxxx@xxxx$ /noracle/home/oracle/product/11.2.0.3/OPatch/opatch apply
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.

[Code].....

View 8 Replies View Related

Data Guard :: Standby Not Applying After Outage

Sep 5, 2013

We have a 11r2 primary and standby running on RHEL 5. The primary lost network connectivity for a time and VMWare locked up. The VM admin restarted the primary VM (it was a hard-boot). This all happened while I was gone and when I came into work, the primary database appeared to be working. I decided to dig deeper and check out the standby. On the standby... 

SQL> select * from v$archive_gap;
Thread#: 1
low_sequence: 123120
high_sequence: 123120
 
[Code]...

 It appears all is well (just very far behind) but when I run the last code block (show database prodDB2;) again ... 

DGMGRL> show database prodDB2;
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 2 days 1 hour(s) 42 minutes 37 seconds
Apply Lag: 2 days 1 hour(s) 42 minutes 37 seconds
Real Time Query: OFF
Instance(s): prodDB

it looks as if the massive backlog is just increasing. How can I confirm that everything that things are going well? I am still learning Data Guard. 

View 8 Replies View Related

Streams :: Applying Conflict Handlers On One Table?

Aug 15, 2012

I create following update conflict handlers, one after other. These are working correctly, my problem is when i create 2nd it replace first one, and when i create 3rd one it replace 2nd one. I want to put all three in action simultaneously. How can i do this.   

{code}DECLAREcols DBMS_UTILITY.NAME_ARRAY;BEGINcols(1) := 'MODIFIED_DATE';cols(2) := 'MODIFIED_BY';cols(3) := 'RATE_CHANGE_DATE';cols(4) := 'PENSION_AMOUNT';cols(5) := 'EASYPAISA_WORK_ON';cols(6) := 'PPO_NO';cols(7) := 'NEW_NIC_NO'; cols(8) := 'OLD_NIC_NO';cols(9) := 'CLAIM_NO';DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(object_name => 'CORE_BUSINESS.CB_PENSIONER',method_name => 'OVERWRITE',resolution_column => 'CLAIM_NO',column_list => cols);END;{code}  {code}DECLAREcols DBMS_UTILITY.NAME_ARRAY;BEGINcols(1) := 'MODIFIED_DATE';cols(2) := 'MODIFIED_BY';cols(3) := 'RATE_CHANGE_DATE';cols(4) := 'PENSION_AMOUNT';cols(5) := 'CLAIM_NO';DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(object_name =>

[code]....

View 3 Replies View Related

Security :: Applying Critical Patch Update?

May 23, 2012

Just to confirm with you if we can apply the October 2011 Critical Patch Update (CPU) to address vulnerabilities covered from CPU 2007 up to CPU 2011?

The PC Server (staging) where patching will be applied is running under Windows Vista have not been patched since it's database creation. This is maintained by our Contractors.

View 2 Replies View Related

While Applying Patches In Oracle Home Space Not Sufficient

Mar 18, 2011

Im applying patch which is an upgrade to 10.2.0.5 from 10.2.0.3. Its a 2 node RAC. While applying patches the installer mentioned space are sufficient, but while applying patches it throws message that in oracle home space not sufficient. Im able to apply patches in cluster home successfully. How much space required in oracle home for this patch? There is no information mentioned the document as well.

View 2 Replies View Related

Forms :: Applying Serial Number In Detail Block

Sep 5, 2011

I have form with master detail relation ship (invoicing form) the detail block is tabular, displaying upto 7 records...

Now my clients wants to show the serial number along with each record while feeding the data (this includes when insert, editing, deleting / clearing records) he wants to see the serial number as in MS access / MS techniques.

I tried to use the system variable to use :system.cursor_record; but this dose not works.(in insert/edit/delete/clear record)

View 5 Replies View Related

Server Administration :: Getting Error While Applying PSU Patch On Windows

Apr 7, 2011

We are applying PSU JAN 2011 patch 10349197 on windows server 2007. While applying the patch we are getting the below error.

Following files are active :

E:oracleproduct10.2.0inoraclient10.dll
E:oracleproduct10.2.0inorapls10.dll
E:oracleproduct10.2.0inoracommon10.dll
E:oracleproduct10.2.0inorageneric10.dll
E:oracleproduct10.2.0inoraplp10.dll
[code]........
--------------------------------------------------------------------------------
The following warnings have occurred during OPatch execution:

1) OUI-67620:Interim patch 10349197 is a superset of the patch(es) [ 8559466 ]
in the Oracle Home
--------------------------------------------------------------------------------
Its not an rac. We stopped all the services running on this server.

OPatch failed with error code = 74

View 4 Replies View Related

Log Applying Service Is Taking More Time In Physical Standby?

Sep 19, 2013

My Database version as followsOracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - ProductionCORE  10.2.0.4.0  ProductionTNS for Linux: Version 10.2.0.4.0 - ProductionNLSRTL Version 10.2.0.4.0 - Production We have datagaurd setup as well - Huge archive logs are generating in our primary database - Archive logs are shipping to standby with no dealy - But applying the archive logs are taking more in our physical standby database -why it was taking more time  to apply archivlogs (sync) in standby ? - What could be possible reasons..? Note : Size of standby redo logs are same as redo log file of primary database - Also standby by redo one or more than online redo log primary. Since i need to report my higer leve stating this is cause for delay in applying archive logs.

View 32 Replies View Related

Application Express :: Applying Aggregate To All Columns In Interactive Report?

Jun 25, 2012

I have few reports that I want to have totals at the bottom of the report.

Is there any way I could apply totals to all columns without doing it one by one?

View 2 Replies View Related

Data Guard :: Applying Incremental Backup On Standby To Resolve Archive Log Gap?

Nov 6, 2013

I have a Primary database and Standby database both in ASM. Recently my archive logs got deleted and i am trying to recover my standby database with an incremental backup based on scn from primary database. But i face the below error when i recover the standby database with the incremental backup taken in primary database.

RMAN> recover database noredo;Starting recover at 06-NOV-13using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=21 device type=DISKchannel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: +STDBY/11gdb/datafile/system.258.805921881destination for restore of datafile 00002: +STDBY/11gdb/datafile/sysaux.259.805921967destination for restore of datafile 00003: +STDBY/11gdb/datafile/undotbs1.260.805922023destination for restore of datafile 00004: +STDBY/11gdb/datafile

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Union Query Need To Add Extra Dummy Field To One Side Of Query

Dec 8, 2005

I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a

select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)

however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.

View 6 Replies View Related

SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)

Dec 5, 2012

I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.

View 1 Replies View Related

Reports & Discoverer :: Excluding SQL Query Data When Linked With XML Query?

Apr 26, 2013

I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?

e.g.:

Table emp:

e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800

XML File

<e_id>
143
876

So, I want the output to be:

e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100

View 2 Replies View Related

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related

SQL & PL/SQL :: Oracle Query Joining A Table With Existing Query?

Jun 19, 2012

I have the following four tables with the following structures Table A

ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE

Table B

ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE

when these two tables were joined like the following:

Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)

Now i have to join the next table C with table B

Table C structure is

ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer

I have the common column such as ColD2 and colB4 to get the Col D3

how do i join the existing query + join between table b and table c?

View 4 Replies View Related

Forms :: F11(Query Mode) And Execute Query In Oracle?

Jul 17, 2011

how to achieve F11(Query mode) and Execute Query in Oracle Forms?

View 1 Replies View Related

SQL & PL/SQL :: Query Using DBLink Returns More Rows Than Direct Query?

Apr 6, 2010

I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.

For example:

select
x,y,z
from
mytable@dblink

returns 788,324 rows

while
select
x,y,z
from
mytable

returns 712,102 rows

It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.

View 10 Replies View Related

Getting Top-N Query To Work As Sub-select In Larger Query?

Mar 10, 2012

Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?

Background:

We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.

The legacy HR system allows multiple phone numbers to be stored in a simple table structure:

SELECT emp_id, phone_type, phone_number
FROM employee_phones

emp_idphone_typephone_number
------- --------------- -------------------
46021CELL2222222222
46021HOME1111111111
46021WORK3333333333

The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)

From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.

That can be represented as:

SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
46021WORK2222222222
46021CELL3333333333

Or similarly with Top N concept:

SELECT *
FROM (SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111

Or really what I want in my export:

SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

phone_number
-------------------
1111111111

However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.

(SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = export.emp_id
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1)

1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?

2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?

Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.

View 3 Replies View Related

SQL & PL/SQL :: How To Read Field From Query In Another Query

Sep 19, 2010

I am having a Select query(below Query1) and I want to use one column(sum(col4)) from this Select query to be displayed in another Select query(Query 2). how to display this.

Query 1 :-
select a.col1,a.col2,b.col3,sum(b.col4)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3

Query 2 :-
select a.col1,a.col2,b.col3,sum(b.col6)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3,b.col5

View 4 Replies View Related

PL/SQL :: Converting Inner Query To Sub Query

Sep 18, 2012

This query is written in inner join, can any one try to write using sub query.

SELECT B.CNO
FROM CUSTEN A
INNER JOIN ORDS B
ON A.CNO = B.CNO
AND A.PRNO = B.PRNO

[Code]...

View 4 Replies View Related

SQL & PL/SQL :: Divide Count From One Query By Count From Another Query

May 24, 2010

I have the folloiwng two queries:

Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;

I need to compute the following:

var:=(yy/zz)*100

How can I achieve this in a single query?

View 3 Replies View Related

SQL & PL/SQL :: Query For Top N Max

Feb 23, 2012

I have a a table like with columns ( date_field, client_id(c_id), transaction_id(trx_id), mobile, amount )

table example data like

date_fieldc_idtrx_idmobileamount
24-JAN-1215100100120111111100100
24-JAN-1217100100220111111112150
24-JAN-1215100100320111111113100
24-JAN-1216100100420111111114200
24-JAN-1215100100520111111115100
24-JAN-1216100100620111111116100
24-JAN-1218100100720111111117100
24-JAN-1216100100820111111118100
24-JAN-1215100100920111111119200
24-JAN-1216100101020111111110100
24-JAN-1215100101120111111111100
24-JAN-1216100101220111111112100
24-JAN-1215100101320111111113100

Now using the unique index (Trx_id) I need to get max 3 records for each client (c_id).

Expecting result should be

date_fieldc_idtrx_idmobileamount
24-JAN-1217100100220111111112150
24-JAN-1218100100720111111117100
24-JAN-1216100100820111111118100
24-JAN-1216100101020111111110100
24-JAN-1216100101220111111112100
24-JAN-1215100100920111111119200
24-JAN-1215100101120111111111100
24-JAN-1215100101320111111113100

View 4 Replies View Related

How To Get COUNT On Query

Mar 4, 2009

I have this query and I want to get the COUNT:

SELECT first_np AS n_p FROM dvc
UNION
SELECT second_np AS n_p FROM dvc
UNION
SELECT n_p FROM dc;

This returns one column which is the n_p; how do I get the count of n_p?

View 2 Replies View Related

Query In 10g Oracle

Apr 18, 2008

I am facing problem with a select query in oracle 10g database from vb.net.It was working for oracle 9. The select statement I have written is as follows

Str=" select UCC.table_name, UCC.constraint_name, UCC.column_name, UCC.position, UC.constraint_type " & "from USER_CONS_COLUMNS UCC,USER_CONSTRAINTS UC " & "where (UCC.constraint_name = UC.constraint_name) " & "and UC.constraint_type = 'P' " & "and UCC.table_name = " & " '" & TableName & "'"

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved