SQL & PL/SQL :: Show Missing Rows In Query

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


ADVERTISEMENT

How To Recover Missing Rows

May 28, 2013

In one of my table i found some rows missing, how can i recover it ?????

View 4 Replies View Related

PL/SQL :: Compare Two Rows From Same Table And Show Field And Difference?

Aug 28, 2013

How can I compare two rows from the same table and show the field and the difference?

Example:

select ename, job, mgr, hiredate, sal, deptno
from EMP t
where t.empno = 7782
or t.empno = 7788

ENAME JOB MGR HIREDATE SAL DEPTNO
1 CLARK MANAGER 7839 6/9/1981 2450.00 10
2 SCOTT ANALYST 7566 6/9/1981 3000.00 20

My report comparing fields should be:

FIELD BASE COMPARE DIFFERENCE
ENAME CLARK SCOTT --
JOB MANAGER ANALYST --
MGR 7839 7566 --
SAL 2450 3000 550 --(Show difference only for numbers)
DEPTNO 10 20 --

View 9 Replies View Related

Server Utilities :: Export / Import From One Oracle DB To Other - Rows Missing?

May 31, 2011

I am new to Oracle DBA. m facing one problem. i imported and exported my data from one oracle DB to another by using exp/imp.....in both exp/imp log files, its shows the fallowing messages at the end.

"Import terminated successfully with warnings."
"Export terminated successfully with warnings."

but when i count there are some rows missing in some tables...

what could be the cause? is there any other way to cross check whether export/import was successful...

View 2 Replies View Related

Query - Missing Right Parenthesis?

Sep 10, 2003

My query

SELECT node_nme, os_nme, os_vrsn_nme, CPU_QTY, mch_id_nme
FROM node_t
WHERE node_nme IN
(
SELECT DISTINCT upper(node_nme) as node_nme FROM node_t WHERE row_status_cde IN (1,4)

[code]...

Now, when I run this i get the missing parenthesis error, though I don't konw why.The subselect in the IN works fine and returns exactly what it should.

View 2 Replies View Related

Performance Tuning :: How To Show Right IO Of Query

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

SQL & PL/SQL :: How To Show Unique Record Only In Query

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

SQL & PL/SQL :: Query To Show Only Transactions For Today

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

SQL & PL/SQL :: Show Same Record Twice With A Single Query?

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

Top / Bottom Query - ORA-00907 / Missing Right Parenthesis

Mar 13, 2007

I'm trying to create a Top / Bottom query from an example i already have but when i executed it, i got an error:

ORA-00907: missing right parenthesis

The coding i've got is:

SELECT customer_id, forename, surname, start_date_time, end_date_time, pos
FROM (
SELECT customer_id, forename, surname, start_date_time, end_date_time,
row_number() over (
partion by start_date_time
order by customer_id asc) pos
from customer, phone_call)
where pos = 1;

The error is on the partition line but i've closed all the open brackets,

View 2 Replies View Related

SQL & PL/SQL :: Error In Query - ORA-00905 - Missing Keyword?

Jun 1, 2010

Check out following query

SELECT LOCALTIMESTAMP,SYSTIMESTAMP,EXTRACT(hour FROM LOCALTIMESTAMP) +2,
CASE WHEN EXTRACT(HOUR FROM LOCALTIMESTAMP) +2 Between 9 and 17 OR
(EXTRACT(HOUR FROM LOCALTIMESTAMP)+2 = '5' AND EXTRACT(MINUTE FROM LOCALTIMESTAMP)+2 > '60')
THEN TO_CHAR(FROM_TZ(LOCALTIMESTAMP,'GMT') AT TIME ZONE '+05:30','DD-MON-YYYY HH24:MI:SS')
CASE WHEN EXTRACT(HOUR FROM LOCALTIMESTAMP)+2 < '9'
THEN TO_CHAR(FROM_TZ(SYSTIMESTAMP,'GMT')+2 AT TIME ZONE '+05:30','DD-MON-YYYY HH24:MI:SS')
END
FROM dual;

ORA-00905: missing keyword

View 8 Replies View Related

SQL & PL/SQL :: Write Query To Show Monthly Salary?

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

SQL & PL/SQL :: How To Query To Show Schema About Metadata In OracleDB

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

SQL & PL/SQL :: Show Result Of Query Just Like Pivot Table?

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

PL/SQL :: Query Is Show All Keepers / Who Cares Of All Animals In Zoo

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

Application Express :: Query Builder Show Nothing?

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

SQL & PL/SQL :: Update Query Fail With Error - Missing Right Parenthesis

Feb 17, 2010

my update query seems to fail with error

ORA-00907: missing right parenthesis

Below is my test case:

create table t1(
sn varchar2(30),
inv_org number);
create table s1(
sn varchar2(30),
trx_date date,
inv_org number);

[code]...

View 8 Replies View Related

SQL & PL/SQL :: Query To Find Missing Dates Between Two Columns In Given Month?

Sep 26, 2012

I need query to find the missing dates between two columns in given month.

CREATE TABLE emp_shift (
empno NUMBER(4),
fr_date DATE,
TO_DATE DATE,
shift VARCHAR2(1));
CREATE TABLE emp (
empno NUMBER(4)
);

[code].....

Required output is

MISSING_DATES EMPNO
---------------------- ----------
09-SEP-12 TO 11-SEP-12 7499
23-SEP-12 TO 26-SEP-12 7499
01-sep-12 TO 30-SEP-12 7521
01-sep-12 TO 30-SEP-12 7788

View 8 Replies View Related

SQL & PL/SQL :: Create A Query Which Will Show Sales Of Products By Month

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

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

Reports & Discoverer :: Case Statement Inside Oracle Query - Missing Right Parenthesis

Jun 13, 2013

Can I use CASE statement Inside a Oracle Report Query. I'm using Oracle Reports Builder 10g.

My Report Query looks like this,

select invh_code, invh_number, invh_date, invh_cm_code, im_description
from invoice_head, invoice_det, unit_of_measurement, item_master
where invd_invh_code = invh_code and im_code = invd_item_code
AND
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4)) BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
else 1
end)
order by invh_number

