I pretty new on Oracle and I'm facing with a issue. I don't know how to convert 'DD-MON-YY HH24:MI:SS' to 'DD-MON-YY Seconds'.So '29-APR-2010 16:32:23' will be ' 29-APR-2010 59543' (16*3600+32*60+23) Is this possible ?
View 6 RepliesI have two columns StartTime and EndTIme.
I have data like this
EndTime StartTime Diff(endtime-StartTime)
23:00 20:00 03:00
22:00 21:30 00:30
How can i get Diff(endtime-StartTime) from hh24:mi format
how can i change row as column in oracle.
I wrote the one query which is return the following output..
Query:
SELECT RMC_N_ID,NVL(DISCOUNT_N_ID,NULL) AS DISCOUNT_N_ID,NVL(NULL,'') AS LOADINGS_N_ID,NVL(NULL,'') AS WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_DISCOUNT WHERE RMC_N_ID =224 AND DISCOUNT_N_ID IS NOT NULL
UNION
SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(LOADING_N_ID,NULL) AS LOADING_N_ID ,NVL(NULL,'') AS WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_LOADING WHERE RMC_N_ID =224
UNION
SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(NULL,NULL) AS LOADING_N_ID ,WARCOMB_N_ID,NVL(NULL,'') AS ENDORSECOMB_N_ID FROM UWRMC_WARCOMB WHERE RMC_N_ID =224
UNION
SELECT RMC_N_ID,NVL(NULL,'') AS DISCOUNT_N_ID,NVL(NULL,NULL) AS LOADING_N_ID ,NVL(NULL,'') AS WARCOMB_N_ID, ENDORSECOMB_N_ID FROM UWRMC_ENDORSECOMB WHERE RMC_N_ID =224
Result:
RMC_N_ID DISCOUNT_N_ID LOADINGS_N_ID WARCOMB_N_ID ENDORSECOMB_N_ID
---------- ------------- ------------- ------------ ----------------
224 0 87 0 0
224 0 88 0 0
224 0 0 0 93
224 0 0 0 94
224 0 0 88 0
224 0 0 89 0
224 82 0 0 0
224 83 0 0 0
I want the result like below
RMC_N_ID DISCOUNT_N_ID LOADINGS_N_ID WARCOMB_N_ID ENDORSECOMB_N_ID
---------- ------------- ------------- ------------ ----------------
224 82 87 88 93
224 83 88 89 94
How can i get this..
how can i convert from oracle forms 6i to oracle developer suite 10g
View 4 Replies View Related my data looks like:-
Ename Job salary
----- ----- -------
A manager 10000
B clerk 60000
C assistant 30000
A manager 50000
B clerk 20000
D accountant 40000
I want the result while select all data should look like:-
A B C D
manager clerk assistant accountant
10000 60000 30000 40000
50000 20000
I use Oracle 11g and a Middleware which sends the SQL Statements to the database. That's why I can't send you loggings.
However, I' got this SQL:
With CTE
AS
(
select * from
data A2
WHERE
A2.ID ='536031'
AND
A2.AttrID = '6'
)
That code gives me the following result:
ID.........Version....AttrID.......group
536031......3.........6..............test5
536031......3.........6..............test6
What I try to do. I want to get all groups. The Groups should be in one column...the the result must be the following:
ID.........group
536031...test5, test6
In SQL server I always used
SELECT ID,
STUFF((SELECT ',' + [group] FROM CTE WHERE ID = c.ID ORDER BY [Version] FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT ID FROM CTE)c
to achieve that. Is there a similar function in oracle like XML PATH? Or another way the get the result?
how to convert row into column
below are my row data from qury
empno ename sal first_name last_name dof
10 rahul 1000 rah rao 01-jan-2000
20 rohit 2000 roh subuu 02-jan-2000
require output
10 20
rahul rohit
1000 2000
rahrao roh
rao subuu
01-jan-2000 02-jan-2000
can we convert RAW to number in sql.because i have to bitand of one raw and number variable.
View 3 Replies View RelatedI have table having below records.
empno ename deptno
101 a 10
102 b 20
103 c 10
104 d 20
105 e 30
Normal Output using Group by.
deptno count(*)
----------------------
10 2
20 2
30 1
I want to display like below(rows into columns)
--------------------------------------------------------
Requiredl Output
----------------------
10 20 30
2 2 1
Deptnos are in first row and cout of deptno nos are second row.
i have a problem ! i have developed my database in oracle 10forms with 10 database! Now i want to convert my 10G forms into 6i as connected with 10G database! how i will configure 10gdatabase with 6i forms and is it possible to convert my 10g forms into 6i forms?
View 5 Replies View Relatedwould need to convert a number in this way:10.1 ---> 10101.90 ----> 190 How can i?
View 1 Replies View Related[URL]....
[URL].....
i want to compare this URL and convert them into 1
i.e any url containing rediff word i want to convert that URL into
[URL]......
To convert the following traditional exp/imp to datapump .
1. exp custom/custom FILE=expgt.dmp TABLES=raj.GENERAL_TABLE QUERY = \"WHERE to_date\(lchg_time\) \= \(select db_date from mtda\) \" indexes=n statistics=none
2.exp custom/custom FILE=expdata.dmp TABLES=raj.BAL_TABLE,raj.GEN_TABLE ,raj.CREDITS_TABLE QUERY = \"WHERE to_date\(lchg_time\) \>= \(select db_date from mtda\) \" indexes=n statistics=none
in the second one there tables to the same dmp file with the same where clause
I have a date saved in varchar2 colomn.
how can I convert the vaules into date, so I can use the date to compare data.
Ex: I have the value '20-03-2007 05:31:29', but this value is saved as varchar2.
how can I take from this value the date '20-03-2007' as date format?
Actually I got a data in hex values and i want to convert it to datetime format. But before i convert the hex value into datetime format, i must convert the hex values into decimal val first for each 2 digits of hex values. This is an example of the input data:-
This is input data in hex value:-
STARTTIME : 080b1317021a
This is decimal value after convert from hex to decimal:-
STARTTIME : 081119230226
My problem is how could i convert hex to decimal in oracle? Below are my coding:-
case
when substr(LOAD_NGNSM.STARTTIME, 1, 2) not between '00' and '99'
or substr(LOAD_NGNSM.STARTTIME, 3, 2) not between '01' and '12'
or substr(LOAD_NGNSM.STARTTIME, 5, 2) not between '01' and '31'
or substr(LOAD_NGNSM.STARTTIME, 7, 2) not between '00' and '59'
or substr(LOAD_NGNSM.STARTTIME, 9, 2) not between '00' and '59'
[code]....
how to convert rows to columns ?
View 1 Replies View RelatedBelow is the schema of a table:
TableName : PropertyValue
Columns: PropertyID Number
Value varchar
ValueID Number
Phone Number
Requirement: Create a view based on the table"PropertValue'. There could be 4 different PropertyIDs for each phone.
e.g.
PropertyID Value ValueID Phone
1 'xyz' null 1234
2 null 11 1234
3 null 12 1234
4 null 13 1234
1 'pqr' null 5678
2 null 14 5678
3 null 15 5678
4 null 16 5678
Required View:
Phone Attrib1 Attrib2 Attrib3 Attrib4
1234 'xyz' 11 12 13
5678 'pqr' 14 15 16
I tried creating the required view using 'CASE' statement and group by but its returning multiple rows.
select case when PropertyID=1 then VALUE end as Attrib1,
case when PropertyID=2 then ValueID end as Attrib2,
case when PropertyID=3 then ValueID end as Attrib3
case when PropertyID=4 then ValueID end as Attrib4
from (
select Phone, PropertyID, ValueID,Value
from PropertyValue
group by Phone, PropertyID, ValueID,Value
)
I have to change some queries from SQL to Oracle but I couldn't convert these queries because they use some system tables in SQL that I don't know the equivalent Oracle tables. Following are SQL Queries
1. SELECT name, xtype FROM sysobjects WHERE xtype IN('U', 'V') AND name <> 'dtProperties' AND objectproperty(id, 'IsMSShipped') = 0 ORDER BY name
2. SELECT tS.name FROM sysobjects AS tS WHERE (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'U') AND xtype ='U') OR (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'V') AND xtype ='V')
3. SELECT o.name as TableName, c.name as FieldName, c.colid as Field_Ordinal, t.name as FieldType, c.length as FieldLength, c.prec as FieldPrecision, c.scale as FieldScale, c.isnullable, c.iscomputed, CASE WHEN c.status & 0x80 > 0 THEN 1 ELSE 0 END AS isidentity, columnproperty(o.id, c.name, 'IsRowGuidCol') as isrowguidcol FROM (sysobjects o JOIN syscolumns c ON o.id = c.id) JOIN systypes t On c.xtype = t.xtype WHERE o.xtype IN ('U', 'V') AND (t.xtype = t.xusertype)
How to convert station & date wise multiple rows to date wise column. I have a table TEST like this,
STN_Name Date State
DELHI1-Sep-201G
DELHI2-Sep-201B
DELHI3-Sep-201G
DELHI4-Sep-201G
DELHI5-Sep-201G
DELHI6-Sep-201M
DELHI7-Sep-201G
DELHI8-Sep-201G
DELHI9-Sep-201G
DELHI ......... ..
DELHI 30 Sep 2012 G
KOLKATA1-Sep-201G
KOLKATA2-Sep-201B
KOLKATA3-Sep-201B
KOLKATA4-Sep-201B
KOLKATA5-Sep-201G
KOLKATA6-Sep-201M
KOLKATA7-Sep-201G
KOLKATA8-Sep-201G
KOLKATA9-Sep-201B
I want output like this
STN_Name1-Sep-20122-Sep-20123-Sep-20124-Sep-20125-Sep-2012............Sep 2012
DELHI G B G G G M
KOLKATA G B B B G M
How can i convert the time part into second. for example
in mu table i have below rows
1753-01-01 09:18:00.000
1753-01-01 09:52:00.000
1753-01-01 09:36:00.000
I want to convert the time part of each rows to number of second in different column. so for the 1st rows it is 09:18:00.000 that means 9hour and 18 min = 9*60*60+18*60.
But that is complex column.
I want to make a query in which i can convert
alphabet
A
B
C
D
asume i have column name alphabet have data A B C D.But i want to retrieve data like this
Apple
Boy
Cow
Duck
I have a program that was developed in Forms 6i.Actually, this program:
- reads a table with a Blob column that contains a MS Word format Document.
- the document needs to be convert , then the program changes some variables that are wildcards.
In forms 6i I use a column with datatype Ole and the flowing procedure that read value from database:
PROCEDURE Pr_Abre_Contrato (pContrato In Contrato.Cont_Numero%Type,
pQuery in Varchar2 ) Is
Workbook OLE2.OBJ_TYPE;
Application OLE2.OBJ_TYPE;
MyDocuments ole2.obj_type;
Args ole2.obj_type;
vWhere Varchar2(200);
[code]....
Now, I need to convert this program to forms10g. I opened the TAR in metalink and the Support said that exec_verb can not be used and it doesn't have another one to replace.
I've got a table with varchar records and I need to separate numerical value and convert into the number datatype.
create table tmp_mape
(remark varchar2(100) )
insert into tmp_mape values ('Dobitie zdarma 3,32EUR 0910105067 02/02')
insert into tmp_mape values ( 'Dobitie pravidelné 9,00EUR' )
I just need to get values 3.32 and 9 from that example into the new column.
The select below returns ORA-01722: invalid number
how to solve it?
select remark,
to_number( to_number(REGEXP_SUBSTR (remark, '[[:digit:]]+', 1) ) ||','|| to_number(substr(REGEXP_SUBSTR (remark, ',[^EUR]+', 1) ,2) ) )
from tmp_mape
I have a Table as below
Attribute_namePrimary Seconday
DNS 204.109.167.1204.109.167.2
DNS 204.109.167.2204.109.167.3
NAT 138.20.37.136138.20.37.137
NAT 138.20.20.116138.20.20.117
For a Specific requirement
I need the select data output like below
[b]DNS NAT Type[/b]
204.109.167.1138.20.37.136Primary
204.109.167.2138.20.20.116Primary
204.109.167.2138.20.37.137Secondary
204.109.167.3138.20.20.117Secondary
How to convert a varchar2 column to CLOB when there is a thousands of records in it.
View 15 Replies View Relatedoracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I am trying to write a function that accepts LOCAL DATE, LOCAL TIME, LOCAL TIME ZONE, COUNTRY CODE as inputs, and UTC DATE, UTC TIME as outputs. LOCAL DATE, LOCAL TIME, LOCAL TIME ZONE has to be of DATETIME datatype, and COUNTRY_CODE of VARCHAR2(3).
I currently have a table with a VARCHAR column which is used to store notes. Currently the notes read something like 'Verified 01/01/2012'. I am trying to convert it to a date column so I can run reports using the date (select between dates etc).
I have tried with the substr function but since the records are all different doesn't really work. (plus that doesn't make it a date so not sure it would work for searching).
how to proceed?
Covert from T-SQL procedure to Oracle procedure.
this is my T-SQL query,
ALTER PROCEDURE dbo.PRO_SELECT_CERTIFICATION_Accreditations
AS
BEGIN
SELECT[AccreditationID], [Accreditation], [Description]
FROM[Accreditations]
END
I know this question has been asked several times. but i am starting out and i am struggling to get my head aroung it.I would like to convert datetime column of Oracle source(RPT.SHIPMENT_VW) to date while loading to sql Here is my
SELECT SHIPMENT_NBR,RECEIVED_DATE_TIME, RCVD_AT_DATE_GMT_FK
FROM RPT.SHIPMENT_VW
where WHERE RECEIVED_DATE_TIME = TO_DATE(TO_CHAR(:fromdate, 'DD/MM/YYYY'))
here RECEIVED_DATE_TIME is in 'DD/MM/YY hh:mi:si' fromat which, i want to convert to 'DD/MM/YY'
the above code throwing an error ORA:01843 not a valid month
if I use where clause like:WHERE (RECEIVED_DATE_TIME = TO_DATE(TO_CHAR(:fromdate, 'DD/MM/YYYY'), 'DD/MM/YY'))then its not retrieving any data
i have a table with this data :
id name
1 a
2 b
3 c
4 d
and i want the o/p like this,
col1 col2 col3 col4 col5
id 1 2 3 4
name a b c d
means i want to convert my columns into rows.