TABLE - 1
CIDDOB(DATE)
12312-MAR-63
58918-JAN-78
658927-DEC-43
46515-FEB-80
TABLE - 2
DIDDOB_INFO(VARCHAR2)
34425 Mar 1967
123 12 MAR 1963;25 FEB 1974;25 AUG 1978
46515 FEB 1980
I want to join the columns DOB from table -1 and DOB_INFO from table 2 but the datatype of DOB is DATE and DOB_INFO is VARCHAR. TO_DATE function is not working here.
I would like to inquire how do I code my select statement if my user enter a search date range(search startDt: 01/08/2012 and search endDt :30/09/2012) and I will like to retrieve only the 7records out from my table as shown below ?
STARTDT_ ENDDT_
01/08/2012 01/08/2012
01/06/2012 31/12/2012
01/08/2012 01/08/2012
01/09/2012 01/09/2012
01/09/2012 31/12/2012
01/07/2012 01/07/2012->not retrieved out
01/08/2012 01/08/2012
01/01/2012 31/12/2012
I need to delete all the registers where the table 1 does join with table 2 in 3 fields... for example:
delete taba1 t1
where t1.campo1 in ( select distinct(tr.campo1)
from tabla1 tr,
tabla2 t2
where t2.error = 0
tr.campo1 = t2.campo1
and tr.campo2 = t2.campo2
[Code]...
I have a editing database with an eversions table:
NAME OWNER
---------------------- --------------------------------
WR5936_DN6676 FRED
WR6739_DN7507 FRED
WR12744_DN13994 FRED
WR6739_DN7511 BARNEY
WR6801_DN7513 BARNEY
I have a process database with a pversions table:
SOID
----------------
5936
6739
12744
6739
I need to select from the editing.eversions table all the records that do not have a matching record in process.pversion. The eversion table is text, and has some additional crap surrounding the characters I want to use for creating a join.
I have oracle table for Leave
LeaveNo - FromDt - ToDt - Query Retrieve
1 - 01/01/2010 - 31/03/2010 - No
2 - 01/02/2010 - 31/05/2010 - Yes
3 - 01/04/2010 - 30/04/2010 - Yes
4 - 25/04/2010 - 15/05/2010 - Yes
5 - 01/05/2010 - 31/05/2010 - No
6 - 15/03/2010 - 14/04/2010 - Yes
7 - 10/04/2010 - 15/04/2010 - Yes
I want find out who was leave on 01/04/2010 to 30/04/2010. user input parameter may vary like 10/04/2010 to 15/04/2010 or 12/04/2010 to 12/04/2010.
how to write oracle sql?
I'm having trouble comparing and subtract two date type fields, can not get a significant result.
e.g.
select id, date.started, date.closed, datediff (day, date.started, date.closed) differences diference from Table1 order by desc;
I want to capture date and time in separate fields from user and save it in single column in table
example: user enters date and time
Date : 08/05/20212
Time : 18:50:00
At the time of save I want to save [08/05/2012 18:50:00].
I want to load a delimited file that contains many records which contained within the table where I'm going to load a date type field and I need to do this by concatenating three fields
field1 = 1 - this is the day
field2 = 11 - this corresponds to the month
field3 = 5 - this corresponds to the year
I need is in the field Save as type date 01/11/2005 i don´t know how to do it but I tried as follows but I get error loading.
"TO_DATE (TO_CHAR (: field1 ||'/'|| DECODE (: field2, 1, 'JAN', 2, 'FEB', 3, 'MAR', 4, 'APR', 5, 'MAY', 6 , 'JUN', 7, 'JUL', 8, 'AUG', 9, 'SEP', 10, 'OCT', 11, 'NOV', 12, 'DEC': field2 )||'/'|| : field3)) "
I'm looking a way to easily join two tables on date value but right table has no all possible dates so I need t fill missing records with MAX(DT) values.
My tables looks like
CREATE TABLE CAL
(DT DATE NOT NULL
);
INSERT INTO CAL
(DT
[code]......
And I was trying to merge them somehow to get following result (for all missing dates from SOME_DATA I want to get value corespond to maximum data not higher than cal.dt):
-- good result
| CAL.DT | SOME_DATA.SOME_VAL |
-----------------------------------
| 01/01/2012 | 10 |
| 02/01/2012 | 10 |
| 03/01/2012 | 15 |
| 04/01/2012 | 15 |
| 05/01/2012 | 15 |
[code]......
In the database we use for transfer articulation, there are numerous tables delivered with the product. The institution decided not to use certain fields, and all instances of those fields have no data. In other words, there might be a field in the table called INSTCD, but no records in the table have ever inserted any data into that particular field. In the table there are thousands of records, and we don't necessarily know which of the fields have never been used (no list has been retained and no one who initially was involved in the decisions is available to ask), as there are multiple fields in each table. How can I write a query that pulls only the fields in the table that contain data. In the example below, the SHRTRIT table contains a field called ACTIVITY_DATE, but there is no data in any record in that particular field, so I don't want it to show up on the output. In this particular case, I KNOW not to pull this field in a SELECT, but in a case where there might be 130,000 records and I DON'T know if a field has records in it, how could I do that?
if the question I'm asking doesn't make sense and I'll attempt to word it better.
create table SHRTRIT
(
SBGI_CODE VARCHAR2(6) NOT NULL,
SBGI_DESC VARCHAR2(10),
ACTIVITY_DATE VARCHAR2(10)
)
[Code]....
I am using the dbms_xmlgen.getXMLfunction to get the result of any query in xml format.
With this XML I also want the metadata information about all the fields used in the query (passed to getXML function). Is it possible and how can I achieve this.
I tried to Google it but couldn't find any solution , it's easy to do it in java where I can get the resultset meta data from the resultset but I have to do it in Oracle function since I want the result in xml format and want to use the oracle XML API.
You may think why I need metadata , the reason is the application will later use this information to sort the data contained in these fields according to their data type provided to.
I got that error bur I checked all the fields on RG query and they are not more 300 char in total. Even I changed the query but I still get this error.
View 2 Replies View RelatedSomething strange happens when i give execute query in a form (Oracle Forms 10g.). On executing query, one field disappeared but when i clicked on the location of the field it appeared again. I am not sure if this has something to do with visual attributes/display properties. This is happening only after migration to 10g.
View 7 Replies View RelatedWhen I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss') it only returns 'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?
View 0 Replies View Relatedwrite a query to delete similar records in particular fields(columns) in different tables.
View 2 Replies View RelatedThe existing format of the records are
MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Cno
------ ---------- ---------- ---------- ---------- ---
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN 102
41958 1102 CHEN ZHEN Sub CHEN HONGJIAN 103
41960 2007 CHEN ZHEN Base CHEN ZHEN 104
41961 2007 CHEN ZHEN Sub CHEN HONGJIAN 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107
This should be modified as below.
MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Indicator Sub Name baseCno SubCno
------ ---------- ---------- ---------- ---------- ---------- -------- ------ ------
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 102 103
41960 2007 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 104 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107
[Code]..
Throught this query, I get a extra record with null value in the Sub_name and Sub_Cno.update this query using a self join.
I have an attendance table in which we have empno, time_in, time_out..etc.
I want to create a matrix report that should shows all dates of any specific required month in columns and present empno in rows and time_in in cells.
I did it with using a temporary table by filling all the required dates and outer join with attendance table, it works fine, but it takes so long, is there any other better fast way to do it?
I have the following 2 tables.
A) Docversion
CREATE TABLE DOCVERSION
("OBJECT_ID" VARCHAR2(250 BYTE),
"OBJECT_CLASS_ID" VARCHAR2(250 BYTE),
);
[Code]..
Join column : object_id and parent_id.
The object id in docversion will have multiple values for element value in listofstring table.
ordinal value represents teh sequence of element value
Eg:
data for docversion:
1 23
2 34
data for LISTOFSTRING:
1 11 0 100
1 11 1 109
1 11 2 119
2 22 0 A
2 22 1 B
ouptut:
I want the output as follows
docversion.objectid,listofstring.elementvalue
1 100,109,119
2 A,B
How to update single table column using join query
Example:
Update table1 t1,table2 t2
set t1.column2 = 'Y'
where t1.column1 = t2.column1
I have come across a join in a query which has the (+) symbol at the end of it.
View 3 Replies View RelatedI have been told to create a query which should give out the same output as the UI of an exception management application . However , many referenced tables have one to many relationship . I created a query using a function which was giving the required output . But the performance of the query was not good as for each row . Is there a method to create a select using joins to show one to many relationship in a table .
View 4 Replies View RelatedIn my Oracle 9i Schema, I have two tables:
Table One
pocOne pocTwo
2 3
2 4
1 2
Table Two
TableTwoId Name
1 Jones
2 Smith
3 Edwards
4 Camden
My SQL to fetch all records with Smith works great:
select Name from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo)
where Name = 'Smith'
Now I need to create an alias for the Name field. Here is my attempt:
select myAliasName from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo),
(select Name as myAliasName from TableTwo)
where myAliasName = 'Smith'
This attempt pulls up all the records instead of just Smith records. How I can create an alias for the Name field in my above query?
I want to convert join to subquery. how cna i do this.
FROM
TABLE_1 A
INNER JOIN TABLE_2 B
ON ( A.COL_1 = B.COL_1)
[Code]....
Lets say the contents of the table are like this -
key dte_start(number) dte_end(number)
1 20010101 20011231
1 20020101 20081231
1 20030601 20071231
1 20100101 20101231
2 20090101 20091231
2 20090401 20101231
2 20101231 20111231
2 20140101 20141231
3 20080101 20091231
3 20070101 20081231
3 20100101 20101231
3 20120101 20120630
I want the query to resolve overlapping dates as well as merge contiguous segment and leave non-contiguous segments as is. The final result of the query should be like this.
key dte_start(number) dte_end(number)
1 20010101 20081231
1 20100101 20101231
2 20090101 20111231
2 20140101 20141231
3 20070101 20101231
3 20120101 20120630
I tried using the lead over partition along with the standard overlap query and case logic to get the results but couldn't get it to work.
the following statement gives each customer owns how many promotions.
Q:) how do i retrieve a customer who has max. promotions?
SELECT C.CUSTOMER_ID,COUNT(P.PROMOTION_ID)
FROM PROMOTIONS P,CUSTOMERS C
WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
=====================================================
CUSTOMER_ID COUNT(P.PROMOTION_ID)
-------------------------------------
001 | 5
002 | 8
003 | 4
004 | 6
005 | 5
006 | 3
In SQL, almost all the thing which are possible with join is possible with sub-query also and vice-a-versa.
So when should I use sub-query and when should I go for join?
How to use outer join condition in my below query. In the table APPS_JP.GEDIS_OFFER_HEADER goh I have more records in the table APPS_JP.GEDIS_ORDER_BUILDS gob I have less number of records.
I want all the records from APPS_JP.GEDIS_OFFER_HEADER goh including other conditions. I have tried goh.OFFER_NO=gob.OFFER_NO(+) but same result.
SELECT GOH.ORIG_SYSTEM,
gsp.USER_NAME,
goh.ORDER_NO,
goh.OMEGA_ORDER_NUMBER,
goh.ORDER_TYPE,
[code].......
joining this query instead of using the left join. Reason is want to show the score column in a different place and also do not want to show the second IPS column that is used in the joined query.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
select * from
(
select i.ips,
p.project_name,
p.project_segment,p.location,p.project_exec_model,
p.project_exec_model||' - '||p.project_config pmodel,
one.score schedule,two.score cost,three.score execution,four.score commercial,
nvl(one.score,0)+nvl(two.score,0)+nvl(three.score,0)+nvl(four.score,0) as total,
[code]....
I saw a strange plan for one query in TESTING DB today. Although 2 tables are involved i dont see any join , NL/HJ/SMJ !! i am not facing any performance issue but curious to know what type of optimization oracle is doing here.
Query text and plan :
SELECT FIRST_NAME
FROM CAMPA.TABLE_A
WHERE NAME_ID =
(SELECT NAME_ID
FROM CAMPA.TABLE_B
WHERE ban=:b1);
[code]....
must generate a Cartesian join, but I do not know why it happens. dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey are primary keys of each table. The problem is: I got same tuple for 8 times.
select dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey, dt.DebtorNo, dt.Name as "debtor Name", dt.State, cl.ClientNo, cl.Name as "Client Name", inv.InvNo, inv.PurchOrd, inv.Amt,
to_char(inv.InvDate, 'MM-DD-YY') invoice_date, to_char(ag.DateLastBuy, 'MM-DD-YY') aging_lastbuy, to_char(ag.DateLastPmt, 'MM-DD-YY') aging_lastpmt
[code]...