SQL & PL/SQL :: Generate Dynamic Unpivot?
Oct 16, 2012
I want to be able to generate unpivot for 1 row of data dynamically.what i am trying to do is based on this linkI just need to be able to pass the table name in format : SCHEMA_NAME.TABLE_NAME and the query should unpivot the data for 1 row. (Will always be 1 row, just need to transpose the column names and the values for 1 row of data)
I made the below query so far. But I am getting an error and can't figure out a way to fix it.pass any table name from your database in the define step
define TAB_NAME='SCOTT.EMPLOYEE'
WITH sel_col AS
(
SELECT DECODE (data_type,
[code]...
ERROR at line 33:
ORA-00904: : invalid identifier
Its the last line of the code. The same seems to work in the select part but not in the IN part.
P.S: I cannot do PL/SQL - as i simply dont have access.
View 9 Replies
ADVERTISEMENT
Mar 6, 2011
Is there any sql script to generate dynamic columns? pivot still needs the programmer to know the data to generate columns just like this.
select * from
(select current_position, fk_department, wages
from employee)
pivot
(sum(wages)
for fk_department in ('INT', 'WEL', 'CEN', 'POL'));
how about if i don't know the data in fk_department. is this possible in sql?
View 39 Replies
View Related
Dec 11, 2012
Cant find any artice on what is better to use unpivot or union all. I can achive same results using either but cant decide what would be better. For example a table has columns:
Name 1, Phone1, Name 2, Phone 2. The result I want would be:
Name 1, Phone 1
Name 2, Phone 2.
View 5 Replies
View Related
Sep 27, 2011
implementing UNPIVOT in..I am using Oracle 11g Release 11.2.0.1.0
OS: windows
Table Structure
CREATE TABLE "EMPLOYEE_ROLE"
("EMPLOYEE_ID" NUMBER(10,0),
"MANAGER_1" VARCHAR2(10 BYTE),
"MANAGER_2" VARCHAR2(10 BYTE),
"MANAGER_3" VARCHAR2(10 BYTE),
"MANAGER_4" VARCHAR2(10 BYTE),
[code]....
EMPLOYEE_ID MANAGER_1 MANAGER_2 MANAGER_3 MANAGER_4 MANAGER_5 ROLE_1 ROLE_2 ROLE_3 ROLE_4 ROLE_5 1345 John Mike Ram Kumar null DBA Sql Dev PLSQL Admin null
1 rows selected
I want a output like this
EMPLOYEE_ID MANAGER ROLE1
1345 John DBA
1345 Mike SQL DEV
1345 Ram PLSQL
1345 Kumar Admin
*I have tried with UNPIVOT *
SELECT
EMPLOYEE_ID
,MANAGER
FROM
EMPLOYEE_ROLE
UNPIVOT( MANAGER
FOR col1
in (MANAGER_1,
MANAGER_2,
MANAGER_3,
MANAGER_4,
MANAGER_5
))
i am able to get manager and role in two different queries
EMPLOYEE_ID MANAGER
1345 John
1345 Mike
1345 Ram
1345 Kumar
4 rows selected
SELECT
EMPLOYEE_ID
,ROLE1
FROM
EMPLOYEE_ROLE
UNPIVOT( ROLE1
FOR col1
in (ROLE_1,
ROLE_2,
ROLE_3,
ROLE_4,
ROLE_5
))
[code]....
Is it possible to add two unpivots in a single UNPIVOT query.
View 5 Replies
View Related
Mar 20, 2013
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]...
View 5 Replies
View Related
May 15, 2013
explain the difference in the VALUE column when DESCRIPTION = 'dept' when the query is run with and without the order by clause.
SELECT * FROM
(
SELECT * FROM emp
--ORDER BY job
)UNPIVOT (VALUE FOR DESCRIPTION IN (sal AS 'salary', deptno AS 'dept'));
View 12 Replies
View Related
Nov 15, 2012
am running an oracle 11gR2 Database.
I have a table of the structure..
CREATE TABLE "RP_RESOLUTION_MASTER"
( "RM_ID" NUMBER,
"SR_ID" NUMBER,
"REQUEST_STATUS" VARCHAR2(200 BYTE),
[code]....
But I get an error
ORA-01790: expression must have same datatype as corresponding expression
01790. 00000 - "expression must have same datatype as corresponding expression" *Cause:
*Action: Error at Line: 3 Column: 51
View 4 Replies
View Related
Mar 31, 2004
ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
View 3 Replies
View Related
Apr 8, 2013
I'm using dynamic sql (DBMS_SQL) to define columns of ref cursor. It works Ok but the problem is when i'm using PL/SQL CURSOR in the REF CURSOR. Then,I'm getting :
Error at line 3
ORA-00932: inconsistent datatypes: expected NUMBER got CURSER
ORA-06512: at "SYS.DBMS_SQL", line 1830
ORA-06512: at "TW.PRINT_REF_CURSOR", line 28
ORA-06512: at line 9
Here is my code:
set serveroutput on
exec DBMS_OUTPUT.ENABLE(1000000);
declare
l_cursor sys_refcursor;
begin
[code]....
Is there a solution or bypass?
View 3 Replies
View Related
Feb 18, 2012
I have a requirement to generate a report in csv file, attached scripts, sql query and actual output and expected output.
In the query, I am hard-coding month&year, when I run in march 2012 it should include march 2012 in output (similarly for every month going forward), to do that I have to modify my query to include march 2012 & preceding months in every individual query, this report needs to be generated once every month.
I am looking for a generic solution which does not need to be modified every month, also in the output,under "Mail File" data should be in ascending order, in the "Total Mailed" all the months should have grand total.
oracle version : 10.2.0.1.0
View 25 Replies
View Related
Sep 12, 2011
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options
How i can get all possible combinations of a given value using SQL or PL/SQL
for example:
1234 - input value
4321
2314
2143
3412
and so on
View 2 Replies
View Related
Jun 10, 2011
I want to generate the 3rd digit to be sequence base on two digit infront. for example:
current data grep direct from table
----------------------------------
data line_number
-----------------
data1 1.1
data2 1.1
data3 3.1
data4 4.1
data5 5.1
data6 5.1
data7 5.1
I want to generate is append in the 3rd digit as below:
data line_number
-----------------
data1 1.1.1
data2 1.1.2
data3 3.1.1
data4 4.1.1
data5 5.1.1
data6 5.1.2
data7 5.1.3
How I can get this?
View 4 Replies
View Related
Oct 16, 2013
I have a requirement to generate the XML file from a pl/sql procedure. The methods which I have searched are either generating the XML from a single query or they are explicitly adding the nodes and elements. I need the method where I can dynamically append child nodes to the file.
View 12 Replies
View Related
Mar 1, 2011
I am unable to generate a fk in the following script..
DROP TABLE PD1 ;
CREATE TABLE PD1 (APP_CODE NUMBER,APP_DTLS VARCHAR2(10),
CONSTRAINT APP_CODE_PK PRIMARY KEY (APP_CODE)
);
DROP TABLE PD2 ;
CREATE TABLE PD2 (LAND_CODE NUMBER,PVT_LAND VARCHAR2(10),
GOV_LAND VARCHAR2(10),OWND_LAND VARCHAR2(10),LEASED_LAND VARCHAR2(10),
CONSTRAINT LAND_CODE_PK PRIMARY KEY (LAND_CODE)--,
-- CONSTRAINT APP_CODE_FK FOREIGN KEY (APP_CODE) REFERENCES PD1(APP_CODE)
[code]....
View 2 Replies
View Related
Apr 28, 2008
i have be requested to create a .txt file. Its for a program that will read the txt file i create to produce a letter i.e I will be extracting, TITLE, FORENAME, SURNAME etc
MR
JOE
BLOGGS
Here is the my code so far,
SELECT
LPA_INPUT.INPUT_TITLE,
LPA_INPUT.INPUT_SURNAME,
LPA_HISTORY.LPA_AMT,
LPA_HISTORY.ELIG_RATE,
LPA_HISTORY.RATE_REBATE,
LPA_HISTORY.RR_AMT,
LPA_HISTORY.LPA_APPLIC,
LPA_HISTORY.LPA_AMT
FROM LPA_HISTORY, LPA_INPUT
WHERE LPA_HISTORY.CLAIM_NO = LPA_INPUT.CLAIM_NO
----------------------------------------------------
Iv been asked to have these eight fields looping over and over for all the records in the database, So im not sure how to do that and how to generate it in a txt file?! I have to produce it in a script and not by a Export/Import wizard in sql server mangement studio!!!
View 11 Replies
View Related
Feb 6, 2012
Can we collect information(generate report) about user sessions connected, database hit ratios, memory usage and other system metrics of all target databases from OEM grid control repository database. Which tables/views in repository DB will be holding these information.
View 1 Replies
View Related
Sep 3, 2013
I have a very big oracle procedure. Since it's too big and calling many other procedures, I am not able to debug the exceptions thrown. Any oracle utility which logs all the procedures called by the master procedure step by step and maintains a detailed record.
View 21 Replies
View Related
Sep 15, 2011
i mean which can generate a primary key when the table name and column name are passed as parameters
View 7 Replies
View Related
Jul 6, 2010
I have 2 question....
1. i have a table and i want to pick data from one field randomly..
i write this query for this purpose...
SELECT SUBJECTIVE_QUES_PK FROM
(SELECT SUBJECTIVE_QUES_PK FROM PMS.SUBJECTIVE_QUES
ORDER BY SYS.DBMS_RANDOM.VALUE)
WHERE QUES_TYPE_FK=3
AND ROWNUM<=5;
2. can i write this query in forms 6i..
i know that this question not relevant but if i write it in forms section then i will be duplicate.
View 27 Replies
View Related
Nov 2, 2011
i want to generate bar code in forms 6i but i don't know the code.
View 3 Replies
View Related
Dec 17, 2011
I need a "solution", guidance to a problem I have to solve. I have different letters with a value associate to it like
A:10, B:20, ..., G:250 and I have a Target to reach TARGET= 90
and I need to find among all letters I have which combination is equal or closest to my target.
A:10
B:20
C:20
D:20
E:30
F:40
G:250
IF there is more than one solution possible, the first found is perfect. With my example I can reach 90 with different combinations:
A+B+C+F
A+B+D+F
A+C+D+F
B+C+D+E
B+E+F
C+E+F
D+E+F
To complicate the things I have up to 10 different letters that need to combine to match my target. If there is no combination that exactly match my target, the closest higher combination is picked.
View 31 Replies
View Related
Mar 28, 2012
during the migration to 11g we had a problem with pdf font.
In 10g oracle report the pdf are generated correctly while in 11g all the character are shrink in the pdf file.
I attached the same file generate with oracle reports 10g.
View 3 Replies
View Related
Jun 21, 2012
Is there a way to generate a unique identifier(length 8), which can contain numbers (0-9) and letters(a-z) in pl/sql or sql ?
Note :- in oracle 9i.
View 23 Replies
View Related
Mar 8, 2013
Oracle 11g r2 version:
I tried to generate alphabet sequence like A-Z followed by AA-ZZ followed by AAA-ZZZ etc... using this query.
WITH chrset AS
( SELECT CHR (ASCII ('A') + ROWNUM - 1) alph
FROM DUAL
CONNECT BY ROWNUM <= 26),
t AS
(SELECT alph FROM chrset
[code]......
View 2 Replies
View Related
Oct 4, 2012
I have a table with a date field. This field storage dates with hours like this:
01-08-2012 8:30:00
01-08-2012 8:15:00
01-08-2012 9:30:00
01-08-2012 9:40:00
02-08-2012 8:30:00
02-08-2012 9:30:00
02-08-2012 9:34:00
...
I have to generate a report with the day and the frequency :
__Day_______ Hour Range__CountRecords
WEDNESDAY 8 - 9 2
WEDNESDAY 9 - 10 2
THURSDAY 8 - 9 3
The block of the hour must be 1 hour (8-9,9-10 and so on)
how generate the hours range.
My database oracle version is 8i. (I know that is very old but I can't change because It´s a legacy system).
View 5 Replies
View Related
Jun 14, 2012
I need to generate a report much like a program guide from the script attached. My problem is that my script works, but it's wrong, all the information is wrong. Here's the two queries I came up with;
SELECT CHANNELS.channel_NO,
CHANNELS.CHANNEL_NAME,
PROGRAM_PACKAGES.PACKAGE_NAME,
SUPPLIER_LISTING.PROGRAM_NAME,
[code]...
And
SELECT CHANNELS.channel_NO,
CHANNELS.CHANNEL_NAME,
PROGRAM_PACKAGES.PACKAGE_NAME,
SUPPLIER_LISTING.PROGRAM_NAME,
[code]...
View 9 Replies
View Related
Sep 13, 2013
write the query for the following requirement.I need to generate the records for the range (Difference between From_val & to_val) ) for each ID
create table test_base(id varchar2(20) , from_val number , to_val number);
insert all
into test_base values ('A', 6,10)
into test_base values ('B', 22,30)
into test_base values ('C', 123,130)
into test_base values ('D', 852,860)
into test_base values ('E', 30 ,30)
select * from dual ;
[code]....
No need to generate any thing for E as the difference is equal to Zero
View 4 Replies
View Related
May 17, 2013
*How can we generate the report of backup status,tablesapce(usedf,free space) for all the databases from OEM or RMAN*
1.)we need generate the report of tablespace used,free, archive...
2.)we also need we generate the Backup status report also
View 4 Replies
View Related
May 9, 2011
I have a table in some db called retailer. I want to generate an email based trigger. What i want to achieve is as follows
I want to generate an email based trigger. What i want to do is that i should store the current retailer id which is until this point and then on each further insertions in retailer id i want that some trigger or script is running in background which is storing the retailer id. When the retailer id advances by 45 then a trigger should be generated saying that retailer id has advanced to 45 numbers from this date.
View 1 Replies
View Related
Mar 1, 2007
I've got SQL code generated by an HP tool to make an update of itself. This code is not modifiable because was execute 'in background' from the tool updater.
The macro operations made by the updater (through sql code) are:
--create a copy of actual table and rename with '_old' suffix
--create new table, with indexes and constraints
--insert data into new table from old tables
Now, only in one table, when the data were inserted, an ORA-00001: unique constraint (ASSET_SVIL.CFG_CFGSECTIONCFGE) violated, appear.
To insert data is used an insert/select statement with an HINT /* APPEND */. We verify all the data existing in the old table (that is the data to 'migrate') but there aren't duplicate record!
BUT IF WE REMOVE THE HINT /* APPEND */, THE INSERT/SELECT STATEMENT WORKS WITHOUT GENERATE ERRORS!!
View 8 Replies
View Related
