SQL & PL/SQL :: Generating (time Taken) Data At Row Level?
May 1, 2013
I have a requirement where i have data spread in rows which i have to pivot kind off operation to find the time taken using a Log table.
below is the existing data is present.
col0 col1 col2 col3 col4
30003695/1/2013 8:41Batch Startedproc_unalloc1345
30003705/1/2013 8:41blah proc_unalloc1345
30003715/1/2013 8:46blah proc_unalloc1345
30003725/1/2013 8:46blah proc_unalloc1345
30003735/1/2013 8:56blah proc_unalloc1345
30003745/1/2013 8:57Unalloc Endedproc_unalloc1345
[code].....
Below is what I am trying to find from the above data.
col1Unalloc Alloc Metric
13450.16 1.07 1.3
the result is Hours.Minutes.
I am trying to minus the two dates and find the time taken for a particular section( eg, unalloc ).
I tried using three union queries .
Select block,timestamp From (
Select 'Unalloc' As Block ,col1,col4 From table_1
Where col2 = 'Batch Started' And col3 = 'proc_unalloc' And
Parms Like '1345'
Union
Select 'Unalloc' As Block ,col1,col4 From table_1
Where col2 = 'Unalloc Ended' And col3 = 'proc_unalloc' And
[code].....
I will minus the start and end time for unalloc to get time taken for un alloc. Then i will minus end time of unalloc and end time for alloc to get time taken for alloc... like wise for metric.
View 6 Replies
ADVERTISEMENT
May 14, 2010
I have a data like,
1) manual_temp_master
auto_idbatch_id sec_idsec_id_type crrncy_cdcreate_Dt price_dt
------------------------------------------------------------------
11234ABC1CUSIPUSD14/05/201014/05/2010
23456XYZ1SEDOLGBP13/05/201013/05/2010
2)manual_temp_detl
auto_idbatch_id Price_bkt_cdscreate_Dtprice_date
---------------------------------------------------------
11234PS114/05/201014/05/2010
11234PS214/05/201014/05/2010
11234PS314/05/201014/05/2010
11234PS414/05/201014/05/2010
[code]....
I want to write a sql query which will fetch the data from manual_temp_master and manual_temp_detl.But from manual_temp_detl table, Price_bkt_cds columns should be displayed as columns. Like the should look like as below:
sec_idsec_id_type crrncy_cd COL_PS1 COL_PS2 COL_PS3 COL_PS4 COL_PS5COL_PS6price_date
--------------------------------------------------------------------------------------
ABC1CUSIPUSDPS1PS2PS3PS4PS5PS614/05/2010
XYZ1SEDOLGBPPS1PS2PS3PS4PS5PS613/05/2010
View 8 Replies
View Related
Dec 2, 2011
I'm working on a project and I can't figure out the procedure I will need to use. I've got a sysdate field in my "Calls" table which generates a call date + time, however I need to insert a severity level of the call after a certain amount of time, e.g. after 1 hour level turns from level 4 to level 3 and so forth until reaching level 1 after x amount of time. I know I'd need to put in an else if statement within this procedure I just can't work out how to do it with the sysdate field that has also been created.
A bit of info about the table, table name = calls, column 1 = date_time and column 2 is called severity_level
View 1 Replies
View Related
May 3, 2013
generating an xml file using the below data.Table name is T_Data having 4 columns as given below with some data.
Region Divison District Store
---------- ----------------------- ----------- -----------
Northwest Northern California San Jose SJStore1
Northwest Northern California San Jose SJStore2
Northwest Northern California North LA LAStore1
Northwest Northern California North LA LAStore2
Northwest Northern California North LA LAStore3
I want to generate an XML File using SQL/XML Functions and the XML File should look like as given below.
<Region>
<Region Name>Northwest</Region Name>
<Divison>
<Division Name> Northern California </Division Name>
<District>
[code]...
View 3 Replies
View Related
May 7, 2012
I am actually having problems with a report that is generating wrong data in Windows 7 only. I have the same report working correctly on many Windows XP Computers.
View 9 Replies
View Related
Jun 29, 2011
whether Oracle has any capability of automatically checking which lossless compression algorithm it should apply by analyzing a data stream on data load? Does Oracle have any compression advisors/wizards that would make recommendations as to type and level of compression?
View 3 Replies
View Related
Feb 17, 2010
Is it possible that we restrict user at data level? For Example 'A' user can only query employeess of deptno 10 only. He can not query employees of others dept.
View 6 Replies
View Related
Nov 17, 2010
what is the difference on DBMS_STATS for table level and partition level , which will provide the best optimizer . If the table xxxx is partitioned from 1 to 10 ,then running gather stats on table xxxx as whole table level or partition level which will provide best result on the performance.
View 1 Replies
View Related
Apr 12, 2011
We are migrating database from HP to AIX. Data is all on SAN.I can do it through RMAN convert. What I want to know is if it is possible to move data at storage level without using Convert command?
View 2 Replies
View Related
Apr 26, 2013
Our client is having requirements that data should be encrypted through-out the process (from transaction level to backup level).
Requirements are as below -
1) Data should be encrypted and can be access only through application (through front end).
2) Even if pl/sql, sql installed, developer user cannot able to fetch data from database.
3) Even DBA, should not have access to fetch the data from the database.
4) Only assigned DBA, should fetch details from database (provided he/she should have details of database as well as application's user / password).
5) It should be restricted by ip and user details (such as ip of application server with user/password of user).
6) Changes should be purely done from database level only.
View 5 Replies
View Related
Nov 14, 2013
How can find the number of standby databases configured for primary database from os level
View 9 Replies
View Related
Oct 22, 2012
Can you take an incremental backup level 1 or level 0 without archivelogs?
syntax would bebackup as compressed backupset cummulative level 1 database.
The reason I ask is because when I run backup as compressed backupset cummulative level 1 database plus archivelogs # it runs fine, but when I run backup as compressed backupset cummulative level 1 database it just hangs.
View 20 Replies
View Related
Apr 11, 2013
between statement level or row level trigger, which trigger will execute first.We have BEFORE_UPDATE_ROWLEVEL_TRIGGER and BEFORE_ UPDATE_ STATEMENT LEVEL_TRIGGER triggers on table product.
which will execute first on update DML event ?
View 2 Replies
View Related
Oct 13, 2011
I am facing the following issue while generating xml using a sql query. I get the below given table using a query.
CODE ID MARK
==================================
1 4 2331 809
2 4 1772 802
3 4 2331 845
4 5 2331 804
5 5 2331 800
6 5 2210 801
I need to generate the below given xml using a query
<data>
<CODE>4</CODE>
<IDS>
<ID>2331</ID>
<ID>1772</ID>
</IDS>
<MARKS>
[code].....
NOTe: IDS which are distinct needs to be displayed. ALL MARKS should be displayed though there are duplicates.
View 1 Replies
View Related
Mar 27, 2013
In the below data, a container is moving from one city to another. 1,2 ,3 can be any number which i want to generate and use as keys to group the cities. Eg: AUH, JEB, CIW belong to the same key=2; SIN, IKT belong to a new group 4. The City where difference between the Seq# is greater than 1 (eg between S8W and AUH), a new group starts.
Conotainer #CitySeqI want this
-------------------------------------------
Container1S8W5251
Container1S8W5261
Container1AUH5362
Container1AUH5372
Container1JEB5382
Container1JEB5392
[code]....
View 4 Replies
View Related
Mar 11, 2012
Below is the procedure I created to generate csv file from oracle procedure.
PROCEDURE proc_csv(p_path varchar2 , p_separator varchar2 default ',')
AS
CURSOR c_data
IS
select * FROM product ;
v_file UTL_FILE.file_type;
BEGIN
[code]...
I have two doubts about this procedures
1) In one schema this procedure is generating empty csv file with zero bytes, while in other schem It's working fine, It's showing data. I am unable to make what's the difference.
2) The csv file is not displaying column headings. I want column headings to be displayed on csv file.
View 4 Replies
View Related
Sep 11, 2012
Im generating some xml from a table which works fine. Here is an example of what I'm doing.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
create table "VTEST"(
"PROJECT_NUMBER" VARCHAR2(50 BYTE),
"ORDER_NUMBER" VARCHAR2(50 BYTE),
"PART_NUMBER" VARCHAR2(50 BYTE),
[code]....
so each section would list its unique values.
View 3 Replies
View Related
Jul 7, 2010
generating oracle table definitions from XML .xsd files?
View 5 Replies
View Related
Apr 1, 2013
I know I am using a outdated Oracle what to do my company are not willing to upgrade because of the economic situtation.
I need generating weekly report.
I have a condition for monthly like this
invoice_date BETWEEN TO_DATE('01-JAN-13 00:00:00', 'dd-mon-yy hh24:mi:ss')
AND TO_DATE('31-JAN-13 23:59:59', 'dd-mon-yy hh24:mi:ss')
now if I want a weekly report Monday - Saturday, How to go about it?
View 2 Replies
View Related
Jun 27, 2011
I have data like this:
a1 a2 a3 a4
ABC ABC xx zz
ABC xx mm mm
fg ui ok pl
I want to give them unique codes like :
Code for column
a1 Code1
ABCC-1
ABCC-1
fg C-2
then column
a2 Code2
ABC C-1
xxC-3
uiC-4
then column
a3 Code3
xxC-3
mmC-5
okC-6
then column
a4 Code4
zzC-7
mmC-5
plC-8
I can add these `columns in the table Code1 , Code2 etc columns to update.
View 1 Replies
View Related
Mar 1, 2010
I have emp table
Empno from_date to_date ScaleBonus_pct Salary
101 31/01/2010 14/02/2010Grade120,10,10,15,25,205000
Reqd. o/p
Emp nofrom_dateScaleEach_WeeK_Sal
10131/01/2010Grade11000(from 20 %X5000
1017/2/2010Grade1500(from 10 %X5000
10114/02/2010Grade1500(from 10 % X5000
10121/02/2010Grade1750(from 15 % X5000
10128/10/2010Grade11250(from 25 % X5000
1017/2/2010Grade11000(from 20 % X5000
we have to split the date by 7 days,should be on Sunday...and if we split by 7 days we have upto 31/01,07/02 and 14/02...but we have to generate 6 days because 20,10,10,15,25,20 (since comma separated value is 6 ).
View 8 Replies
View Related
Feb 5, 2013
In weblogic server Axis2 is deployed and it is showing Active.And we have uploaded ODI invoke service also in AXIS2 succesfully and it is showing Active status also...By using the soap its generating the XML with below mentioned error..
some info:- We rae using ODI 10g.
Weblogic 10.3.5.0
XML GENERATING:--_
<xs:element name="inPhasesUptoAndIncludingPostDispatch" nillable="true" type="xs:anyType" />
<xs:element name="key" nillable="true" type="xs:anyType" />
<xs:element name="localPolicyAssertions" nillable="true" type="xs:anyType" />
[code]...
The XML page cannot be displayed Cannot view XML input using XSL style sheet. correct the error and then click the Refresh button, or try again later.
The operation completed successfully. Error processing resource [URL]
</xs:schema></wsdl:types><wsdl:message name="invokeScenarioMessage"><wsdl:part name="part1" element="ns...
View 2 Replies
View Related
Oct 24, 2010
The below query take time to fetch the data about 25 seconds for 9 rows.
select uctb.member_code As Member_ID,
((uct.price * uct.quantity * i.multiplier) ) As Traded_Value_In_Lacs,
sum(uct.price * uct.quantity) As Total_No_of_Trades,
sum(uct.quantity) As Total_Volume_Of_Trades
[code]......
View 2 Replies
View Related
Sep 13, 2013
we have a requirement for migrating data real time from source db to target db as well as to a queue.
achieve this using any custom technique?
we tried exploring Streams with CDC but Streams being depricated and CDC removed in Oracle 12 c, we are kind of stuck.
View 1 Replies
View Related
Mar 20, 2013
I wanted to select data which is inserted on the same time.
Is that possible? I am trying with below query but facing isses.
SQL> SELECT a,b,c,d FROM tb;
no rows selected
SQL> SELECT a,b,c,d FROM (INSERT INTO tb VALUES(1,1,1,1));
SELECT a,b,c,d FROM (INSERT INTO tb VALUES(1,1,1,1))
*
ERROR at line 1:
ORA-00903: invalid table name
View 11 Replies
View Related
Oct 26, 2006
i have data in date format for in time actually it should be 9:30 am and out time at 2:30 pm
SELECT MIN(IN_TIME) AS IN_TIME,MAX(OUT_TIME) AS OUT_TIME FROM EMP_ATND_DETAIL AS ET, EMP_ATND_INFO AS EAI
WHERE ET.EMP_ATND_INFO_ID=EAI.EMP_ATND_INFO_ID AND ATND_DATE = '2006-10-25' AND EMP_ID =24
+---------------------+---------------------+
| IN_TIME | OUT_TIME |
+---------------------+---------------------+
| 2006-10-20 09:30:00 | 2006-10-13 01:30:00 |
| 2006-10-24 02:30:00 | 2006-10-13 06:30:00 |
| 2006-10-24 09:30:00 | 2006-10-13 01:30:00 |
+---------------------+---------------------+
i have should get 9:30 am because that is lessser then 2:30 pm
do i have
SELECT name, emp_id,
min(to_char(IN_TIME, 'DD/MM/YY HH24:MI')) in_time,
max(to_char(OUT_TIME, 'DD/MM/YY HH24:MI')) out_time,
FROM EMP_ATND_DETAIL
View 3 Replies
View Related
Oct 2, 2012
Is there any API to generate XSDs from Oracle tables? DBMS_XMLSCHEMA.GENERATESCHEMA accepts only Types and not the Table names. I want to generate XSD from the tables including length, null and default validations.
View 6 Replies
View Related
Oct 25, 2010
I have to write a stored procedure/function which has to generate the combination of numbers
For eg: IF I/p is an array of numbers a(i) = [1,2,3]
I want to get various combinations of numbers with these three digits.
writing the stored procedure generating the output for this
View 18 Replies
View Related
Aug 13, 2012
I Have got a requirement where we have to generate an xml conforming to a particular XSD.
What i will be having already in place is an XSD document.
We are thinking of making a procedure which will be recieving the set of inputs required for creating that xml.(Data content of that XML.)We would have to make the XML according to that XSD.
Everytime Parsing the XSD is not feasible.We are looking at a solution which would be easy to make the xml from xsd at runtime.These values (DATA) will be available at run time.
View 7 Replies
View Related
Jun 25, 2011
I am in urgent need of Generating Random numbers for one of the application .The number once generated is getting stored in table
For this purpose i am using Oracle In-Built function
Quote:Select round(dbms_random.value(1,30)) into a from dual;
Problem is :How to eliminate those numbers to generate which are already stored in Table.
View 6 Replies
View Related
