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]...
I want to use the pivot table inside the group.
View 3 Replies View RelatedIs any access rights to be given for using pivot function...i tried below query but it throws error
select * from BI_BALNCE_SHEET
PIVOT(sum(balance) for gl in('Income','Expenditure'));
Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
I have a question about nested materialized view.
Firstly, I have created 3 mv log on 3 table(target,target extension,brand). Secondly, I created the first mv and its log.
Lastly, I created the second mv from the first mv. This time I used the pivot function, but it cannot work now.
--1 create mv log
drop MATERIALIZED VIEW LOG ON target;
drop MATERIALIZED VIEW LOG ON targetextension;
drop MATERIALIZED VIEW LOG ON brand;
CREATE MATERIALIZED VIEW LOG ON target with rowid, sequence(id);
CREATE MATERIALIZED VIEW LOG ON targetextension with rowid, sequence(targetid,brandid,EmailPermission,NumberOfAllOrders);
CREATE MATERIALIZED VIEW LOG ON brand with rowid, sequence(brandid, brandname);
--2 create the first mv and it's log
drop MATERIALIZED VIEW mv_target1;
CREATE MATERIALIZED VIEW mv_target1
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/1440
AS
select t1.rowid t1_rowid, t2.rowid t2_rowid, t3.rowid t3_rowid,
t1.id targetid,
t3.brandname,
[code].....
--3 create the second mv
drop MATERIALIZED VIEW mv_target2;
CREATE MATERIALIZED VIEW mv_target2
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/1440
AS
select * from mv_target1
pivot(max(EmailPermission) EmailPermission, max(NumberOfAllOrders) NumberOfAllOrders for brandName in ('XXX' XXX,'YYY' YYY ,'ZZZ' ZZZ));
Now, Here is a problem, it throws "ORA-12015: cannot create a fast refresh materialized view from a complex query". Then I used dbms_mview.explain_mview to see the reason, and it tell me the following
REFRESH_FAST_AFTER_INSERT "inline view or subquery in FROM list not supported for this type MV"
declare
lv_sqltext varchar2(4000);
begin
execute immediate 'truncate table mv_capabilities_table';
lv_sqltext := 'select * from mv_target1
pivot(max(EmailPermission) EmailPermission, max(NumberOfAllOrders) NumberOfAllOrders for brandName in (''XXX'' XXX,''YYY'' YYY ,''ZZZ'' ZZZ))';
dbms_mview.explain_mview(lv_sqltext,'nested=>TRUE');
commit;
end;
/
I have a requirement to write a single sql query where i can generate the pivot report. Found some of the examples in Google search. But here we are hard coding the values if it is limited like month in this example.
i want to write similar query to represent the amount based on product type , i have around 200 types of products. I can't write case/ decode statement those many times.
query which will produce the output in pivot format , dynamically depending the number of values.
select Product,
sum(case when Month=�Jan� then Amount else 0 end) Jan,
sum(case when Month=�Feb� then Amount else 0 end) Feb,
sum(case when Month=�Mar� then Amount else 0 end) Mar
from Sales
group by Product
Is there anyway to pivot the results of a query?
so if i have:
SELECT GROUP, count(*)
FROM GROUP
GROUP BY GROUP
And it give the following output:
A 10
B 50
c 24
Is there anyway to put into this format?
A B C
10 50 24
I am doubting that there is and that i am going to have to handle this in my code later, but it never hurts to ask!
I've tried for pivot query feature of Oracle 11g, but I'm trying for pivot result on multiple column.
Herewith I'm displaying my try on single column pivot query.
SQL> select * from
2 (select deptno,job,sal
3 --,comm
4 from emp)
5 pivot (sum(sal) as payment for job in('CLERK','SALESMAN','MANAGER'))
6 order by 1;
[code]....
I've tried this one also, but it didn't seems to be working.
SQL> select * from
2 (select deptno,job,sal,comm
3 from emp)
4 pivot (sum(sal) as payment_sal,sum(comm) as payment_comm for job in('CLERK','SALESMAN','MANAGER'))
5 order by 1;
[code]....
I want result like below.
DEPTNO PAYMENT 'CLERK'_PAYMENT 'SALESMAN'_PAYMENT 'MANAGER'_PAYMENT
---------- ------- --------------- ------------------ -----------------
10 SAL1300 2450
20 SAL1900 2975
30 SAL 950 5600 2850
30 COMM 2200
is it possible to have multiple column pivot query.
I'm using pivot query feature of oracle 11g and came across a strange situation where i need to pass a "select statement" in a "in clause" of pivot query.
SQL> CREATE TABLE TEST1
2 (
3 UIN NUMBER(8) NOT NULL,
4 TESTING_ID NUMBER(4),
5 PFA_RESULT VARCHAR2(30 BYTE)
6 );
[code]....
I have tried with pivot xml but it not giving desired output in sql*plus session.It is giving unreadable output.
select * from
(select uin,testing_id,pfa_result from test1)
pivot xml (max(pfa_result) as result
for (testing_id) in (select distinct testing_id from test1));
[code]....
Here actually i want to use "select distinct id from test1" instead of "in (11,12,13,14,15)". Because i don't know how many id's will be there and of which values. e.g. 11 or 21 or 25.
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.
I am attempting to user PIVOT and DENSE RANK in a query the following is the query and the record set it returns (condensed and de-identified)
"select * from(
select * from (select dense_rank() over (partition by 1 order by cal.weeksort desc) WEEK_nbr,
u.user_title Manager_Title, replace(hier.manager, '<br>',' - ') Manager,
replace(hier.user_hin, '<br>',' - ') user_name,
to_char(cal.calendar_date_week - 6, 'MM/DD/YYYY') ||' - '|| to_char(cal.calendar_date_week, 'MM/DD/YYYY') Week_of,
upper(substr(cal.day_of_week, 1,3)) DOW, count(distinct Pers_gen_key) cnt from apexim.hrw_member_action act
[code]....
creating Oracle SQL query to fetch the information using PIVOT option.We are populating audit table using triggers. For every update, there will be two rows into audit table, one row with all OLD values and another with all NEW values. Also every updated is uniquely identified by Sequence No. Example for phone audit is mentioned below :
CREATE TABLE test_audit_phone
(
emplid VARCHAR2(10),
seqno NUMBER,
action VARCHAR2(3),
office NUMBER,
mobile NUMBER
);
Insert some rows into table.
INSERT INTO test_audit_phone VALUES ('100',1,'OLD',1111,9999)
/
INSERT INTO test_audit_phone VALUES ('100',1,'NEW',2222,9999)
/
INSERT INTO test_audit_phone VALUES ('100',2,'OLD',2222,9999)
/
INSERT INTO test_audit_phone VALUES ('100',2,'NEW',2222,8888)
/
Table will look like the following :
SQL> SELECT * FROM sysadm.test_audit_phone ;
EMPLID SEQNO ACT OFFICE MOBILE
---------- ---------- --- ---------- ----------
100 1 OLD 1111 9999
100 1 NEW 2222 9999
100 2 OLD 2222 9999
100 2 NEW 2222 8888
Now we have to present data in different format. For each field, display OLD and NEW values in column format.
EMPLIDFIELDOLDNEW
----- ------ ---- -----
100OFFICE11112222
100MOBILE99998888
Challenges :
1) Make pivoting with old and new values
2) For each field we have to show old and new values
3)if old and new values are same, dont show in report.
Am running the below query and getting the error below :
ORA-00934: group function is not allowed here
SELECT CONTRNO,
SUBNO AS USER_CODE,
TOTAL_POINTS AS AMOUNT,
STARTDATE + 30 AS DATE_F,
'SUB' USER_TYPE
[Code]....
Actually I have one query which contains two parameters from_Date and To_date.
if user pass the values for the parameter like 1-dec-2010 to 30-dec-2010 query will works fine and it fetches the values according to the query selected. but now I am going to add one more parameter, the value is 01-apr-2010 now my query should run for 01-apr-2010 to 30-dec-2010.
Here my question is how should I make this scenario with only one query and at the same time the query should work for to scenarios.
i am having two sql query which i have made union of it but i need group by common.
select
distinct ac.CUSTOMER_NAME cust_name,
ac.CUSTOMER_NUMBER cust_no,
ps.CUSTOMER_SITE_USE_ID,
raa_ship_ps.PARTY_SITE_NAME loc,
sum(days_30.AMOUNT_DUE_REMAINING) D_30,
[code]........
I want to use Analytical function instead of group by clause for below query..
select
CASE
WHEN ADMT.SOURCESYSTEM ='CLU'
THEN COUNT(ADMT.TOTAL_COUNT)*5
ELSE COUNT(ADMT.TOTAL_COUNT)
END TOTAL_COUNT
from ESMARTABC.ABC_DRVR_MFAILS_TMP ADMT
group by ADMT.SOURCESYSTEM
I am trying to use decode funtion with GROUP BY ROLLUP.
MY query is working fine when i use this two queris individually
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP ((DEPTNO),(DEPTNO,JOB));
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP((JOB),(DEPTNO,JOB));
But when i use Decode funtion so that i can combine above two scenarios it is not working
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP ( DECODE(:A,'S',((DEPTNO),(DEPTNO,JOB) ),((JOB),(DEPTNO,JOB) ) ) )
I have a table like below:
COLUMN TYPE
USER_ID VARCHAR2 (10 Byte)
PROCESS_ID VARCHAR2 (30 Byte)
END_TIME DATE(STAMP)
TO_LOC VARCHAR2 (12 Byte)
TO_LOC_TYPE VARCHAR2 (15 Byte)
FROM_LOC VARCHAR2 (12 Byte)
ITEM_ID VARCHAR2 (25 Byte)
CASES NUMBER (12,4)
LMS_UDA1 VARCHAR2 (250 Byte)
ZONE VARCHAR2 (2 Byte)
I only want get one record with all columns, only have one clause MAX(END_TIME) But the other column have difference value. when i use MAX(END_TIME) and GROUP BY USER_ID,PROCESS_ID,CASES,... the sql didnot give one record, It give many records
i want to select columns of 3 tables in such a way that period column should be in the group by function.
create view allocated_budgets_detail as
select ba.ba_fin_year, ba.ba_start_date, ba.ba_end_date, ba.ba_rev_no,
bh.bh_budget_code,
bd.bd_period,
bb.bb_entered_amount
from budget_header bh, budget_allocation ba, budget_distribution bd, budget_balance bb
where bh.bh_budget_id = ba.ba_budget_id
and ba.ba_line_id = bd.bd_budget_line_id
and ba.ba_line_id = bb.bb_budget_line_id
group by bd.bd_period
I have 2 tables used in this problem: ODETAILS and ORDERS.
ODETAILS has the following columns: ONO, PNO, QTY, COST
ORDERS has the following columns: ONO, CNO, ENO, RECEIVED, SHIPPED, ORDER_COST
UPDATE ORDERS
SET ORDER_COST= 1 * ( select SUM(
SELECT COST
FROM ODETAILS
WHERE ORDERS.PNO=ODETAILS.PNO
)
);
In ODETAILS there can be more than 1 row for 1 order. So I'm trying to add all the COSTs in ODETAILS.
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
);
I am new to PL/SQL, could you pls let me know how to solve this requirement using PL SQL
C-A | C-B
A123 | -1
B334 | 4
B567 | 2
B333 | -1
T777 | 2
Y774 | 3
T879 | 4
T654 | 3
T474 | 0
Y432 | -1
I need the output like this
C-1|C-2|C-3|C-4
3 |7 |3/10|7/10
Column-1 (Count of rows where Column-B = -1)
Column-2 (Count of rows where Column-B in(0,1,2,3,4)
Column-3 (Column-1/Total rows)
Column-4 (Column-2/Total rows)
We have one oracle table, which has one column "Creation_Date" of the datatype date.
This column has data like
"14-Aug-2010 01:30:30"
"14-Aug-2010 03:30:30"
"15-Aug-2010 01:30:30"
"16-Aug-2010 01:30:30"
"16-Aug-2010 03:30:30"
We want to write SQL query which will group rows by date wise without considering time..I mean for above rows the date wise output should be
Count Date
------- -------
2 14-Aug-2010
1 15-Aug-2010
2 16-Aug-2010
i am facing group by issue when running the query.the error code is ORA-00979: not a GROUP BY expression.i suspect is due to the subquery in the SELECT clause.
SELECT
WHINR100.COMPANY,
WHINH210.SEQN,
FXINH039.OTBP,
WHINR100.BPID,
TCCOM100.NAMA,
WHINR100.ITEM,
WHINH210.RCNO,
FXINH051.btch,
case when (whinh210.ORNO in (select fxinh033.pdno from fxinh033))
[code]...
I have a report I created and I need to get a Total Count by Plan Code Description for Each State. I do this and get all my data:
SELECT
mv.R1_State,
mv.subscriber_id,
mv.plan_code,
pm.description,
mv.line_of_business,
[code]...
But I cannot get the count I have to do a separate Query to get the count here it is How can I put the two together to get my count information and Report information together in one Report???
Select
Count(pm.description),
mv.R1_State
FROM windsoradm.member_mv mv
[code]....
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
View 1 Replies View RelatedMy need is to pass multiple values as single input parameter into pipelined function. For example - "2" and "3" are values of input parameter "t":
with data as (
select 1 as t from dual union all
select 2 as t from dual union all
select 3 as t from dual union all
select 4 as t from dual union all
select 5 as t from dual
)
select * from data where t in (2,3)
I've the following table:
MEASURE_VALUE(ID, VALUE) containing measure values. I would like to calculate the average of a specific id interval. In my case the id is the position where the value was captures.
Example:
id[m] value[mm]
1 1.2
2 1.5
9 2
11 3
18 1
28 1.2
I would like to group ids in a specified range. For n = 3 the result should look like this:
from_id, to_id, avg
1 2 1.35
9 11 1.5
18 18 1
28 28 1.2
I have to find a way to group ids to chunks.
I have Table Data in format :-
--------------------------
ABC
ABC
ABC
XYZ
XYZ
and i have a requirement where I need the output in this format.
-------------
ABC 1
ABC 2
ABC 3
XYZ 1
XYZ 2
what query to be used for this
I am calling one record group to get the security permissions. That RC is associated with one SELECT query. That RC is already created and used before i call. I m just re-using the same. But my form/module dont have privilages to see the QUERY in that recrod group. I just want to see the query in side that record group. how to print the SELECT query from the record group.
p.s: That record group is built in run-time.