I need to have columns dynamically defined in my SQL based on the column values in the table.
Selct emp_no,dept_no,grade from emp;
emp_no dept_no grade
----------------------------------
1 10 A
2 20 B
3 20 A
4 10 C
5 10 A
6 20 C
7 10 B
8 20 C
I need the output as
dept_no Count_A Count_B Count_C
---------------------------------------------
10 2 1 1
20 1 1 2
I can write hard coded sql to get this output but I need the count columns to be generated dynamically as there could be new entries in the grade column later. For example if there is a entry as D for the grade column my SQL should have a column count_D.
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
I want to be able to name a column created from my query.
Query is:
select A.OrigRef, A.DisplayName, A.ExtCode, count(CalcId) from OrigRefView A, CalcView B where A.OrigRef = B.NewRef and A.OrigRef like 'AB%' group by A.OrigRef, A.DisplayName, A.ExtCode order by A.SusRef
and it returns the Count in "column4" Is there a way I can get the query to output a different column name without creating a whole new table (i.e. not by creating a new table for my query output and then running a new procedure at the end to rename the column...)?
I have a stored procedure that updates a series of columns (set at varchar(500) just to ensure there is ample space for whatever data is inserted into it) .Once it has updated though I've wanted to trim the column width down to the maximum size of the column value. I determine the max size of the column length and use that in a pl/sql command to ensure the width isn't smaller than the largest data entry.
When I try to do this I get the following message:
ORA-01441: cannot decrease column length because some value is too big
Now after hunting this down it seems as though you cannot decrease the size of a column once data has been inserted into it.
Any success in reducing the column width of a table that has data in it?
Why do I need to do this? I am exporting this table from Oracle into a propriety application and I am trying to reduce teh size of the export table by removing any unecessary space the unused spaces in the column. This will benefit the local app in addition to reducing the impact on the filesystem storage.
Inside a trigger I have to dynamically get the : old. values for only these columns.
This I need because during update trigger on a table (say t2) I am supposed pickup the coumn names stored in t1 and get the : old values of only these columns to insert into a new table .
i have creatd view,in which i need to show the entry of year in column wise,though the data is in row /record wise.By iteration i can manipulate the year in decode function like " (DECODE(a.Year,(select distinct max(year)-1 from BI_BALANCE_SHEET), a.Balance))",but is there any way to chachge the alias name /column name,here is the query
select SUM(DECODE(a.Year,(select distinct max(year)-1 from BI_BALANCE_SHEET), a.Balance)) as year2010, SUM(DECODE(a.Year,(select distinct max(year)from BI_BALANCE_SHEET) , a.Balance))as Year2011 from DEMO a
I have two string one is comma separated values as a column name and another string which is having the comma separated value corresponding to the column name.
Employee Number Name Value Rate Type Type Assignment Id ----------------------------------------------------------------- 4 Employee 1 800 N 5 Yr Bonus 64 4 Employee 1 1063 N 6 Months Bouns 64 4 Employee 1 24.04 H Reg Hourly Sal 64 5 Employee 2 6923.08 S Reg Sal 65 6 Employee 3 5961.54 S Reg Sal 66 6 Employee 3 15000 N 6 Months Bouns 66
I want to convert above out to look like following
Emp Num Name Value Rate Type Type Val 1 Rate Type 1 Type 1 Value 2 Rate Type 2 Type 2 Assignt Id ------------------------------------------------------------------------------------------------------------------- 4 Employee 1 24.04 H Reg Hourly Sal 800 N 5 Yr Bon 1063 N 6 Mon Bon 64 5 Employee 2 6923.08 S Reg Sal 65 6 Employee 3 5961.54 S Reg Sal 15000 N 6 MontBon 66
Example of Query Output 2.
Employee Number Name Value Rate Type Type Assignment Id -------------------------------------------------------------------------------- 4 Employee 1 800 N 5 Yr Bonus 64 4 Employee 1 1063 N 6 Months Bouns 64 4 Employee 1 1345 N Patent Bonus 64 4 Employee 1 24.04 H Reg Hourly Sal 64 5 Employee 2 6923.08 S Reg Sal 65 6 Employee 3 5961.54 S Reg Sal 66 6 Employee 3 15000 N 6 Months Bouns 66
Desired Output from Above Query. As you can see column have increased as one more row for same employee has increased
ENu Nm Val RTy Ty Val1 RTy1 Ty1 Val2 RTy2 Ty2 Val3 RTy3 Ty3 AsgId ------------------------------------------------------------------------------------------------------------------- 4 E1 24.04 H Reg Hour Sal 1063 N 6 Mon Bns 1345 N Pat Bon 800 N 5YB 64 5 E2 6923.08 S Reg Sal 65 6 E3 5961.54 S Reg Sal 15000 N 6 Mos Bns 66
After the Data is loaded, we see data look like the above.
(1) Always COL3 column name have data value as 'Gen1' which is the indication for us from where data starts. But Gen1, Gen2, Gen3 etc... is dynamic. ie. This month we get gen1 and gen2 columns followed by null value in a column. Next month we get gen1,2,3,4 followed by null column. (2) Null Column indicate us that there is a break in the column. (3) Then next we need to look for next group of data (Monthly) and then insert into the same table again with different sheet_name column. (4) Next for Quater and then YTD. None of the column Values are fixed and its all dynamic.
If you load the below data, you will come to know what i am looking for. I tried using UNPIVOT. But couldnt able to achieve it. Is there an option to do it in sigle query? or Do I need to go for Stored Procedure?
Insert into TST_TBL (JOB_DETAIL_ID, SHEET_NAME, COL1, COL2, COL3, COL4) Values (100, 'Wire_1', 'Gen1', 'Gen2', 'Gen3', 'Gen4'); Insert into TST_TBL
So, for lack of a better way to explain it, I need to do the following.
I need a EXECUTE IMMEDIATE to be able to use a CURSOR but its saying its not declared. Is their a way to pass the cursor so a Execute Immediate can use it...
I know thats just a small part... but it shows the end goal. I need to append a VARCHAR that has the COLUMN NAME I need from the CURSOR. CUR_DATA is the open cursor. V_DATA is just a VARCHAR with text in it.
<h4><font color="Blue"> CREATE OR REPLACE PROCEDURE SP_SSN_BY_PIN( V_REQUESTEDPIN IN VARCHAR2, V_SSN OUT CHAR) AS BEGIN SELECT
[code]....
As you can see the type of the SSN column is NUMBER(13,0). But I leftpad it with 0 and assign it to my output paramter V_SSN, whose type is CHAR. But I get 111196100099 instead of 0111196100099. I've tried TO_CHAR(LPAD(SSN,13,'0')) but still doesn't work. However, if I return the left padded SSN inside a SYS_REFCURSOR I get what I want.
I'm trying to get size of CLOB column but not getting any output.
SQL> desc TABLE_STEP_INST234 Name Null? Type ----------------------------------------- -------- ---------------------------- NUM_PENDING_PREREQS NOT NULL NUMBER(10) OBJID NOT NULL VARCHAR2(31) OUTFLOW_BITS NUMBER(19) PARAMS CLOB PARENT2PROC_INST NOT NULL VARCHAR2(31) ROOT2PROC_INST NOT NULL VARCHAR2(31) START_TIME DATE STATUS NOT NULL NUMBER(2) [code]...
I have written an SQL which will dynamically generate the Select statement with from and where clause in it. But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.
For that i have used a ref cursor to open and insert the table.
In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn
find below the sample
TYPE ref_csr IS REF CURSOR; insert_csr ref_csr; v_select VARCHAR2 (4000) := NULL;
Depending on the no. of distinct dates in table 1 i have to make those many columns in table 2. I m trying to write a Query in SQL. If its not possible in SQL give me PL/SQL procedure.
i used sql loader to import data from csv file to my db.but every time the columns places are changed.o i need dynamic way to insert data into correct column in the table.
in csv file contains column name and i insert this data to temp table, after that i want to read data over column name.also i read the column names from (All_Tab_Columns) to make combination of column name between temp table and All_Tab_Columns table to insert data to right place...
I have an interactive report which contains the columns like
<li>empno <li>Name
In the above i have "empno" with a column alignment of left and a "Name" with a column alignment of left.
Whether it is possible to override the data alignment that we set in the report according to the value of the application item like
If my application item value is US, then i need the column alignment of both the column"empno" and "Name" to be left and if my application item value is AR, then i need the column alignment of both the column "empno" and "Name" to be right. Whether is it possible to achieve the dynamic alignment to the report data of both the columns "empno" and "Name" according to the application page item value.And also in the case of page item i have a page item with a name P1_NAME in which it has a Element Horizontal/Vertical alignment to left.
I need that element alignment of the page item to behave dynamically according to the application page item, like if the application page item is US then the element alignment of the page item has to be left and if the application page item is AR then the element alignment of the page item has to be right.
I Have an apex page that display a modal window utilizing jquery. In the modal window I have a classic report with a link column that I want to capture its click event.
I was thinking I could create a dynamic action with selection type=jquery selector. Not for sure if I need to do anything on link column and do not know the syntax jquery selector.
DECLARE JOBSFILE UTL_FILE.FILE_TYPE; -- TAKE ALL JOB TITLES FROM JOBS CURSOR JOBSCUR IS SELECT * -- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID FROM TARGET_UCM ; [code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
Currently I have a requirement where I need to create 2 more output rows using each result row.
In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.
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]....