:p_flag is a parameter that i pass from oracle form and based on that value (:p_flag=1) i need to include this specific condition
else omit that condition.

But the CASE statement is throwing Error

ORA-00907 :Missing Right Paranthesis
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4))
==> BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)

View 6 Replies View Related

Application Express :: To Change The Default Behavior Of Hide / Show Region To Show

Jun 26, 2012

I am trying to change the default behavior of Hide/Show Region to show, after some attempts i got it partially working but now clicking the icon to toggle hide/show doesn't work also changed the icons and added type="" but its not working.

View 16 Replies View Related

SQL & PL/SQL :: Query Using DBLink Returns More Rows Than Direct Query?

Apr 6, 2010

I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.

For example:

select
x,y,z
from
mytable@dblink

returns 788,324 rows

while
select
x,y,z
from
mytable

returns 712,102 rows

It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.

View 10 Replies View Related

SQL & PL/SQL :: Query To Get Columns Into Rows?

Aug 10, 2010

I have one issue with the converting columns into rows.

Employee table has manager , additional manager and some other columns. If the employee has additional manager, then i need those employee record with 2 rows : 1st row - manager with other data , 2nd row - additonal manager with other data .

Orginal data is like this:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ADDTIONAL_MGR ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 7698

[code]....

I have tried this , using UNION ALL method, But it is taking more time.

SQL> ED
Wrote file afiedt.buf
1 SELECT DECODE (DUMMYTAB.NUM,'1',MGR, '2',addtional_mgr) MANAGER, empno, ename, job, hiredate, sal, comm, deptno
2 FROM emp E, (SELECT '1' NUM FROM DUAL UNION ALL SELECT '2' NUM FROM DUAL) DUMMYTAB
3* WHERE addtional_mgr IS NOT NULL
SQL> /

