SQL & PL/SQL :: Summary (Row To Column)
Jun 1, 2011
SQL summary for below data:
Table: temp_user_access
NAME Null? Type
------------------------------- --------- -----
USERGROUP VARCHAR2(255)
USERNAME NOT NULL VARCHAR2(40)
NICKNAME VARCHAR2(40)
AAACC VARCHAR2(70)
SYSTEM VARCHAR2(10)
I need the expected result as per attached photo
View 23 Replies
ADVERTISEMENT
Sep 25, 2011
I have one table which has two columns name,qty and it has data like arif,3 pcs i want to display it in 3 lines if the qty is 3 and in 2 lines if the qty is 2 using sql query
View 6 Replies
View Related
Jul 22, 2011
this is my table
create table sequence ( id int not null primary key);
insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);
i need the answer to group the sequence like this
start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1
what should i do?
View 1 Replies
View Related
Jul 22, 2011
this is my table
create table sequence (
id int not null primary key
);
insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);
i need the answer to group the sequence like this
start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1
what should i do?
View 7 Replies
View Related
Nov 30, 2010
I brought in the Northwind database 2003 in to SQL Developer. I was looking at the view 'Summary of Sales by Quarter' But this report brings in everything. It doesent give me summary of sales for Qtr1, Qtr4 etc
These are the columns:
ShippedDate
OrderId
Subtotal
CREATE OR REPLACE FORCE VIEW "NORTHWIND"."SUMMARY_OF_SALES_BY_QUARTER" ("SHIPPEDDATE", "ORDERID", "SUBTOTAL")
AS
[code]....
How can i recreate this view to show me summary data for qtr1, qtr2, qtr3 and qtr 4 for the year 1997??
View 1 Replies
View Related
Mar 26, 2012
I have been sweating on this since the weekend.
My source table
SELECT
'182383' EMPLOYEE_NO
, 'ABACUS' FULL_NAME
, 'ADMIN' DEPARTMENT
, 'ADMIN' PREV_DEPARTMENT
, TO_DATE(20090104,'YYYYMMDD') EMP_START_DATE
[Code]....
EFFECTIVE_START_DATE: SCD field, Date ABACUS moves from one department to another.
EFFECTIVE_END_DATE: SCD field, Date ABACUS left the department from another. To keep history of movement.
The sample report shows possible scenario on my data.
I want to build monthly report to cater from departmental headcount on monthly basis, starting from from ABACUS start date.
see sample report below
MONTH | DEPARTMENT |NEW_HIRES | RELOCATION (OUT) | RELOCATION (IN) | NET TOTAL PREV MONTH | NET TOTAL PRESENT MONTH |
WHERE
MONTH: REPORT MONTH
DEPARTMENT: THE DEPARTMENTS IN THE ORGANIZATION
NEW_HIRES: NUMBER OF NEW STAFFS HIRED THAT MONTH BASE ON EMP_START_DATE
RELOCATION_OUT: NUMBER OF STAFFS MOVEMENT TO ANOTHER DEPARTMENT (EMP_END_DATE IS NULL, EFFECTIVE_START_DATE AND
[Code]....
View 4 Replies
View Related
Aug 13, 2013
I have a below requirement from client.
Create a procedure generate_daily_session_summary that will read the pcm_session_summary (pk sess_id) table and create a per day summary in the pcm_session_summary_daily table.
1. Summarize all records from pcm_session_summary for session start timestamp (START_TS) greater than the max existing SESS_DT from pcm_session_summary_daily table and less than the start of current day. Insert the records into the pcm_session_summary_daily table.
2. Populate the fields in pcm_session_summary_daily as follows :
sess_dt - truncated date from pcm_session_summary.start_ts unique_user_cnt - count of unique cws ids encountered in that day, sess_cnt number - count of total sessions in that day , max_concurrent_sess_cnt number - maximum number of concurrent sessions at any point of time in that day. Concurrent sessions are defined as those that have overlapping start_ts or end_ts - sessions that were active at the same moment in time would be concurrent with each other.
3. The procedure will be scheduled to run every day in a batch job and will summarize session data until the end of the previous day
I have the table with below details.
desc PCM_SESSION_SUMMARY
Name Null Type
---------- -------- ------------
SESS_ID NOT NULL NUMBER
USER_CUPID VARCHAR2(10)
START_TS TIMESTAMP(6)
END_TS TIMESTAMP(6)
ACT_CNT NUMBER
[code]....
I have written the below query to get the result for question no:1
select trunc(start_ts,'DDD') start_ts,count(distinct(user_cupid)) user_cnt,count(SESS_ID) Sess_cnt
from PCM_SESSION_SUMMARY
where START_TS < sysdate or START_TS > (select max(sess_dt) from pcm_session_summary_daily where sess_dt < sysdate)
group by trunc(start_ts,'DDD')
order by start_ts;
However i tried a lot but not sure how to get maximum concurrent session count for a day.One of my friend comment below.
The Pcm_session_summary_daily is empty so you need to handle nulls when doing max. That will fix the issue of no results for the Greater Than condition.
You need to get concurrent count of sessions with respect to one another, the query that you have will not work. Do it in PL/SQL using procedural logic. The queries would return multiple rows, you need to handle that.
View 3 Replies
View Related
Dec 19, 2012
Why cant oracle give an extra parameter for summary function like MAX and MIN...For example
select max(salary,2)
from empWill give the second max salary.
View 11 Replies
View Related
Aug 1, 2012
I've been experiencing strange Oracle behavior from time to time when using "explain plan". what could be the reason? (I'm going to create an Oracle ticket when I could find the cause...)
The total cost of a query was low (ex. ~500) while sub-parts of the query has high cost (ex. ~10000).I don't want to provide the query itself as the issue is observed for huge queries (like 2M characters!).The execution plan looks like this:
+- SELECT STATEMENT , ALL_ROWS, Cost 736
|
+--+- WINDOW SORT , Cost 736
|
[code]...
View 5 Replies
View Related
Apr 9, 2013
we have just upgraded discoverer from 3.1 to 4.1
and we click on create summary folders we got this error Summary management not available because : this user has no privilige or quota to create materialized view in the current schema External summary registeration not available because : this user has no privilige or quota to create materialized view in the current schema
altough i granted privilige for materialized view
View 1 Replies
View Related
Aug 25, 2012
I have one group wise report and my requirement is i need to print both the Outputs in one single report first, is group with details and the summary in the last page showing machine code and summary of qty as totals ,I have given below test case .
create table ot_cut_detail (mach_code varchar2(12),batch_code varchar2(12),pos_no varchar2(12),pos_qty number)
insert into ot_cut_detail values ('L1','01','1001',2);
insert into ot_cut_detail values ('L1','01','1002',2);
insert into ot_cut_detail values ('L1','01','1003',2);
insert into ot_cut_detail values ('L2','01','1004',2);
insert into ot_cut_detail values ('L2','01','1005',2);
SQL> SELECT * FROM OT_CUT_DETAIL;
MACH_CODE BATCH_CODE POS_NO POS_QTY
------------ ------------ ------------ ---------
L1 01 1001 2
L1 01 1002 2
L1 01 1003 2
L2 01 1004 2
L2 01 1005 2
--Detailed output is like this as below
MACH_CODE BATCH_CODE POS_NO A
------------ ------------ ------------ ---------
L1 01 1001 2
1002 2
1003 2
L1 Total 6
L2 01 1004 2
1005 2
L2 Total 4
Grand 10
--Summary report will be as below both in one report.
MACH_CODE BATCH_CODE totals
------------ ------------ ---------
L2 01 4
L1 01 6
View 14 Replies
View Related
Aug 24, 2007
I'm wanting to create a query that will give me a summary of parts and labor from work orders. However there are three tables. work_order, parts, labor. Sometimes there will only be parts, sometimes just labor, or sometimes both. Well my query will only return results when they are on both. Sample query is below. Do I need to create a temp table to gather the data or is there a way to do this with a sub query.
select work_order.wono, sum(parts.cost), sum(labor.cost)
from work_order, parts, labor
where
work_order.wono = parts.wono and
work_order.wono = labor.wono
group by work_order.wono;
View 5 Replies
View Related
Nov 25, 2010
I have a datablock like this
datablock name : jou_tra1_tab
No. of record dispalyed is 5
so datablock display like this
jtt_amt jtt_amt_1 ck_amt
jtt_amt jtt_amt_1 ck_amt
jtt_amt jtt_amt_1 ck_amt
jtt_amt jtt_amt_1 ck_amt
jtt_amt jtt_amt_1 ck_amt
----------------------------
ck_amt_tot
ck_amt is checkbox..
ck_amt_tot is total of jtt_amt_1 [but total only those record whose checkbox is checked]
My task is like this When i checked checkbox whatever value in jtt_amt transfer to jtt_amt_1 field
but i can change value in jtt_amt_1 field ...i want to take addition of that changed field and show that sumation in ck_amt_tot.
I write trigger when-checkbox-changed like this
-------------------------------------------------
IF :jou_tra1_tab.ck_amt = 'Y' THEN
set_item_INSTANCE_property ('jou_tra1_tab.jtt_amt_1',CURRENT_RECORD, UPDATE_ALLOWED, PROPERTY_TRUE);
:jou_tra1_tab.jtt_amt_1 := :jou_tra1_tab.jtt_amt;
else
:jou_tra1_tab.jtt_amt_1 := 0;
END IF;
and when validation item trigger for jtt_amt_1 :
--------------------------------------------------
IF :jou_tra1_tab.ck_amt = 'Y' then
:jou_tra1_tab.ck_amt_tot := :jou_tra1_tab.ck_amt_tot + :jtt_amt_1 ;
else
:jou_tra1_tab.ck_amt_tot := :jou_tra1_tab.ck_amt_tot - :jtt_amt_1 ;
But when i changed value in jtt_amt_1 field i cant get write summation .
View 1 Replies
View Related
Sep 20, 2013
how to put exceptions while defining the summary item ? In one of my datablock there are certain lines whose "inactive_flag" gets a "Y" value on certain events. I want my summary item to exclude such amounts from summing up.
View 3 Replies
View Related
Jun 30, 2011
I have got another curly one with regard to Sql Loader. My original issue via [message #513696] was resolved. In summary, SQL Loader opens a comma delimited csv file and it is able to successfully populate the appropriate Oracle table. However, during the run to process each unique csv file, SQL Loader produces a both a .log file (indicating what transpired during the load) and a .bad file of the records from the csv file that where rejected by SQL Loader.
the users have requested that "is it possible to update a separate table of the summary results of what went wrong and what was right?" It just so happens that the csv file contains some summary records (rows) with processing audit infor...as the following .bad file wrote out....
___________________________
ModuleID,ModuleSN,Lat,Lon,GMT Time,Tag Count,GMT Date,Client,Farm,Field,Variety,Machine
No Module ID ,No SN ,-30.178958,149.63844,9:55:58,0,21/04/2011,101,Widgen ,9,71BRF ,5987
3500B9880611170260179BFB,10202094587,-30.199379,149.661333,7:10:35,2,22/04/2011,101,Widgen ,30,71BRF ,5987
,,,,,,,,,,,
Total Modules,1602,,,,,,,,,,
Mis-reads,1,,,,,,,,,,
Mis-read %,0.06,,,,,,,,,,
1. The first record (starting in the 1st postion is "ModuleID") is a heading record in the csv file and can be disregarded.
2. The secord record (starting in the 1st postion is "No Module ID") is an error record in the csv file and can be disregarded.
3. The third record (starting in the 1st postion 3500B9880611170260179BFB, ) is a valid record, however, is was rejected as it is a duplicate of a preceeding record ...and the log indicates ...
Record 124: Rejected - Error on table PTLIVE.MODULE_CSV_LOADS.
ORA-00001: unique constraint (PTLIVE.MODULE_SERIAL_NUMBER_UK) violated
4. The fourth record (starting in the 1st postion is ,,,,,,,,,,,
) is a blank record in the csv file and can be disregarded.
5. The fifth record (starting in the 1st postion is "Total Modules" has a value of 1602 which I need to capture to write into a separate table.
6. The sixth record (starting in the 1st postion is "Mis-reads" ) has a value of 1 which I need to capture to write into a separate table.
7. The last record in the csv file and can be disregarded.
The summary Oracle table is
CREATE TABLE PTLIVE.SUBMODULES_SUMMARY
( IMPORT_CSV_FILE_NAME VARCHAR2(256 ),
TOTAL_SUBMODULES_LOADED NUMBER (5),
TOTAL_SUBMODULES_MIS_READ NUMVER (5),
DATEIMPORTED DATE default SYSDATE
)
Now I would like to trap the value 1602 and load that into the Oracle column TOTAL_SUBMODULES_LOADED and the value 1 and load this into TOTAL_SUBMODULES_MIS_READ.
The name of the csv file is known and I can (as the previous [message #513696] resolved) simply use a SQL Loader control file command > IMPORT_ CSV_FILE_NAME CONSTANT "Batch_2011Jun29_TEST.csv".
View 2 Replies
View Related
Sep 8, 2013
See my data
Location--------Employee-------------Sales-----------------------Sales per emp
1------------------ 4-----------------------100------------------------------25
2------------------ 5-----------------------120------------------------------20
3------------------ 7-----------------------210------------------------------30
Total---------------16-----------------------430-----------------------------27
The total 27 achieve by (430/16)=27 How can I get using ORacle BI Discoverer 11g Desktop
View 7 Replies
View Related
Apr 27, 2012
how can i monitor the instance status and log switch summary.
View 8 Replies
View Related
Oct 15, 2013
I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically
ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')
500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498
View 1 Replies
View Related
Apr 10, 2013
On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.
I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.
So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:
a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?
The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.
---
"Application Express 4.2.1.00.08"
View 2 Replies
View Related
Jul 13, 2010
I receive the error,
REP-1241 Circular column dependency originating with column 'cenvat_opening'
I have the following return statement for a column total_credit;
function total_creditFormula return Number is
begin
Return (return nvl(:cf_cenvat_closing ,0) - nvl(:cf_duty_on_goods_ed,0);
end;
Now i am using this total_creditFormula for the error formula column 'cenvat_opening'
function cenvat_openingFormula return Number is
f_num_opn number(14,2);
begin
SELECT NVL(Amount,0) INTO F_NUM_OPN
[code]...
here the formula column 'total_credit' has the following return statements;
RETURN Nvl(:cenvat_opening,0) + Nvl(:cenvat_credit_manu,0)
so how can i use this formula column? is there any other option to return the value in the same column.
View 6 Replies
View Related
Jul 22, 2009
I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.
I have copied the code used in
select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);
View 11 Replies
View Related
May 15, 2011
i have two questions.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
View 3 Replies
View Related
Nov 21, 2010
This is what i want to do:
I have a basic report looking like this:
<Column name>
value 1
value 2
value 3
.
.
.
value n
Since the column is small it can fit in a page more then one time. I know how to make it print more then once: i switch the repeating frame to print down and across and i modify the frame that contains it so the horizontal elasticity is variable.
After these changes my report looks like this:
<Column name>
value 1-----------------------value n+1-----------------------value m+1
value 2-----------------------value n+2-----------------------value m+2
value 3-----------------------value n+3-----------------------...
.------------------------------- .
.------------------------------- .
.------------------------------- .
value n-----------------------value m
What i want is my report to look like this:
<Column name>-------<Column name>------------<Column name>
value 1-----------------------value n+1-----------------------value m+1
value 2-----------------------value n+2-----------------------value m+2
value 3-----------------------value n+3-----------------------...
.------------------------------- .
.------------------------------- .
.------------------------------- .
value n-----------------------value m
View 4 Replies
View Related
Sep 10, 2012
I have written the code for sql loader, I have given termination of file is | , i have given five columns name in sequence...My Requirement is that i want to mapped 100th column from Csv file after 5th column how to do it.
OPTIONS (ERRORS=100000000)
LOAD DATA
CHARACTERSET UTF8
INTO TABLE "TARGET_STG_AMC"
TRUNCATE
[code]....
View 10 Replies
View Related
Feb 11, 2013
We are using Oracle 11g with Apex 3.2 on AIX. We are reporting data from customer satisfaction surveys. I'm using the following sql to create my report
<code>select * from(
select month,'Overall Satisfaction' as q_group, 1 as srt,Overall Satisfaction,site,
case when count(*) < 31 then '*' else
round((sum(ttos)/count(*))*100,0)||'/'|round((sum(bfos)/count(*))*100,0)||'/'||count(*) end ospct
from v_XXX_report a,(select distinct month_dt month from v_XXX_report) b
where Overall_Satisfaction is not null and year_dt = 2012
[Code]....
The problem is that site is not allways present and sometimes I have other variables in addition to site. This creates a situation where the month columns will not allways appear at the same column number. For example, When I run this query as is then the "JAN" column is Col3 (first column is a break, col2 is not shown). When I run this query without site then "JAN" is the second column. I would like to create column links for the "JAN" - "DEC" columns but not for any other columns.
Another issue - in the column link creation screen I can create up to 3 variables that I can pass to the next page. Since my query is a pivot I'm uncertain how to pass the column heading or the row value (for col2)
ie
Overall Satisfaction JAN FEB MAR APR MAY ...
Overall Satisfaction 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
Recommend 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
etc.
So if I clicked on the values at Recommend:FEB how can I get "Recommend" and "FEB" into variables that I can use on the next page? I've tried #column_name#, #month#, #q_name# and #APEX_APPLICATION.G_F10# but no luck.
View 0 Replies
View Related
Jun 19, 2012
I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?
View 3 Replies
View Related
Jul 19, 2013
CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
View 4 Replies
View Related
Mar 21, 2013
I have a table where i have description column which free text column, the data in description column is seperated and i want to corvert 1 row data in multiple rows dependeing on the number of words.
eg
id description
78664 Pumps Alarm from CAMS RTU154
In the above example this column has 5 word so i want data in 5 rows like below
78664 Pumps
78664 Alarm
78664 from
78664 CAMS
78664 RTU154
This column data can be varied from 1 to any number of words.
View 11 Replies
View Related
Sep 10, 2010
I am trying to create a trigger which changes the content of one column based on the information within another column.
For Example, if the 'ITEMQUANTITY' field drops below 1 then I want the STATUS column to say 'Out Of Stock'.
View 23 Replies
View Related
Jul 23, 2013
is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:
select ass.column20, ass.column30from table_a pucinner join table_b asson ass.column1 = puc.column1where pc.column_char = 'S'and ass.column_char02 = 'P'
If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO
View 7 Replies
View Related
