Extract Data From Three Related Tables

Aug 29, 2008

Im having a problem with writing an appropriate query for a report in my web application. I need it to extract data from three related tables:

CAR(
PK CAR_ID INT NOT NULL,
TYPE VARCHAR NOT NULL)
REPAIR_CENTER(
PK REPAIR_CENTER_ID INT NOT NULL,
NAME VARCHAR NOT NULL)
[code]...

I need the report to display only available cars. Available cars must have these characteristics:

1. if the CAR_REPAIR table is empty, displays all entries from CAR table...
2. if car has multiple entries in the CAR_REPAIR table display only the latest DATE_RETURN if its lower than todays date (SYSDATE), otherwise don't display that car...
3. don't display cars that are in the CAR_REPAIR table and have DATE_RETURN value of NULL

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Copy Related Data From / To Same Tables - Combined With Changed String And Unique ID

Aug 12, 2010

I'm trying to copy a particular set of rows into two tables. I want to change one varchar column to a different string in one of the tables, and create a new unique ID for the copied rows that will match on both tables. I also want that new unique ID to be one digit higer than the current highest unique ID being used across the tables as a whole.

create table testing_query_dtl
(query_is number not null, query_type varchar2(20) not null, area_code varchar(4), stn_code varchar2(3))
create table testing_query_hdr
(user_name varchar2(30) not null, query_indicator varchar2(1) not null
, query_created date not null,
query_last_used date not null,
query_name varchar2(40) not null
, query_is number not null)
[code]...

I then used 'mymax+test_seq.nextval' but that won't avoid the unique constraint issue on the table testing_query_hdr and on the table testing_ query_ dtl increments the ID on EVERY row even when I don't want it to.

View 4 Replies View Related

PL/SQL :: Extract Data From CSV File And Push Into Various Tables

Aug 20, 2013

I have a requirement. I will get .csv files . I need to extract the data from .csv file and push  into the various tables.

The illegal data will be rejected and sent back to the sender.I need to schedule this logic every weekend. I am on 10gR2 on Solaris.

View 7 Replies View Related

SQL & PL/SQL :: Stored Procedure / Extract Data From Excel To Multiple Tables

Oct 14, 2010

This is my first time running a stored procedure. The procedure is already written.

We have various related table. I need to use this stored procedure and extract information from an excel sheet into the multiple tables.

View 8 Replies View Related

Replication :: Reorganize One Table That Related To Several Other Tables

May 15, 2009

I have to reorganize one table that related to several other tables. The reorg is too slow when it runs on this table. I would like to create one image of the table and synch it with the original one in real time. So when I run the reorg, I will use the image table that does not constrained by indexes and other objects. Once the reorg is done, I would like to rename the table. how could I do the replication in real time?

View 2 Replies View Related

SQL & PL/SQL :: How To Delete A Data Which Is Related To Many No Of Child Table

Apr 13, 2012

how to delete a data which is related to many no of child table and according to setnull and casecad by using subprogram?

View 6 Replies View Related

Forms :: Select Table Name And Retrieve Its Related Data

May 29, 2012

I wanna have a form which has a list box which is including of all table names, by selecting the required table by user all column name and its related data should be retrieve.I have a cursor which give me all field names related to the table by I do not know how can I retrieve table data:

1. How to add item through block base on number of column which selected table has.

2. How to set value of table column in these mentioned column which added in the block.

3. And at the end retrieve the table data.

View 1 Replies View Related

Forms :: Data Block Related To Item Chosen By User

Apr 27, 2010

My problem consists in setting dynamically a data block related to the item chosen by the user.So I ask if it is possible, in forms 10g, to base dynamically a data block with different tables having different columns.

View 5 Replies View Related

XML DB :: How To Select Parent / Child Related Data From XMLTYPE Table

Jan 9, 2013

select * from V$VERSION

BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

I have loaded into an Oracle table defined as XMLTYPE. I'm okay with PL-SQL and stored procedures, where I will ultimately do this processing once I get a handle on XML DB querying from an XMLTYPE table.

Based on reading the oracle documentation about XML DB, and more importantly, reading dozens of posts to this forum,I have been successful in loading the XML files into a SQL XMLTYPE table and doing simple queries against that table to retrieve some of its data so that I can then insert that data into other target oracle relational tables.