View 15 Replies View Related

SQL & PL/SQL :: Update Query For 2 Lac Rows

Jun 12, 2013

I have 2 tables with 3 common columns (Col1, col2,Primary). One common column name is primary (oracle key word).Around 2 lakhs rows to be updated and No indexes are used on these tables. I need to write an update query as shown below.

Emp table

Col1col2primay
100101y
103104n
201105y
100101y

Dept table

Col1col2primay
100101null
103104null
000656null

Update query Result

Col1col2primary
100101y
103104n

View 16 Replies View Related

PL/SQL :: Query - Rows In Columns

Jul 27, 2012

I have following table -

CREATE TABLE     group_device
( group_id          NUMBER (8)     NOT NULL
, member_id     NUMBER (4)     NOT NULL
, device_id     NUMBER (10)     NOT NULL
, install_date     DATE           NOT NULL
, remove_date DATE
);

[Code]...

Current output does looks like below

10     1     123     23-MAY-12     28-MAY-12
10     1     456     28-MAY-12     01-JUN-12
10     1     789     01-JUN-12     
10     2     999     04-MAY-12     17-MAY-12
10     4     1123     22-JAN-12     27-JAN-12
10     4     1456     27-JAN-12     28-JAN-12

Device_id - Replaced_device_id - remove_date :: For group_id=10

e.g. for member_id = 1, device_id=123 got replaced by device_id=456 on 28-May-12
               device_id=456 got replaced by device_id=789 on 01-Jun-12

So output should look like this

10     123     456     28-May-12     -- Replaced     
10      456     789     01-Jun-12     -- Replaced     
10     789                    -- Active

Similarly there will be many groups, so it should list based on group_id

View 8 Replies View Related

Converting Query Rows Into Columns?

Sep 14, 2008

I have seen lots of examples of using PIVOT to return an sql query where the rows have been converted to columns. All the examples I can find require you to have two columns with multiple rows, and also require you to know the data which is in one of columns to make titles for the new columns.

I need something a little different...I have a query using UNION which goes something along the lines of...

SELECT [get data from one place]
UNION
SELECT [get data from another place]
UNION
SELECT [get data from another place]

this returns the following...
a_val
-----
100
200
300

query to return something like this...
a_val1 a_val2 a_val3
------ ------ ------
100 200 300

The names of the columns don't really matter. And I dont want to create a new table in the database just for the result.

View 1 Replies View Related

Select Number Of Rows From Query?

Jun 13, 2007

In sql plus How do I get the number of distinct rows of a certain value?

for example

select group_number from records group by group_number

How would I query for the total number of group_numbers in this query?

View 2 Replies View Related

SQL & PL/SQL :: How To Convert Query Columns Into Rows

Jun 19, 2013

the query used is

SELECT LOC_CD,
TO_CHAR(DT,'fmMon RRRR'),
SUM(QTY) ,
GROUPING_ID(LOC_CD)
FROM WIP
WHERE LOC_CD IN (1,2,3,4)
GROUP BY ROLLUP(LOC_CD),
TO_CHAR(DT,'fmMon RRRR'),TRUNC(DT,'MM')
ORDER BY TRUNC(DT,'MM'),LOC_CD

This query result attached

The red coln is the total I want to place it in row-wise

Date loc_1 loc_2 loc_3 loc_4 Total
May 2012 4,554 6,644 11,198
June 2012 4,986 5,838 777 11,601
22,799

View 1 Replies View Related

PL/SQL :: Limit Order By Rows Without Using Sub Query

Sep 4, 2013

Here is my query to fetch only 10  records from order by result set. 

select *from (select * from EOE_POC.PRODUCT_TEST_REPORT where PRODUCT_CODE='214d' order by CREATE_DATE desc ) where ROWNUM <= 10  

I am having problem binding it with java API . how to query this without using sub query ?

View 11 Replies View Related







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