PL/SQL Help
encoded content removed -- binaries not allowed by ListGuru The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. winmail.dat
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).
FW: SQL help needed
How incredibly frustrating! My original response keeps getting truncated just before the good stuff! Here is one more try with HTML turned off... -- Forwarded Message Date: Fri, 19 Sep 2003 22:47:43 -0700 To: [EMAIL PROTECTED] Sorry for the last response in this thread which was truncated. Very annoying! The full response looks as follows (hope this goes through in it¹s entirety)... -- Forwarded Message Date: Fri, 19 Sep 2003 22:05:19 -0700 To: [EMAIL PROTECTED] 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)? ³. Later on, the ³fsck y² option made the separate ³yes² command unnecessary. Nice thing about the ³yes² command is that it could also answer ³no² endlessly (i.e. ³yes n²) or other themed responses (i.e. ³yes eat me²)... Anyway, here's your INFINITE_DUAL table, obviously requiring Oracle9i or above... SQL create type InfiniteDualType as object (dummy number); 2 / Type created. SQL SQL create type InfiniteDualTable as table of InfiniteDualType; 2 / Type created. SQL SQL create function f_infinite_dual(upper_limit in number default null) 2 return InfiniteDualTable 3 pipelined 4 is 5 v_rtn InfiniteDualType; 6 i integer := 1; 7 begin 8 -- 9 v_rtn := InfiniteDualType(null); 10 while true loop 11 v_rtn.dummy := i; 12 if upper_limit is not null and i upper_limit then 13 exit; 14 end if; 15 i := i + 1; 16 pipe row (v_rtn); 17 end loop; 18 -- 19 return; 20 -- 21 end f_infinite_dual; 22 / Function created. SQL SQL select * from table(f_infinite_dual(10)); DUMMY -- 1 2 3 4 5 6 7 8 9 10 10 rows selected. SQL SQL create view infinite_dual 2 as 3 select * from table(f_infinite_dual); View created. SQL SQL select * from infinite_dual; DUMMY -- 1 2 3 4 5 6 7 8 9 10 11 DUMMY -- 12 13 14 15 16 17 18 19 20 21 22 DUMMY -- 23 24 25 26 27 28 29 30 31 32 33 ..and so on, and so on, and so on, until you hit CTRL-C (or 42, which ever represents infinity in your own belief system)... Just for grins, I had installed an UPPER_LIMIT parameter to the F_INFINITE_DUAL function, but it defaults to infinity. Great idea, Mark! -Tim on 9/17/03 6:24 PM, Mark Richard at [EMAIL PROTECTED] wrote: 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
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)? ³
FW: SQL help needed
Sorry for the last response in this thread which was truncated. Very annoying! The full response looks as follows (hope this goes through in it¹s entirety)... -- Forwarded Message Date: Fri, 19 Sep 2003 22:05:19 -0700 To: [EMAIL PROTECTED] 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
SQL help needed
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
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.
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]
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
[Q] SQL Help!! How to make select left justify and fill space??
Can anyone tell me on select how can make it left justify and fill space? example: IDnumber(8) output: 12345 Emplyeename ^ |--- fill space Thanks. __ 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: mike mon 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 - 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: [Q] SQL Help!! How to make select left justify and fill space
Title: RE: [Q] SQL Help!! How to make select left justify and fill space?? select rpad(to_char(id),8,' ') from your_table / 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: mike mon [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 27, 2003 11:09 AM To: Multiple recipients of list ORACLE-L Subject: [Q] SQL Help!! How to make select left justify and fill space?? Can anyone tell me on select how can make it left justify and fill space? example: ID number(8) output: 12345 Emplyeename ^ |--- fill space Thanks. __ 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: mike mon 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). *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: [Q] SQL Help!! How to make select left justify and fill space??
hi Mike, use ltrim to left justify and then rpad to pad it out with spaces, so something like this rpad(ltrim(id), '', 15) id N. :--Original Message- :-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] :-Behalf Of :-mike mon :-Sent: 27 August 2003 16:09 :-To: Multiple recipients of list ORACLE-L :-Subject: [Q] SQL Help!! How to make select left justify and :-fill space?? :- :- :-Can anyone tell me on select how can make it left :-justify and fill space? :- :-example: :- :- IDnumber(8) :- :-output: :- :-12345 Emplyeename :- ^ :- |--- fill space :- :- :- :-Thanks. :- :- :-__ :-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: mike mon :- 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: Nuala Cullen 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: [Q] SQL Help!! How to make select left justify and fill space??
Use something like FORMAT STDOUT= @ @ $id, $empname
RE: [Q] SQL Help!! How to make select left justify and fill space??
LOL! Mladen Gogala [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/27/2003 10:04 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: [Q] SQL Help!! How to make select left justify and fill space?? Use something like FORMAT STDOUT= @ @ $id,$empname
RE: [Q] SQL Help!! How to make select left justify and fill space??
Title: Message
Well,
people are trying to use SQL*Plus as if it was a report generating tool, which
it isn't.
To
tell the truth, I started using perl because of its formatting capabilities (I
hope you still
do
remember the old ora_login stuff from perl4?) because my report looked so
much nicer
then
with sqlplus. I don't have running sums, but they're rarely needed. On the other
hand,
I can
do left and right justification, top of the page format, multiline fields
(impossible to do
with
sqlplus) and a lot of other good stuff, like conditional printing (that's why
perl has
"if"
statement). The ideal thing for generating pretty reports out of any database is
called
perl.
It's cheap, it's well documented ("Learning Perl", "Perl Programming", "Perl
Cookbook",
"Perl
for Oracle DBA", "Advanced Perl Programming", "Perl for Sys Admins", "Perl
Objects,
References Modules","Learning Perl
Tk", "Object Oriented Perl Programming", I have a
whole
shelf devoted to perl.I have a hunch that when Cary's book gets into
the circulation,
there
will be another book on that shelf. If that is not a well documented
language, then I
don't
know what is) and is ideally suited for the purpose. Yet, people are still using
sqlplus.
Why?
It beats me.
--Mladen GogalaOracle DBA
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]Sent: Wednesday, August 27, 2003 3:00
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
[Q] SQL Help!! How to make select left justify and fill
space??LOL!
"Mladen Gogala"
[EMAIL PROTECTED] Sent by: [EMAIL PROTECTED]
08/27/2003 10:04 AM
Please respond to ORACLE-L
To:
Multiple recipients of list ORACLE-L
[EMAIL PROTECTED] cc:
Subject:RE: [Q] SQL Help!! How to
make select left justify and fill
space??Use something like FORMAT
STDOUT=@
@$id,
$empname
Note:
This message is for the named person's use only. It may contain
confidential, proprietary or legally privileged information. No
confidentiality or privilege is waived or lost by any mistransmission. If
you receive this message in error,please immediately delete it and all
copies of it from your system, destroy any hard copies of it and notify the
sender. You must not, directly or indirectly, use, disclose, distribute,
print, or copy any part of this message if you are not the intended
recipient.Wang Trading
LLCand any of its subsidiaries each reserve the right to
monitor all e-mail communications through its networks. Any views
expressed in this message are those of the individual sender, except where the
message states otherwise and the sender is authorized to state them to be the
views of any such entity.
RE: [Q] SQL Help!! How to make select left justify and fill space??
Mladen, Even I use sqlplus for some reporting. There's even a perl script in Perl for Oracle DBA's that is a driver for sqlplus. Why? For the things that are missing in Perl that I needed for some reporting. Mostly the column breaks. There is no convenient module yet for doing that in Perl. I started one for Perl, but never finished it. Not enough oop experience for the complexity of what I was trying to do ( breaks and sums ). Have a working prototype, but far from finished. Just ran out of time for it. Sqlplus does still have it's place. Jared Mladen Gogala [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/27/2003 12:59 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: [Q] SQL Help!! How to make select left justify and fill space?? Well, people are trying to use SQL*Plus as if it was a report generating tool, which it isn't. To tell the truth, I started using perl because of its formatting capabilities (I hope you still do remember the old ora_login stuff from perl4?) because my report looked so much nicer then with sqlplus. I don't have running sums, but they're rarely needed. On the other hand, I can do left and right justification, top of the page format, multiline fields (impossible to do with sqlplus) and a lot of other good stuff, like conditional printing (that's why perl has if statement). The ideal thing for generating pretty reports out of any database is called perl. It's cheap, it's well documented (Learning Perl, Perl Programming, Perl Cookbook, Perl for Oracle DBA, Advanced Perl Programming, Perl for Sys Admins, Perl Objects, References Modules,Learning Perl Tk, Object Oriented Perl Programming, I have a whole shelf devoted to perl.I have a hunch that when Cary's book gets into the circulation, there will be another book on that shelf. If that is not a well documented language, then I don't know what is) and is ideally suited for the purpose. Yet, people are still using sqlplus. Why? It beats me. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 3:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: [Q] SQL Help!! How to make select left justify and fill space?? LOL! Mladen Gogala [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/27/2003 10:04 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: [Q] SQL Help!! How to make select left justify and fill space?? Use something like FORMAT STDOUT= @ @ $id,$empname Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
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
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!!! <B!
R> 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
Need some SQL help Please...
Good morning List,
Please bare with me, this is somewhat long with
the DDL and DML included.
I have two tables that are populated by triggers
to be used to audit data changes.
The source and target tables are identical in
structure with the addition of the DML and
sequence columns iin the target.
For each key there will be at least an insert (I)
row with possibly update (U) or delete (D) rows
in chronological order.
As each row is inserted it gets a sequence
number.
I am having some trouble getting the data to
return in the correct order.
It must be a chronological return of each parent
rows and its child rows that fall chronologically
between the insert/updates/deletes.
What I expect is this (dates abbreviated) for
par_key = 100:
par_key par_col par_insert par_update par_dml
par_seq chi_key chi_col chi_insert
chi_update chi_dml chi_seq
100 100 1/1/2003I
1100 100 1/1/2003
I1
100 200 1/2/20031/2/2003U
4100 100 1/1/2003
I1
100 200 1/2/20031/3/2003U
5100 100 1/1/2003
I1
100 200 1/2/20031/3/2003U
5100 200 1/1/20031/4/2003
U4
100 200 1/2/20031/3/2003U
5100 300 1/1/20031/5/2003
U5
100 200 1/2/20031/3/2003U
5100 400 1/1/20031/6/2003
U6
I have used this as basis for starting, but can't
seem to get it to show in the correct order or
without extra rows.
select p.par_key,
p.par_col,
p.par_insert,
p.par_update,
p.par_dml,
p.par_seq,
c.chi_key,
c.chi_col,
c.chi_insert,
c.chi_update,
c.chi_dml,
c.chi_seq
from tab_parent p,
tab_child c
where p.par_key = c.chi_key
andc.chi_insert between p.par_insert and
nvl(p.par_update,c.chi_insert)
or c.chi_update between p.par_insert and
nvl(p.par_update,c.chi_insert)
order by
par_key,par_col,par_seq,chi_key,chi_col,chi_seq;
---
drop table tab_parent;
create table tab_parent
(par_key number,
par_col number,
par_insert date,
par_update date,
par_dml char(1),
par_seq number);
drop table tab_child;
create table tab_child
(chi_key number,
chi_col number,
chi_insert date,
chi_update date,
chi_dml char(1),
chi_seq number);
truncate table tab_parent;
truncate table tab_child;
insert into tab_parent
values (100,100,to_date('10-jan-2003
08:00:00','dd-mon- hh24:mi:ss'),null,'I',1);
insert into tab_parent
values (200,200,to_date('10-jan-2003
08:00:00','dd-mon- hh24:mi:ss'),null,'I',2);
insert into tab_parent
values (300,300,to_date('12-jan-2003
20:00:00','dd-mon- hh24:mi:ss'),null,'I',3);
insert into tab_parent
values (100,200,to_date('10-jan-2003
10:00:00','dd-mon- hh24:mi:ss'),
to_date('13-jan-2003
11:00:00','dd-mon- hh24:mi:ss'),'U',4);
insert into tab_parent
values (100,300,to_date('10-jan-2003
10:00:00','dd-mon- hh24:mi:ss'),
to_date('15-jan-2003
12:00:00','dd-mon- hh24:mi:ss'),'U',5);
insert into tab_parent
values (200,200,to_date('10-jan-2003
10:10:00','dd-mon- hh24:mi:ss'),
to_date('17-jan-2003
15:30:00','dd-mon- hh24:mi:ss'),'U',6);
insert into tab_parent
values (200,300,to_date('10-jan-2003
10:10:00','dd-mon- hh24:mi:ss'),
to_date('17-jan-2003
15:30:01','dd-mon- hh24:mi:ss'),'U',7);
insert into tab_parent
values (200,400,to_date('10-jan-2003
10:10:00','dd-mon- hh24:mi:ss'),
to_date('18-jan-2003
16:50:00','dd-mon- hh24:mi:ss'),'U',8);
insert into tab_parent
values (300,300,to_date('12-jan-2003
20:00:00','dd-mon- hh24:mi:ss'),
to_date('19-jan-2003
07:30:00','dd-mon- hh24:mi:ss'),'D',9);
insert into tab_child
values (100,100,to_date('10-jan-2003
10:00:00','dd-mon- hh24:mi:ss'),null,'I',1);
insert into tab_child
values (200,200,to_date('10-jan-2003
10:10:00','dd-mon- hh24:mi:ss'),null,'I',2);
insert into tab_child
values (300,300,to_date('12-jan-2003
20:00:00','dd-mon- hh24:mi:ss'),null,'I',3);
insert into tab_child
values (100,200,to_date('10-jan-2003
10:00:00','dd-mon- hh24:mi:ss'),
to_date('10-jan-2003
12:00:00','dd-mon- hh24:mi:ss'),'U',4);
insert into tab_child
values (100,300,to_date('10-jan-2003
10:00:00','dd-mon- hh24:mi:ss'),
to_date('15-feb-2003
12:00:00','dd-mon- hh24:mi:ss'),'U',5);
insert into tab_child
values (100,400,to_date('10-jan-2003
10:00:00','dd-mon- hh24:mi:ss'),
to_date('16-feb-2003
12:33:00','dd-mon- hh24:mi:ss'),'U',6);
insert into tab_child
values (200,300,to_date('10-jan-2003
10:10:00','dd-mon- hh24:mi:ss'),
to_date('15-jan-2003
08:02:00','dd-mon- hh24:mi:ss'),'U',7);
insert into tab_child
values (200,400,to_date('10-jan-2003
RE: PL/SQL help
David- I've thought of both dbms_sql and dummy conditions too. Oracle Application reports use the dummy conditions all the time. I'm off to see if you can set cursor_sharing at the session level and not use bind params. I'm assuming no one else has figured this out either due to the lack of response. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To: [EMAIL PROTECTED] 09/20/02 01:28 AMcc: Please respond toSubject: RE: PL/SQL help ORACLE-L Ron We've run into this problem and haven't really found a satisfactory answer. It sounds like a job for dbms_sql, but the docs say its slower than native dynamic sql. Has anyone tested this? One other alternative I can think of (but have never tried) is to use dummy conditions in the sql; something like: - c_sql := 'select col1 from atable where col2 = :1 and :2 is null and :3 is null'; -- David Lord -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED]] Sent: 19 September 2002 19:53 To: Multiple recipients of list ORACLE-L Subject: PL/SQL help I am building a dynamic sql statement which will contain varying number of bind variables depending on user selection criteria. As an example, the sql statement may be: c_sql := 'select col1 from atable where col2 = :1' ; or it may be c_sql := 'select col1 from atable where col2 = :1 and col2 between :2 and :3' ; or it may be ... etc. I am trying to avoid ugly code such as: IF case1 THEN OPEN csr FOR c_sql USING var1 ; ELSIF case2 THEN OPEN csr FOR c_sql USING var1, var2, var3 ; ELSIF . END IF ; Once the sql statement is created, it will be opened/closed multiple times, so I want to use bind variables to avoid parsing. So I thought, hum, sounds like a job for EXECUTE IMMEDIATE, but in the various incarnations I tried, could not get it to work. The FM have not been much help (still looking tho). What am I missing? Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas 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). ** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord, David - CSG 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
RE: PL/SQL help
Title: RE: PL/SQL help Go to Tom Kyte's sight and look for contexts - he explains a way to build sql statements using native dynamic sql and refcursors and contexts where the select statement and predicate is variable and the output might also be variable. I get there by searching www.yahoo.com for asktom -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 2:06 PM To: Multiple recipients of list ORACLE-L Subject: RE: PL/SQL help David- I've thought of both dbms_sql and dummy conditions too. Oracle Application reports use the dummy conditions all the time. I'm off to see if you can set cursor_sharing at the session level and not use bind params. I'm assuming no one else has figured this out either due to the lack of response. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To: [EMAIL PROTECTED] 09/20/02 01:28 AM cc: Please respond to Subject: RE: PL/SQL help ORACLE-L Ron We've run into this problem and haven't really found a satisfactory answer. It sounds like a job for dbms_sql, but the docs say its slower than native dynamic sql. Has anyone tested this? One other alternative I can think of (but have never tried) is to use dummy conditions in the sql; something like: - c_sql := 'select col1 from atable where col2 = :1 and :2 is null and :3 is null'; -- David Lord -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED]] Sent: 19 September 2002 19:53 To: Multiple recipients of list ORACLE-L Subject: PL/SQL help I am building a dynamic sql statement which will contain varying number of bind variables depending on user selection criteria. As an example, the sql statement may be: c_sql := 'select col1 from atable where col2 = :1' ; or it may be c_sql := 'select col1 from atable where col2 = :1 and col2 between :2 and :3' ; or it may be ... etc. I am trying to avoid ugly code such as: IF case1 THEN OPEN csr FOR c_sql USING var1 ; ELSIF case2 THEN OPEN csr FOR c_sql USING var1, var2, var3 ; ELSIF . END IF ; Once the sql statement is created, it will be opened/closed multiple times, so I want to use bind variables to avoid parsing. So I thought, hum, sounds like a job for EXECUTE IMMEDIATE, but in the various incarnations I tried, could not get it to work. The FM have not been much help (still looking tho). What am I missing? Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas 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). ** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord, David - CSG 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.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California
RE: PL/SQL help
Ron We've run into this problem and haven't really found a satisfactory answer. It sounds like a job for dbms_sql, but the docs say its slower than native dynamic sql. Has anyone tested this? One other alternative I can think of (but have never tried) is to use dummy conditions in the sql; something like: - c_sql := 'select col1 from atable where col2 = :1 and :2 is null and :3 is null'; -- David Lord -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED]] Sent: 19 September 2002 19:53 To: Multiple recipients of list ORACLE-L Subject: PL/SQL help I am building a dynamic sql statement which will contain varying number of bind variables depending on user selection criteria. As an example, the sql statement may be: c_sql := 'select col1 from atable where col2 = :1' ; or it may be c_sql := 'select col1 from atable where col2 = :1 and col2 between :2 and :3' ; or it may be ... etc. I am trying to avoid ugly code such as: IF case1 THEN OPEN csr FOR c_sql USING var1 ; ELSIF case2 THEN OPEN csr FOR c_sql USING var1, var2, var3 ; ELSIF . END IF ; Once the sql statement is created, it will be opened/closed multiple times, so I want to use bind variables to avoid parsing. So I thought, hum, sounds like a job for EXECUTE IMMEDIATE, but in the various incarnations I tried, could not get it to work. The FM have not been much help (still looking tho). What am I missing? Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas 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). ** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord, David - CSG 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).
PL/SQL help
I am building a dynamic sql statement which will contain varying number of bind variables depending on user selection criteria. As an example, the sql statement may be: c_sql := 'select col1 from atable where col2 = :1' ; or it may be c_sql := 'select col1 from atable where col2 = :1 and col2 between :2 and :3' ; or it may be ... etc. I am trying to avoid ugly code such as: IF case1 THEN OPEN csr FOR c_sql USING var1 ; ELSIF case2 THEN OPEN csr FOR c_sql USING var1, var2, var3 ; ELSIF . END IF ; Once the sql statement is created, it will be opened/closed multiple times, so I want to use bind variables to avoid parsing. So I thought, hum, sounds like a job for EXECUTE IMMEDIATE, but in the various incarnations I tried, could not get it to work. The FM have not been much help (still looking tho). What am I missing? Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas 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
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
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
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
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
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).
SQL help
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).
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).
PL/SQL help - need some quick help
Hallo,
I have some trouble to get this pl/sql code right. Anyone whpom could help me with
this. It is important.
Hallo,
I have some trouble with this pl/sql procedure. I would like
that this lvsql to be run only if the field Borttags_flagg = 0 but i get an error in
the if statement, whatis wrong with this? It get the erromressage:
LS-00103: Encountered the symbol ||AvdNr|| when expecting one of the following:
. ( * % = - + / at in mod not rem then
an exponent (**) or != or ~= = = and or like
betwe..
when I compile the whole procedure. Please help me with this.
(If the borttags_flagg = 1 then it will continue the loop and check for next one.
etc...
If A'||AvdNr||'.ICA_ARTIKEL'||LookUpServerName|| 'BORTTAGS_FLAGG = 0 '' ||
then
(this lvsql is to be run only if field borttags_flagg = 0)
lvSQL := 'SELECT
ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.BORTTAGS_FLAGG,ICA_ARTIKEL.PANTBELOPP ' ||
--PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' ||
'FROM
A'||AvdNr||'.ICA_ARTIKEL'||LookUpServerName|| ' ' ||
--PBK.LPKORGEANREL ' ||
'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' '
||
'AND
ICA_ARTIKEL.DATUMTO_DATE('''||inDatum||''',''-MM-DD'') ' ||
-- 'AND ICA_ARTIKEL.BORTTAGS_FLAGG = 0 '
||
'ORDER BY DATUM DESC';
The whole procedure you can see in the file:
(See attached file: testplsql.SQL)
Thanks in advance Would really appreciate this.
Roland S
(See attached file: testplsql.SQL)
Thanks in advance.
Roland
testplsql.SQL
Description: Binary data
PL/SQL help
Hello, How I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ?? Also is there a way to issue set serveroutput on from within a stored procedure so that when someone run the stored procedure, the stored procedure output will be seen even if the person forgot to set serveroutput on for their sql*plus session ?? TIA K _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- 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).
PL/SQL help
Hello, I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ?? Also is there a way to issue set serveroutput on from within a stored procedure so that when someone run the stored procedure, the stored procedure output will be seen even if the person forgot to set serveroutput on for their sql*plus session ?? TIA K _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- 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).
Re: PL/SQL help
1. there isn't a host command in PL/SQL. Read up on external procedures for this. You need to be painfully aware of the security implications of this. You need to ask yourself if this is really necessary. If so, you can write external procs in Java, C, or even Perl ( that last one isn't well documented... yet ) 2. DBMS_OUTPUT: read up on the docs for it. there's a built in procedure for this, dbms_output.enable. Jared k k [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/22/02 01:13 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:PL/SQL help Hello, How I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ?? Also is there a way to issue set serveroutput on from within a stored procedure so that when someone run the stored procedure, the stored procedure output will be seen even if the person forgot to set serveroutput on for their sql*plus session ?? TIA K _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- 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: 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: PL/SQL help
You can't SET SERVEROUTPUT ON from within a PL/SQL block, because that's a SQL*Plus command, not a PL/SQL function. You *can* invoke dbms_output.enable(), but that still doesn't set the flag within SQL*Plus that tells SQL*Plus to fetch and display the output. If you tend to forget a lot, you could issue the SET SERVEROUTPUT ON command from your SQL*Plus login file. I know of no way to directly execute commands from within a PL/SQL block. I have heard of using DBMS_PIPE to send commnands to a daemon (written in a language such as C) that executes commands on your behalf. I've never actually done that myself, but I can see where it's possible. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com Friday, February 22, 2002, 4:13:19 PM, you wrote: kk I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ?? kk Also is there a way to issue set serveroutput on from within a stored kk procedure so that when someone run the stored procedure, the stored kk procedure output will be seen even if the person forgot to set serveroutput kk on for their sql*plus session ?? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick 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: PL/SQL help
Re the use of PIPE's to execute commands: There's a page on my website on using DBMS_PIPE and perl for debugging PL/SQL. A slight change is all that's need to make it execute a command. Simple to do, and fraught with security implications. http://www.cybcon.com/~jkstill/util/debug_pipe/debug_pipe.html Jared Jonathan Gennick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/22/02 02:28 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: PL/SQL help You can't SET SERVEROUTPUT ON from within a PL/SQL block, because that's a SQL*Plus command, not a PL/SQL function. You *can* invoke dbms_output.enable(), but that still doesn't set the flag within SQL*Plus that tells SQL*Plus to fetch and display the output. If you tend to forget a lot, you could issue the SET SERVEROUTPUT ON command from your SQL*Plus login file. I know of no way to directly execute commands from within a PL/SQL block. I have heard of using DBMS_PIPE to send commnands to a daemon (written in a language such as C) that executes commands on your behalf. I've never actually done that myself, but I can see where it's possible. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com Friday, February 22, 2002, 4:13:19 PM, you wrote: kk I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ?? kk Also is there a way to issue set serveroutput on from within a stored kk procedure so that when someone run the stored procedure, the stored kk procedure output will be seen even if the person forgot to set serveroutput kk on for their sql*plus session ?? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick 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: 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: PL/SQL help
I do not know a way to enable DBMS from within a procedure either. The
second item Jared mentions does not seem to set the session to recieve
output. I just ran a check to see and the 'enable' portion of DBMS did not
set the toggle the session for output.
I used this simple script
Begin
dbms_output.enable(1);
dbms_output.put_line('Jared is wrong!');
end;
/
IF you run this script you will recieve the procedure completed successfully
message. If you the issue a 'set serveroutput on' and run again you will
recieve the output text twice.
If anyone knows how get DBMS to output without the serveroutput option being
explicitly set I would love to hear it.
Alec
-Original Message-
[EMAIL PROTECTED]
Sent: Friday, February 22, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L
1. there isn't a host command in PL/SQL. Read up on external
procedures for this. You need to be painfully aware of the
security implications of this. You need to ask yourself if this
is really necessary. If so, you can write external procs in
Java, C, or even Perl ( that last one isn't well documented... yet )
2. DBMS_OUTPUT: read up on the docs for it. there's a
built in procedure for this, dbms_output.enable.
Jared
k k [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
02/22/02 01:13 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:PL/SQL help
Hello,
How I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ??
Also is there a way to issue set serveroutput on from within a stored
procedure so that when someone run the stored procedure, the stored
procedure output will be seen even if the person forgot to set
serveroutput
on for their sql*plus session ??
TIA
K
_
Join the world's largest e-mail service with MSN Hotmail.
http://www.hotmail.com
--
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:
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: Alec Macdonell
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: PL/SQL help
Begin
dbms_output.enable(1);
dbms_output.put_line('Jared is wrong!');
end;
/
Oh well, ain't the first time. :)
I always put 'set serveroutput on size 100'
in glogin.sql
Jared
Alec Macdonell [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
02/22/02 04:33 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: PL/SQL help
I do not know a way to enable DBMS from within a procedure either. The
second item Jared mentions does not seem to set the session to recieve
output. I just ran a check to see and the 'enable' portion of DBMS did not
set the toggle the session for output.
I used this simple script
Begin
dbms_output.enable(1);
dbms_output.put_line('Jared is wrong!');
end;
/
IF you run this script you will recieve the procedure completed
successfully
message. If you the issue a 'set serveroutput on' and run again you will
recieve the output text twice.
If anyone knows how get DBMS to output without the serveroutput option
being
explicitly set I would love to hear it.
Alec
-Original Message-
[EMAIL PROTECTED]
Sent: Friday, February 22, 2002 2:09 PM
To: Multiple recipients of list ORACLE-L
1. there isn't a host command in PL/SQL. Read up on external
procedures for this. You need to be painfully aware of the
security implications of this. You need to ask yourself if this
is really necessary. If so, you can write external procs in
Java, C, or even Perl ( that last one isn't well documented... yet )
2. DBMS_OUTPUT: read up on the docs for it. there's a
built in procedure for this, dbms_output.enable.
Jared
k k [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
02/22/02 01:13 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:PL/SQL help
Hello,
How I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ??
Also is there a way to issue set serveroutput on from within a stored
procedure so that when someone run the stored procedure, the stored
procedure output will be seen even if the person forgot to set
serveroutput
on for their sql*plus session ??
TIA
K
_
Join the world's largest e-mail service with MSN Hotmail.
http://www.hotmail.com
--
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:
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: Alec Macdonell
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:
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: PL/SQL help
i had similar situation, 1. wrote .sql script which has 'set serveroutput on 'in it 2.set it in glogin.sql -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Saturday, February 23, 2002 3:39 AM To: Multiple recipients of list ORACLE-L Subject: Re: PL/SQL help 1. there isn't a host command in PL/SQL. Read up on external procedures for this. You need to be painfully aware of the security implications of this. You need to ask yourself if this is really necessary. If so, you can write external procs in Java, C, or even Perl ( that last one isn't well documented... yet ) 2. DBMS_OUTPUT: read up on the docs for it. there's a built in procedure for this, dbms_output.enable. Jared k k [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/22/02 01:13 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:PL/SQL help Hello, How I can i issue host commands from within a PL/SQL block in 8i(8.1.7) ?? Also is there a way to issue set serveroutput on from within a stored procedure so that when someone run the stored procedure, the stored procedure output will be seen even if the person forgot to set serveroutput on for their sql*plus session ?? TIA K _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- 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: 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: Ghadge,Sameer 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: Pl/SQL help - please help me
PROCEDURE A as
stack_info VARCHAR2(4096);
BEGIN
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('Start-B -'||lv_time);
B;
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('End-B -'||lv_time);
EXCEPTION
WHEN OTHERS THEN
stack_info:=DBMS_UTILITY.FORMAT_CALL_STACK;
dbms_output.put_line(stack_info);
END;
-
PROCEDURE B as
stack_info VARCHAR2(4096);
BEGIN
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('Start-C -'||lv_time);
C;
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('End-C -'||lv_time);
EXCEPTION
WHEN OTHERS THEN
stack_info:=DBMS_UTILITY.FORMAT_CALL_STACK;
dbms_output.put_line(stack_info);
END;
--
PROCEDURE C as
stack_info VARCHAR2(4096);
i NUMBER :=0;
BEGIN
-- Division By Zero will Rise
INSERT INTO ttt VALUES(7/0);
EXCEPTION WHEN OTHERS THEN
stack_info:=DBMS_UTILITY.FORMAT_CALL_STACK;
dbms_output.put_line(stack_info);
- The error code _The errormessage
dbms_output.put_line(TO_CHAR(SQLCODE)||'-'||SQLERRM);
END;
- pick out the name of the procedure that is currently running,
- pick out the start_time of the procedure
- pick out the end_time of the procedure
This eg. will do Above Question.
set serveroutput on
BEGIN
lv_time varchar2(60);
DECLARE
BEGIN
-- Procedure A Start Time
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('Start- A -'||lv_time);
A
lv_time:=lv_time:= to_char(sysdate,'DD/MON/ HH:MI:SS AM');
dbms_output.put_line('End- A -'||lv_time);
-- Procedure A Start Time
END;
END;
-
CREATE TABLE ttt
(
f1 NUMBER
)
Nitheesh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Pullikol Kumar
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: Pl/SQL help - please help me
I suggest you go buy yourself a good PL/SQL manual. Nothing that you are asking for here is all that complicated. I recommend the O'Reilly book The Oracle PL/SQL CD Bookshelf. -Original Message- [EMAIL PROTECTED] Sent: Sunday, January 27, 2002 9:15 PM To: Multiple recipients of list ORACLE-L Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to write the code, please. Thanks in advance Roland -- 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: Kimberly Smith 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).
Pl/sql help needed
Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to do this, please. Thanks in advance Roland -- 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).
Pl/sql help needed
Hallo, anyone who canhelp me with this? I have PL/sql procedure and if something goes wrong I would like the following things to occur. Please help me with them If some errors occur I want this to happen. - pick out the name of the procedure thatis currently running, -pick out the start_time of the procedure - pick outthe end_time of the procedure when it fails - pick out the number of rows that were inserted inthe insertstatement inthe procedure. - The error code _Th errormessage All these things I want to be inserted in a table. Give me a good example on how to do this, please. Thanks in advance Roland -- 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).
Outer join sql help?
This sql doesn't give me the additional rows I'm expecting.. Is this because of the
sum and group by expressions? Is there a way around this behaviour?
Oracle 8.1.6
SELECT
a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE a.LEAVE_TYPE_CD(+) = l.evnt_type_cd AND l.INTERNAL_EMPL_ID='000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Steve
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: Outer join sql help?
Steve,
Generally, Sum and Group by functions do not prevent data from being
returned.
I'm not sure what you mean by not giving you the rows you expect. I would
look closely at the where clause to be sure you are selecting all the
records you want to get.
Select the rows without the group by to see what records seem to be missing.
Look especially closely at the date columns. Your BETWEEN clause only
selects dates with time stamps between 1/1/2001 00:00:00 and 12/31/2001
00:00:00 - note that records with dates of 12/31 will not be selected if
they have a time stamp 0. You might try changing the between clause to:
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001 235959','MM/DD/ hh24miss')
Hope this helps
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, December 12, 2001 12:42 PM
To: Multiple recipients of list ORACLE-L
This sql doesn't give me the additional rows I'm expecting.. Is this because
of the sum and group by expressions? Is there a way around this behaviour?
Oracle 8.1.6
SELECT
a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE a.LEAVE_TYPE_CD(+) = l.evnt_type_cd AND
l.INTERNAL_EMPL_ID='000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Steve
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: Mercadante, Thomas F
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: Outer join sql help?
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE a.LEAVE_TYPE_CD(+) = l.evnt_type_cd
AND l.INTERNAL_EMPL_ID='000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
This should work if you are looking for LEAVE_DETL_TBL records without
records in EFF_LEVPOL_EVNT_TYPE, which does not sound right.
I'm thinking you want the a records even when there is no supporting l
records:
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
But then the date range gives you a problem, so:
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(decode(sign(l.EFFECTIVE_DT -
TO_DATE('01/01/2001','MM/DD/'),
1,0,NULL,0,
decode(l.EFFECTIVE_DT -
TO_DATE('12/31/2001','MM/DD/'),
1, 0 , l.ORIGINAL_INPUT_AM
) ) )
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
Nasty - or is my initial guess off base?
Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Norrell, Brian
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: Outer join sql help?
I want to show all the available leave types in table a even if there are no records
in table l
-- Original Message --
Reply-To: [EMAIL PROTECTED]
Date: Wed, 12 Dec 2001 10:15:33 -0800
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE a.LEAVE_TYPE_CD(+) = l.evnt_type_cd
AND l.INTERNAL_EMPL_ID='000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
This should work if you are looking for LEAVE_DETL_TBL records without
records in EFF_LEVPOL_EVNT_TYPE, which does not sound right.
I'm thinking you want the a records even when there is no supporting l
records:
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
But then the date range gives you a problem, so:
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(decode(sign(l.EFFECTIVE_DT -
TO_DATE('01/01/2001','MM/DD/'),
1,0,NULL,0,
decode(l.EFFECTIVE_DT -
TO_DATE('12/31/2001','MM/DD/'),
1, 0 , l.ORIGINAL_INPUT_AM
) ) )
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
Nasty - or is my initial guess off base?
Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Norrell, Brian
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: Johnston, Steve
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).
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).
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).
sql help --urgent
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).
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: MINUS--Sql help
The MINUS operation is prety straight forward. The result set from query 1 is compared to the result set of query 2 and any records that exist in both are removed. With this in mind, if your first query and your second query result sets have NO RECORDS IN COMMON then you will ONLY get the records from your first queries result set. Kevin -Original Message- Sent: Thursday, July 12, 2001 5:47 PM To: Multiple recipients of list ORACLE-L I am writing a SQL query using the MINUS operator.Both the select statements return a number result and I am trying to get the o/p with the difference of both the queries using the MINUS between the two select statements.But I am getting the o/p of the first query only and the displayed result is not the subtracted value.Both select statements give different number o/p when run individually.What could be wrong. Is there anything that i am missing to consider when using the MINUS operator. Pls help 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: 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).
MINUS--Sql help
I am writing a SQL query using the MINUS operator.Both the select statements return a number result and I am trying to get the o/p with the difference of both the queries using the MINUS between the two select statements.But I am getting the o/p of the first query only and the displayed result is not the subtracted value.Both select statements give different number o/p when run individually.What could be wrong. Is there anything that i am missing to consider when using the MINUS operator. Pls help 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).
Re: MINUS--Sql help
Rivandra, The minus operator is a set operator. It subtracts one result set from another. That is, the rows from the second result set that exist in the first result set are removed from the first result set, and the remaining rows are returned. To substract one sql numeric result from another, use sqlplus variables. col a new_value a_hold select 15 a from dual; -- a_hold now contains 15 select 25 - a_hold from dual; will provide the desired result. hth, Yosi Ravindra Basavaraja wrote: I am writing a SQL query using the MINUS operator.Both the select statements return a number result and I am trying to get the o/p with the difference of both the queries using the MINUS between the two select statements.But I am getting the o/p of the first query only and the displayed result is not the subtracted value.Both select statements give different number o/p when run individually.What could be wrong. Is there anything that i am missing to consider when using the MINUS operator. Pls help 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: Yosi Greenfield 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).
