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
ADVERTISEMENT
May 27, 2013
I have the following table structure with values
CREATE TABLE DUMMY
(
SR_NUMBER VARCHAR2(100 CHAR),
ASSIGNMENT_GROUP VARCHAR2(100 CHAR),
REASSIGNMENT_COUNT VARCHAR2(100 CHAR),
CREATED DATE,
[code]...
?I have the following requirement, the output should be:
Ticket count (sr_number)
% of tickets inside DL
Number of tickets inside DL
Average cycle time (cycle time = closed date - created date)
Total cycle time (cycle time = closed date - created date)
Number of reassignments (sum)
DL - (deadline) formula is, closed date <= target_date
This should be displayed, grouped by year, then month and then by assignment group. The values should be in descending order(dates) Not sure how group by will work here.I am able to write the basic code for the above, but group by based on year, month and assignment group is pretty confusing to me.
View 10 Replies
View Related
May 7, 2013
I am using Oracle database version 11.2.1 and would like to extract the level change and level start date where reason_code is 'PROMO' split by ID.
The test script is below:
create table test(
id number,
start_date date,
reason_code varchar2(10),
level number
)
insert into test values(001, '01-JAN-13', 'PROMO', 2);
[code]....
The expected output would be:
Fields - ID, old_level, old_level_start_date, new_level, new_level_start date
e.g.
001, 2, '08-MAR-13' , 3, '05-MAY-13'
002, 4, '13-APR-13' 5, 02-MAY-13'
View 2 Replies
View Related
Feb 7, 2012
SQL> select * from t1;
NAME
----------
JACK
JOHN
JENN
SQL> select * from t2;
NAME
----------
JACK
PAUL
SQL> select a.name from t1 a, t2 b where a.name <> b.name
2 order by name;
NAME
----------
JACK
JENN
JENN
JOHN
JOHN
I would have expected to see the following:
name
-----
JOHN
JENN
PAUL
how to fix this query? In additon, is there a way to print the table name or some arrows (>>, <<) to show which table the values came from
I.e
name
=====
JOHN t1 or <<
JENN t1 or <<
PAUL t2 or >>
View 14 Replies
View Related
Jun 21, 2011
I use oracle 11g r2
i have created a table for objects
create table table_t
(Record_no number,
t_no number,
position SDO_GEOMETRY
, occupation_time number);
and inserted many values (examples below )
INSERT INTO tra VALUES
(2,
20503,
SDO_GEOMETRY
(2001,
NULL,
SDO_POINT_TYPE (1387, 0, NULL),
NULL,
NULL),
23037
)
/
and( position) indexed as Rtree spatial index
now when i run spatial query such as
SELECT * FROM tra t WHERE
SDO_FILTER(t.position, MDSYS.SDO_GEOMETRY(2001,NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(0,0,9000,0)), 'querytype=WINDOW') = 'TRUE' and t.position.sdo_point.X=1;
i do not know how many IO accrued ?
i tried set autotrace on
but the physical read is 0 , this is not possible because i have more than 100000 objects there and all indexed as R-tree
View 2 Replies
View Related
Jun 20, 2012
I am getting a following odd result where the same a Job could be linked to multiple enquiries. What I want it to show is just one job if its linked to multiple enquiries. I show you some sample result:I wrote the following SQL for it:
SELECT
'Enquiry' as Origin,
job.job_number,
enquiry.enquiry_number as Defect_or_Enquiry,
job.site_code,
central_site.site_name,
job.priority_code,
priority.priority_name,
job.target_comp_date,
'Target Completion Date required' as Due_Date
[code]....
Is there anything you would add so that it shows just a unique job number where the job has multiple enquiries linked to it? I tried putting SELECT DISTINCT but that did not work.
View 11 Replies
View Related
Sep 12, 2010
I need to create a report that will show all activity for the day that the report is run. The report will be run or auto-refreshed throughout the day. So, at 10am, 11am, 2pm, 4pm etc. I just need to display quantities received, shipped etc. for that same day. Since the report will not be run at a fixed time, I can't use sysdate - .5 for example.
View 6 Replies
View Related
Sep 24, 2012
I have a scenario for which I need to show same record twice.
i.e
SELECT EMPID, ENAME FROM EMP WHERE EMPID IN (101, 102, 101);
Result:
101 - Rahul
102 - Ravi
101 - Rahul
Is this possible with a single query.
View 7 Replies
View Related
Jul 12, 2012
i have table structure (emp_no, emp_sal,emp_startwork)
i want query to show the monthly salary such as jan month salary Feb month salary
View 2 Replies
View Related
Apr 23, 2010
i got problem that i want to know Metadata in my Oracle DB 2 version there're OracleDB 9i and OracleDB 10g ,So i want to know about sql command to show all metadata of these Database, because i want to list My metadata and improve to Metadata's Standard(ISO/IEC 11179). How can i show all metadata of these database. is it the same sql command line with this 2 version Database to show all metadata?
View 1 Replies
View Related
Jul 20, 2011
me in building a query. I want to show the result of the query just like pivot table.
Test case
CREATE TABLE CPF_YEAR_PAYCODE
(
CPF_NO NUMBER(5),
INC_DATE DATE,
PAYCODE_TYPE CHAR(1 BYTE),
[code]...
I want that my query should look like the format as attached in the xls sheet.
View 1 Replies
View Related
Jan 29, 2013
I have such a database "ZOO" in oracle PL/SQL. URL.....The query is "Show all the keepers, who cares of all animals in the zoo.
View 14 Replies
View Related
Sep 16, 2013
i am trying to use the query builder in my 11g xe database but vainly ,there are no results shown ?
View 6 Replies
View Related
Jul 12, 2004
I'm trying to create a query which will show sales of products by month, something like this: -
JanFebMarApr
Prod 11020511
Prod 200510
Prod 31921015
The data is held on 3 tables: -
TB_ARTICLES
ID
CODE
DESCRIPTION
TB_TRANSACTIONS
ID
BOOKKEEPING_DATE
TRANS_DATE
[code]....
Running a query for one month is no problem at all, I just don't know how to create a cross tab style one.
View 12 Replies
View Related
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
Oct 18, 2010
I have a sql like this
SELECT RO_NO, HANDLED_BY
FROM MSS_VEH_INPUT
GROUP BY HANDLED_BY, RO_NO
I want the output like this with row num grouped by handled_by,ro_no
SNO HANDLED_BY RO_NO
1 KUMAR 12
2 KUMAR 13
3 KUMAR 14
1 LEE 15
2 LEE 16
1 MARVIN 17
2 MARVIN 18
So from this I can know Kumar how many ro,lee how many ro and marvin ro.?
View 6 Replies
View Related
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
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
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
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
View Related
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
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
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
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
Mar 9, 2010
resolving this problem the query is
SELECT DAY_NBR ||' '||HOUR_NBR ||' '||MINUTE_NBR AS "@TIMEPOINT",
RTRIM ( xmlagg (xmlelement (c, procedure_nm || ', ')).extract ('//text()'), ', ' ) AS "@LABEL",
RTRIM ( xmlagg (xmlelement (c, procedure_id || ', ')).extract ('//text()'), ', ' ) AS "@PROCEDURE_ID",
cursor
(SELECT SUBJECT_NBR AS "@LABEL",TO_CHAR (PSUB.procedure_tm,'HH24:MI') AS "@TIME",
PROCEDURE_ID AS "@PROCEDURE_ID", PROCEDURE_SUB_ID AS "@PROCEDURE_SUB_ID"
FROM PROCEDURE_SUBJECT_TIME PSUB WHERE PSUB.ACTIVE_FLG = 1
AND PSUB.PROCEDURE_ID in pr.PROCEDURE_ID
ORDER BY PSUB.procedure_tm,PROCEDURE_ID
)AS VOLUNTEER
FROM SCHEDULE_PROCEDURE PR
WHERE active_flg = 1 and SCHEDULE_ID = 200900195269 group BY day_nbr,hour_nbr,minute_nbr,procedure_nm;
and the error getting is ORA-00979: not a GROUP BY expression
View 2 Replies
View Related
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
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
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
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
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