How To Confirm Order Of Data In Results Of Two Sql Queries

Feb 9, 2009

I want to confirm the order of the data in results of two sql queries.

For example, in my table 5 columns are there.c1 to c5.

c1 c2 c3 c4 c5
--------------------------------------------------
test100 1 A 1-Jan-2006 2-Jan-2006
test100 2 B 3-jan-2006 4-Jan-2006
test101 1 A 01-Jan-2006 2-Jan-2006
test101 2 B 03-Jan-2006 4-Jan-2006
test101 3 c 05-Jan-2006 06-Jan-2006

In this table if you order by c2,

select c3
from tab1
where c1='test101'
order by c2;

c3
---------
A
B
C

If you order by c4,

select c1,c2,c3
from tab1
where c1='test101'
order by c4;

c3
------
A
B
C

Sometimes order of these data may change like. the result of the 2nd query may change (bcos of c4 date column changes)

C3
--
A
C
B

So,@ run time i want to check the order of these 2 results should match....if not,i need to display the corresponding c1 column.

View 3 Replies


ADVERTISEMENT

Data Guard :: Confirm Particular Archive Log File Applied Or Not?

Aug 5, 2011

I need a clarification on the below query.

I am having a Dataguard setup as Logical.

I am copying all the archive log files generated in the live server into the local server in order to apply into the Data Guard.

I am running a script for this process.

Question:How i can verify whether particular archive log file was applied into the data guard or not.

View 1 Replies View Related

SQL & PL/SQL :: Results Different After Using Order By Clause

Mar 18, 2011

Result are different after adding the Order By clause in below query.

Query

select sum(wpsv1.primary_qty) detail_qty,
wpsv1.from_subinventory subinventory,
wdd.revision revision,

[Code]....

View 7 Replies View Related

Does Trcsess Combine Various Trace Files In Order In Which Queries Executed

May 28, 2013

Does trcsess combine various trace files in the order in which the queries were executed?

View 3 Replies View Related

Application Express :: Js-confirm Box With Request And Passing Value

Aug 16, 2012

User clicks on link-column field in tablular form, Confirm box pops up with a confirmation message, with "OK" generates a request and should pass a value from the form into an item for further processing.

The URL in the column link as of now looks like that: javascript: apex.confirm("message goes here...", {request:"EQDONE", set:{"P2100_X": "#EQMID#"}});For some reason just nothing happens. When I omit the "javascript:", I get an unvalid-url error message. But if I change the procedure name, shoudln't I get some error message also?

View 4 Replies View Related

PL/SQL :: Queries To Get Data In Batches From A Huge Data Table

Jan 3, 2013

Here is my problem, i need to create some files with my own format(let say 5000 records each) from a huge data table (May contain 5 Million records). And i want this creation to be multi threaded.

so how can i form queries efficiently to fetch records like 1..5000 and 5001..10000 and so on. I can form some thing like select * from table where rownum<5000 and not exists ( already fetched records) . but it is not the efficient one.

View 5 Replies View Related

Converting MySQL Queries To Oracle Compatible Queries

Jan 23, 2007

our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.

this is the error message:

The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.

SELECT productID,productName FROM products order by productName;

select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3

SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'

SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1

View 6 Replies View Related

Function Not Returning Data (Desired Results)

Jul 14, 2010

I'm trying to debug this function to get the desired results. See attachment for the function code and the test data insert script.

----Create Test Table
CREATE TABLE VC_WORKINGDAYS
(
WK_ID number NUMBER(10,0),
WK_DATE DATE,
);

-- Insert test Data
INSERT INTO VC_WORKINGDAYS_1 VALUES (308, '25-MAR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (316, '06-APR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (324, '18-APR-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (332, '03-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (340, '13-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (348, '25-MAY-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (356, '06-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (364, '16-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (372, '28-JUN-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (380, '08-JUL-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (388, '20-JUL-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (396, '01-AUG-11');
INSERT INTO VC_WORKINGDAYS_1 VALUES (404, '11-AUG-11');

SQL
----- Result Should be WHY
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),1)) FROM DUAL 14/JUL/10 15/JUL/10 is 1 working day from today
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),2)) FROM DUAL 14/JUL/10 16/JUL/10 is 2 working days from today
SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),3)) FROM DUAL 14/JUL/10 19/JUL/10 is 3 working days from today

Attached File(s)

 create_Function.zip ( 6.39K )
Number of downloads: 1

 InsertData.txt ( 84.34K )
Number of downloads: 0

