PL/SQL :: Compare 2 Result Sets?
Nov 13, 2013I have two queries which count the number of lines of the tables of the same schema in two databases . How can I compare the two result set.
View 7 Replies
ADVERTISEMENT
PL/SQL :: Compare 2 Result Sets In Procedure
Apr 17, 2013I have two queries. One selects 5 values (all number datatypes), the second query selects 2 values (both number datatypes).If a value from query 2 exists in the resultset of query 1 - go make table updates using the second value in the select statment of query 2. If there is not a match, update a table with values from query 1.
Currently, I have two cursor for loops, which are nested. This works but of course is horribly slow. What is a better way to process this data?The number of rows varies between the two resultsets.
Oracle 11.2.0.3.2
SQL & PL/SQL :: Different Number Of Result Sets While Adding Further Columns In Select Clause
May 4, 2010The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.
Before adding the columns 4,5,6,the result set count was 11.
SELECT PAYMENT_METHOD_MAP.NETTINGGROUP_ID,
PAYMENT_METHOD_MAP.CREDITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.DEBITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.AGENT_ID,
SETTLEMENT.NETTINGGROUP_ID,
SETTLEMENT.SETTLEMENTDATE
[code]....
SQL - Using Row Number Over Partition By - With Grouping Sets
Jan 2, 2013I am trying to write a single SELECT statement that groups at 2 levels of aggregation (using grouping sets) and assigns row numbers (to rank each item) that are partitioned at the correct level for each grouping set. I have the grouping sets figured out but I can't find a way to make Partition By match each level of aggregation.
What I am looking for (in a single SELECT statement) is logically equivalent to:
SELECTweek
,region
,NULL as country
,item
,SUM(qty)
,ROW_NUMBER OVER (PARTITION BY week, region ORDER BY SUM(qty) DESC) as rownumFROM base
GROUP BY week
,region
,item
UNION ALL
SELECTweek
,NULL as region
,country
,item
,SUM(qty)
,ROW_NUMBER OVER (PARTITION BY week, country ORDER BY SUM(qty) DESC) as rownumFROM base
GROUP BY week
,country
,item
I hoped that I could do something like this:
SELECTweek
,region
,country
,item
,SUM(qty)
,ROW_NUMBER OVER (PARTITION BY week, GROUPING SETS (region, country) ORDER BY SUM(qty) DESC) as rownumFROM base
GROUP BY week
,GROUPING SETS (region, country)
,item
But it looks like I am not allowed to partition by grouping sets -- I get the error ORA-00907: missing right parenthesis. I didn't expect it to work but I am not sure how else to partition by multiple levels.
let me know if I could have tagged my code or met other forum standards better.
SQL & PL/SQL :: Multilevel Collection Comparison Using Sets?
Sep 1, 2010I need to populate a table based on the results of comparing sets of data. I decided to do this using MULTISET EXCEPT, but having created the structure, do not know whether it is actually possible, and if so, what syntax to use.
I have created:
CREATE OR REPLACE
TYPE NUMBER_TBL IS TABLE OF NUMBER;
/
CREATE OR REPLACE
TYPE PACKAGE_OPTION_GROUP_OBJ AS OBJECT( ID NUMBER, benefits NUMBER_TBL )
/
CREATE OR REPLACE
TYPE PACKAGE_OPTION_GROUP_TBL AS table of PACKAGE_OPTION_GROUP_OBJ
/
I have populated the following:
selectedTable PACKAGE_OPTION_GROUP_TBL := PACKAGE_OPTION_GROUP_TBL();
defaultTable PACKAGE_OPTION_GROUP_TBL := PACKAGE_OPTION_GROUP_TBL();
groupTable PACKAGE_OPTION_GROUP_TBL := PACKAGE_OPTION_GROUP_TBL();
comparisonTable PACKAGE_OPTION_GROUP_TBL := PACKAGE_OPTION_GROUP_TBL();
Once populated they contain the following data:
***selectedTable***
GROUP_ID : 121
BENEFITS : 161
---------------------------
GROUP_ID : 123
BENEFITS : 165
BENEFITS : 167
---------------------------
***defaultTable***
GROUP_ID : 121
BENEFITS : 161
---------------------------
GROUP_ID : 122
BENEFITS : 162
BENEFITS : 163
---------------------------
***groupTable***
GROUP_ID : 121
BENEFITS : 161
---------------------------
GROUP_ID : 123
BENEFITS : 165
BENEFITS : 166
---------------------------
I first need to compare the ID field only of selectedTable with defaultTable to find where they differ, something like
comparisonTable := selectedTable MULTISET EXCEPT(ID) defaultTable
I am expecting the result to be something like:
***comparisonTable***
GROUP_ID : 123
BENEFITS : 165
BENEFITS : 167
---------------------------
However, I don't know if this is possible and if so, what the syntax would be.
At a later stage, I will need to compare the benefits between selectedTable and groupTable, where the GroupID's match, which is why I have the tables structured in this way.
SQL & PL/SQL :: Grouping Sets / Dictionary Views
Aug 20, 2010I'm having a problem with grouping sets over dictionary views.
10g output:
SQL> select
2 -- 10g results
3 segment_name,
4 round(sum(bytes/1024/1024),2) mb
5 from dba_segments
[Code]...
ERROR at line 10: ORA-03001: unimplemented feature
Elapsed: 00:00:00.12
The query is fine over a non-dictionary table however (My actual code isnt against dual, but this makes it generic, the error is consistent)
11g output
SQL> select
2 -- 11g results
3 segment_name,
4 round(sum(bytes/1024/1024),2) mb
5 from dba_segments
[Code]....
ERROR at line 8: ORA-00904: : invalid identifier
Different error and that syntax, as far as I can tell, is sound - error message be damned.
An example of a query working on both versions is
select
employee_id,
sum(salary)
from
employees
group by grouping sets ((employee_id),null)
;
Am I missing something? Does grouping sets not work over the dictionary views?
Edit: Added version tags over the code to make it easier to read
SQL & PL/SQL :: Dataset From Two Sets Of Tables Based On Condition
Nov 26, 2010have two queries that will return same columns from two different set of tables ( column mapping has been taken care of). The return type is out ref cursor. (P_SUPPLY_REORDER )
Query 1-SO
-----------------------------------
select
so.SMO_NO,
so.SPLY_ORD_DT,
so.fk_CUST_ID as CUST_ID,
so.CUST_PO_NO,
so.ATTENTION_NAME,
[code].....
Query-2 Xcom
--------------------------------------
select
null as sMO_NO,
xso.created_date as SPLY_ORD_DT,
xso.fk_cust_id as cust_id,
cust.cust_po_no as cust_PO_NO
,(sta.SHIP_TO_ATTN_FIRST_NAME||''||sta.SHIP_TO_ATTN_LAST_NAME) as attention_name,
xsol.CARTONS_ORDERED as SPLY_ORD_QTY,
[code].......
Now the requirement is
One of four conditions are possible for each Supply Reorder Number:
. Both table queries return no records- Populate all the P_SUPPLY_REORDER output fields with nulls
. SUPPLY_ORDER returns a record, but XCOM_ORDER_HEADER returns no records
- Populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
. SUPPLY_ORDER returns no records, but XCOM_ORDER_HEADER returns one record
- Populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES.
. SUPPLY_ORDER returns a record, and XCOM_ORDER_HEADER returns a record; find out the latest order by comapring max(SPLY_ORD_DT)
from SUPPLY_ORDER with max(CREATED_DATE) from XCOM_ORDER_HEADER.
- If the latest order is in SUPPLY_ORDER, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
- If order dates are equal from both join results, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
- If the latest order is in XCOM_ORDER_HEADER, then populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES.
Question is how can we switch over the queries to pull respective dataset based on these conditions ( checking that which table join is going to return a row and then based upon latest order if both tables return a row) and all this logic as part of single SQL statement that is returned as OUT Ref Cursor.
Storing Multiple Currencies And Character Sets?
Aug 19, 2008i basically need to setup a schema for globalization. i need to handle brittish dollar signs, american dollar signs. etc.what is a global character setting i can use to handle almost everything?
my problem right now is that when i store a brittian dollar sign it converts it to a box. i need this preserved so i can select it and use it later.will the WE8ISO8859P1 character setting work? where is a good place to view all of the types of character settings? and what they are for. etc. and also how do you change such things? not for the session, but for a schema wide
here are my current sessions
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
[code]...
RMAN :: Find Backup Sets Are Encrypted Or Not?
Feb 9, 2013is there any way to find is rman backup sets are encrypted or not.
View 1 Replies View RelatedRMAN :: Backup Sets Versus Image Copies
Feb 5, 2013I am trying to understand What is the advantage of RMAN backup image copies over backup sets? ( image copies to the disk under FRA location )
Database version: 11.2.0.3
SQL & PL/SQL :: Delete Last N Sets Of Rows Of A Table With Same Dates In Sort Field
Mar 15, 2013I know how to select the last N sets of rows, using DENSE_RANK - where multiple rows have the same timestamp but I want to only select those rows which do NOT have the top 2 unique timestamps.
i.e.:
SELECT *
FROM ( SELECT DENSE_RANK() OVER (ORDER BY myTimestamp DESC) DENSE_RANK, HISTORYID, USER_ID, myTimestamp, STATUS, FROM TXN_HIST)
WHERE DENSE_RANK > 2 order by myTimestamp DESC, HISTORYID, USER_ID;
But how do I DELETE these same rows?
SQL & PL/SQL :: Handling Circular Data In Oracle / Get Disjoint Sets Of Data Connected By 2 Values
Sep 29, 2011I have this table :
column1 column2
--------- ---------
value1 value2
value1 value3
value2 value4
value3 value7
value7 value1
value8 value9
What I was trying to retrieve is something like that:
Quote:
output_column
---------------
value1, value2, value3, value4, value7
value8, value9
I don´t care about the order of the values in the row. In other words, I want to get disjoint sets of data connected by any of both values.Every pair in the input table is unique.
I have seen in the web that it is possible to do using connect by and hierarchical retrieving but I've been trying to make a lot of combinationts and I can reproduce the output.
Server Utilities :: Difference In Character Sets Of Server And Client?
Mar 16, 2011I've a question regarding difference of character sets, while taking a export(logical backup) of database on directly to server(linux RHEL 2.1 AS) and export on a client (windows xp prof machine, where only a oracle 9i client is installed). On server it seems to fine and okay, but on client node i'm getting following error for almost all tables.
EXP-00091: Exporting questionable statistics.
My question is :
[1] Is it creating any sort of problem, if later on i import the data which was taken from client node.
[2] Why there is a difference(marginal) in dump(.dmp) file size.
[3] Is there any way to overcome it, or it is the natural behave of it. Means not a problem.
[4] If i'm using a long or blob as datatype for some of my table,is they have any problem if i persist like above.
Additional Information about character sets On server node :
Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion)
On client node :
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set server uses US7ASCII character set (possible charset conversion)
Backup & Recovery :: How To Know The Latest Backup Sets
Dec 14, 2011I have backup my db many times in one day,and there are many backup sets,how to know which is the the latest backup sets?
View 10 Replies View RelatedHow To Compare A Value In SQLLDR By Using WHEN
Feb 25, 2013Can 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]....
how to compare a value in sqlldr control file.
SQL & PL/SQL :: Compare Two Different Table?
Jul 11, 2011I want to compare two tables column by column and find out which columns are matching and inserting matching columns value into another table.
View 1 Replies View RelatedSQL & PL/SQL :: How To Compare The Four Tables
Jan 5, 2013I Have Four Tables
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
SQL & PL/SQL :: To Compare Two Strings
Apr 8, 2008I 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
Do we have any oracle built ins to compare ?
SQL & PL/SQL :: How To Compare Time
Jun 30, 2010Checkout the following code
CREATE TABLE CHK(dt_request_datetime DATE);
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.
SQL & PL/SQL :: How To Compare Hours
Jan 10, 2011I want to write a function that gets:
1. event_date (dd/mm/yyyy)
2. event_start_time (??/??/???? HH24:MI)
3. event_end_time (??/??/???? HH24:MI)
I got a table called EVENTS that got 2 fields:
1. event_date (dd/mm/yyyy HH24:MI)
2. event_end_date (??/??/???? HH24:MI)
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.
Compare Two Columns Relationship
Apr 29, 2011I'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.
Col1Col2
1106011060
1106011640
1106011142
1106011095
1106013029
1106014058
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)???.
How To Compare 2 URLs And Convert Into 1
Apr 12, 2008[URL]....
[URL].....
i want to compare this URL and convert them into 1
i.e any url containing rediff word i want to convert that URL into
[URL]......
Compare Two Time Values In Sql?
Dec 4, 2009is 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 RelatedCompare Records In A Recordset?
Oct 28, 2010Currently,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.
SQL & PL/SQL :: Compare Varchar With Date
Feb 26, 2012I have one command compare varchar type with date type by Pl/SQL.
date_entry gave values:" 2012/01/06 22:28:24",
now i want to convert it to date and compare with sysdate.
Select * From qvsystem
Where to_char(to_date(date_entry, 'rrrr-mm-dd hh24:mi:ss'), 'dd-mm-rrrr') = to_char(Sysdate, 'dd-mm-rrrr');
----
note:date_en2012/01/06 22:28:24
after I executed that command It's appear one message .
SQL & PL/SQL :: Compare Columns Of Two Table?
Apr 5, 2013I 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..
View 17 Replies View RelatedForms :: How To Compare Two FMB Files
Jun 19, 2008I 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.
View 15 Replies View RelatedSQL & PL/SQL :: To Compare Records In Two Columns
Oct 8, 2010I need to compare records in the two columns (varchar). I need to find almost the same names from that columns.
For instance:
'MAPE STUD' <-> MAPE STUDY
And I should see that these records are the same because different is only in the one word.
SQL & PL/SQL :: Compare 2 Tables On Columns Value?
Oct 25, 2010I 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';
SQL & PL/SQL :: Compare Two Table Data
Mar 5, 2010How to identify the columns which are different between two oracle tables. I have nearly 30 columns in each table comprising of million rows
View 11 Replies View Related