Is 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'));
- we have user id parameter.can we update the parameter(:parameter.p_userid) before firing "WHEN NEW FORM INSTANCE TRIGGER"(when new form instance trigger contain code for tree node), for login another canvas as per user rights?
- i have created a login form in one canvas.
- also tree node Hierarchy form created on another canvas. now we want to login through login screen, after login only those forms should show in tree Hierarchy which users have rights. for this purpose we want to pass the parameter of userid before connecting to tree node form through the following query
SELECT COUNT (*)
INTO v_count
FROM usersinfo
WHERE usersname = :USERBLOCK.usernames AND passwords = :USERBLOCK.passwords ;
[code]...
Every thing is working but when we click on button in login form at that time parameter.p_userid will return null. because above code is define on button click in login screen, i cannot understand where i need to define the above code .
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 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 would like to extract the user rights from the ERP that I use with SQL/Oracle.
There are 3 databases. The first gives me all the users with the main key id_user. The second one gives me the actions with the main key id_action. The third one only gives me what the user can't do.
I would like to know all the actions that all users can do or not.
The result must be something like:
Action 1
User 1 Yes
User 2 No
Action 2
User 1 No
User 2 Yes
....
just a short generally Oracle question with (i think) many solutions:
How is it possible to show the rights of a user on a tablespace?
I have created two types and a list of the first type:
create type type1;
/
create type type1_list as table of ref type1;
/
create type type2;
/
I have now just created the two types as follows:
create type type1 as object(
id# number
);
/
create type type2 as object(
attribute1 type1_list,
MEMBER FUNCTION function1 RETURN NUMBER
);
/
Ok, I've created the tables (I don't know if it's necessary to point out my problem)
CREATE TABLE type1_table OF type1;
/
CREATE TABLE type2_table OF type2
NESTED TABLE attribute1 STORE AS nested_type1_list_table;
/
And what I wanted to do now is to implement the member function1 and check something of the attributes of type1 in the list of attribute1... And that's where my question occurs, how does it work, I can't figure it out. I tried something like this:
Quote:
create or replace
TYPE BODY type2 AS
MEMBER FUNCTION function1 RETURN NUMBER AS
[Code]....
But I don't get the right way, it doesn't work
I have build a sw containing 20 forms & 35 reports. I have created users table.
Now I want to assign different rights to different users like update, view , update, print, delete etc etc. Enable disable add/delete/update buttons on basis of user rights.
When I invoke SOAP Web-Service using APEX_WEB_SERVICE.MAKE_REQUEST, then I'm able to get response from web-service. However all German character are replaced by JUNK data. However data is coming fine when I test web-services using SOAP UI.
I tried to invoke web-service using UTL_HTTP. However when I use UTL_HTTP, then I'm getting following error.
ORA-24247: network access denied by access control list
<li> Why German characters are replaced by Junk data while invoking web-service from APEX, while it's working fine from SOAP UI
<li> When I can access web-service successfully using APEX_WEB_SERVICE, then why it's throwing ORA-24247 error when I call using UTL_HTTP?
DB: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
APEX: 4.0.2.00.07
Web-Server: EPG
SELECT * FROM NLS_DATABASE_PARAMETERS;
[code]...
I am having oracle database version 11.2.0.3.0 standard edition, where one of my users requirement is that he wanted to send mails from oracle database but he is getting below error
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "HF_REPORTING.SEND_MAIL", line 12
ORA-06512: at line 18
is it possible to send mails from oracle database in STANDARD Edition?
While Running the proc,it's raising the below error.
Error report:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "LISTER_SHIFT.SOLN_GENERIC_SENDMAIL", line 33
ORA-06512: at line 1
[code]....
How can you use pivot with a parameter? If you attempt to use a parameter then an ORA-500: bind variable not supported is given
Below is a simplified version of the sql to illustrate teh problem.
create table test_pivot
(
ord_no number,
qty number,
loc varchar2(10)
):
insert into test_pivot values (123, 3, 'LOC1');
insert into test_pivot values (123, 2, 'LOC1');
insert into test_pivot values (234, 4, 'LOC1');
insert into test_pivot values (234, 5, 'LOC1');
-- this one is fine
Select * From
(
Select Ord_No, Qty, Loc
From Test_Pivot
Where Ord_No In ('123', '234')
)
Pivot (Sum(Qty) For Ord_No In ('123', '234') )
-- how to use pivot with parameters
Select * From
(
Select Ord_No, Qty, Loc
From Test_Pivot
Where Ord_No In (:Ord1, :Ord2)
)
Pivot (Sum(Qty) For Ord_No In (:Ord1, :Ord2) )
I have the following table structure:
PK1,PK2,PK3,A1,B1,C1,A2,B2,C2
Which I want represent as follows:
PK1,PK2,PK3,A1,A2
PK1,PK2,PK3,B1,B2
PK1,PK2,PK3,C1,C2
Is pivot the right command to use? If so, how do I do this? Most pivot examples I've looked at use an aggregate like SUM, which is not really want I am trying to accomplish here.
pivot the table. Table
dataNAMELINE_TYPE_REF_IDClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserskkondNode NamePB15CKYNum Contacts3ClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserdrcoulNode NameC4F6JJ1Num Contacts21ClassCreateShipmentServiceAdapterImplMethodgetContactsAndSitesForAccountUserdrcoulNode NameC4F6JJ1Num
[Code]....
I'm trying to use a PIVOT on the following data set:
ID STATUS_DESC PAY_STATUS PAID_DATE TRANSACTION_TYPE TRANSACTION_DESC DEBIT TOTAL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9876 In Progress 2nd Payment Made 11-DEC-12 19.38.57 Card Payment Payment 2 349 349
9876 In Progress 2nd Payment Made 06-DEC-12 14.33.57 Card Payment Payment 1 100 100
However I'm still getting two rows as per the below. Ideally all data should be on a single row.
ID STATUS_DESC PAY_STATUS PAYMENT_1_DATE PAYMENT_1_AMT PAYMENT_2_DATE PAYMENT_2_AMT TOTAL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9876 In Progress 2nd Payment Made 06-DEC-12 14.33.57 100 100
9876 In Progress 2nd Payment Made 11-DEC-12 19.38.57 349 349
I have constructed my pivot using the following on the outer select:
PIVOT (MAX (insert_timestamp) AS paid_date
,SUM (debit) AS amt
FOR transaction_desc IN ('Payment 1' AS payment_1,
'Payment 2' AS payment_2)) ;
I've used MAX to pivot the date and also tried using NVL on the insert_timestamp but still no luck.
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
I have this following data in a single table
Student
Semester
Subject
Marks
and I want to achieve the following.
I am asked to write a query with the students as Rows with thier subjects and marks as per thier semester which is the columns.
new to this type of queries...This is somewhat like pivot..
Those who have not appeared for a semester should be null just exactly as shown above.Is it posible ?
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 am joining two oracle provided basic tables emp and dept and want to show result as a department detail and followed by employee detail belong to that.
Standard result of joining two tables looks like below:
deptno dname empname
10 ACCT SCOTT
10 ACCT MILLER
20 SALES JOHN
20 SALES XYZ
30 FINANCE AAA
Now I need the output as below as per a report requirement.
entity_type name/no
DEPT 10
EMP SCOTT
EMP MILLER
DEPT 20
EMP JOHN
EMP XYZ
I am using oracle 10g release 10.2.0.1.0. Can I use oracle analytic function here?
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?
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 |
----------------------------------------------------------------------------------------
This is how data is in table.
SQL> SELECT order_num, jcds.change_num, jcds.object_status, jcds.time_changed
2 FROM tjcds_f jcds
3 WHERE change_num = '001'
4 AND object_status IN ('E0001', 'I0002', 'I0009', 'I0028')
5 and order_num='000001000001';
ORDER_NUM CHANGE_NU OBJECT_STAT TIME_CHANGED
------------ --------- ----------- --------------------------------------------
000001000001 001 I0002 01-NOV-10 02.52.56.000000 PM
000001000001 001 I0009 01-NOV-10 09.30.20.000000 AM
000001000001 001 I0028 01-NOV-10 10.36.32.000000 AM
Needed Output:
ORDER_NUM CHANGE_NU E0001 I0002 I0009 I0028
------------ --------- ----------- ------------------------------- ------------------------------ ---------------------------------
000001000001 001 01-NOV-10 02.52.56.000000 PM 01-NOV-10 09.30.20.000000 AM 01-NOV-10 10.36.32.000000 AM
My Query:
SELECT order_num, jcds.change_num,
decode(jcds.object_status,'E0001', jcds.time_changed) E0001 ,
decode(jcds.object_status,'I0002', jcds.time_changed) I0002,
decode(jcds.object_status,'I0009', jcds.time_changed) I0009,
[code]...
how do i get the above requirement in one row? is it possible.
I am not sure if we can do this as i tried many solution for pivoting/unpivot My data looks like from table when i do Select * from table_name
col1 col2 col3 col4 col5 col6 col7 col8
20130320_0938 C11 1416 98 93 30 30 32
20130320_0938 C22 26 92 73 50 50 65
20130320_0938 C33 86 95 81 63 63 76
I want the data to look like
[code]...
with t1 as
(
select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
union all
select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
union all
select 'initial val' param_name, 'mb256_type' param_type,'30' param_value from dual)
select param_name,param_type,param_value from t1;
desired output:
need output in a row in three different columns
param_value
01-01-1970 31-12-9999 30
I tried below query
SELECT *
FROM (
with t1 as
(
select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
union all
select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
[code]...
and am getting output as
param_type eff_date_param_value disc_date_param_value initial_ignoring_param_value
mbn256_type <null> <null> <null>
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.
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
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 tried to follow Pivot query using XML option in APEX
I can get the data into the xml serialized state, but how do I display the actual values in a region? See my example app below. I want the two far right columns in the Pivot XML region to duplicate what is in the original version.
[URL].......
I just so happen to be the one trail blazing the pivot function for the section of the company I work in. (Needless to say, a Sesame Street style answer will not be offensive.) We are literally in the process of upgrading to 11g (11.2.0.1.0). Sadly, none of our more experienced programmers now anything about the pivot function. Not really surprising to me since we've been working in 10g. Anyway, I am using SQL Developer version 3.0.04 which I know is not the newest but I don't yet have permission to upgrade. I used [URL] to get me as far as I am on this function.
The script I am having problems with is:
SELECT *
FROM
(SELECT
[Code]....
The error I'm getting is:
ORA-01738: missing IN keyword
01738. 00000 - "missing IN keyword"
*Cause:
*Action:
Error at Line: 16 Column: 2
The error indication bounces between line 15 and 16. If I put IN at the end of 15 I then have a missing right parenthesis error...