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


ADVERTISEMENT

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 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

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

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 :: 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

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 :: Show Last 4 Digits

Mar 4, 2010

I have a requirement where I have a value stored in varchar2(12), and no matter what the size of data, I should mask all the characters except the last 4

eg: - 23115645

after masking should be - xxxx5645,

create table test1(x varchar2(12))
insert into test1 values('34567745')
/
insert into test1 values('438765311')
/
insert into test1 values('1112345')
/
commit

[Code].....

y should store value of x in such a way that no matter what the size of x is (7 characters or 9), we should see only the last 4, but the remaining should be masked with xxxx

I tried the above update, but using substr, i am able to get this if x has 9 characters

not sure how to achieve this if x has 7, or 4 characters

View 22 Replies View Related

SQL & PL/SQL :: Row Value Show In Column Wise

Mar 1, 2012

I have a table which have three column.

1) empid
2) date
3) status

I want to show the value empid intime outime.

select in1.empid,in1.atttime,out.atttime from (select empid ,atttime from attend
where status=1 and to_char(atttime,'mmyyyy')='022012' order by empid,atttime desc) in1,
(select empid,atttime from attend where status =0 and to_char(atttime,'mmyyyy')='022012'
order by empid,atttime desc) out
where in1.empid=out.empid and in1.empid='02256'
order by in1.atttime,out.atttime

But this query do .one value relation with all column.means first february in time with all out time.

View 1 Replies View Related

Forms :: Reports Does Not Show Any Value

Feb 17, 2011

i am using oracle 10 forms ! in report i have used form Query "select sum(sale_price) from (table) expense. i attached it to oracle forms but at that time it does nt show any value!

View 3 Replies View Related

SQL & PL/SQL :: How To Show Nanoseconds Of CURRENT_TIMESTAMP

Jan 20, 2011

I am using 10g database on Microsoft Windows family OS

when i run following query

sql> SELECT CURRENT_TIMESTAMP FROM DUAL;

then i get result

20-01-2011 11:52:17.921000 AM +05:00

but i want to get result as

20-01-2011 11:52:17.921256 AM +05:00

is there any way to get up mentioned result.

View 1 Replies View Related

SQL & PL/SQL :: Want To Show Data Quarterly

Apr 8, 2013

i want to show data quarterly.My quarter structure is 3 mOnths.

q1 - apr-2013 to jun-2013
q2 - jul-2013 to sep-2013
q3 - oct-2013 to dec-2013
q4 - jan-2014 to apr-2014

I input parameter is from date and to date..

Suppose user enter from date - '01-apr-2012' and to date - '01-aug-2012',
only q1 shows..

and user enter from date - '01-apr-2012' and to date - '30-sep-2012',
only q1,q2 shows..

how i do that..My data is like this

create table t1( t1_month VARCHAR2(10),
t1_val1 number(10),
t1_val2 NUmber(10)
);
INSERT INTO t1('APR-13',100,200);
INSERT INTO t1('MAY-13',100,200);
INSERT INTO t1('JUN-13',100,200);
INSERT INTO t1('JUL-13',100,200);
INSERT INTO t1('AUG-13',100,200);
[code]...

View 2 Replies View Related

SQL & PL/SQL :: Show Result Random

Mar 24, 2010

I run follwing query.

select * from crp_00_08

it shows result like

BAS_CODBAS_DES

1MAIN GATE (FRONT)
2NEAR MILL # 3, 3-1
3NEAR MILL # 3, 3-2
4NEAR MILL # 3, 3-3

[Code]...

i want that when i run above query every time bas_cod column should start with difference value. now it is starting from 1, i need when i run next time it should start with another number except 1,

i mean whenever i run above query,order of bas_cod must be change from last.

how can i do it.

View 12 Replies View Related

SQL & PL/SQL :: Show End Date Based On Next?

May 19, 2010

How to write a single query for the below scenario?I got only start date column, but I need show the end date based on next (start date-1). For the last row, need to show the same date in the end date column.

For Example:I have the data as shown below,

Start date End date
1-Jan-10
3-Mar-10
3-May-10
5-Jul-10
1-Aug-10

I wanted to output as shown below

