Forms :: Pivot Table Inside Group
Mar 21, 2011I want to use the pivot table inside the group.
View 3 Replies
ADVERTISEMENT
SQL & PL/SQL :: Group Through Expression Inside Group By?
May 17, 2011Refer 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
SQL & PL/SQL :: Pivot Query - ORA-00937 - Not A Single Group Function?
May 8, 2012I 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]...
Server Administration :: Organize Or Group Schemas Inside Instance?
Jul 8, 2011I 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.
PL/SQL :: Pivot The Table
Sep 24, 2013pivot the table. Table
dataNAMELINE_TYPE_REF_IDClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserskkondNode NamePB15CKYNum Contacts3ClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserdrcoulNode NameC4F6JJ1Num Contacts21ClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserdrcoulNode NameC4F6JJ1Num
[Code]....
PL/SQL :: Pivot Table Sql
Apr 18, 2013i 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
SQL & PL/SQL :: Transpose (Not Pivot) A Table
Aug 18, 2010I 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?
SQL & PL/SQL :: Report Using Pivot Table
Jul 19, 2013I 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 |
----------------------------------------------------------------------------------------
SQL & PL/SQL :: Pivot Table Using UNIX Timestamp
Feb 23, 2010I 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.
SQL & PL/SQL :: Pivot Table Comparison Between Months
Jun 28, 2010SQL> 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
PL/SQL :: Pivot Table With Variables Columns
Aug 27, 2013I 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 ))
Dynamic Pivot Table (Partition) Creation
Oct 31, 2010I 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 RelatedPIVOT XML Results To HTML Table In SQLPlus?
Jul 27, 2012I 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 RelatedSQL & PL/SQL :: How To Pivot Table So That Rows Become Column Names
May 9, 2012Oracle 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
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.
Application Express :: Pivot Table Layout?
Nov 5, 2012in 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%
Table Creation Inside Function
Feb 25, 2012i 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;
SQL & PL/SQL :: Function Returning A Table Inside Where Clause?
Apr 5, 2010DECLARE
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').
SQL & PL/SQL :: Create And Drop Table Inside The Procedure
Jun 10, 2013I'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]........
Forms :: Calling Function Inside The Procedure
Jun 2, 2011I 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 RelatedSQL & PL/SQL :: Function That Returns A Table Type Inside A Package Body
Jul 26, 2011CREATE 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?
Procedure To Generate Crosstable From Data Inside Calves_per_breed Table
Dec 11, 2006I 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]....
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.
Forms :: Load Html Page Inside A Canvas?
Dec 30, 2010I 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 RelatedForms :: Using PIPELINED Return Inside Custom Library
Jun 5, 2013I 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.
Forms :: Connecting To Other Database User Inside A Form
Mar 8, 2012I 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'
Application Express :: Exporting Inside Database In Export Repository - Owner And Table Name?
Jun 12, 2013We 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 RelatedGroup Records With Less Than One Hour Separation And Count How Many Per Group
Nov 1, 2013I'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.
Forms :: How To Use Group By Command In 6i
Oct 28, 2011How to use group by command in forms 6i
View 4 Replies View RelatedGroup By Month - Query A Transaction Table?
Oct 19, 2010sql 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