Tim Gorman wrote:
Great idea, Mark!
By the way, does anyone remember the yes command in UNIX? Same concept.
I think it was invented to answer ³yes² to any program which mindlessly
prompt for ³yes/no² responses in situations where only ³yes² makes sense.
Case in point: ³fsck². As in:
y
y
y
y
y
..
On Fri, 2003-09-19 at 22:09, Tim Gorman wrote:
Great idea, Mark!
By the way, does anyone remember the yes command in UNIX? Same concept.
I think it was invented to answer yes to any program which mindlessly
prompt for yes/no responses in situations where only yes makes sense.
y
y
y
y
Continue to repair or build a new filesystem-
Do you want to build a new filesystem (y/n)?
y
Oops ;-)
..
On Fri, 2003-09-19 at 22:09, Tim Gorman wrote:
Great idea, Mark!
By the way, does anyone remember the yes command in UNIX? Same
concept.
I think it was invented to
Great idea, Mark!
By the way, does anyone remember the yes command in UNIX? Same concept.
I think it was invented to answer ³yes² to any program which mindlessly
prompt for ³yes/no² responses in situations where only ³yes² makes sense.
Case in point: ³fsck². As in: ³Do you want to repair this
You raise an interesting idea in my mind...
How useful would it be for us if Oracle created an INFINITE_DUAL table -
One where you could select as many rows as you wished. I guess it might
be
dangerous but it would at least be very efficient if Oracle coded it as a
special table. Then you
You would require something like this:
http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:504432994857
HTH
GovindanK
Hello Listers,
I need some SQL help.
Not sure if this is possible in plain SQL or not, but here it is:
I have a table with two columns:
EMPNBR LINENBR
I was thinking along the same lines minus the outer query which is totally
unnecessary, but it is the beginning of my day here :-).
SF
- --- Original Message --- -
From: Wolfgang Breitling [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Tue,
- --- Original Message --- -
From: Mark Richard [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Tue, 16 Sep 2003 19:59:41
I guess I should have added some criteria like:
1) user_objects must have enough rows in it to
cover the range (if not
create viex xdual
as select rownum ID
from sys.col$;
Cannot have a column named rownum ... Going to be a difficult day. And we are only
half-week.
Regards,
Stephane Faroult
Oriole
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL
Hi Stephane,
I commiserate with you 8-)
Regards,
Guido
[EMAIL PROTECTED] 17.09.2003 10.44 Uhr
create viex xdual
as select rownum ID
from sys.col$;
Cannot have a column named rownum ... Going to be a difficult day. And we are only
half-week.
Regards,
Stephane Faroult
Oriole
--
Please
Hi!
This one is a very interesting consideration. The use of 'pivot' tables,
as in this case, without being something you meet daily is fairly frequent
(completing series like here is one usage, otherwise I commonly use them to
generate test data). It should be some standard feature, a kind of
Hello All,
Thanks for your wonderful ideas.
I just forwarded those to my dear Developer (my wife, that is :)
After posting my question to the list last night, I was told that the query will be
run against
an Informix database :)
Typical Developers !!! ;)
Cheers!
- Kirti
---
I know you said that this was Informix, but I'll post the article below anyway for the
benefit of other listers.
Oracle Technology Network Oracle Magazine September/October 2002
Turning On Pivot Tables By Jonathan Gennick
http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html
Describes a method
Hey there kirti , good to have you back.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, September 17, 2003 19:09
Hello All,
Thanks for your wonderful ideas.
I just forwarded those to my dear Developer (my wife, that is :)
After
]
[EMAIL PROTECTED]cc:
orp.com Subject: RE: Re: SQL help needed
Easy
select rownum+minlinenbr-1 from user_objects where rownum =
maxlinenbr-minlinenbr
minus
select linenbr from table
Replace the words with bind variables or whatever is appropriate for your
application. Oh, and be sure to test it for accuracy - I'm typing without
testing and could be
There is no way you can do it without some procedural programming.
PL/SQL is easy and, knowing you and your great knowledge , I'm not
going to offer that kind of advice. SQL was modelled on the set theory,
which essentially means that you are selecting subset, based on relatively
static criteria
[EMAIL PROTECTED]
ban.com.au cc:
Sent by: Subject: Re: SQL help needed
How about
select linenbr from (select rownum+min as linenbr from dba_objects where
rownum (max-min)
minus select linenbr from kirti where linenbr min and linenbr max)
/
pollux.stats.scott select * from kirti;
EMPNBRLINENBR
-- --
100 1
200
Naveen,
J J J
You are rightthe table is
structured that way. I want to find at which point in the carton numbers from 1
to 600 does have Item C. The final output would be like:
Item C:
Carton# Item qty weight
1-48 C 46 20
281-310 C 20 14
And so on.
Thanks in advance
De-aggregating aggregated values is an endeavour comparable to putting the toothpaste
back into the tube. You can't.
If you have the detail somewhere, read about MINUS and INTERSECT, or possibly NOT
EXISTS.
- --- Original Message --- -
From: Cabansay, Yoyong [EMAIL PROTECTED]
"Open the cartons and see!" ;-)
By the way can you reframe your question? Do you mean that you table
stores data as follows:
item carton_no qty weight
- --- --
A1
410
A 2
4 10
B 1
5 12
B 3
5 12
C 1
35
and so on?
RegardsNaveen
-Original Message-From:
Try something like ...
select id, col_1, col_2,sum(web_c),sum(other_c)
from
(select id, to_char(received_date,'-MM') col_1,yr||seq_no||ck
col_2,
case when seq_no 4000 then 1
else 0 end web_c,
case when seq_no= 4000
then 1 else 0 end other_c
from t
where received_date between
Thanks to ALL for your help on this!
As always your advice is greatly appreciated!
Viktor"Jamadagni, Rajendra" [EMAIL PROTECTED] wrote:
Try something like ...
select id, col_1, col_2,sum(web_c),sum(other_c)
from
(select id, to_char(received_date,'-MM') col_1,yr||seq_no||ck col_2,
case
I'm not sure I entirely understand the result you are trying to achieve.
Perhaps this will help
select received_date, msno, sum(count_web), hardcopy
from (
select id ,
to_char(received_date, '-mm') received_date,
id||yr||seq_no||ck MSNO
CASE WHEN seq_no 4000 then
Jared,
Thanks for your fast reply! Sorry if I didn't explain myself clear enough. Unfortunately count_web is not a column, id||yr||seq_no||ck is a combination of 4 columns that make up a primary key. if seq_no 4000,in aid||yr||seq_no||ck row, it's a"web row", if not then it's not. what I would
Dave,
Thanks much! I appreciate your help. One other thing I might have forgotten is there a way to calculate non_web_count - to - total%
and web_count - to total %
so in this way:
output would be like:
ID YEARNON WEB % WEB %NON-WEB
AC 2003-01 47 9867.6 32.4AC 2003-02 26 112AC 2003-03 57
select id, received_date,
count(count_non_web) non_web_count,
count(count_web) web_count,
(count(count_non_web) / count(*) * 100) non_web_count_percent,
(count(count_web) / count(*) * 100) web_count_percent
from
(select id, to_char(received_date, '-mm') received_date,
(case when seq_no = 4000
PROTECTED]
08/26/2003 01:29 PM
To:[EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject: Re: SQL HELP!!!Jared, Thanks for your fast reply! Sorry if I didn't explain myself clear enough. Unfortunately count_web is not a column, id||yr||seq_no||ck is a combination of
select id, received_date,
count(count_non_web) non_web_count,
count(count_web) web_count
from
(select id, to_char(received_date, '-mm') received_date,
(case when seq_no = 4000 then 1 else null end) count_non_web,
(case_when seq_no 4000 then 1 else null end) count_web
from t
where
Viktor,
By using an inline view, count_web does indeed become a column.
Did you try the query?
Jared
Viktor [EMAIL PROTECTED]
08/26/2003 01:29 PM
To:[EMAIL PROTECTED], [EMAIL PROTECTED]
cc:
Subject:Re: SQL HELP!!!
Jared,
Thanks for your fast reply
by: Subject: RE: SQL help
[EMAIL PROTECTED]
om
Title: RE: SQL help
Not tested but
select sysdate, 'test1','test2'
from dual
where exists ( your union clause)
union
your_union_clause
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion
Title: RE: SQL help
Rick,
Is this in SQL*Plus? If so, use the COLUMN command to
populate the column headings with the information you want. You can populate a
variable with SYSDATE and use that in the column heading.
Dan
-Original Message-From: Jamadagni, Rajendra
[mailto
If you don't want to apply criteria to the first part to see if data from
the second part is returned, you can avoid it by using an analytic function.
Ok, the examples below might look a little complex, but basically you have
your UNION ALL in the inner most in-line view. In the in-line view
Hello sultan
You can create a view for the cc table and use with
the bb table to display the output .
create view cc_view as select id, sum(amount) amt
from cc group by id
select a.id,a.amount ,b.amount from bb a,cc_view b
where a.id=b.id;
Hope this solution work's for u.
I will also
Select a.id,a.amount,ccinlineview.bamt from bb a,(Select
id,sum(b.amount) bamt from cc b group by id) ccinlineview where a.id = ccinlineview.id
ID
AMOUNT BAMT
-- -- --
1
1000
1000
1 row selected.
-Original Message-From: sultan
[mailto:[EMAIL
sultan wrote:
Hi gurus
I have two tables like this
SQL select * from bb;
ID AMOUNT DT
-- -- --
1 1000 10-MAY-02
SQL select * from cc;
ID AMOUNT
--
This should get you a list of the combos of ID, Company Country that are
repeated in the table:
warning--air SQL!
SELECT ID, Company, Country, COUNT(*) NumRecs
FROMmy_table
GROUP BY ID, Company, Country
HAVING COUNT(*) 1
/warning--air SQL!
Or if you need all the records that belong to
Here is your answer,
SELECT ID,COMPANY,COUNTRY
FROM table_name a
WHERE rowid (SELECT min(rowid)
FROM table_name b
WHERE b.ID = a.ID and
a.COMPANY= b.COMPANY
and a.COUNTRY=b.COUNTRY) ;
Replace Select witth DELETE if you want to delete duplicates.
Bunyamin K.
k k wrote:
Hello all,
I need some SQL help .. I have a table with containing duplicate records but
because they have differents status they really are duplicate .. i need to
find these .. here is an example of what the table contains :
IDCompany Country Status
5521 ABC
HI K,
You didn't say whether you have (or want) a unique constraint on these
fields,
but if you did (or do) you may consider the use of the exceptions table to
identify which rows violate these constraints.
An example below:
SQL create table junk (id varchar2(4), company varchar2(3),
2
select distinct segment_type, owner, segment_name, tablespace_name from
dba_segments
This will get them all in one simple query.
-Original Message-
Sent: Saturday, July 21, 2001 10:45 AM
To: Multiple recipients of list ORACLE-L
hi dba's
how to find out a users all objects+the
Try this simple script
set pagesize 66 linesize 132 verify off
select tablespace_name Tablespace ,segment_type Object_type ,segment_name
Object
from user_extents
order by tablespace_name,segment_type;
-Message d'origine-
De : kommareddy sreenivasa [mailto:[EMAIL PROTECTED]]
Envoyé :
Hi,
Try this query :- SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME IN (SELECT
SEGMENT_NAME FROM DBA_SEGMENTS);
Regards,
Anand.
-Original Message-
sreenivasa
Sent: Saturday, July 21, 2001 9:15 PM
To: Multiple recipients of list ORACLE-L
hi dba's
how to find out a users all
Hi,
Select a, decode(sign(a), -1, 0, a-1) from Table Name
For summing up : sum(decode(sign(a), -1, 0, a-1))
Hope this helps
Bye
Sundar
Ravindra Basavaraja wrote:
I have a table with a number column(Col A).I want to display A-1.There could
be some negative values in
this A-1 column.But
Srinivas:
Try this:
break on TYPE
SELECT 'TABLE' AS TYPE
, table_name AS object_name
FROM sys.dba_tables
WHERE owner = 'SCOTT'
UNION
SELECT 'INDEX'
, index_name
FROM sys.dba_indexes
WHERE owner = 'SCOTT'
UNION
SELECT 'CLUSTER'
, cluster_name
FROM
decode(sign(a), -1, 0, a)
Ron Thomas
Hypercom, Inc
[EMAIL PROTECTED]
I'm too sexy for my code. - Awk Sed Fred
ravindra@sent
select sum(column) where column 0
or try a decode
-Original Message-
Basavaraja
Sent: Friday, July 20, 2001 3:41 PM
To: Multiple recipients of list ORACLE-L
I have a table with a number column(Col A).I want to display A-1.There could
be some negative values in
this A-1 column.But I
Use the function SUBSTR( TO_CHAR ( date_column, 'DAY' ),3) = 'FRI' in your
where clause
HTH!
Aleem
-Original Message-
Sent: Wednesday, March 14, 2001 6:11 AM
To: Multiple recipients of list ORACLE-L
Subject:SQL HELP
Hello,
Now I know this must have been discussed here
50 matches
Mail list logo