Start date End date
1-Jan-10 2-Mar-10
3-Mar-10 2-May-10
3-May-10 4-Jul-10
5-Jul-10 31-Jul-10
1-Aug-10 1-Aug-10

View 10 Replies View Related

SHOW TABLES In Oracle?

Mar 31, 2004

In MySQL, I can get a list of all tables w/ this query: "SHOW TABLES"

Whats the syntax w/ Oracle 9i ?

Looked through the docs online, but w/o knowing what you're trying to look up, you can't really look anything up (kind of a catch22).

View 9 Replies View Related

PL/SQL :: Not Show Last Row Of Test Number?

Feb 7, 2013

I have below mentioned records in mount so whenever i select any mount it should now show the the last row of that mount i.e. test no-3 any records of mount which will not show last row of test number

mount     day     org1 test_No org2 org3     org4
299.00 1     0     1     1     0     7186
299.00     1     0     2     1     0     7186
299.00     0     3     3     0     7186

[code]...

but i want test_no should be in asceding order

View 3 Replies View Related

11g RAC Listener Show Only One Instance?

Jan 31, 2013

Though I have it all configures:

On node1:
SQL> show parameter listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.28.155.79)(PORT=1521))))

[code]....

View 9 Replies View Related

Show Parameter Audit_trail And Function

Dec 16, 2010

Is there any way to get audit_trail.VALUE? I know the statement: show parameter audit_trail; can show that value. But, i want to build a function which will return a string that contain audit_trail.VALUE

View 1 Replies View Related

SQL & PL/SQL :: How To Show Sequential Numbers For Each Group

Oct 4, 2010

MyTable has missing records:

id_group id_column
-------------------------
1 1
1 2
1 3
1 5
1 9
2 1
2 2
2 4
2 6

As you see, it could be missed record 4,6,7 and 8 of group 1 and record 3 and 5 of group 2. That's is an example.

I need to show all sequential records, incluing missing ones, like this:

Id_group Id_column
-------------------------------------------------
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
2 1
2 2
2 3
2 4
2 5
2 6

I try this query:

/*---*/
Select G.id_group, C.rownum id_column
from (select distinct id_group from MyTable) G
cross join (SELECT rownum FROM dual CONNECT BY LEVEL <= (select
max(id_column) from MyTable)) C
/*---*/

but it shows record 7, 8 and 9 as members of group 2.

View 9 Replies View Related

SQL & PL/SQL :: How To Show Only Address For Yes Tax Reporting Site

Aug 22, 2013

I have a sql to pull all payments from vendors for a specific time period; however, now tasked to only show the Address of each Tax Reporting Site. I have tried several commands but have not been successful.

View 7 Replies View Related

SQL & PL/SQL :: Show Date In Form With Abbreviated Name Of Day

Dec 1, 2010

i want to show the date exactly in following format:

00:16:15 Wed 1st Dec 2010

I have tried :
select to_char(to_date('01/12/2010 00:16:15' , 'dd/mm/yyyy hh24:mi:ss'), 'hh24:mi:ss Day dd Mon yyyy')
from dual

Result it is giving
00:16:15 Wednesday 01 Dec 2010

How to show date in form 1st 2nd 3rd and so on?
How to show Abbreviated name of the day?

View 13 Replies View Related

Forms :: How To Show Sql Statements Generated

Jul 19, 2010

Is there a way to show the sql statements generated by the Forms?

View 4 Replies View Related

SQL & PL/SQL :: How To Show Total Number Of Department

Apr 19, 2013

how to show total number of department with their department name assign to employee table.

View 2 Replies View Related

SQL & PL/SQL :: External Table Does Not Show All Records

Jun 24, 2010

I have an external table as follows:

CREATE TABLE EXT1
(
COL1 NVARCHAR2(2000),
COL2 CLOB
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY FILE_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
( "COL1" CHAR,
[code]....

From here I can count all records and the total number of records matching the file record count, but could not read the clob columns. it's not picking up the values. but if I take this out

( "COL1" CHAR,
"COL2" RAW
)

then I am missing the record counts.Also if you put all varchar and char, that also missing the count against the file.

View 6 Replies View Related







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