View 1 Replies View Related

PL/SQL :: Transposing Data And Joining The Results To A Query

Apr 10, 2013

I am new to writing queries for an oracle database and I was giving a bit of challenge. Here's the situation. I have 3 tables I am using. 2 of the tables are being used to transpose people's names from rows to columns by account number (there are multiple people associated with each account). The last table is a pretty straight forward query. I can run each query by itself and I get the results I want. But when I try to compile the two together, I start getting a variety of errors. Below is the two queries:

Query 1 (returns about 1,500 rows):
SELECT DISTINCT CAST (EIS_DW.ACCTCOMMONLOAN.ACCOUNT as VARCHAR(20)) as ACCOUNT_NUM,
EIS_DW.ACCTCOMMONLOAN.ACCOUNT_STATUS,
EIS_DW.ACCTCOMMONLOAN.MINOR_DESCRIPTION,
EIS_DW.ACCTCOMMONLOAN.OWNER_NAME,
[code]......

Query 2 (returns about 570 rows):
SELECT ACCTNBR,
max(DECODE (rn , 1, FULLNAME)) GUARANTOR_1,
max(DECODE (rn , 2, FULLNAME)) GUARANTOR_2,
max(DECODE (rn , 3, FULLNAME)) GUARANTOR_3,
max(DECODE (rn , 4, FULLNAME)) GUARANTOR_4,
[code]....

Ideally, I want to join these two queries on ACCOUNT and ACCTNBR. I have tried working my first query into my second query, but the best I get with that, is the 570 or so accounts, not all the accounts.

View 4 Replies View Related

PL/SQL :: Use Two Temporary Tables To Assign Color To Data Results?

Jul 16, 2012

Example Tables and Data:

I have 5 Courses, each course has 2 assignments.

