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 need to transpose this table to below one. But since my rows in 1 table is dynamic and it will increment each week and month I couldn't get a correct result from unpivot.
I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production versionI have following table - drop table t2;
create table t2(BATot,Ly_BATot,LLy_BATot,BScTot,Ly_BScTot,LLy_BScTot,BAMSTot,Ly_BAMSTot,LLy_BAMSTot) as select 5000,2000,12600,20000,45600,35000,45000,56000,65000 from dual ;
select null class, batot,ly_batot,lly_batot,bsctot,ly_bsctot,lly_bsctot,bamstot,ly_bamstot,lly_bamstot from t2; Simple DML I am using - SELECT * FROM T2;
I need to select the value of a column into different variables depending on the condition.
As in, I have
FILE_TYPE_CODERETENTION_DAYS CLR 5 SIZ 6 UOM 7 ADB 8
I need to get the Rention days into the 4 variables v_color_file_type_code, v_size_file_type_code, v_buyer_file_type_code AND v_uom_file_type_code Depending whether the FILE_TYPE_CODE is 'CLR' or 'SIZ' or 'ADB' or 'UOM'
I'm using Oracle Database 11g R2 need to upload Telecom CDRs to the database on daily basis , it's huge data and changeable , an example of my file in linux Redhat 5 server as below ,
there is no problem with this i think i can use SQLLDR to upload this file , but the problem here the positions of the columns in the file could change depending on user behavior it could be the first row comes in the third row or any row and maybe more rows appears ,
this is sample of the file i could be more than 100 rows , and the position of the field and field names could be change every time depending on the Subscriber usage , is there any way to upload the file but after checking the field name in the file and matching to corresponding column name in the table .
I have a classical report with a column as "Affirmation Status". This column can take three value as "Affirmed, Rejected or NA". My requirement is I have to show all row in one color like red wherever it has value affirmed, and all row in color like green wherever it is rejected.
We have a master detail form with report that inserts the details in both region/ sub region.
However our requirement is that in sub region we have a LOV to chose the service, and each service has a price. How can we populate the Price column based on the selection of the Service? There are some similar posts but we cannot apply to our form.
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 have the following data (columns IDx, STARTx and DURATION) and want to create the column RESULT. Example:
create table zTEST ( IDx number, STARTx number, DURATION number, RESULT number ); insert into zTEST (IDx, STARTx, DURATION, RESULT) values ( 1 , null , null , null );
[code]...
The logic behind the table is like this: when there is a Start-ID and a Duration, the field Result shall show the Start-ID for the next records (Duration period). Even if there is a new Start-ID in row 9, it is ignored since threre is already a current signal from row 6 which is still running.
QUOTE (thiyagusham @ Mar 27 2012, 11:40 PM) SQL> select * from samp;
NAME DOJ QUAL EMPID ---------- --------------- ---------- ----- sam 21-mar-2012 mca sony 03-jan-2000 mba 10610 maya 21-mar-2012 m.arch sonna 18-mar-2012 mis
[code]...
i want to display in result column 10610 in second row , because emp sony having id "10610 "should i use sub-query ? without sub-query how can i display ?
SELECT SUM(salary) FROM employees WHERE department_id =60 O/P is : 28800But when i use the above query in anonymous block it gives me 684400 DECLARE v_sum_sal NUMBER;
[code]....
The above output statements gives me 684400 as output.. But the expected is 28800
I have two tables T1 and T2. T1 is the original backup snapshot for changed records from overnight batch in a big table and T2 is the overnight batch changed records. Both tables have similar number of rows (T2 might have more for newly inserted rows) and you can find out the differences by comparing these two according to action column in T2 (C - Update, A - Insert and D - Delete)
how to compare these two tables to generate something like the following. I can join these two tables to generate the diff but it is one row per account.
client_nbr branch_cd, account_cd, action column, old_value, new_value 8888 123 45678 C account_clsfn_cd 004 005 8888 123 45678 C buy_cd 98 99 8888 012 34546 A sell_cd 12 8888 321 98765 D dividend_cd 1
I am using Oracle 10g so Unpivot cannot be used.
CREATE TABLE T1 ( CLIENT_NBR CHAR(4 BYTE) NOT NULL, BRANCH_CD CHAR(3 BYTE) NOT NULL, ACCOUNT_CD CHAR(5 BYTE) NOT NULL, ACCOUNT_CLSFN_CD CHAR(3 BYTE), SELL_CD CHAR(2 BYTE), BUY_CD CHAR(2 BYTE),
I have a table MESSAGE which has some billion entries. The columns are msg_id, vehicle_id, timestamp, data, etc.I have another table VEHICLE which holds static vehicle data (about 20k rows) such as vehicle_id, licenceplate, etc.
My first target was to partition the table via timestamp (by range) and subpartition by vehicle_id (by hash).So I could easily drop old data by dropping old partitions and tablespaces.
Now comes the new difficult 2nd target: the messages of some vehicles must be kept forever.My idea is to add a column KEEP_DATA to the table MESSAGE. I could try to partition by timestamp AND KEEP_DATA, subpartion by vehicle_id.The problem of this idea is that i have to update billions of rows.
It would be perfect if there is a possibility to add this KEEP_DATA-flag to the table vehicle.Is there any way to "link" this information to a column in MESSAGE table?
I mean something like this:
alter table MESSAGE add column (select keep_data from vehicle where VEHICLE.vehicle_id = MESSAGE.vehicle_id as keep_message) ;
Is there some possibility like that? Would the partitioning on this column / statement work?Would the value of the keep_message be calculated on runtime?
If so will the performance influence be noticeable?If so will the performance also sink if the application is querying all rows except the keep_message?
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 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 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.
