) [mailto:[EMAIL PROTECTED]Sent: Tuesday,
August 19, 2003 12:49 AMTo: Multiple recipients of list
ORACLE-LSubject: Performance Query - help
Hi
all,
I have a query which takes 4 seconds to execute. The query looks
like:
SELECT DISTINCT tfc_fct_value
FROM
Hi
all,
I have a query which takes 4 seconds to execute. The query looks
like:
SELECT DISTINCT tfc_fct_value
FROM
PD_TMP_AGG_VALS_5071_544,PD_OUTPUT_ITEMS,tp_fact_ctl
WHERE OUI_OUT_ID=5071
AND OUI_FACT_ID IS NOT NULL
AND
oui_item_type=tfc_item_type
AND vd17=oui_fact_id
ORDER BY
I have a table with records like this
CID S TO_CHAR(DATECREATED, MESSAGE PID
- - --
2 N 01-feb-1974 19:45:45 service change1*
3 N 01-feb-1974 19:45:45 service change
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
i have a query that returns 2 rows with one column being id and the other
being date-time stamp.
i want to select the row with the latest timestamp among those two
records.they have difference id values
SELECT ID,LastModDate
FROM Tab
ID LastModDate
-- ---
2 1/20/2003
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
:
[EMAIL PROTECTED] Subject: SQL Query Help
recipients of list ORACLE-L
Subject: SQL Query Help
i have a query that returns 2 rows with one column being id
and the other
being date-time stamp.
i want to select the row with the latest timestamp among those two
records.they have difference id values
SELECT ID,LastModDate
FROM Tab
]
Sent by: cc:
[EMAIL PROTECTED] Subject: SQL Query Help
;
-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 2 rows with one column being id
and the other
being date-time stamp.
i want to select the row
: Re: SQL Query Help
[EMAIL PROTECTED
Hi people,
I have this table X, whose
structure is described below:
Name
Null? Type--
NAME
VARCHAR2(20)EMP
NUMBER(5)BOSS
NUMBER(5)
The table is having the following
data:
NAME
PROTECTED]]Sent: Thursday, December 26,
2002 4:09 PMTo: Multiple recipients of list
ORACLE-LSubject: Query Help
Hi people,
I have this table X,
whose structure is described below:
Name
Null? Type
Joan,
Can't this be done as a series of ors instead of the union alls as this
would presumably reduce it to one full table scan of each table.
e.g.
SELECT T11.TRUNK TRUNK_FOUND
,T21.IDTARGET_ID
,T21.SSN TARGET_SSN
,T21.FULLNAME
ORACLE-L [EMAIL PROTECTED]
sfaroult@orio cc:
le.com Subject: Re: slow query help
Sent by:
[EMAIL PROTECTED]
om
18/12/2002
07
Title: RE: slow query help
Joan,
Here is a suggestion ...
if this is going to be your most used part, I'd look into Intermedia ... you'll have a lot more options to work with and they will work good.
Until then, I'd recommend replacing instr() with appropriate LIKE clause because at-least
Raj,
I thought it would only do the union all if it was able to use an index and
all the instr's look as though they'd stop that. Even then I thought it was
generally just rule that would do that unless you used the use_concat hint.
I can't see why a full table scan of each wouldn't be
Hi,
This is the query bothered us very much recently. It run at least 15
min. and sometimes crashed the temp tablespace. Do you have any idea how
to make it run better. Our developer tried used two cursors to compare
the result, but the result is not optimized. We tried used last name and
first
Joan Hsieh wrote:
Hi,
This is the query bothered us very much recently. It run at least 15
min. and sometimes crashed the temp tablespace. Do you have any idea how
to make it run better. Our developer tried used two cursors to compare
the result, but the result is not optimized. We tried
]
sfaroult@orio cc:
le.com Subject: Re: slow query help
Sent
Title: RE: slow query help
It need not be generatd by a tool, I have worked for a blood bank in one of my previous projects. When it comes to matching a donor in th records, you have to take a lot of precautions to see if you have a duplicate donor etc.
To me this seems to be logic to find
recipients of list
ORACLE-L [EMAIL PROTECTED]
sfaroult@orio cc:
le.com Subject: Re: slow query help
Sent by:
[EMAIL PROTECTED]
om
18/12/2002
-
From: Mark Richard [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 17, 2002 4:51 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: slow query help
I tend to agree with Stephane...
It looks like that query was possibly generated by a tool. If not you
should go talk
Hey list Guru, Can anyone help me with this query?Many thanks,
SELECT
A.COUNTY_CODE,
C.COUNTY_NAME,
lpad(B.PRECINCT,4,' '),
count(*),
sum(DECODE(0,floor((months_between(sysdate,
A.DOB)-(18*12))/(1*12)),1,0)),
sum(DECODE(0,floor((months_between(sysdate,
Title: RE: Select Query - Help required
I posted an answer on a similar question about 2 weeks ago. The underlying concept is how to pivot a result set. I've attached the thread below.
HTH
Tony Aponte
Home Shopping Network
-Original Message-
From: Aponte, Tony
Sent: Tuesday
: Select Query - Help required
|
|
|Gurus,
|
|Please read the following problem and help me if you have any
|solution.
|
|Select product_id from tname where id = 2;
|
|Product_ID
|--
|A
|B
|C
|D
|
|But I want the output as follows:
|
|Select product_id from tname where id = 2
Gurus,
Please read the following problem and help me if you have any solution.
Select product_id from tname where id = 2;
Product_ID
--
A
B
C
D
But I want the output as follows:
Select product_id from tname where id = 2;
Product ID
-
ABCD.
Thanks in advance.
A similar question was asked a while ago and I kept the answer as I thought
it might be useful
The following PL/SQL snippet should demonstrate a way to do what you want
set serveroutput on 100
Declare
sn varchar2(2000);
cursor c_devices is
select name from ashoke;
Begin
for
Hi all,
How to do this query, I have three tables:
SERVICE_LOCATION a, SERVICE_LOC_AREA b, FRANCHISE_AREA
c.
The relationship between them is:
a.SERVICE_LOCATION_ID = b.SERVICE_LOCATION_ID and
b.FRANCHISE_ID = c.FRANCHISE_ID
I need to update CENTRAL_OFFICE_CODE column in table
Title: RE: update query??? HELP!!!
-Original Message-
From: Janet Linsy [mailto:[EMAIL PROTECTED]]
How to do this query, I have three tables:
SERVICE_LOCATION a, SERVICE_LOC_AREA b, FRANCHISE_AREA
c.
The relationship between them is:
a.SERVICE_LOCATION_ID
update SERVICE_LOCATION a set CENTRAL_OFFICE_CODE =
(select FRANCHISE_NAME
from FRANCHISE_AREA c, SERVICE_LOC b where
a.SERVICE_LOCATION_ID = b.SERVICE_LOCATION_ID and
b.FRANCHISE_ID = c.FRANCHISE_ID)
At 02:55 PM 11/14/01 -0800, you wrote:
Title: RE: update query??? HELP!!!
-Original Message-
From: Janet Linsy [mailto:[EMAIL PROTECTED]]
I got
(select c.franchise_name
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one
row
How can I solve this? Thank you again!
SQL update
Hi
I need help to get query
sno is primary key of table
sday and eday will be between (1 and 15)
rowno, sdayeday
1 2 5
2 4 4
3 4 5
4 8 9
5 9 10
the day output will be the no which can be equal to sday
or equal to eday or between
If I understand it right you need a count for every day in interval records.
I think, that the easiest way how to get right numbers (it's not much
sophisticated, but ...) is:
1. create table day_count( day_id number, day_nbr number );
2. fill table day_count with tuples where day_id starts at
Seema,
The following would work (there will be better ways to do it especially if
you're on Oracle 8) but I'm stuck with 7.3. You'll need to have access to a
table which will always have at least have 15 rows (I've used all_objects
here).
SELECT day, COUNT(*)
FROM table_name,
(SELECT
Title: QUERY HELP
Dear Guru's,
How can i refer the previous record detail(s), when oracle fetchs the current row details?.
sql SELECT rownum rnum, empno eno, ename FROM EMP;
RNUM ENO ENAME
--
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
7 7782 CLARK
Title: QUERY HELP
One
way to do this is in the procedure, use variables that hold the previous values
(e.g. last_rnum := rnum). Then, do your comparison of your current value to your
last stored value (e.g. if rnum - last_rnum 1 then flag='*'). I am sure
there are more than one way to skin
Title: RE: QUERY HELP
Have you tried this:
select tab2.col1, tab2.col2, x.col1, x.col2
from
(select column1 col1,
column2 col2
from tab2
where ( your independent conditions here, can't refer to outer query here)
) x,
tab2
where x.col1 = tab2.col1 [etc...]
Is that what you
Nirmal,
You said I need this in reports. If you mean Oracle Reports, there are a
few ways to do it. If not Oracle Reports, skip down to the SQL part.
1) Create a placeholder column outside query (or use a package variable,
whatever floats your boat).
2) Create a formula column within the group.
Title: QUERY HELP
Do you
want a query to return the missing numbers, or do you want a query to return the
records AFTER some numbers have been skipped ?
The
first can be done in pl/sql (loop with counter compared to rownum), the latter
in sql (use "where not exists ...").
--- Leslie Lu [EMAIL PROTECTED] wrote:
Just to clearfy my previous question (as follow):
if 1 has F and A and B, that what I want.
If 1 has F all the time, that's not what I want.
If 1 has A, B, C, but never F, that's not what I
want
either.
--- Leslie Lu [EMAIL PROTECTED] wrote:
try this..
SELECT DISTINCT AA FROM AA A WHERE STATUS='F' AND
AA IN (SELECT AA FROM AA B WHERE A.AA=B.AA AND STATUS 'F'
GROUP BY B.AA HAVING COUNT(B.AA) 1)
Ramana
--- Leslie Lu [EMAIL PROTECTED] wrote:
Just to clearfy my previous question (as follow):
if 1 has F and A and B, that what
Just to clearfy my previous question (as follow):
if 1 has F and A and B, that what I want.
If 1 has F all the time, that's not what I want.
If 1 has A, B, C, but never F, that's not what I want
either.
--- Leslie Lu [EMAIL PROTECTED] wrote:
Hi,
If I have this:
Customer_id Status
Hi,
If I have this:
Customer_id Status
-- ---
1 F
1 A
1 B
2 F
2 F
3 A
3 B
How do I found out a customer who has both F and not F
for them. (If he only gets F, or gets other than F,
that's fine). In this
Hi Leslie,
This will be crude but it's a start. Gang, feel free to correct/improve:
select customer_id
from table_name
where
customer_id in (select customer_id from table_name where status = 'F')
and
customer_id in (select customer_id from table_name where status = 'A')
and
customer_id in
SELECT *
FROM customer c1
WHERE status = 'F'
AND EXISTS (SELECT 1
FROM customer c2
WHERE c2.customer_id = c1.customer_id
AND c2.status != 'F');
-Original Message-
Sent: Friday, June 22, 2001 2:06 PM
To: Multiple recipients of list ORACLE-L
Just to clearfy
Is 'F' the largest value? If so, then:
SELECT
customer_id
FROM
(
SELECT customer_id
, SUM(DECODE(status,'F',1,0)) stat_f
, SUM(DECODE(status,'F',0,1)) stat_no_f
FROM my_table
Here is one way:
select distinct customer_id c1 where exists
(select 'X' from customer_id where customer_id = c1.customer_id and status
= 'F')
and exists
(select 'X' from customer_id where customer_id = c1.customer_id and status
'F')
At 10:05 AM 6/22/01 -0800, you wrote:
Just to clearfy my
How about:
select f.customer_id
from table_name f, table_name a, table_name.b
where f.customer_id = a.customer_id and
f.customer_id = b.customer_id and
a.customer_id = b.customer_id and
f.status = 'F' and
a.status = 'A' and
b.status = 'B';
Much cleaner than the one
]
Sent by: cc:
root@fatcity.Subject: Query help !!!
com
Hi Gurus
I have following data in table emp
empcode empname SAL
a001 X 2000
b001 Y 4000
c001 A 5000
d001 C 8000
If sal is =5000 there will be another columns grade and print A else B
The outout would be like
empcode empname SALGrade
a001
Title: RE: SQL QUERY HELP
-Original Message-
From: Seema Singh [mailto:[EMAIL PROTECTED]]
I have following data in table emp
empcode empname SAL
a001 X 2000
b001 Y 4000
c001 A 5000
d001 C 8000
If sal is =5000 there will be another columns grade and
print
Try this.
select empcode, empname, sal,
decode(to_char(trunc((nvl(sal,0)+1)/5001)),'0','A','B') NewCol
from emp
/
- Original Message -
To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]
Sent: Thursday, April 05, 2001 1:40 PM
Hi Gurus
I have following data in table
53 matches
Mail list logo