how to select parent/child data from XMLTYPE tables. I am able to follow the forum examples and can replicate the methods shown on the many example XML contents shown on this forum, but not against the XML that I have to process. I am wondering if my struggle is caused by my lack of knowledge, or by ill-formed XML content supplied to me by the educational vendor.The XML content has structured the XML content nodes in such a way that I do not seem to be able to apply the parent/child sql methods.I have been able to use for other XML examples I have tested against.

My XML file shown below represents High School Transcript data, for which I need to be able to parse out into my own oracle relational tables for that student, his personal info, and his course info, etc. i.e., for our example, which courses he has taken for which High School grade levels. The vendor-supplied XML seems to put the Courses and the High School grade level in "parallel nodes," instead of parent/child nodes, so I am struggling to be able to use SQL to differentiate which course the student took in NinthGrade versus TenthGrade.

-- WHat I would like to determine from a select statement:

LASTNAME GradeLevel COURSETITLE
=============================
Smith NinthGrade PHYS ED 101
Smith TenthGrade CALCULUS 201
Smith TenthGrade ZOOLOGY 202

(The data has been simpliied and masked, but is true to the content and is queryable).

select * from V$VERSION

BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]....

View 3 Replies View Related

SQL & PL/SQL :: How To Extract Data Between [ ]

Jun 8, 2012

