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
ADVERTISEMENT
Sep 20, 2012
There is a nested table with in a nested table type and i want to print the value and again assign a new value to the next subscript and i have tried a lot but couldn't find any solution.
declare
type type_name is table of varchar2(10);
type type_name1 is table of type_name;
names type_name1:=type_name1(type_name('hello'));
begin
-----HOW TO PRINT A VALUE--------
-----HOW TO ASSIGN A NEW VALUE TO NEW SUBSCRIPT
null;
end;
1) need to print the values of names(1)
2)Assign a value to names(2)
View 3 Replies
View Related
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
Mar 22, 2013
I am describing a SQL statement to get it's column list:DECLARE
cur NUMBER;
col_cnt INTEGER;
rec_tab DBMS_SQL.DESC_TAB;
[Code]....
Now I need to get out the columns list from rec_tab.col_name and put it to my_colls collection. Have Oracle any build-in to do that?
View 4 Replies
View Related
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
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
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
View Related
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
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
Feb 9, 2013
is there any way to find is rman backup sets are encrypted or not.
View 1 Replies
View Related
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
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
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
Oct 13, 2011
Currently I am working on conversion project. We want to verify the both applications updated database values are same or not.
if you run same transaction in both application values are updated in the database. I want compare both database tables, its updated same values in table or any mismatch in the table, is there any tools available right now to compare the two rows values in same tables.
View 2 Replies
View Related
Dec 15, 2010
Select
to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a1,
to_char(to_date('10-02-2006 01:30:00 PM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a2,
Case
when to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') >
[code]...
from the above query i was expecting value '2' but its returning '1'. As I am using TO_CHAR its trying to compare characters. Is there a way, to compare times alone like less than, greater than?
View 3 Replies
View Related
Apr 3, 2011
When we create sql and some pieces of sql are implemented as oracle function. and we run this sql only once every day. I read article that function after first run located in cache. This part of cache ( with function ), is it really consume one oracle resources? Or it will be erased after while.
View 3 Replies
View Related
Jul 27, 2010
I have two columns in database
INPUTDATE DDMMYYYY
and
OUTPUTDATE YYYYMMDD
I want to compare both the columns because of format of columns i am getting problem to compare.
View 2 Replies
View Related
Jun 28, 2010
SQL> select * from query;
CUSTO SWITCH DATE_X METRIC COUNT_X SEQUENCE_NO FILE
------- ------- --------- ------- ---------- ----------- ----
JCI S1 28-JUN-10 PORT1 10 4 TNB
JCI S1 28-JUN-10 PORT2 4 4 TNB
JCI S1 28-JUN-10 PORT3 8 4 TNB
JCI S2 28-JUN-10 PORT1 1 2 TNB
JCI S2 28-JUN-10 PORT2 5 2 TNB
JCI S3 28-JUN-10 PORT1 6 8 TNB
JCI S5 28-JUN-10 PORT2 2 4 TNB
------->s5 only in the current month-it was not in the previous month data collection
-----------------------------------------------------------------------------
JCI S1 28-MAY-10 PORT1 10 3 TNB
JCI S1 28-MAY-10 PORT3 5 3 TNB
JCI S2 28-MAY-10 PORT1 4 1 TNB
JCI S2 28-MAY-10 PORT2 2 1 TNB
JCI S2 28-MAY-10 PORT3 8 1 TNB
JCI S3 28-MAY-10 PORT2 7 7 TNB
JCI S3 28-MAY-10 PORT3 5 7 TNB
JCI S4 28-MAY-10 PORT1 2 10 TNB
------->s4 in the previous month data
15 rows selected.
1 SELECT A.CUSTO,A.SWITCH,
2 A.PORT1-B.PORT1,
3 A.PORT2-B.PORT2,
4 A.PORT3-B.PORT3
5 FROM (SELECT
6 A.CUSTO
[Code] ......
CUSTO SWITCH A.PORT1-B.PORT1 A.PORT2-B.PORT2 A.PORT3-B.PORT3
------- ------- --------------- --------------- ---------------
JCI S1 0 4 3
JCI S2 -3 3 -8
JCI S3 6 -7 -5
How to include the Switch S4 and S5 in the output. how implemented FULL OUTER JOIN
View 13 Replies
View Related
Jul 11, 2013
I ran SPA for SQL workload of around 94 SQLs.In comparison report that is generated by SPA, there is a metrics "Top 94 SQL Sorted by Absolute Value of Change Impact on the Workload".
In this metrics there is a column "Impact on Workload". This column hold a value in percentage.how this is calculated by SPA. What formula is used by SPA to calculate "Impact on Workload".
View 2 Replies
View Related
Mar 11, 2011
I am trying to compare the ranges of low pair and high pair,if they are within the range then source_conn_id should remain same,else it should be updated to null,which i had written it in else block.How can I implement the IF block and what to write in that block so that source_conn_id can remain the same.
SQL> CREATE OR REPLACE PROCEDURE fp_complements_src(p_id varchar2,ftr_con_id varchar2)
2 AS
3 BEGIN
4 FOR i IN(SELECT SOURCE_CONN_ID,LOW_PAIR,HIGH_PAIR FROM COMP_TEMP1 WHERE SOURCE_CONN_ID=ftr_con_id)
[Code]....
View 2 Replies
View Related
Jul 5, 2012
Provide details about performance comparison between Oracle Sun Sparc 10 and IBM P7 server running oracle 11gR2 for OLTP system.
I know that licensing wise Sun will be much cheaper but i want to know other aspects such as performance, storage, transaction per second. Basically on a nut shell which server to buy for our OLTP system.
View 1 Replies
View Related
Sep 25, 2008
When attempting to create the following table I recieve the error
count(m.mission_id) < 4
ERROR at line 9:
ORA-00905: missing keyword.
assist in order to resolve this error.
CREATE TABLE AM_agents
AS
(
SELECT
a.first_name || ' ' || a.last_name fullname,
'agent' person_type,
[code]...
View 4 Replies
View Related
Dec 9, 2008
Environment Setup
Oracle Server 11g on HP-UX
Oracle Client on Windows
I am using swingbench tool to generate load on DB and using OLTP like benchmark i am comparing the performance of plain data and encrypted data.
I have created two different database. one for tde and other for plain. I have populated same number of rows in both databases. Then i start running the benchmark and i use SAR to collect disk I/O's, VSAR to CPU usage.
From the sar report it seems that,
Oracle plain has faster transactions, it uses minimum CPU. But when look in tot the Reads/Writes TDE has lower than the plain.
If TDE needs to encrypt the data to store in the disks it should occupy more space than the plain data. Then the I/O should be more in TDE..
Note: Bcz the DB parameters are same, number of rows in the tables are same. File system and its block size are same. I will run the swingbench seperately for both the databases.
I am attaching the excel sheet for sar results. Let me know if you need more information
View 7 Replies
View Related
Apr 17, 2013
My primary objective was to compare objects in schemas in two different databases and find out the differences, Execute DDL's in the database where objects are missing and syn schemas in two different databases.
So I need to compare schemas in databases. Which tool will be user friendly to make a comparison of database objects existing in schemas in two different databases.
I'd like to see if I can get a list of pro and cons between Toad and SQL Developer for comparing schemas pros and cons. How to make a comparison. I have some idea on using TOAD but was not familiar with SQL Developer.
Below is my requirement:-
Connect to Source
Connect to Target
Compare schemas with different object types
Find out differences
Generate DDL's for the missing objects or for the objects in difference report
Run them in missing instace(Source/Target)
Make sure both are in sync.
View 2 Replies
View Related
May 25, 2011
Could it be that it's impossible to change the date format in the default_where clause?
The table column PROPOSAL_END in the database that I want to compare with, is in Format DD.MM.YYYY.
I tried:
set_block_property('Tours' , default_where, 'Number_of_places > 0 AND PROPOSAL_END <= ' || to_char(to_date(sydate,'DD.MM.YYYY')));
set_block_property('Tours' , default_where, 'Number_of_places > 0 AND PROPOSAL_END <= ' || to_char([-- A date item with the intial value $$date$$ the output is in Fomat DD.MM.YYYY by default --]));
set_block_property('Tours' , default_where, 'Number_of_places > 0 AND PROPOSAL_END <= ' || to_char(to_date([-- A date item with the intial value $$date$$ the output is in Fomat DD.MM.YYYY by default --],'DD.MM.YYYY')));
It all does dot matter. Every time the generated select-statement shows the format DD-MMM-YY. How can I change that?
View 6 Replies
View Related
Apr 17, 2013
I have a below requirement to compare the development and production objects.if any association_type or association_role are not exists in production then i need to return a message like "the Type Object found in Development,but not Production"
Below is the tree structure
development
ProcessingSite(Association type1)
TreatingSite(role1)
MoodedActivity(role2)
MaterialName(role3)
production
ProcessingSite(Association type1)
TreatingSite(role1)
MaterialName(role2)
Processing Site is an association_type and it is having 3 association_roles. we can observe same association_type in the production, but Mooded Activity(association_role) is not available. in this case we need to return "Type Object found in Development,but not Production".
View 2 Replies
View Related
Nov 4, 2013
We have migrated database data from physical servers to virtual servers. i want to ensure all database parameters are set correctly in both physical and vblock servers. My question is what are all the parameters need to check and compare in both servers to ensure database from both servers ( physical/vblock ) are in sync.
Database used -- RAC database
OS used -- RHL 5.7
View 5 Replies
View Related
Jul 25, 2013
I have written one program with dynamic SQL and piece of code is follows.
sql_stmt := 'SELECT '||CBID(i)||',BID,'||CBEID(i)||',''NA'',''NA'',''NA'' FROM DIM_ORGNISATION WHERE BID in(select PARENT_B_ID from ORG_DIM_LOD where CHILD_B_ID ='||CBID(i)||') and to_Date(start_Date,''DD/MM/YYYY'') = TO_DATE ( trunc('||Cstart_date_type(i)||'),''DD-MON-YY'',''NLS_DATE_LANGUAGE=ENGLISH'')'; EXECUTE IMMEDIATE sql_stmt BULK COLLECT INTO tempBID, tempSBD, tempLBD, tempL3BD, tempL4BD, tempSABD And ,
when i'm executing dynamic SQL gives the error as follows.
ORA-00904: "JAN": invalid identifierORA-06512: at "LWNER.SHY_CREATE_MAPING", line 184ORA-06512: at line 2
when displaying with using
DBMS_OUTPUT DBMS_OUTPUT.PUT_LINE('Cstart_date_type(i)'||Cstart_date_type(i)||)); It's diaplaying it as "01-JAN-70".
View 7 Replies
View Related
Apr 29, 2013
i have 2 tables in two different sources.
I have loaded data from source to destination.
some rows are missed while loading.
i want to know the missing rows
View 11 Replies
View Related
Jul 13, 2010
I want to determine the process which will give the difference of object structures in different environments.
View 10 Replies
View Related