Re: SQL help needed
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: ³Do you want to repair this block (y/n)? ³ Tim, You made me discover a great command. yes rtfm has opened new vistas to me. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SQL help needed
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. Case in point: fsck. As in: Do you want to repair this block (y/n)? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SQL help needed
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 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 block (y/n)? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Binley Lim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SQL help needed
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 block (y/n)? ³
Re: Re: SQL help needed
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 could just do things like select rownum from Btw, Oracle's current DUAL table isn't very efficient, if you have very heavy tight loops selecting from dual, you might want to experiment with local dual table with only one row in it (put an unique index on it), or use IOT with one row, or create a view on x$dual and select from there (the view is requires for non sys-users being able to select from x$ table). The x$ part get's kind of hacking, but seems to be the fastest. Also, if you use a custom dual table, you should analyze it as well if using CBO, for sys.dual table CBO knows that there's only one row, but for custom one you have to have statistics. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SQL help needed
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 -- --- 100 1 200 4 300 8 400 9 50014 How would I find out the missing linenbrs (2,3,5,6,7) if I was given minlinenbr as 1 and maxlinenbr as 10 that would be passed as variables (using ). Thanks. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: GovindanK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Re: SQL help needed
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, 16 Sep 2003 20:04:37 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 4 300 8 400 9 500 14 5 rows selected. pollux.stats.scott select linenbr from (select rownum+min as linenbr from dba_objects where rownum (max-min) 2 minus select linenbr from kirti where linenbr min and linenbr max) 3 / Enter value for min: 1 Enter value for max: 10 LINENBR -- 2 3 5 6 7 5 rows selected. pollux.stats.scott undef min pollux.stats.scott undef max pollux.stats.scott select linenbr from (select rownum+min as linenbr from dba_objects where rownum (max-min) 2 minus select linenbr from kirti where linenbr min and linenbr max) 3 / Enter value for min: 4 Enter value for max: 14 LINENBR -- 5 6 7 10 11 12 13 7 rows selected. pollux.stats.scott undef min pollux.stats.scott undef max pollux.stats.scott select linenbr from (select rownum+min as linenbr from dba_objects where rownum (max-min) 2 minus select linenbr from kirti where linenbr min and linenbr max) 3 / Enter value for min: 4 Enter value for max: 10 LINENBR -- 5 6 7 3 rows selected. At 07:29 PM 9/16/2003 -0800, you wrote: 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 -- --- 100 1 200 4 300 8 400 9 50014 How would I find out the missing linenbrs (2,3,5,6,7) if I was given minlinenbr as 1 and maxlinenbr as 10 that would be passed as variables (using ). Thanks. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Re: SQL help needed
- --- 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 consider some other table) 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 'extended dual' (XDUAL ?). Perhaps this (to be run as SYS) should be added as a standard part of the catalogue : create view xdual as select rownum from sys.col$; grant select on xdual to public; create public synonym xdual for xdual; sys.col$ always contains a 'respectable' number of rows (10,000+, even without SAP :-)). Although indeed a standard sys.source$ is twice bigger just after install .. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
CORRECTION RE: RE: Re: SQL help needed
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 PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: CORRECTION RE: RE: Re: SQL help needed
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 see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Re: SQL help needed
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 'extended dual' (XDUAL ?). Perhaps this (to be run as SYS) should be added as a standard part of the catalogue : create view xdual as select rownum from sys.col$; grant select on xdual to public; create public synonym xdual for xdual; sys.col$ always contains a 'respectable' number of rows (10,000+, even without SAP :-)). Although indeed a standard sys.source$ is twice bigger just after install .. If you definitely want to use a table for that, create a separate table with only one column - and insert it full of NULLs. That way you'll be fitting the most rows in one block, thus saving in performance (also put pctfree to 0). Another way could be to use a pipelined table function with indefinite loop inside it (but I've heard pipelined functions are currently fairly slow). SQL create or replace type num_typ as table of number; 2 / Type created. SQL SQL create or replace function f return num_typ pipelined is 2i number := 1; 3 begin 4loop 5 pipe row(i); 6 i:=i+1; 7end loop; 8return; 9 end; 10 / Function created. SQL SQL select * from table(f) where rownum =10; COLUMN_VALUE 1 2 3 4 5 6 7 8 9 10 10 rows selected. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SQL help needed
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 --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: 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 -- --- 100 1 200 4 300 8 400 9 50014 How would I find out the missing linenbrs (2,3,5,6,7) if I was given minlinenbr as 1 and maxlinenbr as 10 that would be passed as variables (using ). Thanks. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SQL help needed
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 for Oracle 9i where you don't have to worry that the table you are using to generate sequential numbers does not have enough rows for your query. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Kirtikumar Deshpande 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 -- --- 100 1 200 4 300 8 400 9 50014 How would I find out the missing linenbrs (2,3,5,6,7) if I was given minlinenbr as 1 and maxlinenbr as 10 that would be passed as variables (using ). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SQL help needed
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 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 --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: 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 -- --- 100 1 200 4 300 8 400 9 50014 How would I find out the missing linenbrs (2,3,5,6,7) if I was given minlinenbr as 1 and maxlinenbr as 10 that would be passed as variables (using ). Thanks. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Re: SQL help needed
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 could just do things like select rownum from infinite_dual where rownum = 100 without concern or consideration. As you said - you wouldn't use it every day, but I'm sure it would be handy to have. It would also provide new ways to crash Oracle I guess, with queries like select rownum the_row from infinite_dual order by the_row. Hmm, perhaps this idea needs some additional thought? I'm sure it would be practical to create something which works efficiently and safely though. Regards, Mark. Stephane Faroult To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: orp.com Subject: RE: Re: SQL help needed Sent by: [EMAIL PROTECTED] .com 17/09/2003 18:39 Please respond to ORACLE-L - --- 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 consider some other table) 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 'extended dual' (XDUAL ?). Perhaps this (to be run as SYS) should be added as a standard part of the catalogue : create view xdual as select rownum from sys.col$; grant select on xdual to public; create public synonym xdual for xdual; sys.col$ always contains a 'respectable' number of rows (10,000+, even without SAP :-)). Although indeed a standard sys.source$ is twice bigger just after install .. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately
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 out by one on some of the maths. Regards, Mark. Kirtikumar Deshpande kirtikumar_deshpandeTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @yahoo.com cc: Sent by: Subject: SQL help needed [EMAIL PROTECTED] 17/09/2003 13:29 Please respond to ORACLE-L 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 -- --- 100 1 200 4 300 8 400 9 50014 How would I find out the missing linenbrs (2,3,5,6,7) if I was given minlinenbr as 1 and maxlinenbr as 10 that would be passed as variables (using ). Thanks. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them. -- Please see the official ORACLE-L FAQ:
Re: SQL help needed
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 which can be defined with certain set of fixed conditions. I don't think that your condition falls into that category. If there is a purist SQL solution, which I doubt, then your only chance lies in the analytical functions. Off hand, I don't know anything specific and I'm too tired to start digging. Good luck. If anybody can find a solution, you can. On 2003.09.16 23:29, Kirtikumar Deshpande wrote: 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 -- --- 100 1 200 4 300 8 400 9 50014 How would I find out the missing linenbrs (2,3,5,6,7) if I was given minlinenbr as 1 and maxlinenbr as 10 that would be passed as variables (using ). Thanks. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SQL help needed
I guess I should have added some criteria like: 1) user_objects must have enough rows in it to cover the range (if not consider some other table) 2) this will work fine up to a few thousand records (depending on the size of your sever, etc) - it would work, but isn't ideal, when you move into the millions of records range. 3) I guess a where not exists (select 1 from table where linenbr = therow) type subquery could be written to reduce the sort size, but I doubt performance would be much better. Regards, Mark. Mark Richard [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ban.com.au cc: Sent by: Subject: Re: SQL help needed [EMAIL PROTECTED] .com 17/09/2003 13:39 Please respond to ORACLE-L 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 out by one on some of the maths. Regards, Mark. Kirtikumar Deshpande kirtikumar_deshpandeTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @yahoo.com cc: Sent by: Subject: SQL help needed [EMAIL PROTECTED] 17/09/2003 13:29 Please respond to ORACLE-L 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 -- --- 100 1 200 4 300 8 400 9 50014 How would I find out the missing linenbrs (2,3,5,6,7) if I was given minlinenbr as 1 and maxlinenbr as 10 that would be passed as variables (using ). Thanks. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail
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 4 300 8 400 9 500 14 5 rows selected. pollux.stats.scott select linenbr from (select rownum+min as linenbr from dba_objects where rownum (max-min) 2 minus select linenbr from kirti where linenbr min and linenbr max) 3 / Enter value for min: 1 Enter value for max: 10 LINENBR -- 2 3 5 6 7 5 rows selected. pollux.stats.scott undef min pollux.stats.scott undef max pollux.stats.scott select linenbr from (select rownum+min as linenbr from dba_objects where rownum (max-min) 2 minus select linenbr from kirti where linenbr min and linenbr max) 3 / Enter value for min: 4 Enter value for max: 14 LINENBR -- 5 6 7 10 11 12 13 7 rows selected. pollux.stats.scott undef min pollux.stats.scott undef max pollux.stats.scott select linenbr from (select rownum+min as linenbr from dba_objects where rownum (max-min) 2 minus select linenbr from kirti where linenbr min and linenbr max) 3 / Enter value for min: 4 Enter value for max: 10 LINENBR -- 5 6 7 3 rows selected. At 07:29 PM 9/16/2003 -0800, you wrote: 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 -- --- 100 1 200 4 300 8 400 9 50014 How would I find out the missing linenbrs (2,3,5,6,7) if I was given minlinenbr as 1 and maxlinenbr as 10 that would be passed as variables (using ). Thanks. - Kirti Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SQL HELP.....
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 Yoyong Cabansay Email: [EMAIL PROTECTED] From: Naveen Nahata [mailto:[EMAIL PROTECTED] Sent: Thursday, August 28, 2003 6:59 PM To: Multiple recipients of list ORACLE-L 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? Regards Naveen -Original Message- From: Cabansay, Yoyong [mailto:[EMAIL PROTECTED] Sent: Thursday, August 28, 2003 3:45 PM To: Multiple recipients of list ORACLE-L Subject: SQL HELP. I have a table with the following columns: - item - carton_number -(min) - carton_number -(max) - qty -(sum) - weight -(sum) Item A has qty of 4515 and present in cartons 1 to 1129 Item B has qty of 7255 and present in cartons 1 to 1129 Item C has qty of 400 and present in cartons 1 to 600 (???) I want to find which cartons from 1 to 600 does not contain Item C. Can you give me a sql/plsql script to do this. Thanks a lot, Yoyong Cabansay Email: [EMAIL PROTECTED] DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission.
RE: SQL HELP.....
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] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thu, 28 Aug 2003 02:15:02 I have a table with the following columns: - item - carton_number---(min) - carton_number---(max) - qty---(sum) - weight ---(sum) =20 Item A has qty of 4515 and present in cartons 1 to 1129 Item B has qty of 7255 and present in cartons 1 to 1129 Item C has qty of 400 and present in cartons 1 to 600 (???) =20 I want to find which cartons from 1 to 600 does not contain Item C. =20 Can you give me a sql/plsql script to do this.=20 =20 Thanks a lot, =20 Yoyong Cabansay Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: SQL HELP.....
"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: Cabansay, Yoyong [mailto:[EMAIL PROTECTED]Sent: Thursday, August 28, 2003 3:45 PMTo: Multiple recipients of list ORACLE-LSubject: SQL HELP. I have a table with the following columns: - item - carton_number -à(min) - carton_number -à(max) - qty -à(sum) - weight -à(sum) Item A has qty of 4515 and present in cartons 1 to 1129 Item B has qty of 7255 and present in cartons 1 to 1129 Item C has qty of 400 and present in cartons 1 to 600 (???) I want to find which cartons from 1 to 600 does not contain Item C. Can you give me a sql/plsql script to do this. Thanks a lot, Yoyong Cabansay Email: [EMAIL PROTECTED]DISCLAIMER:This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission.
RE: SQL HELP!!!
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 your_start_date and
your_end_date)
group by id, col_1, col_2
/
Raj
Rajendra dot Jamadagni at nospamespn dot
com All Views expressed in this email
are strictly personal. QOTD: Any clod
can have facts, having an opinion is an art !
-Original Message-From: Viktor
[mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 5:00
PMTo: Multiple recipients of list ORACLE-LSubject: SQL
HELP!!!
Hello,
I was wondering if someone can help me with a report. I am stuck figuring
out what I can do to complete it. Here is what I need to do:
for a given date range, i..e. start_date - end_date ('01/01/2003',
'12/31/2003'), I need to count all "web", and "non-web" records.
Ifseq_no 4000, then it's web, otherwise non-web. seq_no is not
unique. So it's like 2 different where claused in a single select. Could I
somehow use CASE or decode to accomplish this. Here is what I am trying to do
in select:
select id ,
to_char(received_date,
'-mm'),
id||yr||seq_no||ck
MSNO CASE WHEN
seq_no 4000 then count(seq_no) ELSE 0 end
Count_WEB,CASE
WHEN seq_no =4000 then count(seq_no) ELSE0
ENDHARDCOPY
from twhere received_date between '01/01/2003' and
'12/31/2003'group by id, to_char(received_date,
'-mm'),
sequence_no, id||yr||seq_no||ck
/
AR 2003-01 AR030023T
0
1AR 2003-01 AR0200302
0
1AR 2003-01 AR020047K
0
1AR 2003-01 AR020077N
0
1
I would like to show Year-Month once and count all instances
ofid||yr||seq_no||ck (primary_key)for that Year-Month, but
not tobreak on it, and unfortunaley it won't let me do it without
grouping on seq_no
Please advise!!!
Any help is greatly appreciated!
Do you Yahoo!?Yahoo!
SiteBuilder - Free, easy-to-use web site design
software
This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*2
RE: SQL HELP - SOLVED!!!
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 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 your_start_date and your_end_date)
group by id, col_1, col_2
/
Raj
Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-From: Viktor [mailto:[EMAIL PROTECTED]Sent: Tuesday, August 26, 2003 5:00 PMTo: Multiple recipients of list ORACLE-LSubject: SQL HELP!!!
Hello,
I was wondering if someone can help me with a report. I am stuck figuring out what I can do to complete it. Here is what I need to do:
for a given date range, i..e. start_date - end_date ('01/01/2003', '12/31/2003'), I need to count all "web", and "non-web" records. Ifseq_no 4000, then it's web, otherwise non-web. seq_no is not unique. So it's like 2 different where claused in a single select. Could I somehow use CASE or decode to accomplish this. Here is what I am trying to do in select:
select id , to_char(received_date, '-mm'), id||yr||seq_no||ck MSNO CASE WHEN seq_no 4000 then count(seq_no) ELSE 0 end Count_WEB,CASE WHEN seq_no =4000 then count(seq_no) ELSE0 ENDHARDCOPY
from twhere received_date between '01/01/2003' and '12/31/2003'group by id, to_char(received_date, '-mm'),
sequence_no, id||yr||seq_no||ck
/
AR 2003-01 AR030023T 0 1AR 2003-01 AR0200302 0 1AR 2003-01 AR020047K 0 1AR 2003-01 AR020077N 0 1
I would like to show Year-Month once and count all instances ofid||yr||seq_no||ck (primary_key)for that Year-Month, but not tobreak on it, and unfortunaley it won't let me do it without grouping on seq_no
Please advise!!!
Any help is greatly appreciated!
Do you Yahoo!?Yahoo! SiteBuilder - Free, easy-to-use web site design softwareThis e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
Re: SQL HELP!!!
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 count(seq_no) ELSE 0 end Count_WEB,
CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END HARDCOPY
from t
where received_date between '01/01/2003' and '12/31/2003'
group by id,
to_char(received_date, '-mm'),
sequence_no,
id||yr||seq_no||ck
)
group by received_date, msno, hardcopy
/
Viktor [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
08/26/2003 01:59 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:SQL HELP!!!
Hello,
I was wondering if someone can help me with a report. I am stuck figuring out what I can do to complete it. Here is what I need to do:
for a given date range, i..e. start_date - end_date ('01/01/2003', '12/31/2003'), I need to count all web, and non-web records. If seq_no 4000, then it's web, otherwise non-web. seq_no is not unique. So it's like 2 different where claused in a single select. Could I somehow use CASE or decode to accomplish this. Here is what I am trying to do in select:
select id ,
to_char(received_date, '-mm'),
id||yr||seq_no||ck MSNO
CASE WHEN seq_no 4000 then count(seq_no) ELSE 0 end Count_WEB,
CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END HARDCOPY
from t
where received_date between '01/01/2003' and '12/31/2003'
group by id,
to_char(received_date, '-mm'),
sequence_no,
id||yr||seq_no||ck
/
AR 2003-01 AR030023T 0 1
AR 2003-01 AR0200302 0 1
AR 2003-01 AR020047K 0 1
AR 2003-01 AR020077N 0 1
I would like to show Year-Month once and count all instances of id||yr||seq_no||ck (primary_key) for that Year-Month, but not to break on it, and unfortunaley it won't let me do it without grouping on seq_no
Please advise!!!
Any help is greatly appreciated!
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
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 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 like to see is:
ID Received_date Non-web count Web_count
AR 2003-01 0 4AR 2003-02 0 6AR 2003-0 1 8
and so forth.
Again thanks for any suggestions you may have!
Thanks very much!
[EMAIL PROTECTED] wrote:
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 MSNOCASE WHEN seq_no 4000 then count(seq_no) ELSE 0 end Count_WEB,CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END HARDCOPY from twhere received_date between '01/01/2003' and '12/31/2003'group by id,!
p;
to_char(received_date, '-mm'), sequence_no, id||yr||seq_no||ck ) group by received_date, msno, hardcopy /
Viktor [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED]
08/26/2003 01:59 PM Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:SQL HELP!!!Hello, I was wondering if someone can help me with a report. I am stuck figuring out what I can do to complete it. Here is what I need to do: for a given date range, i..e. start_date - end_date ('01/01/2003', '12/31/2003'), I need to count all "web", and "non-web" reco!
rds. If
seq_no 4000, then it's web, otherwise non-web. seq_no is not unique. So it's like 2 different where claused in a single select. Could I somehow use CASE or decode to accomplish this. Here is what I am trying to do in select: select id ,to_char(received_date, '-mm'),id||yr||seq_no||ck MSNOCASE WHEN seq_no 4000 then count(seq_no) ELSE 0 end Count_WEB,CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END HARDCOPY from twhere received_date between '01/01/2003' and '12/31/2003'group by id, to_char(received_date, '-mm'), sequence_no, id||yr||seq_no||ck !
/ AR 2003-01 AR030023T 0 1AR 2003-01 AR0200302 0 1AR 2003-01 AR020047K 0 1AR 2003-01 AR020077N 0 1 I would like to show Year-Month once and count all instances of id||yr||seq_no||ck (primary_key) for that Year-Month, but not to break on it, and unfortunaley it won't let me do it without grouping on seq_no Please advise!!! Any help is greatly appreciated!
Do you Yahoo!?Yahoo! SiteBuilder - Free, easy-to-use web site design software
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
Re: SQL HELP!!!
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 121AC 2003-04 40 124
And so forth...
Thanks Dave and evryone else for your help!Dave Hau [EMAIL PROTECTED] wrote:
select id, received_date,count(count_non_web) non_web_count,count(count_web) web_countfrom(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_webfrom twhere received_date between '01/01/2003' and '12/31/2003')group by id,received_dateHTH,Dave[EMAIL PROTECTED] wrote: 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 a id||yr||seq_no||ck row, it's a "web row", if not then it's not. what I would like to see is: ID Received_date No!
n-web
count Web_count AR 2003-01 0 4 AR 2003-02 0 6 AR 2003-0 1 8 and so forth. Again thanks for any suggestions you may have! Thanks very much![EMAIL PROTECTED] wrote: 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 count(seq_no) ELSE 0 end Count_WEB, CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END HARDCOPY from t where received_date between '01/01/2003' and '12/31/2003' group by id, nbs! p; to_char(received_date, '-mm'),
sequence_no, id||yr||seq_no||ck ) group by received_date, msno, hardcopy / Viktor <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 08/26/2003 01:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: SQL HELP!!! Hello, I was wondering if someone can help me with a report. I am stuck figuring out what I can do to complete it. Here is what I need to do: for a given date range, i..e. start_date - end_date ('01/01/2003', '12/31/2003'), I need to count all "web", and "non-web" reco! rds. If seq_no 4000, then it's web, otherwise non-web. seq_no is not unique. So it's like 2 different where claused in a single select. Could I somehow use CASE or !
decode to
accomplish this. Here is what I am trying to do in select: select id , to_char(received_date, '-mm'), id||yr||seq_no||ck MSNO CASE WHEN seq_no 4000 then count(seq_no) ELSE 0 end Count_WEB, CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END HARDCOPY from t where received_date between '01/01/2003' and '12/31/2003' group by id, to_char(received_date, '-mm'), sequence_no, id||yr||seq_no||ck ! / AR 2003-01 AR030023T 0 1 AR 2003-01 AR0200302 0 1 AR 2003-01 AR020047K 0 1 AR 2003-01 AR020077N 0 1 I would like to show Year-Month once and count all instances of id||yr||seq_no||ck (primary_key) for that Year-Month, but not to break on it, and unfortunaley it won't let me do it without grouping on seq_no Please advise!!! Any help is g!
reatly
appreciated! Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Dave HauINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
Re: SQL HELP!!!
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 then 1 else null end) count_non_web,
(case_when seq_no 4000 then 1 else null end) count_web
from t
where received_date between '01/01/2003' and '12/31/2003')
group by id,
received_date
HTH,
Dave
[EMAIL PROTECTED] wrote:
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 YEAR NON WEB % WEB %NON-WEB
AC 2003-014798 67.632.4
AC 2003-0226 112
AC 2003-0357 121
AC 2003-0440 124
And so forth...
Thanks Dave and evryone else for your help!
Dave Hau [EMAIL PROTECTED] wrote:
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 received_date between '01/01/2003' and '12/31/2003')
group by id,
received_date
HTH,
Dave
[EMAIL PROTECTED] wrote:
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
a id||yr||seq_no||ck row, it's a web row, if not then it's not.
what I
would like to see is:
ID Received_date No! n-web count Web_count
AR 2003-01 0 4
AR 2003-02 0 6
AR 2003-0 1 8
and so forth.
Again thanks for any suggestions you may have!
Thanks very much!
[EMAIL PROTECTED] wrote:
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 count(seq_no) ELSE 0 end
Count_WEB,
CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END
HARDCOPY
from t
where received_date between '01/01/2003' and '12/31/2003'
group by id,
nbs! p; to_char(received_date, '-mm'),
sequence_no,
id||yr||seq_no||ck
)
group by received_date, msno, hardcopy
/
Viktor
Sent by: [EMAIL PROTECTED]
08/26/2003 01:59 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
cc:
Subject: SQL HELP!!!
Hello,
I was wondering if someone can help me with a report. I am stuck
figuring out what I can do to complete it. Here is what I need to do:
for a given date range, i..e. start_date - end_date ('01/01/2003',
'12/31/2003'), I need to count all web, and non-web reco! rds.
If seq_no 4000, then it's web, otherwise non-web. seq_no is not
unique. So it's like 2 different where claused in a single select.
Could I somehow use CASE or ! decode to accomplish this. Here is what
I am trying to do in select:
select id ,
to_char(received_date, '-mm'),
id||yr||seq_no||ck MSNO
CASE WHEN seq_no 4000 then count(seq_no) ELSE 0 end
Count_WEB,
CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END
HARDCOPY
from t
where received_date between '01/01/2003' and '12/31/2003'
group by id,
to_char(received_date, '-mm'),
sequence_no,
id||yr||seq_no||ck !
/
AR 2003-01 AR030023T 0 1
AR 2003-01 AR0200302 0 1
AR 2003-01 AR020047K 0 1
AR 2003-01 AR020077N 0 1
I would like to show Year-Month once and count all instances of
id||yr||seq_no||ck (primary_key) for that Year-Month, but not to
break on it, and unfortunaley it won't let me do it without grouping
on seq_no
Please advise!!!
Any help is g! reatly appreciated!
Do you Yahoo!?
Yahoo! SiteBuilder
-
Free, easy-to-use web site design software
Do you Yahoo!?
Yahoo! SiteBuilder
- Free,
easy-to-use web site design software
--
Re: SQL HELP!!!
Jared,
I had a temporary brain freeze :), thanks much! I have modified it a bit, and it seems to be working fine! One other small thing. Is there a quick wahy to calculate percentage of web/non-web and vice-versa in the same select after the column "sums"?
select distinct id, received_date, sum(count_web), sum(count_hardcopy)from (select distinct id , to_char(received_date, '-mm') received_date, id||yr||seq_no||ck MSNO, CASE WHEN seq_no 4000 then count(*) ELSE 0 end Count_WEB, CASE WHEN seq_no =4000 then count(*) ELSE 0 END Count_HARDCOPYfrom twhere id = 'AC'and received_date between '01/01/2003' and '12/31/2003'group by id, to_char(received_date, '-mm'),
seq_no, id||yr||seq_no||ck)group by id, received_date
Thanks again![EMAIL PROTECTED] wrote:
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! 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 a id||yr||seq_no||ck row, it's a "web row", if not then it's not. what I would like to see is: ID Received_date Non-web countWeb_count AR 2003-01 0 4AR 2003-02 0 6AR 2003-0 1 8 and so forth. Again thanks for any suggestions you may have! Thanks very much!
[EMAIL PROTECTED] wrote: 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 MSNOCASE WHEN seq_no 4000 then count(seq_no) ELSE 0 end Count_WEB,CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END HARDCOPY from twhere received_date bet!
ween
'01/01/2003' and '12/31/2003'group by id,; to_char(received_date, '-mm'), sequence_no,id||yr||seq_no||ck ) group by received_date, msno, hardcopy /
Viktor [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED]
08/26/2003 01:59 PM Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]cc:Subject:SQL HELP!!!Hello, I was wondering if someone can help me with a report. I am stuck figuring out what I can do to complete it. Here is what I need to do: for a given date range, i..e. start_date - end_date ('01/01/2003', '12/31/2003'), I need to count all "web", and "non-web" recor ds. If seq_no 4000, then it's web, otherwise non-web. seq_no is not !
unique.
So it's like 2 different where claused in a single select. Could I somehow use CASE or decode to accomplish this. Here is what I am trying to do in select: select id ,to_char(received_date, '-mm'),id||yr||seq_no||ck MSNOCASE WHEN seq_no 4000 then count(seq_no) ELSE 0 end Count_WEB,CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END HARDCOPY from twhere received_date between '01/01/2003' and '12/31/2003'group by id,to_char(received_date, '-mm'), sequence_no,id||yr||seq_no||ck BR/ AR 2003-01 AR030023T 0 1AR 2003-01 AR0200302 0 1AR 2003-01 AR020047K 0 !
1AR 2003-01 AR020077N 0 1 I would like to show Year-Month once and count all instances of id||yr||seq_no||ck (primary_key) for that Year-Month, but not to break on it, and unfortunaley it won't let me do it without grouping on seq_no Please advise!!! Any help is greatly appreciated!
Do you Yahoo!?Yahoo! SiteBuilder - Free, easy-to-use web site design software
Do you Yahoo!?Yahoo! SiteBuilder - Free, easy-to-use web site design software
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
Re: SQL HELP!!!
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 received_date between '01/01/2003' and '12/31/2003')
group by id,
received_date
HTH,
Dave
[EMAIL PROTECTED] wrote:
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
a id||yr||seq_no||ck row, it's a web row, if not then it's not. what I
would like to see is:
ID Received_date Non-web count Web_count
AR 2003-010 4
AR 2003-020 6
AR 2003-0 1 8
and so forth.
Again thanks for any suggestions you may have!
Thanks very much!
[EMAIL PROTECTED] wrote:
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 count(seq_no) ELSE 0 end
Count_WEB,
CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END
HARDCOPY
from t
where received_date between '01/01/2003' and '12/31/2003'
group by id,
nbs! p; to_char(received_date, '-mm'),
sequence_no,
id||yr||seq_no||ck
)
group by received_date, msno, hardcopy
/
Viktor [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
08/26/2003 01:59 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:SQL HELP!!!
Hello,
I was wondering if someone can help me with a report. I am stuck
figuring out what I can do to complete it. Here is what I need to do:
for a given date range, i..e. start_date - end_date ('01/01/2003',
'12/31/2003'), I need to count all web, and non-web reco! rds.
If seq_no 4000, then it's web, otherwise non-web. seq_no is not
unique. So it's like 2 different where claused in a single select.
Could I somehow use CASE or decode to accomplish this. Here is what
I am trying to do in select:
select id ,
to_char(received_date, '-mm'),
id||yr||seq_no||ck MSNO
CASE WHEN seq_no 4000 then count(seq_no) ELSE 0 end
Count_WEB,
CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END
HARDCOPY
from t
where received_date between '01/01/2003' and '12/31/2003'
group by id,
to_char(received_date, '-mm'),
sequence_no,
id||yr||seq_no||ck !
/
AR 2003-01 AR030023T 0 1
AR 2003-01 AR0200302 0 1
AR 2003-01 AR020047K 0 1
AR 2003-01 AR020077N 0 1
I would like to show Year-Month once and count all instances of
id||yr||seq_no||ck (primary_key) for that Year-Month, but not to
break on it, and unfortunaley it won't let me do it without grouping
on seq_no
Please advise!!!
Any help is greatly appreciated!
Do you Yahoo!?
Yahoo! SiteBuilder
http://us.rd.yahoo.com/evt=10469/*http://sitebuilder.yahoo.com -
Free, easy-to-use web site design software
Do you Yahoo!?
Yahoo! SiteBuilder
http://us.rd.yahoo.com/evt=10469/*http://sitebuilder.yahoo.com - Free,
easy-to-use web site design software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Dave Hau
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: SQL HELP!!!
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! 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 a id||yr||seq_no||ck row, it's a web row, if not then it's not. what I would like to see is:
ID Received_date Non-web countWeb_count
AR 2003-01 0 4
AR 2003-02 0 6
AR 2003-0 1 8
and so forth.
Again thanks for any suggestions you may have!
Thanks very much!
[EMAIL PROTECTED] wrote:
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 count(seq_no) ELSE 0 end Count_WEB,
CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END HARDCOPY
from t
where received_date between '01/01/2003' and '12/31/2003'
group by id,
; to_char(received_date, '-mm'),
sequence_no,
id||yr||seq_no||ck
)
group by received_date, msno, hardcopy
/
Viktor [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
08/26/2003 01:59 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:SQL HELP!!!
Hello,
I was wondering if someone can help me with a report. I am stuck figuring out what I can do to complete it. Here is what I need to do:
for a given date range, i..e. start_date - end_date ('01/01/2003', '12/31/2003'), I need to count all web, and non-web recor ds. If seq_no 4000, then it's web, otherwise non-web. seq_no is not unique. So it's like 2 different where claused in a single select. Could I somehow use CASE or decode to accomplish this. Here is what I am trying to do in select:
select id ,
to_char(received_date, '-mm'),
id||yr||seq_no||ck MSNO
CASE WHEN seq_no 4000 then count(seq_no) ELSE 0 end Count_WEB,
CASE WHEN seq_no =4000 then count(seq_no) ELSE 0 END HARDCOPY
from t
where received_date between '01/01/2003' and '12/31/2003'
group by id,
to_char(received_date, '-mm'),
sequence_no,
id||yr||seq_no||ck BR/
AR 2003-01 AR030023T 0 1
AR 2003-01 AR0200302 0 1
AR 2003-01 AR020047K 0 1
AR 2003-01 AR020077N 0 1
I would like to show Year-Month once and count all instances of id||yr||seq_no||ck (primary_key) for that Year-Month, but not to break on it, and unfortunaley it won't let me do it without grouping on seq_no
Please advise!!!
Any help is greatly appreciated!
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
RE: SQL help
Thanks but unfortuneately we only have standard edition so I do not have these features. Rick Larry Elkins elkinsl@flash To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .netcc: Sent by: Subject: RE: SQL help [EMAIL PROTECTED] om 09/13/2002 02:08 AM Please respond to ORACLE-L 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 enclosing that you use the count(*) over to know the total count of rows. You then evaluate that count in the outer most in-line view. If the bottom half returned rows, and you already know the top half will return 1, then a count greater than 1 means you found rows in the bottom query. In this example, note the bottom part of the UNION ALL says 1=2, no row will be returned by the bottom query, and since the total count of rows will be one, no rows are returned (the header is suppressed): 1 select y.x 2 From (select x.x, count (*) over () cnt 3from (select 'x' x 4 from dual 5 UNION ALL 6 Select 'y' 7 from dual 8 where 1=2) x ) y 9* where y.cnt 1 SQL / no rows selected In this case, the bottom half returns a row (1=1), so you will get rows since the cnt is 1: SQL ed Wrote file afiedt.buf 1 select y.x 2 From (select x.x, count (*) over () cnt 3from (select 'x' x 4 from dual 5 UNION ALL 6 Select 'y' 7 from dual 8 where 1=1) x ) y 9* where y.cnt 1 SQL / X - x y Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 4:48 PM To: Multiple recipients of list ORACLE-L Subject: SQL help Hi DBAs, I have a query something like SELECT sysdate,'txt1','txt2' FROM dual UNION SELECT date1,txtfield1,txtfield2 FROM t1,t2,...,tn WHERE ... ; The output would be one header record from the first select then the data from the second select. If there are no records selected in second select the header record is still selected. If there are no records in second select I do not want header record selected. How can I suppress it? I do not want to apply the where clause in 2nd select to the first because of the complexity. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538
RE: SQL help
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 expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 5:48 PM To: Multiple recipients of list ORACLE-L Subject: SQL help Hi DBAs, I have a query something like SELECT sysdate,'txt1','txt2' FROM dual UNION SELECT date1,txtfield1,txtfield2 FROM t1,t2,...,tn WHERE ... ; The output would be one header record from the first select then the data from the second select. If there are no records selected in second select the header record is still selected. If there are no records in second select I do not want header record selected. How can I suppress it? I do not want to apply the where clause in 2nd select to the first because of the complexity. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: SQL help
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:[EMAIL PROTECTED]]Sent: Thursday, September 12, 2002 4:03 PMTo: Multiple recipients of list ORACLE-LSubject: 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 expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 5:48 PM To: Multiple recipients of list ORACLE-L Subject: SQL help Hi DBAs, I have a query something like SELECT sysdate,'txt1','txt2' FROM dual UNION SELECT date1,txtfield1,txtfield2 FROM t1,t2,...,tn WHERE ... ; The output would be one header record from the first select then the data from the second select. If there are no records selected in second select the header record is still selected. If there are no records in second select I do not want header record selected. How can I suppress it? I do not want to apply the where clause in 2nd select to the first because of the complexity. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: SQL help
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 enclosing that you use the count(*) over to know the total count of rows. You then evaluate that count in the outer most in-line view. If the bottom half returned rows, and you already know the top half will return 1, then a count greater than 1 means you found rows in the bottom query. In this example, note the bottom part of the UNION ALL says 1=2, no row will be returned by the bottom query, and since the total count of rows will be one, no rows are returned (the header is suppressed): 1 select y.x 2 From (select x.x, count (*) over () cnt 3from (select 'x' x 4 from dual 5 UNION ALL 6 Select 'y' 7 from dual 8 where 1=2) x ) y 9* where y.cnt 1 SQL / no rows selected In this case, the bottom half returns a row (1=1), so you will get rows since the cnt is 1: SQL ed Wrote file afiedt.buf 1 select y.x 2 From (select x.x, count (*) over () cnt 3from (select 'x' x 4 from dual 5 UNION ALL 6 Select 'y' 7 from dual 8 where 1=1) x ) y 9* where y.cnt 1 SQL / X - x y Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED] Sent: Thursday, September 12, 2002 4:48 PM To: Multiple recipients of list ORACLE-L Subject: SQL help Hi DBAs, I have a query something like SELECT sysdate,'txt1','txt2' FROM dual UNION SELECT date1,txtfield1,txtfield2 FROM t1,t2,...,tn WHERE ... ; The output would be one header record from the first select then the data from the second select. If there are no records selected in second select the header record is still selected. If there are no records in second select I do not want header record selected. How can I suppress it? I do not want to apply the where clause in 2nd select to the first because of the complexity. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: 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 mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Help Urgent!!!!!!!!!
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 try without using view . Regards Sreeraman - Original Message - From: sultan To: Multiple recipients of list ORACLE-L Sent: Monday, May 13, 2002 10:53 AM Subject: SQL Help Urgent! 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-- --- 1 200 1 300 1 500 My query like this = SQL select a.id,sum(a.amount),sum(b.amount) from bb a,cc b where 2 a.id=b.id 3 group by 4 a.id; ID SUM(A.AMOUNT) SUM(B.AMOUNT) -- - 1 3000 1000 Based on the details table rows it sum up three times the master amount ,that is why it shows 3000. But my output should be like this ID SUM(A.AMOUNT) SUM(B.AMOUNT) -- - 11000 1000 Anybody can help me in this issue please. Regards. syed
RE: SQL Help Urgent!!!!!!!!!
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 PROTECTED]]Sent: Monday, May 13, 2002 10:53 AMTo: Multiple recipients of list ORACLE-LSubject: SQL Help Urgent! 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-- --- 1 200 1 300 1 500 My query like this = SQL select a.id,sum(a.amount),sum(b.amount) from bb a,cc b where 2 a.id=b.id 3 group by 4 a.id; ID SUM(A.AMOUNT) SUM(B.AMOUNT) -- - 1 3000 1000 Based on the details table rows it sum up three times the master amount ,that is why it shows 3000. But my output should be like this ID SUM(A.AMOUNT) SUM(B.AMOUNT) -- - 11000 1000 Anybody can help me in this issue please. Regards. syed
Re: SQL Help Urgent!!!!!!!!!
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 -- --- 1200 1300 1500 My query like this = SQL select a.id,sum(a.amount),sum(b.amount) from bb a,cc b where 2 a.id=b.id 3group by 4a.id; ID SUM(A.AMOUNT) SUM(B.AMOUNT) -- - 1 3000 1000 Based on the details table rows it sum up three times the master amount ,that is why it shows 3000. But my output should be like this ID SUM(A.AMOUNT) SUM(B.AMOUNT) -- - 1 1000 1000 Anybody can help me in this issue please. Regards. syed Do the GROUP BY in an in-line view. BTW in the case you give as example, you just need to put B.AMOUNT in the GROUP BY clause. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: SQL help
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 repeated combos of ID, Company Country, you could say something like: warning--air SQL! SELECT t.* FROM my_table t, (SELECT ID, Company, Country, COUNT(*) NumRecs FROM my_table GROUP BY ID, Company, Country HAVING COUNT(*) 1) sq WHERE t.ID = sq.ID AND t.Company = sq.Company AND t.Country = sq.Country /warning--air SQL! Maybe that would suit? HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, April 29, 2002 3:12 PM To: Multiple recipients of list ORACLE-L 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 US 1 5521 ABC US -1 8877 DEF UK 0 8877 DEF UK 1 I want to pull the records where all the columns are the same except for the status column . Any help is greatly apprecieted K _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: k k 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: Pardee, Roy E 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: SQL help
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. Karadeniz Oracle DBA / Developer Civilian IT Department Havelsan A.S. Eskisehir yolu 7.km Ankara Turkey Phone: +90 312 2873565 / 1217 Mobile : +90 535 3357729 - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 30, 2002 1:12 AM 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 US 1 5521 ABC US -1 8877 DEF UK 0 8877 DEF UK 1 I want to pull the records where all the columns are the same except for the status column . Any help is greatly apprecieted K _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: k k 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: Bunyamin K. Karadeniz 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: SQL help
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 US 1 5521 ABC US -1 8877 DEF UK 0 8877 DEF UK 1 I want to pull the records where all the columns are the same except for the status column . Any help is greatly apprecieted K You have several solutions. GROUP BY ID, COMPANY, COUNTRY HAVING COUNT(STATUS) 1 is one. WHERE EXISTS (SELECT NULL WHERE A.ID = B.ID AND A.COMPANY = B.COMPANY AND A.COUNTRY = B.COUNTRY AND A.STATUS B.STATUS) is another. I am sure you can also do something with MINUS or INTERSECT ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: SQL help
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 country varchar2(2), status number);
Table created.
SQL insert into junk values ('5521','ABC','US',1);
1 row created.
SQL insert into junk values ('5521','ABC','US',-1);
1 row created.
SQL insert into junk values ('8877','DEF','UK',0);
1 row created.
SQL insert into junk values ('8877','DEF','UK',1);
1 row created.
SQL commit;
SQL alter table junk add constraint junk_unq unique (id,company,cou
alter table junk add constraint junk_unq unique (id,company,country)
*
ERROR at line 1:
ORA-02299: cannot validate (SCOTT.JUNK_UNQ) - duplicate keys found
u, we knew that :-)
run the script to create the exceptions table
and enable the constraint again using the exceptions clause
SQL @c:\oracle\ora901\rdbms\admin\utlexcpt.sql
Table created.
SQL alter table junk add constraint junk_unq unique (id,company,country)
2 exceptions into exceptions;
alter table junk add constraint junk_unq unique (id,company,country)
*
ERROR at line 1:
ORA-02299: cannot validate (SCOTT.JUNK_UNQ) - duplicate keys found
SQL select * from exceptions;
ROW_ID OWNER TABLE_NAME
-- --
CONSTRAINT
--
AAAH4LAABAAAO+HAAA SCOTT JUNK
JUNK_UNQ
AAAH4LAABAAAO+HAAB SCOTT JUNK
JUNK_UNQ
AAAH4LAABAAAO+HAAC SCOTT JUNK
JUNK_UNQ
AAAH4LAABAAAO+HAAD SCOTT JUNK
JUNK_UNQ
We can now use this table to view which rows have duplicates and to
decide based
on your business rules which should be deleted.
SQL select * from junk where rowid in
2 (select row_id from exceptions e where e.table_name='JUNK');
ID COM CO STATUS
--- -- --
5521 ABC US 1
5521 ABC US -1
8877 DEF UK 0
8877 DEF UK 1
Hope this helps,
John
[EMAIL PROTECTED] 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 US 1
5521 ABC US -1
8877 DEF UK 0
8877 DEF UK 1
I want to pull the records where all the columns are the same except
for the status column .
Any help is greatly apprecieted
K
_
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ora NT DBA
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: sql help
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 tablespaces in which they reside. i tried using the flwng way: select tablespacename,table_name from dba_tables where obj in (select obj_nm from dba_objects where obj_type ='table' and owner='xyz') union select for indexes union select for clusters ; can anybody tell me any alternative statement for the above. thnx in adv. srinivas __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa 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: Kevin Lange 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: sql help
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é : samedi 21 juillet 2001 15:45 À : Multiple recipients of list ORACLE-L Objet : sql help hi dba's how to find out a users all objects+the tablespaces in which they reside. i tried using the flwng way: select tablespacename,table_name from dba_tables where obj in (select obj_nm from dba_objects where obj_type ='table' and owner='xyz') union select for indexes union select for clusters ; can anybody tell me any alternative statement for the above. thnx in adv. srinivas __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa 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: GL2Z/ INF DBA BENLATRECHE 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: sql help
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 objects+the tablespaces in which they reside. i tried using the flwng way: select tablespacename,table_name from dba_tables where obj in (select obj_nm from dba_objects where obj_type ='table' and owner='xyz') union select for indexes union select for clusters ; can anybody tell me any alternative statement for the above. thnx in adv. srinivas __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa 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: Anand 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: sql help --urgent
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 I want to display 0 whereever negative value appears.I can use the ROUND(A-1) but this will round the other positive value to the next positve integer which I don't want to happen. select a,a-1,round(a-1) from t; AA-1 ROUND(A-1) - -- -- 3 2 2 1 0 0 .6-.4 0 .8-.2 0 6 5 5 3.52.5 3 .69 -.31 0 2.71.7 2 sum 10.29--this value is the sum of all the negative and positive numbers I want the o/p to be like this.The decimal numbers should not be rounded off to the next number. select a,a-1,round(a-1) from t; AA-1 MY REQUIREMENT - -- -- 3 2 2 1 0 0 .6-.4 0 .8-.2 0 6 5 5 3.52.5 2.5 .69 -.31 0 2.71.7 1.7 Sum 11.2(My REQUIREMENT) My requirement is to compute the sum of the columns for exact value i.e I want the sum of only the positive numbers(negative numbers should not be counted for the sum that's why I want to display 0 for negative value as the sum function adds all the positive and negative numbers) How can I do this Thanks Ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ravindra Basavaraja 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: Rangachari Sundar 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: sql help
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 sys.dba_clusters WHERE owner = 'SCOTT' ORDER BY 1 , 2; Jon Walthour - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, July 21, 2001 11:45 AM hi dba's how to find out a users all objects+the tablespaces in which they reside. i tried using the flwng way: select tablespacename,table_name from dba_tables where obj in (select obj_nm from dba_objects where obj_type ='table' and owner='xyz') union select for indexes union select for clusters ; can anybody tell me any alternative statement for the above. thnx in adv. srinivas __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa 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: Jon Walthour 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: sql help --urgent
decode(sign(a), -1, 0, a) Ron Thomas Hypercom, Inc [EMAIL PROTECTED] I'm too sexy for my code. - Awk Sed Fred ravindra@sent ica.com To: [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: sql help --urgent com 07/20/01 12:40 PM Please respond to 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 want to display 0 whereever negative value appears.I can use the ROUND(A-1) but this will round the other positive value to the next positve integer which I don't want to happen. select a,a-1,round(a-1) from t; AA-1 ROUND(A-1) - -- -- 3 2 2 1 0 0 .6-.4 0 .8-.2 0 6 5 5 3.52.5 3 .69 -.31 0 2.71.7 2 sum 10.29--this value is the sum of all the negative and positive numbers I want the o/p to be like this.The decimal numbers should not be rounded off to the next number. select a,a-1,round(a-1) from t; AA-1 MY REQUIREMENT - -- -- 3 2 2 1 0 0 .6-.4 0 .8-.2 0 6 5 5 3.52.5 2.5 .69 -.31 0 2.71.7 1.7 Sum 11.2(My REQUIREMENT) My requirement is to compute the sum of the columns for exact value i.e I want the sum of only the positive numbers(negative numbers should not be counted for the sum that's why I want to display 0 for negative value as the sum function adds all the positive and negative numbers) How can I do this Thanks Ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ravindra Basavaraja 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).
RE: sql help --urgent
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 want to display 0 whereever negative value appears.I can use the ROUND(A-1) but this will round the other positive value to the next positve integer which I don't want to happen. select a,a-1,round(a-1) from t; AA-1 ROUND(A-1) - -- -- 3 2 2 1 0 0 .6-.4 0 .8-.2 0 6 5 5 3.52.5 3 .69 -.31 0 2.71.7 2 sum 10.29--this value is the sum of all the negative and positive numbers I want the o/p to be like this.The decimal numbers should not be rounded off to the next number. select a,a-1,round(a-1) from t; AA-1 MY REQUIREMENT - -- -- 3 2 2 1 0 0 .6-.4 0 .8-.2 0 6 5 5 3.52.5 2.5 .69 -.31 0 2.71.7 1.7 Sum 11.2(My REQUIREMENT) My requirement is to compute the sum of the columns for exact value i.e I want the sum of only the positive numbers(negative numbers should not be counted for the sum that's why I want to display 0 for negative value as the sum function adds all the positive and negative numbers) How can I do this Thanks Ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ravindra Basavaraja 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: Michael E. Cupp, Jr. 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: SQL HELP
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 before, but how do you construct SQL to get all the fridays between 2 dates: Sort of like: SELECT some_date_column (want to see only fridays) FROM some_table WHERE...? Thanks a lot. __ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices. http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viktor 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: Abdul Aleem 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).