I have a requirement where the data between [] or ][ has to be extracted from a string.

Here is my situation :

INPUT:
[abc] [def]-[ghi][jlk]

OUTPUT:
row_num field_name
1 abc
2 <blank_space>
3 def
4 -
5 ghi
6 null
7 jkl

View 5 Replies View Related

How To Extract The Data From DBF Files

Aug 13, 2010

I'm a SAP consultant working in SQL on NT platforms. This is the first conversion from Oracle that I have done. My client has provided us with a "Cold" backup of the Oracle dbase on a HD formatted in Unix, I have the partition mounted and I'm able to view the files. I have the ORDATA folder with all the .DBF files.

Q: How do I extract the data from the .DBF files. I need to export to something workable with SQL.

Original database was on Unix, I'm operating on Windows platform.

View 4 Replies View Related

SQL & PL/SQL :: Extract Data Into Excel

Mar 21, 2012

Data that enter by user in database having " in the sentences
example:

column1
----------
"My Smurf Village

column2
--------
Farmvile

column3
--------
"Testing

My select statement is

Select column1,column2,column3 from table. Output in toad perfect but When I export into excel using pipe | as a separator, the output become

column1 in excel
----------------
My Smurf Village|Farmvile

column2 in excel
---------------
Testing

How I can code my program so that when extract to excel " is recognized as sentences. output are correct as in database?

View 5 Replies View Related

SQL & PL/SQL :: Extract Data - REGEXP?

May 25, 2012

How to extract data from the below string.
END OF FILE. ROW COUNT: 23,9
Output should be divided into 3 parts

END OF FILE. ROW COUNT:
23
9

View 8 Replies View Related

Extract Data For Express 6 OLAP?

Sep 8, 2007

Is there a way where i can extract the data from Oracle Express 6 (OLAP data) into an excel or any other format, so that the same can be loaded onto a MS Sql or a normal Oracle database.

View 1 Replies View Related

JDeveloper, Java & XML :: Extract Data From XML

Dec 20, 2010

I want to retrieve values from a XML data stored on CLOB data type and store it in different table.

Retrieving data from Soap Envelope and Soap Body?

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope>
<soap:Header>
<company>US</company>
</soap:Header>
<soap:Body>
Data
</soap:Body>
<Account>ABCD1234</Account>
</soap:Envelope>

View 8 Replies View Related

Replication :: Using Goldengate To Extract Data From ASM - 10G

Feb 17, 2011

We are getting following error when we are trying to extract data from ASM.

GGS ERROR 500 Oracle GoldenGate Capture for Oracle, ext_1.prm: Getting attributes for ASM file +DATA/testgg/onlinelog/group_1.257.742844671, SQL <BEGIN dbms_diskgroup.getfileattr('+DATA/testgg/onlinelog/group_1.257.742844671', :filetype, :filesize, :lblksize); END;>: (6550) ORA-06550: line 1, column 7: PLS-00201: identifier 'DBMS_DISKGROUP.GETFILEATTR' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignoredNot able to establish initial position for begin time 2011-02-16 16:42:05.

Database version is 10.2.0.4.

View 1 Replies View Related

XML DB :: Parse XML File And Extract Data

Jul 22, 2013

I'd like to parse an XML file and get some data extracted as columns. Input file country.xml:

<?xml version="1.0" encoding="UTF-8"?>
<MAS Action="Insert">
<Country ObjectId="100000000000000009" VersionId="8"><Id>1</Id><NlTexts><Name Language="de">Land1</Name><Name Language="en">Country1</Name></NlTexts></Country>

[Code]....

View 2 Replies View Related

Extract Data From Oracle To Text Files?

May 13, 2004

I would like to extract some data value from oracle to a text file...and i m not sure how to set the delimiter between the columns data value

SET echo off
SET space 0
SET pagesize 0

SPOOL a.txt

SELECT emp_id, name, add
FROM table1
/
SPOOL OFF

Where do i set the delimiter?

Can i do something like in SQL*Loader?

fields terminated by ',' enclosed by '"'

I would like the text file to be display as
"123","ABCD","123 abc road"
"234","XYZ","234 xyz road"

View 5 Replies View Related

JDeveloper, Java & XML :: How To Extract XML Data Having Namespace

Mar 18, 2013

Below is the XML i have :

<?xml version="1.0" encoding="UTF-8" ?>
- <Transaction xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.test.com/Support/Services/test1/2012" xsi:schemaLocation="http://schemas.test.com/Support/Services/test1/2012 Support.test1.v1.xsd">
- <Request>
<Memid>10</Memid>
<Actid>32</Actid>
[code].......

I want to extract the element values :

The below code will be useful when i have no namespace , what must be done in order to work with namespace and extract element value

v_string_xml :=
' //Request[' || TO_CHAR (counter_xml) || ']/Memid/text()';
v_ssp_table (v_ssp_table.COUNT).memid :=
p_xml_in.EXTRACT (v_string_xml).getnumberval ();
v_string_xml :=
[code].......

View 2 Replies View Related

JDeveloper, Java & XML :: How To Extract Data Using XSD File

Feb 9, 2012

How to extract the data from XML using the xsd file. attached files.

Explanation: first check the EmailMessage tage from order_conf.xml compared with Email.xml(<xsd:element name="EmailMessage">) if exists then go to next node.
EmailMessage(exists tag in order xml file)
->next <ns1:emailNotificationype> this tag should be follow under the EmailMessage tag(<xsd:element ref="emailNotificationype">) in Email.xml
->next <ns1:orderNotification> -> check this tag in <xsd:element name="orderNotification"> in Email.xml.
-> next <ns1:templateFormatInfo> -> it should follow under <xsd:element name="orderNotification"> in Email.xml.
-> next <ns1:templateFormatInfo> -> it should follow these tages <xsd:element name="templateFormatInfo"> <xsd:element ref="templatecode"/>
<xsd:element ref="templateversion"/>

i have to extract the value in between tags.

templatecode -> ORDCONF
templateversion ->1.0

Finally i have to load the data into table.

View 17 Replies View Related

PL/SQL :: Extract Data From Table On Hourly Basis?

Oct 23, 2012

I have a table which have two columns date on hourly basis and response time. I want to pull the previous date's data on hourly basis with the corresponding response time. The data will be loaded to the table every midnight.

eg: Today's date 23/10/2012
I want to pull data from 22/10/12 00 to 22/10/12 23

The below query is pulling the date as required but I am not able to pull the response time.

with a as
(select min(trunc(lhour)) as mindate, max(trunc(lhour)) as maxdate from AVG_HR)
SELECT to_char(maxdate + (level/25), 'dd/mm/yyyy hh24') as dates FROM a CONNECT BY LEVEL <= (1)*24 ;

View 4 Replies View Related

SQL & PL/SQL :: Extract Data From A Table Using UTL File Utilities

Sep 3, 2010

I have a requirement to extract the data from a table using the UTL file utilities.

My problem is, Say i have a table t1 with column C1,C2, C3, C4, C5. This table t1 gets loaded everyday. i need to pickup the data only that which has changed/inserted in the last load. How can i achieve this ? There is no timestamp in this table.

View 3 Replies View Related

SQL & PL/SQL :: Show Grouped Data - Extract Subsets In Same Query

Sep 5, 2013

the point is that I have a table with two columns and I am trying to find and easy way to show grouped data.

CREATE TABLE TEST_DATA(C1 VARCHAR2(1), C2 VARCHAR2(2));

INSERT INTO TEST_DATA VALUES('1','A1');
INSERT INTO TEST_DATA VALUES('1','A2');
INSERT INTO TEST_DATA VALUES('1','A3');

[Code] .....

With the sample data there are 4 groups based in c1 column:

1 : A1,A2,A3
2 : A1,A2,A4
3 : A1,A2,A3
4 : A1,A2,A4
5 : A6,A7

I'm trying to get and output like :

A1,A2,A3 : 1,3
A1,A2,A4 : 2,4

I was trying something like :

select t1.c1 as gr1, t2.c1 as gr2, t1.c2
from test_data t1,test_data t2
where t1.c1<>t2.c1 and t1.c2=t2.c2
and
(select count(*) from test_data t3 where t3.c1=t1.c1)=
(select count(*) from test_data t4 where t4.c1=t2.c1)
order by 1 asc, 2 asc

but I don't find the way to refilter to group the data as expected. The idea is find subsets and show the set of data and values in column c1.

View 4 Replies View Related

Can Access Extract Data From Oracle DB For Automated Upload

Dec 14, 2012

Is it possible for Access to extract data from an Oracle database and upload it directly?

Currently we have a business process where data is being extracted in scheduled queries (30+) to Excel spreadsheets, then manually edited to remove heading lines and imported to an Access database. I see an opportunity to automate a time consuming manual activity by having the Access db extract the data and directly upload it.

View 3 Replies View Related

SQL & PL/SQL :: Extract Data From Local Database Table To Text File?

Jun 27, 2013

i want to extract data from my local database table to text file using plsql

View 4 Replies View Related

Client Tools :: TOAD Data Extract Excel 2007

Nov 4, 2010

I have a question regarding data extraction to Excel.Is there any patch or update that makes it possible to extract to Excel 2007 and not only 2003?

View 6 Replies View Related

Export/Import/SQL Loader :: Oracle 11g - Put Batch ID To Extract Data

Apr 19, 2013

In our application, we are allowing user to upload data using excel sheet in UI. We are using PHP script in UI and using SQL Loader to load data from excel sheet to temp_table.

The temp_table has a primary key.

Here my question is , Is there any way to put some batch id for every upload in that table in automatic way ? so that we can easily extract the data by using batch id . we are using Oracle 11g.

View 11 Replies View Related

Maintain Large Tables / Cleanup Data From Our Tables

May 18, 2011

I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?

To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.

I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.

View 5 Replies View Related

Implementation Where Data From DB2 Tables Are Moved To Oracle Tables

Sep 3, 2012

I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?

View 1 Replies View Related

JDeveloper, Java & XML :: Extract Data From Clob Field Using Regular Expression

Oct 6, 2011

I want to extract the data from the Clob field. I have the following table,create table test123(col1 char(24), col2 clob); And following data,

Insert into test123 (col1,col2) values ('ABCDE','<?xml version="1.0" encoding="UTF-8"?>
<Attributes>
<Attribute DataType="Text-40" DisplayName="DropDirectory"
IsNotDeletable="Y" Modifiable="Y" Name="DropDirectory" Value="${Dir1}"/>
<Attribute DataType="Text-40" DisplayName="PrinterAlias"
IsNotDeletable="Y" Modifiable="Y" Name="PrinterAlias" Value="\Printer3Printer4"/>
<Attribute DataType="Text-40" DisplayName="PrintServerHostName"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerHostName" Value=""/>
<Attribute DataType="Count" DisplayName="PrintServerPort"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerPort" Value="2723"/>
</Attributes>');
[code]....

The data will be available like this only, The same XML tag is used everywhere.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved