Forms :: Pivot Table Inside Group

Mar 21, 2011

I want to use the pivot table inside the group.

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Group Through Expression Inside Group By?

May 17, 2011

Refer to the txt file to create table and insert data.

I executed the following query-

SELECT priority, detail, COUNT(1) FROM TEST GROUP BY priority, detail

and got the following result-

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial TSS 1
StandardInitial development 10
StandardProduction deployment5
High PriorPatch 1

Now I want that Initial TSS and Initial development should be combined as Initial together and I should get the result as follows:

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial 11
StandardProduction deployment5
High PriorPatch 1

View 3 Replies View Related

SQL & PL/SQL :: Pivot Query - ORA-00937 - Not A Single Group Function?

May 8, 2012

I am having the following pivot query but I am not able to run it is giving error msg

ORA-00937: not a single-group group function

SELECT DISTINCT
C.SHORT_NAME,
MAX (
COUNT (DECODE (TO_CHAR (a.reg_date_cur, 'MON'), 'JAN', A.CAR_NO))

[code]...

View 6 Replies View Related

Server Administration :: Organize Or Group Schemas Inside Instance?

Jul 8, 2011

I have a lot of shemas inside an instance... How to organize them?

What I see is a long list of schemas which could be difficult to manage if number of schemas grows up... For example, I'd like to group Schema_1, Schema_2 and Schema_3 in something like Group_1, so it could be seen more organized.

Group_1
Schema_1
Schema_2
Schema_3

Sometimes a java or .net application uses 3 or 4 schemas... these schemas are in the same instance which has other schemas for other applications.

View 2 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

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

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

Table Creation Inside Function

Feb 25, 2012

i am trying to create table inside function where in after creating table when am trying to access the table with select statement oracle is throwing error 'Table/view doesnot exist -00942', below is the code snippet

create or replace function example (mkey in varchar2) return varchar2
is
g_key varchar2(100);
l_tbl_ntext exception;
pragma exception_init(l_tbl_ntext , -942);
begin

begin
execute immediate 'select * from example1';

exception
when l_tbl_ntext then
null;
end;
execute immediate 'create table example1(skey varchar2, g_key varchar2) storage(buffer_pool, keep)';
end example;
/

select * from example;

View 8 Replies View Related

SQL & PL/SQL :: Function Returning A Table Inside Where Clause?

Apr 5, 2010

DECLARE
cnt number(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227')));
DBMS_OUTPUT.PUT_LINE (cnt);
END;

MY_PACK.STR2TBL() is a function which takes '|' delimited string, extracts values and returns a table of zipcodes. The function works fine and returns 46227 but the count returned is 0 instead of 280(count returned by replacing inner select with '46227').

View 22 Replies View Related

SQL & PL/SQL :: Create And Drop Table Inside The Procedure

Jun 10, 2013

I've to create a table every time a procedure is run, initially the table should be dropped and then created every time. From this procedure, the table is neither created nor dropped.

I cant track the error. Why is it so?

CREATE OR REPLACE PROCEDURE TESTPROC IS
S_SQL VARCHAR2(1000);
BEGIN
S_SQL := 'DROP TABLE MYTEST PURGE';
EXECUTE IMMEDIATE S_SQL;
[code]........

View 18 Replies View Related

Forms :: Calling Function Inside The Procedure

Jun 2, 2011

I have created a function in form field(when validate item) this should be called in separate procedure. How to call this function in procedure?

View 4 Replies View Related

SQL & PL/SQL :: Function That Returns A Table Type Inside A Package Body

Jul 26, 2011

CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....

I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?

I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?

View 10 Replies View Related

Procedure To Generate Crosstable From Data Inside Calves_per_breed Table

Dec 11, 2006

I have two Tables, the one table is called (calves_per_breed) and contains all my query results. I then have another table (calves_per_breed_crosstable) which is used to place the generated count values in calves_per_breed to create a crosstable from the count data into calves_per_breed_crosstable.

I'm using the following procedure to generate the crosstable from the data inside the calves_per_breed table:

PROCEDURE pcalves_per_breed_genCrossTable
IS
BEGIN
---------------------------------------------------------------------------
--SEX CODES
update calves_per_breed_crosstable
set (m, f) = (select count(decode(geslag, 'M', 1, null)),
count(decode(geslag, 'F', 1, null))
from calves_per_breed)
[codee]....

Now this procedure works 100%, the only problem is it generates the ENTIRE table. if you know how a crosstable works, thetop right section of the table is exactly the same as the bottom left section of the table. I wish to optimize my code so that it only generates the values for the needed columns, and not ALL the columns, as values are generated twise now, which increases the query time! Here is a tipical output of the kalwers_per_ras_crosstable:

Note: You will notice that i used a Column called TID with string values to indicate the vertical columns for the crosstable. The Vertical Columns are the same as the top Columns(which are actual columns, and not row values as the vertical columns)

select *from kalwers_per_ras_crosstable:

TID M F NFR A B C SP RED BLACK SC1 SC2 SC3
----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
M 138 0 0 0 0 0 138 122 16 74 64 0
F 0 173 0 0 0 0 173 161 12 92 81 0
NFR 0 0 0 0 0 0 0 0 0 0 0 0
A 0 0 0 0 0 0 0 0 0 0 0 0
[code]....

View 5 Replies View Related

Forms :: Code To Store Images Inside Database

Jan 11, 2011

//To load an image from the Hard Disk
//Level : Item
//Type : Trigger
//Name : When Button Pressed
Declare
[code]......

Now what should be the code to store this image from the image box inside the database? I done a lot of time with Oracle Data Base 11g with the Form Builder 6i and 10g but all in vain . No image store inside the database but browse button work well.

View 10 Replies View Related

Forms :: Load Html Page Inside A Canvas?

Dec 30, 2010

I have got a requirement to split a canvas into two and load one portion with forms elements and the other with the HTML page.

View 5 Replies View Related

Forms :: Using PIPELINED Return Inside Custom Library

Jun 5, 2013

I would like to know if it's possible to use PIPELINED return inside a custom library with Oracle Forms Builder? For now, i try to use it, but i get error about client side.

my code.

PACKAGE TEST IS
TYPE measure_record IS RECORD(
l4_id VARCHAR2(50),
l6_id VARCHAR2(50),
[code].......

the body compile, but not the other part. I would like after call the select * from test.get_ups(0); with a cursor into another function.

View 3 Replies View Related

Forms :: Connecting To Other Database User Inside A Form

Mar 8, 2012

I have a problem concerning change of the connection inside a form. I have this code in when validate item trigger

LOGOUT ;
LOGON ('FIFES_HN','FIFES_HN@PRD_253');
MESSAGE (GET_APPLICATION_PROPERTY(USERNAME));
MESSAGE (GET_APPLICATION_PROPERTY(USERNAME));
MESSAGE (:DAILY_TRX_CONT.ORG_ID);
[code].........

how the last message is 'FIFES_HN' it show be 'SIFESD'

View 7 Replies View Related

Application Express :: Exporting Inside Database In Export Repository - Owner And Table Name?

Jun 12, 2013

We are exporting our apex application inside the database in the Export Repository.  what owner and table name are those export located exactly?  

View 1 Replies View Related

Group Records With Less Than One Hour Separation And Count How Many Per Group

Nov 1, 2013

I'm trying to group sets of data based on time separations between records and then count how many records are in each group.

In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5

SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';

[code]....

Group 1 = 8:00:22 to 8:41:22

Group 2 = 11:35:47 to 11:35:47

Group 3 = 15:13:46 to 15:13:46

Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.

View 4 Replies View Related

Forms :: How To Use Group By Command In 6i

Oct 28, 2011

How to use group by command in forms 6i

View 4 Replies View Related

Group By Month - Query A Transaction Table?

Oct 19, 2010

sql statement to query a transaction table that stores transactions of items bought from my organisation.The report i would like to generate is one that lists the items bought and this should be grouped month by month.

View 2 Replies View Related







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