(Cognizant) [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
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
select from A)
where
and datecreated = (select min(datecreated)
from 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 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
Sent by: Subject: Re: SQL Query Help
roup 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 2 rows with one column being id
and the other
being date-time stamp.
i want to sele
IL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: SQ
5 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 with the latest timestamp among those two
> reco
cc:
[EMAIL PROTECTED] Subject: SQ
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 He
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 2:
1RASHI
21
31SMRITI
12
22SUMEET
22 32
SQL>
-Original Message-From: Samar Saxena
[mailto:[EMAIL 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--
NAME
VARCHAR2(20)EMP
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 fairly
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 b
ry possibility - that
> should save Oracle some heartache.
>
> Regards,
> Mark.
>
>
> Stephane
> Faroult To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
>
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
> algorithm was worse though .
>
> Raj
>
> -Original Message-
> 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...
>
d to trying every possibility - that
> should save Oracle some heartache.
>
> Regards,
> Mark.
>
>
> Stephane
> Faroult To: Multiple recipients of list
>ORACLE-L <[EMAIL PROTECTED]>
>
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
Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
Subject: Re: slow query help
Sent by:
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
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 na
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,
A.DOB)-
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
: Select Query - Help required
|
|
|Gurus,
|
|Please read the following problem and help me if you have any
|solution.
|
|Select product_id from where id = 2;
|
|Product_ID
|--
|A
|B
|C
|D
|
|But I want the output as follows:
|
|Select product_id from where id = 2;
|
|Product ID
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 v_devic
Gurus,
Please read the following problem and help me if you have any solution.
Select product_id from where id = 2;
Product_ID
--
A
B
C
D
But I want the output as follows:
Select product_id from where id = 2;
Product ID
-
ABCD.
Thanks in advance.
regards,
Kar
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 so
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:
>H
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:
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
SERVICE_LOCATION
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 ROWNU
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 1
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
should be something like .
select custid
from sample o
where status='F'
group by custid , status
having count(*) >=1
and count(*) != (
select count(*)
from sample i
where
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 ...").
H
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 <<>> part.
1) Create a placeholder column outside query (or use a package variable,
whatever floats your boat).
2) Create a formula column within the grou
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
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 on
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
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
--- 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 PROTECTE
To: [EMAIL PROTECTED]
Sent by: cc:
root@fatcity.Subject: Query help !!!
How about:
select f.customer_id
from f, a, .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 below.
--- Rocky Welch <[EMAI
Leslie,
The query below will find all those with any number of 'F's and at least one
status that is not 'F'.
select distinct customer_id
from mytablename t
where status = 'F'
and exists (select ''
from mytablename t2
where t2.customer_id = t.customer_id
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
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
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
Hi Leslie,
This will be crude but it's a start. Gang, feel free to correct/improve:
select customer_id
from
where
customer_id in (select customer_id from where status = 'F')
and
customer_id in (select customer_id from where status = 'A')
and
customer_id in (select customer_id from where stat
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 case
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
>
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 tabl
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
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
55 matches
Mail list logo