RE: Query Help
SQL SELECT * FROM emp; NAME EMP BOSS-- -- --SAMAR 10 20ASHOK 20 30ASHWINI 30 40MONIKA 11 21RASHI 21 31SMRITI 12 22SUMEET 22 32 7 rows selected. SQL SELECT * 2 FROM emp 3 WHERE emp NOT IN ( SELECT emp 4 FROM emp 5 START WITH emp = 10 CONNECT BY PRIOR emp = boss) 6 AND emp NOT IN ( SELECT emp 7 FROM emp 8 START WITH emp = 10 CONNECT BY PRIOR boss = emp); NAME EMP BOSS-- -- --MONIKA 11 21RASHI 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 NUMBER(5)BOSS NUMBER(5) The table is having the following data: NAME EMPBOSS-- --- --SAMAR 10 20ASHOK 20 30ASHWINI 30 40MONIKA 11 21RASHI 21 31SMRITI 12 22SUMEET22 32 The table is storing the employee and its boss relationship in a hierarchical format. eg. employee 10's boss is 20. employee 20's boss is 30 and 30's boss is 40. Now my question is thatI want to select all therecords from the table where the employee no. returned doesn't have any hierarchical relationship with theemployee no. passed in the where condition of the query. for eg. if select from X where .. and emp = 10; then I don't want the following records : SAMAR 10 20 ASHOK20 30 ASHWINI 30 40 But want all the following records: MONIKA11 21RASHI21 31SMRITI12 22SUMEET22 32 Awaiting help. thanks, Samar CONFIDENTIALITY/PROPRIETARY NOTE This communication is confidential / proprietary and is intended for use only by the addressee. IGT Solutions Private Limited accepts no responsibility for any mistransmission of,or interference with, this communication.
re: query help.
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)-(19*12))/(11*12)),1,0)),
sum(DECODE(0,floor((months_between(sysdate,
A.DOB)-(30*12))/(10*12)),1,0)),
sum(DECODE(0,floor((months_between(sysdate,
A.DOB)-(40*12))/(10*12)),1,0)),
sum(DECODE(0,floor((months_between(sysdate,
A.DOB)-(50*12))/(10*12)),1,0)),
sum(DECODE(0,floor((months_between(sysdate,
A.DOB)-(60*12))/(5*12)),1,0)),
sum(DECODE(0,floor((months_between(sysdate,
A.DOB)-(65*12))/(10*99)),1,0)),
sum(decode(A.DOB, NULL, 1, 0))
FROM ONLINE_VOTERS A, ADDRESS_INDEX B, COUNTIES C
WHERE A.COUNTY_CODE = B.COUNTY_CODE
AND A.ADDRESS_ID = B.ADDRESS_ID
AND A.COUNTY_CODE = C.COUNTY_CODE
AND A.STATUS IN ('V','S')
GROUP BY A.COUNTY_CODE, C.COUNTY_NAME, lpad(B.PRECINCT,4,' ')
UNION ALL
SELECT
D.COUNTY_CODE,
C2.COUNTY_NAME,
lpad(D.PRECINCT,4,' '),
count(*),
sum(DECODE(0,floor((months_between(sysdate,
D.DOB)-(18*12))/(1*12)),1,0)),
sum(DECODE(0,floor((months_between(sysdate,
D.DOB)-(19*12))/(11*12)),1,0)),
sum(DECODE(0,floor((months_between(sysdate,
D.DOB)-(30*12))/(10*12)),1,0)),
sum(DECODE(0,floor((months_between(sysdate,
D.DOB)-(40*12))/(10*12)),1,0)),
sum(DECODE(0,floor((months_between(sysdate,
D.DOB)-(50*12))/(10*12)),1,0)),
sum(DECODE(0,floor((months_between(sysdate,
D.DOB)-(60*12))/(5*12)),1,0)),
sum(DECODE(0,floor((months_between(sysdate,
D.DOB)-(65*12))/(10*99)),1,0)),
sum(decode(D.DOB, NULL, 1, 0))
FROM OFFLINE_VOTERS D, COUNTIES C2
WHERE D.COUNTY_CODE = C2.COUNTY_CODE
AND D.STATUS IN ('V','S')
GROUP BY D.COUNTY_CODE, C2.COUNTY_NAME, lpad(D.PRECINCT,4,' ');
_
Free email with personality! Over 200 domains!
http://www.MyOwnEmail.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: cw
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: QUERY HELP?
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 and goes up to max( eday of your source table), day_nbr is 0. 3. for every tuple in your source table do: update day_count set day_nbr := day_nbr +1 where day_id = sday and day_id =eday; 4. simply select Best regards JP On Wed 12. September 2001 15:20, you wrote: 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 sday and eday we should get output as day count 2 1 ( in row 1, 2 is equal to sday ) 3 1 ( it is in row 1, b/n 2 and 5 ) 4 3 ( in row 2,3 equal to sday and eday ,and b/n 2and5 in row 1 ) 5 2 like that... 8 1 9 2 101 Thanks Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: QUERY HELP?
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 ROWNUM day FROM ALL_OBJECTS WHERE ROWNUM 16) WHERE day BETWEEN sday AND eday GROUP BY day -Original Message- Sent: 12 September 2001 14:20 To: Multiple recipients of list ORACLE-L 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 sday and eday we should get output as day count 2 1 ( in row 1, 2 is equal to sday ) 3 1 ( it is in row 1, b/n 2 and 5 ) 4 3 ( in row 2,3 equal to sday and eday ,and b/n 2and5 in row 1 ) 5 2 like that... 8 1 9 2 10 1 Thanks Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: QUERY HELP
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 a cat on this one--but, I do this for some processing, and it works fine. lc -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Nirmal Kumar Muthu KumaranSent: Tuesday, June 26, 2001 9:01 AMTo: Multiple recipients of list ORACLE-LSubject: 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 8 7788 SCOTT 10 7844 TURNER In the above, can i able to put * mark in record 7 and 10, since before these two records, some records are missing. Is this possible to do this by query. I need this in reports. Basically my question is, How can i refer the previous row detail(s), when oracle fetchs the current row details?. Thanks in adv. REgards, Nirmal.
RE: QUERY HELP
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 are looking for? HTH Lisa Koivu Clumsy Ninja-ette Ft. Lauderdale, FL, USA -Original Message- From: Nirmal Kumar Muthu Kumaran [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 9:01 AM To: Multiple recipients of list ORACLE-L Subject: 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 8 7788 SCOTT 10 7844 TURNER In the above, can i able to put * mark in record 7 and 10, since before these two records, some records are missing. Is this possible to do this by query. I need this in reports. Basically my question is, How can i refer the previous row detail(s), when oracle fetchs the current row details?. Thanks in adv. REgards, Nirmal.
RE: QUERY HELP
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. In it's PL/SQL: a) Compare the value against the value in the placeholder and setup the return value. b) Set the placeholder column equal to the current value. c) Return the value derived in step a. Following is example PL/SQL for the formula column where CP_1 is the placeholder column and STEP is the value we are comparing for gaps: function CF_1Formula return Char is v_out varchar2(2); begin If :CP_1 is not null Then If :CP_1 :step-1 Then v_out := '**'; else v_out := null; End if; End if; :CP_1 := :step; return(v_out); end; There might be a more efficient way to do this in Oracle Reports, but, this is the first thing that popped to mind. SQL A method that avoids a self join. I try to minimize the number of formula's, frames, etc in Oracle Reports. So, an alternative method using pure (Oracle's) SQL, if on 8.1.6 or higher, would be to use LAG analytical function. This will allow you to avoid a self join. Following is an example with multiple columns so that you can see how the LAG/LEAD functions work. This SQL could be plugged directly into Oracle Reports, or, used as is. Note the nvl stuff I did to handle the first row since the lag value for the first row would be null (it could have been handled many other ways): SQL l 1 SELECT 2 Decode(step-1,nvl_lag_step,null,'**') Flag, 3 x.step, 4 x.ename, 5 x.lag_step, 6 x.lead_step, 7 x.nvl_lag_step, 8 x.nvl_lead_step 9 FROM ( 10select 11 step, 12 ename, 13 lag(step,1) over (order by step) lag_step, 14 lead(step,1) over (order by step) lead_step, 15 nvl(lag(step,1) over (order by step),step-1) nvl_lag_step, 16 nvl(lead(step,1) over (order by step),step+1) nvl_lead_step 17from nirmal ) x 18* order by step SQL / FL STEP ENAMELAG_STEP LEAD_STEP NVL_LAG_STEP NVL_LEAD_STEP -- -- -- -- -- - 1 SMITH 20 2 2 ALLEN 1 31 3 3 WARD2 52 5 ** 5 JONES 3 73 7 ** 7 MARTIN 5 85 8 8 BLAKE 7 97 9 9 CLARK 8 10810 10 SCOTT 9 15915 ** 15 KING 10 16 1016 16 TURNER 15 21 1521 ** 21 ADAMS 16 23 1623 ** 23 JAMES 21 25 2125 ** 25 FORD 23 28 2328 ** 28 MILLER 25 2529 Last but not least, if you are *not* talking about Oracle Reports, and, you are on a version *earlier* than 8.1.6, get back to me. There are other ways to approach this -- a self join, a function keeping track of a package variable, etc. Regards, Larry G. Elkins [EMAIL PROTECTED] -Original Message- Muthu Kumaran Sent: Tuesday, June 26, 2001 8:01 AM To: Multiple recipients of list ORACLE-L 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; RNUMENO ENAME -- 1 7369SMITH 2 7499ALLEN 3 7521WARD 4 7566JONES 7 7782CLARK 8 7788SCOTT 10 7844TURNER In the above, can i able to put * mark in record 7 and 10, since before these two records, some records are missing. Is this possible to do this by query. I need this in reports. Basically my question is, How can i refer the previous row detail(s), when oracle fetchs the current row details?. Thanks in adv. REgards, Nirmal. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of
RE: QUERY HELP
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 ..."). HTH, Remco -Oorspronkelijk bericht-Van: Nirmal Kumar Muthu Kumaran [mailto:[EMAIL PROTECTED]]Verzonden: dinsdag 26 juni 2001 15:01Aan: Multiple recipients of list ORACLE-LOnderwerp: 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 8 7788 SCOTT 10 7844 TURNER In the above, can i able to put * mark in record 7 and 10, since before these two records, some records are missing. Is this possible to do this by query. I need this in reports. Basically my question is, How can i refer the previous row detail(s), when oracle fetchs the current row details?. Thanks in adv. REgards, Nirmal.
thank you, all! -- Re: Query help !!!
--- 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: 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, I should get 1. Thank you! I need this badly! __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leslie Lu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: thank you, all! -- Re: Query help !!!
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 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 -- --- 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, I should get 1. Thank you! I need this badly! __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leslie Lu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramana INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Query help !!!
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 -- --- 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, I should get 1. Thank you! I need this badly! __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leslie Lu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Query help !!!
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 customer_id from table_name where status = 'B'); Pretty messy and resource intensive but it should work. HTH, -Rocky --- 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: 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, I should get 1. Thank you! I need this badly! __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leslie Lu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rocky Welch INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Query help !!!
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 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 -- --- 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, I should get 1. Thank you! I need this badly! __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leslie Lu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Query help !!!
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 GROUP BY customer_id ) WHERE stat_f 0 AND stat_no_f 0; should work. May not be the best way, but I THINK it'll get the job done! Good luck! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA STANDARD DISCLAIMER APPLIES! (Code is as-is, no warranties, etc) -Original Message- Sent: Friday, June 22, 2001 12:52 To: Multiple recipients of list ORACLE-L 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, I should get 1. Thank you! I need this badly! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Query help !!!
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 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 -- --- 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, I should get 1. Thank you! I need this badly! __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leslie Lu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Query help !!! - Rewrite
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 below. --- Rocky Welch [EMAIL PROTECTED] wrote: 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 customer_id from table_name where status = 'B'); Pretty messy and resource intensive but it should work. HTH, -Rocky --- 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: 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, I should get 1. Thank you! I need this badly! __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leslie Lu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rocky Welch INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rocky Welch INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Query help !!!
Is this what you are trying to do? select a.customer_id from table a, table b where a.customer_id = b.customer_id and a.status = 'F' and b.status 'F' Ron [EMAIL PROTECTED] [EMAIL PROTECTED] Karaoke: Japanese for migraine leslie_y_lu@y ahoo.com To: [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: Query help !!! com 06/22/01 10:51 AM Please respond to ORACLE-L 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, I should get 1. Thank you! I need this badly! __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leslie Lu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
