Viktor wrote:
Hello all,
I am working with a query that does some counts. I've hit a brick wall
and can't get passed trying to figure out how I can make the query show
percentages %.
Here is the query:
select COUNTRY,
count (MSS) COUNT_MSS
FROM (
SELECT DISTINCT
Thanks for your reply! Will try it now. Will this work in 8i?
Viktor
Bricklen Anderson [EMAIL PROTECTED] wrote:
Viktor wrote: Hello all, I am working with a query that does some counts. I've hit a brick wall and can't get passed trying to figure out how I can make the query show percentages
Viktor wrote:
Thanks for your reply! Will try it now. Will this work in 8i?
Viktor
If it works at all, then it should work in both 8i and 9i, although I
don't have a version of 8i handy right now to try this on.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
hi,
There is a function RATIO_BY_PERCENT or something very similar to this in
SQL
just find it out
this gives u individual contributions as compared to the whole thing.
Regds,
Rohan
From: Viktor [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL
Bambi,
I think that the query (2) will return the the same count as query (1)
if you use the
column user_bytes from the dba_data_files rather than the column
bytes.
If I sum the bytes from dba_extents for a tablespace_name xxx
and sum the user_bytes from dba_data_files for tablespace_name xxx I
Nice try. Same result.
1 select a. tablespace_name, sum(b.user_bytes)/(1024*1024) allocated,
2 sum(a.bytes)/(1024*1024) used, round(sum(a.bytes)/sum(b.user_bytes),4)
pct
3 from dba_extents a, dba_data_files b
4 where a.tablespace_name=b.tablespace_name
5 and
Bambi,
Your second query is wrong because all extents in a tablespace don't
necessarily belong to the same datafile. Try the query without the
aggregate functions and the GROUP BY, and you'll understand your
mistake.
HTH,
SF
Bellow, Bambi wrote:
Friends --
Why would these two queries
Odder still, I get inconsistent results. megs_allocated is always
wrong, but megs_used is right when run against one tablespace, wrong
against another:
TABLESPACE_NAMEMEGS_ALLOCATED MEGS_USEDPCT
-- -- -- --
Bambi,
I tried your sql on my test server and the used space is the same.
here are the results. The ALLOCATED and PCT are way out, I'm looking.
1 select
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,2)*100
pct
2 from (select
But Stephane, I am aggregating by tablespace for both extents and for
data_files. There is nothing here that is separating out anything by
datafile. And, if I take away the GROUP BY, I lose the ability to aggregate
at all, which is the point of this...
-Original Message-
Sent: Friday,
I encountered the same issues. Also, just to make things interesting, if
you replace DBA_EXTENTS with DBA_FREE_SPACE, the number of MEGS_ALLOCATED is
different. Still wrong, mind you, but different.
None of this makes any sense to me. My bet is that I got me a bug.
Bambi.
-Original
Below is a very nice tablespace script, followed by some sample output.
Thought you might like it. I found in somewhere.
REM name: freespace.sql
REM This script is used to list database freespace, total database
REM space, largest extent, fragments and percent freespace.
REM
REM Usage
The aggregate function operates last, on a result set. Why I suggested
to suppress the GROUP BY is that then you would have seen that the
number of bytes from each datafile is returnedtoo many times.
If you have F1 and F2 associated to your database, with E1 and E2 in F1
and E3 in F2 (I hope the
]
[EMAIL PROTECTED]cc:
.com Subject: RE: SQL Query
You can't join DBA_EXTENTS and DBA_DATA_FILES based on an
equality of tablespace_name, and then add up the bytes of the files
for the tablespace.
ie.
select
b.tablespace_name,
b.bytes
from dba_extents a, dba_data_files b
where a.tablespace_name=b.tablespace_name
Try running that query, and
Ah, I see your point, now, and that's quite correct. Which means I can
either aggregate on fileid, or aggregate by table and join. Nice catch, and
mystery solved. No bug report.
Take care and have a great weekend!
Bambi.
-Original Message-
Sent: Friday, November 14, 2003 2:50 PM
To:
Jared
--
That is expected behavior for
this query. What was weird is that I expected the aggregation in the group
by to apply to multiple fileids making GROUP BY a.tablespace_name,
b.tablespace_name to be able, then, to join a.tablespace_name to
b.tablespace_name as a 1-1 join rather than
]
To: Multiple recipients of list ORACLE-L
Sent: Saturday, November 15, 2003 9:54
AM
Subject: Re: SQL Query
You can't join DBA_EXTENTS and
DBA_DATA_FILES based on an equality of
tablespace_name, and then add up the bytes of the files for the tablespace. ie. select
b.tablespace_name
:
Subject:RE: SQL Query
Jared --
That is expected behavior for this query. What was weird is that I expected the aggregation in the group by to apply to multiple fileids making GROUP BY a.tablespace_name, b.tablespace_name to be able, then, to join a.tablespace_name to b.tablespace_name as a 1-1
Sorry, don't understand the DBA part ( #2 ).
Binley Lim [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
11/14/2003 02:09 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: SQL Query
Would have thought:
1
, November 15, 2003 1:19
PM
Subject: Re: SQL Query
Sorry, don't understand the
DBA part ( #2 ).
"Binley Lim" [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
11/14/2003 02:09 PM
Please respond to ORACLE-L
To:
Johan,
First, you don't need the distinct. The proper query will return
1 row per ip. Second, take the max(timestamp) out of the group
by. That is causing the problem.
Daniel
Johan Muller wrote:
I have multiple timestamps values for single ip in a table, I
need the max(timestamp) for each
select ip, max(timestamp) from table
group by ip;
-Original Message-From: Johan Muller
[mailto:[EMAIL PROTECTED]Sent: Thursday, October 02, 2003 10:45
AMTo: Multiple recipients of list ORACLE-LSubject: Sql
query : select max timestamp value from table
I have multiple
Title: RE: Sql query : select max timestamp value from table
select ip, max(timestamp) from table
group by ip;
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-Original Message-
From: Johan Muller [SMTP:[EMAIL PROTECTED]
I have
Johann,
Take the timestamp out of your group by.
Cheers,
Melanie
***
Melanie Caffrey
Proximo Consulting
Services, Inc.
[EMAIL PROTECTED]
(212) 686-6004 Ext. 32
-Original Message-
From: [EMAIL
Title: Message
select
ip, max(timestamp) from table
group
by ip;
-Original Message-From: Johan Muller
[mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 10:45
AMTo: Multiple recipients of list ORACLE-LSubject: Sql
query : select max timestamp value from table
Imran,
Tom Kyte has a thread that might help:
http://asktom.oracle.com/pls/ask/f?p=4950:8:1554835115460038644::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:229614022562,
Essentially, you can do this:
CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR )
RETURN VARCHAR2 IS
ret
Mike,
Just wanted to supplement your example with another one from my
environment (uses nested tables with CAST and MULTISET to achieve the
same):
SELECT c.customer_id
,customer_name
,utils.code_table_to_string(CAST( MULTISET(
SELECT distinct dlr_dealer_code
FROM
Err, not quite, actually.
The solution presented below will simply generate the cartesian product of
the sum of attributes from the table.
The (grossly pedantic) solution is:
select distinct a.col1, a.col2, a.col3||b.col3||c.col3
from fred a, fred b, fred c
where a.col3 = 'A'
and b.col3 = 'B'
Assume your table is name fred:
select a.col1 a.col2 a.col3||b.col3||c.col3
from fred a, fred b, fred c;
Allan
-Original Message-
Sent: Friday, September 19, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L
Hi,
I have the following data in a table:
col1 col2 col3
1
select columns from table A)
where predicates
and datecreated = (select min(datecreated)
from table b where b.cid = a.cid and b.pid = a.pid)
At 08:14 PM 6/13/2003 -0800, you wrote:
I have a table with records like this
CID S TO_CHAR(DATECREATED, MESSAGE PID
Given the low elapsed time for each iteration of the query I wonder if
the problem might be susceptible to either of the following approaches.
1. Calling the query less often. I'm guessing from the object names etc
that this is some sort of scientific analysis program, and it may be
that you are
craete a another table that just has
the rows I need, I can often significantly improve performance.
From: Niall Litchfield [EMAIL PROTECTED]
Date: 2003/06/12 Thu PM 01:15:08 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: sql query optimization
Given the low
This is an interesting (and relatively complex) query with what I think are
several opportunities to tune it. I'd probably spend some time looking at
the following to see if they might help you out:
1) Look at the sub-select with the connect by clause... Try executing
that query on it's own
All very good suggestions, but given the low elapsed time and cost figures,
I suspect most of the time is taken up jumping around buffer cache locating
and pinning blocks.
What would help a lot is to eliminate the table access by index rowid by
including all query columns in your indexes. There
Hi:
Thanks for your help and suggestions, Mark.
I have done some of the things you suggested already. Please see my text
below.
On Tue, 10 Jun 2003, Mark Richard wrote:
This is an interesting (and relatively complex) query with what I think are
several opportunities to tune it. I'd
by: cc:
[EMAIL PROTECTED]Subject: Re: sql query optimization
.com
Add group by ID; as in:
SELECT ID,MAX(LastModDate) FROM Tab
group by ID;
-Original Message-
From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 28, 2003 8:15 PM
To: Multiple recipients of list ORACLE-L
Subject: SQL Query Help
i have a query that returns
RB:
Try :
SELECT ID, LastModDate
FROM Tab a
WHERE TRUNC(LastModDate) = (Select MAX(TRUNC(LastModDate)) From Tab b)
The explanation of the error message follows.
RWB
===
I had tried this as per Oracle Metalink Note.When I add the group by ID it will
display both the records like
2 1/20/2003 2:56:18 AM
1 4/23/2003 10:26:42 PM
but I want to see the only the record with the latest time stamp like
1 4/23/2003 10:26:42 PM
-Original Message-
This will work.But the actual query that I have is a join b/w two tables
and not as simple as it looks in the example I had given
SELECT ID,LastModDate
FROM Tab
I bascially will have to include my complete main query in the sub query with
TRUNC(MAX
This might be costly on the
SELECT ID,MAX(LastModDate)
FROM Tab
where id = 1
group by ID;
--
Daniel W. Fink
http://www.optimaldba.com
Basavaraja, Ravindra wrote:
I had tried this as per Oracle Metalink Note.When I add the group by ID it will
display both the records like
2 1/20/2003 2:56:18 AM
1 4/23/2003
: Re: SQL Query Help
[EMAIL PROTECTED
Reddy
There are a variety of techniques to accomplish this, and it is difficult
to determine which will work best in your situation. Here is a link to a
classic article on this topic by the great Jonathan Gennick who participates
in this list from time to time.
Hello
9i (ORA-01489! be aware):
SELECT LTRIM(SUBSTR(SYS_CONNECT_BY_PATH(RPAD(ename, 20, ' '), '/'), 2, 19)) ename
, ename mgrs
FROM emp
CONNECT BY PRIOR mgr = empno
/
Eberhard, Jeff wrote:
Using the EMP table as an example I want to create a query that will show a
list of employees and
Vladimir Begun wrote:
SELECT LTRIM(SUBSTR(SYS_CONNECT_BY_PATH(RPAD(ename, 20, ' '), '/'), 2,
typo: ^RTRIM
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
--
Please see the official ORACLE-L FAQ:
Correction
I am using
select * from cust where
to_char(DATECREATED,'DD-MON-YY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
-Original Message-
From: Basavaraja,
Title: RE: sql query: to_date() :ORA-01830: date format picture ends before
Why are you comparing a date to a char?
select *
from cust
where to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = '01-FEB-03 07:53:28'
/
should work it appears that your NLS_DATE_FORMAT kicked in when your
What's the to_char for?
try
select * from cust where
DATECREATED = to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
-Original Message-
Sent: Tuesday, March 11, 2003 12:20 PM
To: Multiple recipients of list ORACLE-L
before
Hi...
I am getting the following error with a query like
You are comparing CHAR to DATE, you would need one of the following
conditions
1- where DATECREATED = to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
2- where to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = '01-FEB-03 07:53:28'
to be able to complete the WHERE clause
Rick Weiss
Oracle DBA
Basavaraja, Ravindra wrote:
Hi...
I am getting the following error with a query like this
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input
Why are trying to compare date to string: to_char(...) = to_date(...) ?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, March 11, 2003 3:20 PM
Hi...
I am getting the following error with a
Hello Ravindra,
You should use to_char OR to_data not both.
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = '01-FEB-03 07:53:28'
Wednesday, March 12, 2003, 2:20:17 AM, you wrote:
BR Hi...
BR I am getting the following error with a query like this
BR select * from cust
Ravindra,
All the solutions given by others are correct.
The reason for the error is that: In your where clause the date that you are
converting to char, is re-converted to date for comparison with a date
value. This conversion uses Oracle's implicit date conversion, the implicit
date conversion
Hi Roland,
Is the query producing right results in the first place?
If there are n tables in the FROM clause, there must be atleast n-1 joins in
the where clause to avoid the Cartesian product (which definitely screws up
performance and rarely produces correct results.)
In the FROM clause of
There is no join condition between first two tables (PBK.UNDERLAG,
PBK.VARUKORGEANREL_ULAG) and last four tables (rik2.vare,
rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp).
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L
Roland,
A quick look suggest you have cartesian joins unless rik2.vare,
rik2.hierarki_tekst, rik2.art_hierarki, pbk.sortiment_vgrp all have only one
row.
Iain Nicoll
-Original Message-
Sent: 05 February 2003 14:53
To: Multiple recipients of list ORACLE-L
I have this sql query. I am
Hi Roland,
I make a little modification to your query below,
SELECT distinct PBK.VARUKORGEANREL_ULAG.ean, 0, 0, 0,
rik2.vare.varenavn, rik2.vare.str,
PBK.VARUKORGEANREL_ULAG.lagstapris,
rik2.vare.hylletxt2, rik2.art_hierarki.vgrp,
You would be surprised to discover what you could do with OR and suitably placed
parentheses.
- Original Message -
From: Krishnaswamy, Ranganath
[EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Wed, 18 Dec 2002 23:53:44
Hi all,
How do I forumlate the
Try this,
SELECT DISTINCT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND
((TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
I think you can translate it as OR, but you have to use some DISTINCT on
output rows (because you use UNION and not UNION ALL).
Without DISTINCT it is like:
SELECT
H1.OID HISTORIEOID
,FAHRZEUG.AMTLICHESKENNZEICHEN
,FAHRZEUG.OID
,H1.PRODUKT
,H1.AUFTRAGSPOSITIONSNR
,H1.MYTECHOBJEKT
FROM FAHRZEUG,
sql server 7???
and you are trying to find an answer on Oracle
board?
Igor Neyman, OCP DBA[EMAIL PROTECTED]
- Original Message -
From:
Santosh
Varma
To: Multiple recipients of list ORACLE-L
Sent: Friday, October 04, 2002 2:23
AM
Subject: RE: Sql query
HELP!
-Original Message-
Sent: 04 October 2002 14:53
To: Multiple recipients of list ORACLE-L
sql server 7???
and you are trying to find an answer on Oracle board?
Igor Neyman, OCP DBA
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
- Original Message -
To: Multiple
ARE YOU AN IDIOT
-Original Message-
Sent: 04 October 2002 16:13
To: Multiple recipients of list ORACLE-L
HELP!
-Original Message-
Sent: 04 October 2002 14:53
To: Multiple recipients of list ORACLE-L
sql server 7???
and you are trying to find an answer on Oracle board?
Obligatory...
ARE YOU AN IDIOT?
-Original Message-
Sent: Friday, October 04, 2002 11:13 AM
To: Multiple recipients of list ORACLE-L
HELP!
-Original Message-
Sent: 04 October 2002 14:53
To: Multiple recipients of list ORACLE-L
sql server 7???
and you are trying to find an
HELP!
is this also sql server 7 command? :-)
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, October 04, 2002 11:13 AM
HELP!
-Original Message-
Sent: 04 October 2002 14:53
To:
Are you an idiot?
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, October 04, 2002 11:13 AM
HELP!
-Original Message-
Sent: 04 October 2002 14:53
To: Multiple recipients of list ORACLE-L
sql server 7???
and you are
ARE YOU AN IDIOT! ;o)
-Original Message-
Sent: Friday, October 04, 2002 10:13 AM
To: Multiple recipients of list ORACLE-L
HELP!
-Original Message-
Sent: 04 October 2002 14:53
To: Multiple recipients of list ORACLE-L
sql server 7???
and you are trying to find an answer on
3 ïËÔÑÂÒØ 2002 12:03, ÷Ù ÎÁÐÉÓÁÌÉ:
I have a table test((NAME VARCHAR2(10),AGE NUMBER(2));
data of the table is
NAME AGE
-- -
ANAND 1
BALU 2
CHANDU3
DAVID4
I want a query
why do
u want such a query?
-Original Message-From: Anand Kumar N
[mailto:[EMAIL PROTECTED]]Sent: Thursday, October 03, 2002
1:33 PMTo: Multiple recipients of list ORACLE-LSubject:
SQL Query
I have a table
test((NAME VARCHAR2(10),AGE NUMBER(2));
data of the table
TRY THIS :
SELECT NAME, 4-AGE+1 FROM test;
--- Naveen Nahata [EMAIL PROTECTED] wrote:
why do u want such a query?
-Original Message-
Sent: Thursday, October 03, 2002 1:33 PM
To: Multiple recipients of list ORACLE-L
I have a table test((NAME VARCHAR2(10),AGE
NUMBER(2));
the below query adds/substracts the value in the age column...
SELECT NAME, 4-AGE+1 FROM test;
does not work..
-Original Message-
SALAYMEH
Sent: Thursday, October 03, 2002 5:28 PM
To: Multiple recipients of list ORACLE-L
TRY THIS :
SELECT NAME, 4-AGE+1 FROM test;
--- Naveen Nahata
Santosh,
If you could tell why do you want the ages be reversed?
Aleem
-Original Message-
Sent: Thursday, October 03, 2002 5:35 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: SQL Query
the below query adds/substracts the value in the age column...
SELECT NAME
huh?
new value
ANAND 1 4-1+1 = 4
BALU 2 4-2+1 = 3
CHANDU 3 4-3+1 = 2
DAVID 4 4-4+1 = 1
looks right to me.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Thursday, October
Santosh,
Till now you haven't exactly specified what you want and WHY you want such a
thing.
So I can only make a guess at what you want. If you want the age of the last
record to be shown with the name of the first record and so on, then
following is the query:
SQL SELECT * FROM test;
NAME
Santosh,
your
query is working. See below
SQL CREATE TABLE CLIENT ( 2 CLIENTID NUMBER PRIMARY
KEY, 3 NAME VARCHAR2(10) 4
);
Table
created.
SQL CREATE TABLE PROJECT( 2 PROJECTID NUMBER PRIMARY
KEY, 3 NAME VARCHAR2(10), 4 CLIENTID REFERENCES
CLIENT(CLIENTID) 5 );
Table
created.
SQL
]]On Behalf Of Naveen NahataSent:
Thursday, October 03, 2002 9:03 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: Sql query
Santosh,
your
query is working. See below
SQL CREATE TABLE CLIENT ( 2 CLIENTID NUMBER PRIMARY
KEY, 3 NAME VARCHAR2(10) 4
);
Table created
Thank you Stephane! Your final idea of FIRST_ROWS as a winner! Oracle
slapped the data back in just a second. Thanks everyone for the ideas to
try.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, September 10, 2002 3:42
Dennis,
If you use the ordered hint and have sa then so then am and also hint to use
the index on sa(ret) then I think that would be about the best as you'd be
starting with the best filter ie 1.3m/281 giving less than 5000 on average
(assuming ret is indexed). I don't know if you'd have to
DENNIS WILLIAMS wrote:
I am trying to tune a SQL query on Oracle 8.1.6. I have tried several
optimizations, but
so far have made no improvements. I would appreciate any suggestions.
SELECT am.lid, am.name
FROM am, so, sa
WHERE so.lid = am.lid
AND so.key_ = sa.so_key
AND am.active = 1
Dennis,
What is the distribution of sa.ret?
I didn't see it included in an index.
Jared
DENNIS WILLIAMS [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/10/2002 12:18 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Dennis,
You're better off not having an index on the AM table. With 220,000 out of 250,000
rows having the same value, an index will do you more harm than good. You're not much
better off on the SO table with only 12 different values out of 1.3 million. The final
table SA has 281
Thanks everyone for your wonderful suggestions. And thanks for leaving the
hey stupid off your reply header :-)
Rachel - Thanks for the bitmapped idea. These tables don't change often, so
that may be a good alternative.
Iain - Thanks so much for the detailed suggestions.
Rick - Good sanity
Just in case anyone out there is interested, we use the term Mickey
Mouse schema to refer to a very specific design tactic. We're *not*
using the term's slang meaning of unimportant or uninspired.
(...Which always seemed odd to me, because MM is a really strong, high
quality brand.)
Subject: RE: SQL Query tuning help
Thanks everyone for your wonderful suggestions. And thanks
for leaving the
hey stupid off your reply header :-)
Rachel - Thanks for the bitmapped idea. These tables don't
change often, so
that may be a good alternative.
Iain - Thanks so much
Title: RE: SQL Query
I hope this is not to late for you. Anyway, this questions comes up often. Below is the solution to pivot rows for up to 12 values of field1. Just adjust to fit your range of values.
HTH
Tony Aponte
Home Shopping Network, Inc.
create table tab1 (field1 number,field2
hi vandana!
use
DESC[RIBE] TABLE NAME
regards
daniel
Vandana wrote:
I am using an Oracle database running in Linux. I would like to
view the description of a table. For ex., if there is a table called
'person'. I would like to see the names of the columns in this table,
their
At SQL*Plus it is the same command
Describe or desc and name of the table
You also need to be connected to the database
HTH
Aleem
-Original Message-
Sent: Saturday, July 13, 2002 11:53 AM
To: Multiple recipients of list ORACLE-L
Subject:SQL query
I am using an
I am sorry for not being precise in my question. I would like to
view the primary key, foreign key and other 'constraints' information of
my table. While this could be viewed with 'desc' in other dbmss, this
information is not provided with the 'desc' in oracle. With what command
can
Title: RE: SQL query
Same
Desc TableName
Arslan Zaheer Dar
[EMAIL PROTECTED]
Database Administrator
Shaukat Khanum Memorial Cancer Hospital Research Centre
www.shaukatkhanum.org.pk
+ 92 (042) 5180725 - 34 Ext: 2323
-Original Message-
From: Vandana [mailto:[EMAIL PROTECTED
select * from dba_cons_columns;
On Saturday 13 July 2002 01:08, Vandana wrote:
I am sorry for not being precise in my question. I would like to
view the primary key, foreign key and other 'constraints' information of
my table. While this could be viewed with 'desc' in other dbmss, this
select object_name
from all_objects
where
lower(object_name)
like
'%cons%';
will give you all the data dictionary tables/views that deals with constraints
ltiu
On Saturday 13 July 2002 01:08, Vandana wrote:
I am sorry for not being precise in my question. I would like to
view the primary
Vandana,
Put this into a script, save to hard drive, and run
from sqlplus using @path\script:
SET ECHO OFF
accept table_name prompt Enter the name of the Table
:
set heading on
set verify on
set newpage 0
ttitle 'Table Description - Space Definition'
spool tfstbdsc.lst
btitle off
OJNK? Your term Larry? Or do I need to RTFM?
Didn't realize this had a name.
Jared
On Wednesday 26 June 2002 18:48, Larry Elkins wrote:
Greg,
When the situation is appropriate, and I don't want a correlated approach,
I will typically use the hash aj as illustrated by your second
Roland,
This has just been covered in the past day or two. In this case the best
solution, where a straight minus doesn't look applicable is the rather
elegant (sorry I've forgotten whose it was) construct below.
select a.id, a.name
from table1 a,
table2 b
where a.id = b.id(+)
and b.id
SELECT id
,name
FROM table_a
WHERE EXISTS ( SELECT id
FROM table_b
);
-Original Message-
Sent: 26 June 2002 14:13
To: Multiple recipients of list ORACLE-L
Hallo,
anyone whom can give me an example on how to pick out field id and name from
select field from table1 where (select 1 from table2 where table2.field
= table1.field) is null;
-Original Message-
Sent: Wednesday, June 26, 2002 9:14 AM
To: Multiple recipients of list ORACLE-L
Hallo,
anyone whom can give me an example on how to pick out field id and name
from
Ignore my message when it comes through...I gave you the opposite answer to
the one you were looking for!
;o)
-Original Message-
Sent: 26 June 2002 14:13
To: Multiple recipients of list ORACLE-L
Hallo,
anyone whom can give me an example on how to pick out field id and name from
table
SELECT ID,NAME FROM table1 a WHERE NOT EXISTS (SELECT 0 FROM table2 b WHERE
a.ID=b.ID AND a.NAME=b.NAME)
JP
On Wednesday 26 June 2002 15:13, you wrote:
Hallo,
anyone whom can give me an example on how to pick out field id and name
from table one and get only the id's that exists in table
Feckmy email to ignore this email arrived before this email didgo
figure
-Original Message-
Sent: 26 June 2002 15:09
To: Multiple recipients of list ORACLE-L
SELECT id
,name
FROM table_a
WHERE EXISTS ( SELECT id
FROM table_b
);
1 - 100 of 153 matches
Mail list logo
