SQL & PL/SQL :: Pivot Table Comparison Between Months

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


ADVERTISEMENT

SQL & PL/SQL :: Table Comparison - Loaded Data From Source To Destination

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

PL/SQL :: Split Result Of Table Data Comparison To Key(s) / Column / Old - New Each Row

Nov 8, 2012

I have two tables T1 and T2. T1 is the original backup snapshot for changed records from overnight batch in a big table and T2 is the overnight batch changed records. Both tables have similar number of rows (T2 might have more for newly inserted rows) and you can find out the differences by comparing these two according to action column in T2 (C - Update, A - Insert and D - Delete)

how to compare these two tables to generate something like the following. I can join these two tables to generate the diff but it is one row per account.

client_nbr branch_cd, account_cd, action column, old_value, new_value
8888 123 45678 C account_clsfn_cd 004 005
8888 123 45678 C buy_cd 98 99
8888 012 34546 A sell_cd 12
8888 321 98765 D dividend_cd 1

I am using Oracle 10g so Unpivot cannot be used.

CREATE TABLE T1
(
CLIENT_NBR CHAR(4 BYTE) NOT NULL,
BRANCH_CD CHAR(3 BYTE) NOT NULL,
ACCOUNT_CD CHAR(5 BYTE) NOT NULL,
ACCOUNT_CLSFN_CD CHAR(3 BYTE),
SELL_CD CHAR(2 BYTE),
BUY_CD CHAR(2 BYTE),

[code]....

View 4 Replies View Related

PL/SQL :: Pivot The Table

Sep 24, 2013

pivot the table. Table

dataNAMELINE_TYPE_REF_IDClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserskkondNode NamePB15CKYNum Contacts3ClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserdrcoulNode NameC4F6JJ1Num Contacts21ClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserdrcoulNode NameC4F6JJ1Num

[Code]....

View 4 Replies View Related

PL/SQL :: Pivot Table Sql

Apr 18, 2013

i have a table which looks like this

TRANS_ID TRANS_TYPE CONNECTION_DATE CLOSED_DATE
LK490 4 01-01-2009 01-01-2011
LK490 3 01-01-2010 01-01-2012

SELECT TRANS_ID, TRANS_TYPE, CONNECTION_DATE , CLOSED_DATE FROM TABLE_A

and want to pivot it like this

TRANS_ID 4_CONNECTION_DATE 4_ CLOSED_DATE 3_CONNECTION_DATE 3_ CLOSED_DATE
LK490 01-01-2009 01-01-2011 01-01-2010 01-01-2012

View 3 Replies View Related

SQL & PL/SQL :: Transpose (Not Pivot) A Table

Aug 18, 2010

I have a table in the following format:

CTitle1, CTitle2, CTitle3, CTitle 4, CTitle5
Row1Val1,Row1Val2,Row1Val3,Row1Val4,Row1Val5
Row2Val1,Row2Val2,Row2Val3,Row2Val4,Row2Val5
Row3Val1,Row3Val2,Row3Val3,Row3Val4,Row3Val5
Row4Val1,Row4Val2,Row4Val3,Row4Val4,Row4Val5
Row5Val1,Row5Val2,Row5Val3,Row5Val4,Row5Val5

I have an application that requires the data to be in the following format:

CTitle1, Row1Val1,Row2,Val1,Row3Val1,Row4Val1,Row5Val1
CTitle2, Row1Val2,Row2,Val2,Row3Val2,Row4Val2,Row5Val2
CTitle3, Row1Val3,Row2,Val3,Row3Val3,Row4Val3,Row5Val3
CTitle4, Row1Val4,Row2,Val4,Row3Val4,Row4Val4,Row5Val4
CTitle5, Row1Val5,Row2,Val5,Row3Val5,Row4Val5,Row5Val5

So I am truly trying to transpose the columns and rows and not doing a traditional pivot. How I can do this in Oracle 9i?

View 9 Replies View Related

SQL & PL/SQL :: Report Using Pivot Table

Jul 19, 2013

I have a table tab1 which contains columns Cust, ProdSeq, StartDat, EndDat, AttrId, AttrValue. I can have different attributes represented by attrId (say 1,2,3,4..etc.,) and its value by AttrValue respectively.

My question is for a given a customer, prodSeq and date range say 01-Jan-2013 to 31-Jan-2013, I want a report like below

Example input data
----------------------------------------------------------------
|CUST | PRODSEQ | STARTDAT | ENDDAT |ATTRID |ATTRVALUE |
----------------------------------------------------------------
|C1 | 1 | 1/1/2013 | 1/15/2013 | 1 | 1 |
|C1 | 1 | 1/1/2013 | 1/10/2013 | 2 | A |
|C1 | 1 | 1/1/2013 | | 3 | X |
|C1 | 1 | 1/1/2013 | | 4 | G |
|C1 | 1 | 1/11/2013 | 1/31/2013 | 2 | B |
|C1 | 1 | 1/16/2013 | 1/20/2013 | 1 | 2 |
|C1 | 1 | 1/21/2013 | 1/31/2013 | 1 | 3 |
----------------------------------------------------------------

Expected output
----------------------------------------------------------------------------------------
|CUST | PRODSEQ | STARTDAT | ENDDAT | ATTR1VAL | ATTR2VAL | ATTR3VAL | ATTR4VAL|
----------------------------------------------------------------------------------------
| 1 | 1 | 01-JAN-2013 | 10-01-2013 | 1 | A | X | G |
| 1 | 1 | 11-JAN-2013 | 15-01-2013 | 1 | B | X | G |
| 1 | 1 | 16-JAN-2013 | 20-01-2013 | 2 | B | X | G |
| 1 | 1 | 21-JAN-2013 | 31-01-2013 | 3 | B | X | G |
----------------------------------------------------------------------------------------

View 1 Replies View Related

SQL & PL/SQL :: Pivot Table Using UNIX Timestamp

Feb 23, 2010

I am trying to produce results in pivot format. I want to know how many entries per day per month. In other words how many entries on a Monday for each month, how many on a Tuesday for each month and so on.

The main problem I have is that the date column in the database uses UNIX time stamp. I have managed to get the results I want for a particular month. This code selects the entries for last month.

select
max(decode(pdayno, 1, cnt, null)) Sunday,
max(decode(pdayno, 2, cnt, null)) Monday,
max(decode(pdayno, 3, cnt, null)) Tuesday,
max(decode(pdayno, 4, cnt, null)) Wednesday,
[code].......

This code produces the result:

What I need is for another column at the start of the results to give the month so I end up with 12 rows, 1 for each month.

I did try to group by PENTERED(which is the unix time stamp column)
select pentered,
max(decode(pdayno, 1, cnt, null)) Sunday,
max(decode(pdayno, 2, cnt, null)) Monday,
max(decode(pdayno, 3, cnt, null)) Tuesday,
max(decode(pdayno, 4, cnt, null)) Wednesday,
[code]......

This gave me thousands of rows as each UNIX time stamp is unique. Is there a way of grouping on UNIX time stamp.

View 4 Replies View Related

PL/SQL :: Pivot Table With Variables Columns

Aug 27, 2013

I need a helo to pivot table with variable columns, I have a pivot table :

 SELECT a.*FROM (SELECT codigo_aluno,nome_aluno , id_curso,dia FROM c_frequencia where dia like '201308%') PIVOT (sum(null)   FOR dia IN ('20130805' ,'20130812','20130819','20130826'))

a but I need to run the select with values for dia , getting from a other table : 

SELECT a.*FROM (SELECT codigo_aluno,nome_aluno , id_curso,dia FROM c_frequencia where dia like '201308%') PIVOT (sum(null)   FOR dia IN (select dia from v_dia_mes ))

View 3 Replies View Related

Dynamic Pivot Table (Partition) Creation

Oct 31, 2010

I have 2 tables , which can be mapped with a common column and the second table's rest of the columns should become like columns(like pivot) and in the second table itself 1 more column called value , this sum of the value becomes a value to the pivot column value. For this I am using CASE structure and I am writing the individual case for each column , but I am sure after some extent case don't work at all and I would like to do it dynamically( when ever the new entry will come into the source table , my proc has to pick automatically the new value and put the new value as part of pivot structure) , so pls share your inputs and if possible provide some sample code.

View 3 Replies View Related

PIVOT XML Results To HTML Table In SQLPlus?

Jul 27, 2012

I have a PIVOT XML query that returns results that I want to display as an HTML report from SQLPlus. Is there a way that this can be done readily? I have searched the net an found references to XML Publisher but in my current situation we do not have the product available.

View 4 Replies View Related

SQL & PL/SQL :: How To Pivot Table So That Rows Become Column Names

May 9, 2012

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

See attachment for table creation and table data.

The table tbl_completed has two columns, id and completed. I have created a query that does provide me with the result set that I need, but I need to pivot the rows into columns. So instead of a result set to look like this:

select completed, count(completed) as theCount from tbl_completed group by completed;

COMPLETED THECOUNT
Y 772
N 720

I need a result set to look like this:

COMPLETED,Y,N
THECOUNT,772,720

The best solution that I have discovered is this, but I figuring their might be a better way.

select
(select 'THECOUNT' from dual) as COMPLETED,
(select count(completed) from tbl_completed where completed = 'Y') as Y,
(select count(completed) from tbl_completed where completed = 'N') as N
from dual;

COMPLETED,Y,N
THECOUNT,772,720

View 3 Replies View Related

Forms :: Pivot Table Inside Group

Mar 21, 2011

I want to use the pivot table inside the group.

View 3 Replies View Related

SQL & PL/SQL :: Show Result Of Query Just Like Pivot Table?

Jul 20, 2011

me in building a query. I want to show the result of the query just like pivot table.
Test case
CREATE TABLE CPF_YEAR_PAYCODE
(
CPF_NO NUMBER(5),
INC_DATE DATE,
PAYCODE_TYPE CHAR(1 BYTE),

[code]...

I want that my query should look like the format as attached in the xls sheet.

View 1 Replies View Related

Application Express :: Pivot Table Layout?

Nov 5, 2012

in my application i have a requirement as follows

1) column 1 indicator
2) column 2 important.

