PL/SQL :: Compare 2 Result Sets In Procedure

Apr 17, 2013

I 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

View 7 Replies


ADVERTISEMENT

PL/SQL :: Compare 2 Result Sets?

Nov 13, 2013

I 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 View Related

SQL & PL/SQL :: Different Number Of Result Sets While Adding Further Columns In Select Clause

May 4, 2010

The 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]....

View 8 Replies View Related

SQL & PL/SQL :: Get Result Set Back From Stored Procedure?

Apr 9, 2012

I come from a world of MSSQL and have been thrown into doing some Oracle work. Great! Ok, moving on.. I work in an environment where I do not have direct access to the database tables that I need data from. As a workaround, I have been asked to create a stored procedure that will be loaded into our CRM system's production db once it goes through the internal "approval" process.Basically, I need to return a result set back to the client by calling a stored procedure.

Version 1 of this that was already in place was done with the following code.

procedure events_by_day (p_start_date IN OPERATION_LOG.DT%type,
p_end_date IN OPERATION_LOG.DT%type,
p_results OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_results FOR

[code]....

Then the code is executed from the client side like so:

events_by_day(p_start_date => to_date('2012-3-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
p_end_date => to_date('2012-3-29 23:59:59', 'YYYY-MM-DD HH24:MI:SS'),
p_results => r_cursor);
LOOP
FETCH r_cursor
INTO event_date, event_acct_no, event_type, event_count;

[code]....

As you can see, a SYS_REFCURSOR was used in this case to pass the data back.

View 3 Replies View Related

SQL & PL/SQL :: Procedure Compiled Successfully But Not Show Result

Aug 17, 2010

I made one code for understanding purpose its complied successfully, but not show result...its give me message procedure successfully completed.

Code is :

BEGIN
For emp_rec in (SELECT * FROM EMP)
LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.ename);
END LOOP;
END;
/

View 4 Replies View Related

SQL - Using Row Number Over Partition By - With Grouping Sets

Jan 2, 2013

I 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.

View 1 Replies View Related

SQL & PL/SQL :: Multilevel Collection Comparison Using Sets?

Sep 1, 2010

I 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.

View 6 Replies View Related

SQL & PL/SQL :: Grouping Sets / Dictionary Views

Aug 20, 2010

I'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

View 4 Replies View Related

SQL & PL/SQL :: Dataset From Two Sets Of Tables Based On Condition

Nov 26, 2010

have 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.

View 7 Replies View Related

Storing Multiple Currencies And Character Sets?

Aug 19, 2008

i 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]...

View 5 Replies View Related

RMAN :: Find Backup Sets Are Encrypted Or Not?

Feb 9, 2013

is there any way to find is rman backup sets are encrypted or not.

View 1 Replies View Related

RMAN :: Backup Sets Versus Image Copies

Feb 5, 2013

I 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

View 2 Replies View Related

SQL & PL/SQL :: Delete Last N Sets Of Rows Of A Table With Same Dates In Sort Field

Mar 15, 2013

I 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?

View 3 Replies View Related

SQL & PL/SQL :: Handling Circular Data In Oracle / Get Disjoint Sets Of Data Connected By 2 Values

Sep 29, 2011

I 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.

View 2 Replies View Related

Server Utilities :: Difference In Character Sets Of Server And Client?

Mar 16, 2011

I'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)

View 7 Replies View Related

Backup & Recovery :: How To Know The Latest Backup Sets

Dec 14, 2011

I 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 Related

How To Compare A Value In SQLLDR By Using WHEN

Feb 25, 2013

Can 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.

View 1 Replies View Related

SQL & PL/SQL :: Compare Two Different Table?

Jul 11, 2011

I 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 Related

SQL & PL/SQL :: How To Compare The Four Tables

Jan 5, 2013

I 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

View 2 Replies View Related

SQL & PL/SQL :: To Compare Two Strings

Apr 8, 2008

I 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 ?

View 6 Replies View Related

SQL & PL/SQL :: How To Compare Time

Jun 30, 2010

Checkout 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.

View 2 Replies View Related

SQL & PL/SQL :: How To Compare Hours

Jan 10, 2011

I 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.

View 2 Replies View Related

Compare Two Columns Relationship

Apr 29, 2011

I'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)???.

View 1 Replies View Related

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]......

View 3 Replies View Related

Compare Two Time Values In Sql?

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

Compare Records In A Recordset?

Oct 28, 2010

Currently,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.

View 3 Replies View Related

SQL & PL/SQL :: Compare Varchar With Date

Feb 26, 2012

I 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 .

View 7 Replies View Related

SQL & PL/SQL :: Compare Columns Of Two Table?

Apr 5, 2013

I 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 Related

Forms :: How To Compare Two FMB Files

Jun 19, 2008

I 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 Related

SQL & PL/SQL :: To Compare Records In Two Columns

Oct 8, 2010

I 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.

View 31 Replies View Related







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