with Courses as (
select 100 courseID from dual union all
select 200 from dual union all
select 300 from dual union all
select 400 from dual union all
select 500 from dual
), Assigns as (

[code]...

I need each assignment to get a color assigned to it according to it's courseID. So my theory is that I'd have a temporary table with a list of colors; each courseID would get assigned a color but my question is how do I assign each courseID a color without anything to join the temporary table on? Once each courseID has a color, I'd join in Assigns so that each assignment has a color associated with it according to it's courseID.

--Color table with 6 colors
with Colors as (
select 'red' color from dual union all
select 'blue' from dual union all
select 'yellow' from dual union all
select 'green' from dual union all
select 'purple' from dual union all
select 'teal' from dual
)

View 8 Replies View Related

Update AGENTS Table With Data Based On Queries?

Feb 3, 2009

I was trying to update the AGENTS table with the data based on queries. But all the records updates with the same data. I can't understand why.

Write a PL script to populate these columns as follows:

TRAVEL_STATUS is one of three values:
GLOBETROTTER: agent has visited five or more different locations in the course of his missions
ROVER: agent has visited between one and four locations
SLOB: agent has been on no missions.

CONTACTS is the number of targets that share the agent's home location.

some of the tables and columns are
agents table: agent_id, location_id
targets table: target_id, location_id
missions_agents table: agent_id, mission_id
missions_targets: target_id, mission_id
missions table: mission_id, location_id

My code is

DECLARE
status AGENTS.TRAVEL_STATUS%TYPE;
contact AGENTS.CONTACTS%TYPE;
CURSOR loc_cur
IS
SELECT
a.AGENT_ID id,

[code].....

View 7 Replies View Related

SQL Queries Taking Long Time After 11g Data Migration

Sep 14, 2010

I have upgraded oracle database from 9i to 11g using export and import utility. After migration we are facing performance issue in report generation, We have observed that First execution of report is taking very long time and when we generate the same report 2 -3 times there is considerable change in the execution time and it is more better than the first execution.

2 days back I have restarted the database and found the same issue. There are around 300 Reports and it is not possible to generate all the reports 2-3 times every time we restart the database.

View 5 Replies View Related

Data Guard :: How To Tune SQL Queries For A Standby database

Nov 19, 2012

We know and perform lots of tuning stuff on our databases, mainly on primary side.

but the set of queries running on standby databases (like to use for reporting purpose) are totally different from one running at primary.

so How can we tune out our Standby DB to perform well ?

View 6 Replies View Related

Performance Tuning :: Order Of Steps In Execution Path And Order Of Predicates In The Plan

Mar 20, 2012

Which step in the following plan is the first step of execution

I reckon it is "TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE"

Is that correct?

In the "Predicate Information (identified by operation id):"

section the predicates - access and filter for the step "TABLE ACCESS FULL | PYMNT_DUES" are displayed first

Isn't there any relation between the order of execution steps and the order in which predicates are displayed?

Execution Plan
----------------------------------------------------------
Plan hash value: 538700484
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2364 | 15 (14)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 2364 | 15 (14)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 2364 | 14 (8)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 2313 | 13 (8)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 2281 | 12 (9)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 2255 | 11 (10)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 175 | 6 (17)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INDX_2 | 12 | 612 | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PYMNT_DUES | 43 | 5332 | 3 (0)| 00:00:01 |
| 10 | VIEW PUSHED PREDICATE | | 1 | 2080 | 5 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 154 | 5 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 103 | 4 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE | 1 | 32 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | INDX_BBS_1 | 3 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| DAILY_CHECK | 1 | 71 | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | INDX_SEARCH | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | INDX_IAM_SR_NO | 1 | 26 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | INDX_2 | 1 | 32 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------

View 3 Replies View Related

SQL & PL/SQL :: How To Get Data In Logic Order

Dec 28, 2010

my table have four cols such as

start_value trans_value result_value create_time
1 4 5 20101018 22:33:40
5 -2 3 20101018 22:33:40
3 3 6 20101018 22:33:40
6 -4 2 20101018 22:33:40
2 +3 5 20101018 22:33:40
5 9 14 20101018 22:33:40

but the data is not in the logic order they are confusing and there no origina sequence such as

start_value trans_value result_value create_time
1 4 5 20101018 22:33:40
3 3 6 20101018 22:33:40
5 -2 3 20101018 22:33:40
2 +3 5 20101018 22:33:40
5 9 14 20101018 22:33:40
6 -4 2 20101018 22:33:40

how to get they in logic order.now i have to create a combination in mem and use loop to get the logic order.

View 6 Replies View Related

SQL & PL/SQL :: Get The Data On Order By Wise

Nov 15, 2011

i am getting the data like bellow i need the data with out null values

date col1col2col3col4
16-Nov-1123
17-Nov-1112
18-Nov-11321
19-Nov-1123
20-Nov-11132
16-Nov-112321
17-Nov-11112
18-Nov-11211
19-Nov-11132
20-Nov-1112
16-Nov-1145
17-Nov-11465
18-Nov-112123
19-Nov-11132
20-Nov-1165
16-Nov-11456
17-Nov-11546
18-Nov-114656
19-Nov-11566
20-Nov-11564

need out like below

date col1col2col3col4
16-Nov-1123232145456
17-Nov-1112112465546
18-Nov-1132121121234656
19-Nov-1123132132566
20-Nov-111321265564

View 3 Replies View Related

PL/SQL :: Retrieve Data By Using Both Group By And Order By Clauses?

Nov 16, 2012

I have a table name as angdata77 having attributes like asigno..i want to retrieve data from angdata77 by using both group by & order by clauses.. for total count..am using the query as

select asigno,count(*) from angdata77 group by asigno order by asigno;

Is there any other query for retrieving the data from angdata77

View 5 Replies View Related

SQL & PL/SQL :: Result Order In 2 Table Joins Without Order By

Sep 6, 2011

I have below tables,

describe rpthead

Name Null Type
--------------------------- -------- -------------
RPTNO NOT NULL NUMBER
RPTDATE NOT NULL DATE
RPTD_BY NOT NULL VARCHAR2(25)
PRODUCT_ID NOT NULL NUMBER

describe rptbody

Name Null Type
------------- -------- -------------
RPTNO NOT NULL NUMBER
LINENO NOT NULL NUMBER
COMMENTS VARCHAR2(240)
UPD_DATE DATE

The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.

SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0 , RPTHEAD rpthead
WHERE
(
t0.RPTNO = rpthead.RPTNO
AND
t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss')
AND
rpthead.PRODUCT_ID IN ('4647')
)

I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.

View 5 Replies View Related

Forms :: Order By Control Block Data Using Oracle 10g

Apr 11, 2013

I have two block....both are the control block.. in first block i select the date and in second block the data of that date is populated.but the data is populated using cursor in when-button-pressed trigger of that first block button.in cursor the data is selected and placed in field of detail block using into clause.... each field and one item of detail block is srno which is create in post-query of detail block using :sysyem.trigger_record.

Now i want after populated the detail block the data is sorted desc one of the field of the detail block..Can this possible using set_block_property() of block although the block is control block if yes where i should do this?

View 7 Replies View Related

Forms :: Data Block Ignoring Order By Clause?

Mar 6, 2012

I have a real problem with form, specifically one of its data blocks. In the order by property of the block i specify it to sort on a branch in ascending order(i tried descending as well) but for some reason the form ignores that and sorts it on the ROWNUM. I even removed the where clause, the order by clause and changed the query data source type to FROM clause and changed the data source name to pre-query. I then created the query string in the block's pre-query trigger and set query_data_source_name property to that query string and still the data in the block is not being sorted on the branch number but instead on the ROWNUM.

View 6 Replies View Related

Forms :: Custom ORDER BY In Multi-record Data Block

Jun 15, 2011

I have data block that has hundreds of records. On a screen I display 20 records at a time. On that same screen I have "find badge" function where user types badge/employee id and presses a button to find the record in a data block. I have no problem finding the records in a list and pointing the curser to appropriate record. My question is (which is a user request) if the badge/employee id is found to make that record to be the first record in a list.

View 2 Replies View Related

Server Utilities :: Data Pump Import Order - Causing Constraint Violation

Nov 16, 2010

We are trying to import data into existing tables in a schema using data pump

However the foreign key tables are being imported first and then the master table data thus violating the constraints

Apparently it seems larger tables are being imported first regardless of referential integrity constraints thus causing constraint violation (contrary to my understanding)

Is it a normal behaviour during data pump import?

Is it possible that the keys being sequence generated are causing this?

As I understand import will commit after each table In that case can we defer commit at all at the expense of large undo, set constraints to deferrable and try the import?

View 3 Replies View Related

Performance Tuning :: Same Data But Different Performance Results

Sep 3, 2010

what the principal things to look at when we have for the same query different performance results are?I have 2 different bases: the plan and data are the same but performance results are very differents.

View 10 Replies View Related

Use MAX Function Without It Being In Results?

Apr 4, 2007

i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?

View 1 Replies View Related

SQL & PL/SQL :: Getting Duplicate Results

Feb 7, 2012

I am getting duplicate results..I have been at it for hours now.This is what I have:

DECLARE
v_course_id classes.course_id%TYPE := :course_id;
v_instr_id classes.instr_id%TYPE := :instructor_id;
[code]...

The output I get is this (There is only 1 of each in the database)

Class ID: 1 Status: Enrolled Name: John O'Reilly
Class ID: 1 Status: Enrolled Name: Natacha Hansen
Class ID: 1 Status: Enrolled Name: Reed Jetto
Class ID: 1 Status: Enrolled Name: Janis Greenberg
Class ID: 1 Status: Enrolled Name: Vishal Singh
Class ID: 1 Status: Enrolled Name: Francis Hamilton
[code] ...

View 2 Replies View Related

UNION ALL Vs 2 Queries

Nov 12, 2012

is there any difference between

- returning from the procedure 2 ref cursors containing result set of 2 queries
- returning from the procedure 1 ref cursor containing result set of that 2 queries as one (with UNION ALL)?

Will 2nd option be faster or similar to 1st?

View 1 Replies View Related

Calling SP Results In ORA-00911

Apr 20, 2007

I am testing a supposedly very simple stored procedure in XE. It compiles with no errors but when I enter the following command in the SQL Command window I get an ORA-00911: invalid character.

call get_all_customer_orgs;

The get_all_customer_orgs procedure is defined as.

create or replace procedure get_all_customer_orgs (p_recordset out sys_refcursor)
is
begin
open p_recordset for
select * from customerorgs;
end get_all_customer_orgs;

I don't understand what the invalid character could be.

View 5 Replies View Related

Any Way To Pivot Results Of A Query?

Apr 23, 2007

Is there anyway to pivot the results of a query?

so if i have:

SELECT GROUP, count(*)
FROM GROUP
GROUP BY GROUP

And it give the following output:

A 10
B 50
c 24

Is there anyway to put into this format?

A B C
10 50 24

I am doubting that there is and that i am going to have to handle this in my code later, but it never hurts to ask!

View 1 Replies View Related

Select Field Where Results Are The Same

Feb 26, 2008

I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.

lets see .. so a count of records where widget = widget or something along those lines.

View 9 Replies View Related

Getting Total Results Count

Aug 27, 2010

I would like to give back to the our application user a page of results for a given query along with the total result count, something like: "Showing 1-25 of 650 total results".

Currently I am doing this by submitting a second query:

select count(*) from (<previous query criteria>)

Is there a better performing approach I could be using?

View 4 Replies View Related







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