and the other columns are target actual and current year, previous year and next year.the layout should be in pivot table lay out as follows here the three columns will be saved dynamically as 2012,2011,2013 and the respecitive percentage in target and actual column

current_year previous_year next_year
target 10% 12% 5%
actual 5% 8% 5%

View 1 Replies View Related

SQL & PL/SQL :: File Data Versus Table Data Comparison

Jun 21, 2010

I have written a java code which reads 2 millions of data under a particular column from CSV file and store it into a set. Now there is a table in Oracle database which contains 10 millions of records for that particular column. Now, I want to form a SQL query which select those records under that particular column from the database table which is in CSV file but not in database table. For e.g.

If I consider the CSV file name as employee.csv and it has column called employee_name under which the records are as follows

employee_name
---------------------
Sudip
Kaushik
Joyanta
Soumya
Ritesh
Gautam

And the database table name called cmp_employee and it has column called employee_name under which the records are as follows

employee_name
--------------------
Sudip
Kaushik
Joyanta
Soumen
Souvik
Sahoo

Now, the "SQL Query" should return ---- "Soumya, Ritesh, Gautam" this three names.

View 2 Replies View Related

Comparison Rows In Two Different Database

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

SQL & PL/SQL :: Time Comparison Alone Like Less / Greater Than?

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

SQL & PL/SQL :: Comparison Function And Sql Statement

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

SQL & PL/SQL :: Date Comparison - Two Columns In Database

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

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

Client Tools :: SPA Comparison Report

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

SQL & PL/SQL :: Range Comparison - Low And High Pair

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

Performance Comparison Between Oracle Sun Sparc 10 And IBM P7

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

Unable To Use Count / Comparison Operations Within A Case?

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

Performance Comparison TDE Versus Plain Tablespace

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

Comparison Between Toad And SQL Developer To Compare Schemas

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

Forms :: Date Comparison In Default_where Clause

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

PL/SQL :: Comparison In Trees (development And Production Objects)

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

Server Administration :: Database Parameter Comparison

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







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