RE: UNION ALL Query: Riddle
If what you are describing is completely accurate, ( no DML, change S_A_S fixes the problem ) then it would appear you have encountered a bug. A search on MetaLink is in order, and failing that, you need to open a TAR. Jared On Thu, 2004-01-29 at 04:59, Wendry wrote: > I have the same problem like you Rajesh, the query also gives different > rowcount each time executed eventhough there's no one updating base > tables, in my opinion it's because of the sorting operation (your group > by clause). In my case after I remove some group functions, the result > goes well. Also I reduce the use of order by clause where it's not > needed. > > I still haven't found the exact solution to this problem. But just now > I've tried to decrease the sort area size parameter value (I think I > oversize it), and run the query again, the result goes stable with the > problematic query but it runs slower. I haven't tried intensively, I try > to do that tomorrow. Meanwhile if, there's any of the Gurus can give us > clearer explanation, please do so... Thank you all in advance. > > Regards, > > Wendry. > > -Original Message- > Pillai, Rajesh > Sent: Thursday, January 29, 2004 2:24 AM > To: Multiple recipients of list ORACLE-L > > Hi Jared, > Thanks for your response. different results mean that number of > records are different sometimes, and sometimes the some of the > quantities are not correct. Your help is really appreciated. > > Thanks, > Rajesh > -Original Message- > Sent: Tuesday, January 27, 2004 2:29 PM > To: Multiple recipients of list ORACLE-L > > Q: What does "different results" mean? > > Different row count? > > Completely different data? > > Partially different data? > > Some columns have incorrect value? > > What about doing it without the parallel hints? The tables aren't > so big that it would take a long time to find out. > > Jared > > > > > "Pillai, Rajesh" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 01/27/2004 01:09 PM > Please respond to ORACLE-L > > To:Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:UNION ALL Query: Riddle > > > > Hi All, > The following query is giving different results in each > run. I assure that no data modified between consecutive runs - > INSERT /* append parallel (z,8) */ > INTO some_table > (SELECT /*parallel (a,8) */ > a.item, > a.loc, > SUM(a.qty_type_1), > SUM(a.qty_type_2) > FROM > (select /*parallel (x,8) */ >item, >loc, >qty_type_1, >to_number(NULL) > > from >table_a x > UNION ALL > select /*parallel (y,8) */ >item, >loc, >to_number(NULL), >qty_type_2 > > from >table_b y > ) a > GROUP BY > a.item, > a.loc); > > Additional info - > > Number of records in table_a and table_b is around 3M and 6M. > > SQL> select * from v$version; > > BANNER > > Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production > PL/SQL Release 8.1.7.2.0 - Production > CORE8.1.7.0.0 Production > TNS for Solaris: Version 8.1.7.2.0 - Production > NLSRTL Version 3.4.1.0.0 - Production > > I would appreciate any help in solving this mystery and all hints are > welcome. > > Thanks, > Rajesh Pillai > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Pillai, Rajesh > 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: htt
RE: UNION ALL Query: Riddle
I have the same problem like you Rajesh, the query also gives different rowcount each time executed eventhough there's no one updating base tables, in my opinion it's because of the sorting operation (your group by clause). In my case after I remove some group functions, the result goes well. Also I reduce the use of order by clause where it's not needed. I still haven't found the exact solution to this problem. But just now I've tried to decrease the sort area size parameter value (I think I oversize it), and run the query again, the result goes stable with the problematic query but it runs slower. I haven't tried intensively, I try to do that tomorrow. Meanwhile if, there's any of the Gurus can give us clearer explanation, please do so... Thank you all in advance. Regards, Wendry. -Original Message- Pillai, Rajesh Sent: Thursday, January 29, 2004 2:24 AM To: Multiple recipients of list ORACLE-L Hi Jared, Thanks for your response. different results mean that number of records are different sometimes, and sometimes the some of the quantities are not correct. Your help is really appreciated. Thanks, Rajesh -Original Message- Sent: Tuesday, January 27, 2004 2:29 PM To: Multiple recipients of list ORACLE-L Q: What does "different results" mean? Different row count? Completely different data? Partially different data? Some columns have incorrect value? What about doing it without the parallel hints? The tables aren't so big that it would take a long time to find out. Jared "Pillai, Rajesh" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/27/2004 01:09 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: UNION ALL Query: Riddle Hi All, The following query is giving different results in each run. I assure that no data modified between consecutive runs - INSERT /* append parallel (z,8) */ INTO some_table (SELECT /*parallel (a,8) */ a.item, a.loc, SUM(a.qty_type_1), SUM(a.qty_type_2) FROM (select /*parallel (x,8) */ item, loc, qty_type_1, to_number(NULL) from table_a x UNION ALL select /*parallel (y,8) */ item, loc, to_number(NULL), qty_type_2 from table_b y ) a GROUP BY a.item, a.loc); Additional info - Number of records in table_a and table_b is around 3M and 6M. SQL> select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production PL/SQL Release 8.1.7.2.0 - Production CORE 8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I would appreciate any help in solving this mystery and all hints are welcome. Thanks, Rajesh Pillai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh 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: Wendry 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
RE: UNION ALL Query: Riddle
It would be my guess that someone was doing DML on your table while you're running the first query, and you don't see the results of that until the second query. Try running your SQL statement twice in a single transaction and see if the results are the same then. eg. rollback; set transaction read only; The results should be the same. Or, you could get the old ORA-1555, if a number of changes have been made and your rollback segments can't keep up. Jared On Wed, 2004-01-28 at 11:24, Pillai, Rajesh wrote: > Hi Jared, > Thanks for your response. different results mean that number of > records are different sometimes, and sometimes the some of the > quantities are not correct. Your help is really appreciated. > > Thanks, > Rajesh > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, January 27, 2004 2:29 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: UNION ALL Query: Riddle > > > Q: What does "different results" mean? > > Different row count? > > Completely different data? > > Partially different data? > > Some columns have incorrect value? > > What about doing it without the parallel hints? The tables > aren't > so big that it would take a long time to find out. > > Jared > > > > > > "Pillai, Rajesh" > <[EMAIL PROTECTED]> > Sent by: > [EMAIL PROTECTED] > > 01/27/2004 01:09 PM > Please respond to > ORACLE-L > > > > To: > Multiple recipients > of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject: > UNION ALL Query: > Riddle > > > Hi All, > The following query is giving different > results in each run. I assure that no data modified between > consecutive runs - > INSERT /* append parallel (z,8) */ > INTO some_table > (SELECT /*parallel (a,8) */ > a.item, > a.loc, > SUM(a.qty_type_1), > SUM(a.qty_type_2) > FROM > (select /*parallel (x,8) */ >item, >loc, >qty_type_1, >to_number(NULL) > from >table_a x > UNION ALL > select /*parallel (y,8) */ >item, >loc, >to_number(NULL), >qty_type_2 > from >table_b y > ) a > GROUP BY > a.item, > a.loc); > > Additional info - > > Number of records in table_a and table_b is around 3M and 6M. > > SQL> select * from v$version; > > BANNER > > Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production > PL/SQL Release 8.1.7.2.0 - Production > CORE8.1.7.0.0 Production > TNS for Solaris: Version 8.1.7.2.0 - Production > NLSRTL Version 3.4.1.0.0 - Production > > I would appreciate any help in solving this mystery and all > hints are welcome. > > Thanks, > Rajesh Pillai > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Pillai, Rajesh > INET: [EMAIL PROTECTED] > > Fa
RE: UNION ALL Query: Riddle
Hi Jared, Thanks for your response. different results mean that number of records are different sometimes, and sometimes the some of the quantities are not correct. Your help is really appreciated. Thanks, Rajesh -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 27, 2004 2:29 PMTo: Multiple recipients of list ORACLE-LSubject: Re: UNION ALL Query: RiddleQ: What does "different results" mean? Different row count? Completely different data? Partially different data? Some columns have incorrect value? What about doing it without the parallel hints? The tables aren't so big that it would take a long time to find out. Jared "Pillai, Rajesh" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/27/2004 01:09 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: UNION ALL Query: RiddleHi All, The following query is giving different results in each run. I assure that no data modified between consecutive runs - INSERT /* append parallel (z,8) */ INTO some_table (SELECT /*parallel (a,8) */ a.item, a.loc, SUM(a.qty_type_1), SUM(a.qty_type_2) FROM (select /*parallel (x,8) */ item, loc, qty_type_1, to_number(NULL) from table_a x UNION ALL select /*parallel (y,8) */ item, loc, to_number(NULL), qty_type_2 from table_b y ) a GROUP BY a.item, a.loc);Additional info - Number of records in table_a and table_b is around 3M and 6M.SQL> select * from v$version;BANNEROracle8i Enterprise Edition Release 8.1.7.2.0 - ProductionPL/SQL Release 8.1.7.2.0 - ProductionCORE 8.1.7.0.0 ProductionTNS for Solaris: Version 8.1.7.2.0 - ProductionNLSRTL Version 3.4.1.0.0 - ProductionI would appreciate any help in solving this mystery and all hints are welcome.Thanks,Rajesh Pillai-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Pillai, Rajesh INET: [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).
Re: UNION ALL Query: Riddle
Q: What does "different results" mean? Different row count? Completely different data? Partially different data? Some columns have incorrect value? What about doing it without the parallel hints? The tables aren't so big that it would take a long time to find out. Jared "Pillai, Rajesh" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/27/2004 01:09 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: UNION ALL Query: Riddle Hi All, The following query is giving different results in each run. I assure that no data modified between consecutive runs - INSERT /* append parallel (z,8) */ INTO some_table (SELECT /*parallel (a,8) */ a.item, a.loc, SUM(a.qty_type_1), SUM(a.qty_type_2) FROM (select /*parallel (x,8) */ item, loc, qty_type_1, to_number(NULL) from table_a x UNION ALL select /*parallel (y,8) */ item, loc, to_number(NULL), qty_type_2 from table_b y ) a GROUP BY a.item, a.loc); Additional info - Number of records in table_a and table_b is around 3M and 6M. SQL> select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production PL/SQL Release 8.1.7.2.0 - Production CORE 8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I would appreciate any help in solving this mystery and all hints are welcome. Thanks, Rajesh Pillai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh 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: "union all" problems
Yes, this is a trace file that contains ORA-07445: exception encountered: core dump... - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Thursday, July 31, 2003 3:39 PM Subject: RE: "union all" problems What does the trace file says on the server ??? do you see a ora-7445 trace file? 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: Ed Lewis [mailto:[EMAIL PROTECTED]Sent: Thursday, July 31, 2003 2:59 PMTo: Multiple recipients of list ORACLE-LSubject: "union all" problems Hi, We have a query which uses a "union all". After upgrading to a patch release of Oracle this query no longer works. We get the following error : ERROR at line 1:ORA-03113: end-of-file on communication channel ORA-24323: value not allowedError accessing package DBMS_APPLICATION_INFOERROR:ORA-03114: not connected to ORACLE I've searched metalink, but have been unsuccessful finding a solution. As a quick fix, we changed the "union all" to a "union", and that worked. We'll still like to find the root cause though. The environment is AIX 4.3.3. It worked with Oracle 8.1.7.2, but after upgrading to 8.1.7.4 we get this error. Has anyone experienced this ? thanks. ed
RE: "union all" problems
What does the trace file says on the server ??? do you see a ora-7445 trace file? 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: Ed Lewis [mailto:[EMAIL PROTECTED]Sent: Thursday, July 31, 2003 2:59 PMTo: Multiple recipients of list ORACLE-LSubject: "union all" problems Hi, We have a query which uses a "union all". After upgrading to a patch release of Oracle this query no longer works. We get the following error : ERROR at line 1:ORA-03113: end-of-file on communication channel ORA-24323: value not allowedError accessing package DBMS_APPLICATION_INFOERROR:ORA-03114: not connected to ORACLE I've searched metalink, but have been unsuccessful finding a solution. As a quick fix, we changed the "union all" to a "union", and that worked. We'll still like to find the root cause though. The environment is AIX 4.3.3. It worked with Oracle 8.1.7.2, but after upgrading to 8.1.7.4 we get this error. Has anyone experienced this ? thanks. ed *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: "union all" problems
Hi! I haven't seen this issue before, but it seems like a bug. Check for .trc files in your user_dump_dest. The contents are probably quite cryptic, but you can send it to Support through Metalink. Tanel. - Original Message - From: Ed Lewis To: Multiple recipients of list ORACLE-L Sent: Thursday, July 31, 2003 9:59 PM Subject: "union all" problems Hi, We have a query which uses a "union all". After upgrading to a patch release of Oracle this query no longer works. We get the following error : ERROR at line 1:ORA-03113: end-of-file on communication channel ORA-24323: value not allowedError accessing package DBMS_APPLICATION_INFOERROR:ORA-03114: not connected to ORACLE I've searched metalink, but have been unsuccessful finding a solution. As a quick fix, we changed the "union all" to a "union", and that worked. We'll still like to find the root cause though. The environment is AIX 4.3.3. It worked with Oracle 8.1.7.2, but after upgrading to 8.1.7.4 we get this error. Has anyone experienced this ? thanks. ed
Re: Union quries: INTERSECT, MINUS, etc
Jonathan, We use MINUS technique heavily in our DW environment to get the source system changes since we last extracted. We do 2-way minus (src to ods and ods to src). Best Regards, Prasad -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Union quries: INTERSECT, MINUS, etc
There's nothing like a good high colonic to make you want to have kids! > -Original Message- > > not very slick but I used MINUS yesterday to find parents with no > children so as to purge them > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: Union quries: INTERSECT, MINUS, etc
not very slick but I used MINUS yesterday to find parents with no children so as to purge them we do this a lot in this 3rd party app. RI is sketchy at best and the app blows up if childless parents exists --- [EMAIL PROTECTED] wrote: > > Jonathan, > > I've used MINUS heavily in sql scripts and pl/sql > to determine the differences in schemas: both > structure and data. > > Of interest to DBA's and developers, and least when I > did it it was for the developers. > > Jared > > > On Thu, 24 Jul 2003, Jonathan Gennick wrote: > > > I'm doing research for an article on union queries. I'm > > interested in finding examples of problems that were solved > > using UNION, UNION ALL, INTERSECT, or MINUS, with the latter > > two being of special interest because I don't see them used > > very often. If you can think of an interesting problem > > you've solved using one of these keywords, I'd love to hear > > about it. > > > > Best regards, > > > > Jonathan Gennick --- Brighten the corner where you are > > http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] > > > > Join the Oracle-article list and receive one > > article on Oracle technologies per month by > > email. To join, visit > http://four.pairlist.net/mailman/listinfo/oracle-article, > > or send email to [EMAIL PROTECTED] and > > include the word "subscribe" in either the subject or body. > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: > 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). __ 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: Rachel Carmichael 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: Union quries: INTERSECT, MINUS, etc
Jonathan, I've used MINUS heavily in sql scripts and pl/sql to determine the differences in schemas: both structure and data. Of interest to DBA's and developers, and least when I did it it was for the developers. Jared On Thu, 24 Jul 2003, Jonathan Gennick wrote: > I'm doing research for an article on union queries. I'm > interested in finding examples of problems that were solved > using UNION, UNION ALL, INTERSECT, or MINUS, with the latter > two being of special interest because I don't see them used > very often. If you can think of an interesting problem > you've solved using one of these keywords, I'd love to hear > about it. > > Best regards, > > Jonathan Gennick --- Brighten the corner where you are > http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] > > Join the Oracle-article list and receive one > article on Oracle technologies per month by > email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, > or send email to [EMAIL PROTECTED] and > include the word "subscribe" in either the subject or body. > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Union quries: INTERSECT, MINUS, etc
Jonathan, I can't think of any specific examples but the four operators all have their place: UNION - A quick way to merge result sets. If, for example, you have actual financial data in one table and budget financial data in another table and need to spool all data to a file then UNION is an easy way to merge the two tables into a single cursor. I guess a natural alternative would be a view - but this then breaks the statement up into two statements SELECT (with selection criteria) and VIEW (which will effectively hide the underlying tables from the main query) - making maintenance worse but could be useful if the tables are joined often. UNION ALL - More significant when you may be deliberately creating duplicate records and need to show both records or when you know that no duplicates will be created and can therefore save on a sort operation. My first example would be better implemented as union all since "actual" and "budget" being extracted as constants from each table ensures no overlap. INTERSECT - Can often be used in the same scenario's as "WHERE EXISTS" or "IN" but may allow more complex conditions to be compared. MINUS - Can often be used to implement complex "WHERE NOT EXISTS" or "NOT IN". For example, a "student" table may hold "number_of_enrolled_subjects" and a "studentsubject" table may map students to subjects... If you need to return the students which have this attribute set incorrectly (ie: corrupt data) then a simple MINUS query can compare the attribute to the COUNT(*) from "studentsubject". I think the important thing to remember is that all of these operations can normally be accomplished using different SQL syntax. The decision comes down to a couple of factors: 1) Maintenance - some ways of writing a query may represent the underlying logic much easier. MINUS, for example, can break a complex statement down into two simpler queries which may make their purpose easier to understand. UNION may negate the need for a view - which can be a good or bad thing depending on other factors. 2) Execution approach. Often the above operators are resolved using a sort - the volume of records in each side of the query and configuration of your database may make this desirable, or it may not. "WHERE EXISTS", on the other hand will normally be resolved using nested loops or hash joins. With small recordsets (not necessarily the final resultset since two of these operators are effectively data filters) the approach probably doesn't matter, but as data volumes and performance demands increase the decision can be significant. Hopefully this has added some food for thought. Jonathan Gennick <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> .com>cc: Sent by: Subject: Union quries: INTERSECT, MINUS, etc [EMAIL PROTECTED] .com 24/07/2003 23:04 Please respond to ORACLE-L I'm doing research for an article on union queries. I'm interested in finding examples of problems that were solved using UNION, UNION ALL, INTERSECT, or MINUS, with the latter two being of special interest because I don't see them used very often. If you can think of an interesting problem you've solved using one of these keywords, I'd love to hear about it. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gen
Re: Union quries: INTERSECT, MINUS, etc
Jonathon, I've got a query for you that uses all 3 set operators at once! I wrote it to compare two different versions of our 3rd Party Student Information System (SASI) in two different databases. We were getting ready to upgrade Production, having already upgraded a Test instance. The query hit the local schema, as well as the remote schema across a DB Link. The results of this query and a couple of others that showed brand-new tables/columns and dropped tables/columns helped our programmers figure out which of their reports, etc. needed modifications. I was impressed at performance, considering it queried across a DB Link, but mainly because this horrendous mess of an application has over 50,000 tables (User_Tab_Columns has over 1.4 million rows!). One of the DBs is on HP-UX, but the Production DB is on Win2k. Anyway, hope this is interesting enough. ;-) BTW, if you can find a way to improve it, please let me know. I sort of "threw it together", knowing it would be a one-time thing, so it could probably be made better with some expert critique. /* Get a list of columns that have changed from SASI 4.5 to 5.0 for tables that are present in both versions only for the current school year. List only the first 4 characters of the table names, since all campuses will be the same. */ Spool SASI_45_50_Table_Compare.txt ( Select Substr(TABLE_NAME,1,4) "Table" -- New 5.0 Columns ,COLUMN_NAME "Column" ,'5.0'"Ver" ,DATA_TYPE"DType" ,DATA_LENGTH "DLn" ,DATA_PRECISION "DPr" ,DATA_SCALE "DSc" ,NULLABLE "N?" >From User_Tab_Columns Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) <> 'D' AndTable_Name In ( Select Table_Name --...for Tables in both 4.5 and 5.0 From User_Tables Intersect Select Table_Name From [EMAIL PROTECTED] ) Minus --...remove unchanged columns ( Select Substr(TABLE_NAME,1,4) ,COLUMN_NAME ,'5.0' -- Constant allows Minus to work ,DATA_TYPE ,DATA_LENGTH ,DATA_PRECISION ,DATA_SCALE ,NULLABLE From User_Tab_Columns Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) <> 'D' Intersect Select Substr(TABLE_NAME,1,4) ,COLUMN_NAME ,'5.0' -- Constant allows Minus to work ,DATA_TYPE ,DATA_LENGTH ,DATA_PRECISION ,DATA_SCALE ,NULLABLE From [EMAIL PROTECTED] Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) <> 'D' ) ) Union ( Select Substr(TABLE_NAME,1,4) "Table"-- Old 4.5 columns... ,COLUMN_NAME "Column" ,'4.5'"Ver" ,DATA_TYPE"DType" ,DATA_LENGTH "DLn" ,DATA_PRECISION "DPr" ,DATA_SCALE "DSc" ,NULLABLE "N?" >From [EMAIL PROTECTED] Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) <> 'D' AndTable_Name In ( Select Table_Name From User_Tables Intersect Select Table_Name From [EMAIL PROTECTED] ) Minus ( Select Substr(TABLE_NAME,1,4) ,COLUMN_NAME ,'4.5' ,DATA_TYPE ,DATA_LENGTH ,DATA_PRECISION ,DATA_SCALE ,NULLABLE From User_Tab_Columns Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) <> 'D' Intersect Select Substr(TABLE_NAME,1,4) ,COLUMN_NAME ,'4.5' ,DATA_TYPE ,DATA_LENGTH ,DATA_PRECISION ,DATA_SCALE ,NULLABLE From [EMAIL PROTECTED] Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) <> 'D' ) ) / Spool Off Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Jonathan Gennick <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> .com>cc: Sent by: Subject: Union quries: INTERSECT, MINUS, etc [EMAIL PROTECTED] .com
Re: Re: Union quries: INTERSECT, MINUS, etc
- Original Message - > what do you mean by 'arc'? > > have a look: http://www.docm.mmu.ac.uk/online/SAD/T07/erd2.htm much better explanation than I can give here. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: Union quries: INTERSECT, MINUS, etc
At a previous job, I used MINUS as part of a package to perform automated testing of transaction processing. Compared actual result set with expected result set via minus. IF rows returned then if failed and returned rows were written to error table for review. Worked well for what we needed it to do. David Phillips Support DBA Gasper Corp -Original Message- Sent: Thursday, July 24, 2003 9:04 AM To: Multiple recipients of list ORACLE-L I'm doing research for an article on union queries. I'm interested in finding examples of problems that were solved using UNION, UNION ALL, INTERSECT, or MINUS, with the latter two being of special interest because I don't see them used very often. If you can think of an interesting problem you've solved using one of these keywords, I'd love to hear about it. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word "subscribe" in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: Dave Phillips 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: Union quries: INTERSECT, MINUS, etc
what do you mean by 'arc'? > > From: "Nuno Souto" <[EMAIL PROTECTED]> > Date: 2003/07/24 Thu AM 09:39:29 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: Union quries: INTERSECT, MINUS, etc > > - Original Message - > > > > I'm doing research for an article on union queries. I'm > > interested in finding examples of problems that were solved > > using UNION, UNION ALL, INTERSECT, or MINUS, with the latter > > two being of special interest because I don't see them used > > very often. If you can think of an interesting problem > > you've solved using one of these keywords, I'd love to hear > > about it. > > > > UNION is useful to implement arcs. > INTERSECT I've used very successfully > with two CONNECT BY queries to retrieve all > possible paths of travel between two points > A and B in a table that implements flight legs. > So don't go around saying it isn't used: I need > it or the RAAF can't book people to flights. ;) > > Cheers > Nuno Souto > [EMAIL PROTECTED] > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Nuno Souto > 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: Union quries: INTERSECT, MINUS, etc
- Original Message - > I'm doing research for an article on union queries. I'm > interested in finding examples of problems that were solved > using UNION, UNION ALL, INTERSECT, or MINUS, with the latter > two being of special interest because I don't see them used > very often. If you can think of an interesting problem > you've solved using one of these keywords, I'd love to hear > about it. > UNION is useful to implement arcs. INTERSECT I've used very successfully with two CONNECT BY queries to retrieve all possible paths of travel between two points A and B in a table that implements flight legs. So don't go around saying it isn't used: I need it or the RAAF can't book people to flights. ;) Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: UNION
Use ORDER BY 1 (ie, the first column) At 10:31 AM 1/17/02 -0800, you wrote: >Hi, > >I try to use union and order by first column of first select statment and >also first column of second select statment but get error, Any Idea how to >do this?? > >SELECT A,B,C FROM TABLEABC >UNION >SELECT D,E,F FROM TABLEDEF >ORDER BY A,D > > > > >Hamid Alavi >Office 818 737-0526 >Cell818 402-1987 > >The information contained in this message and any attachments is intended >only for the use of the individual or entity to which it is addressed, and >may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from >disclosure under applicable law. If you have received this message in error, >you are prohibited from copying, distributing, or using the information. >Please contact the sender immediately by return e-mail and delete the >original message from your system. >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Hamid Alavi > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: UNION
SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY 1 Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, January 17, 2002 1:31 PM > Hi, > > I try to use union and order by first column of first select statment and > also first column of second select statment but get error, Any Idea how to > do this?? > > SELECT A,B,C FROM TABLEABC > UNION > SELECT D,E,F FROM TABLEDEF > ORDER BY A,D > > > > > Hamid Alavi > Office 818 737-0526 > Cell818 402-1987 > > The information contained in this message and any attachments is intended > only for the use of the individual or entity to which it is addressed, and > may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from > disclosure under applicable law. If you have received this message in error, > you are prohibited from copying, distributing, or using the information. > Please contact the sender immediately by return e-mail and delete the > original message from your system. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hamid Alavi > 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: Igor Neyman 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: UNION
Did a little testing. I think that the columns names are decided by the first select only. The union adds the rows from the second select to the result set created by the first select. That's mean that your columns are a , b , c. So order by 'a' will work. It did in my test. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: Hamid Alavi [SMTP:[EMAIL PROTECTED]] > Sent: Thu, January 17, 2002 8:31 PM > To: Multiple recipients of list ORACLE-L > Subject: UNION > > Hi, > > I try to use union and order by first column of first select statment and > also first column of second select statment but get error, Any Idea how to > do this?? > > SELECT A,B,C FROM TABLEABC > UNION > SELECT D,E,F FROM TABLEDEF > ORDER BY A,D > > > > > Hamid Alavi > Office 818 737-0526 > Cell818 402-1987 > > The information contained in this message and any attachments is intended > only for the use of the individual or entity to which it is addressed, and > may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from > disclosure under applicable law. If you have received this message in > error, > you are prohibited from copying, distributing, or using the information. > Please contact the sender immediately by return e-mail and delete the > original message from your system. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hamid Alavi > 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 was scanned by the eSafe Mail Gateway > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= 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: UNION
- Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, January 17, 2002 20:31 > > SELECT A,B,C FROM TABLEABC > UNION > SELECT D,E,F FROM TABLEDEF > ORDER BY A,D SELECT A as ord_col,B,C FROM TABLEABC UNION SELECT D as ord_col,E,F FROM TABLEDEF ORDER BY ord_col? hth, Marin "...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. " -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov 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: UNION
Try: select a,b,c from tableabc union select d,e,f from tabledef order by 1; Hamid Alavi wrote: > Hi, > > I try to use union and order by first column of first select statment and > also first column of second select statment but get error, Any Idea how to > do this?? > > SELECT A,B,C FROM TABLEABC > UNION > SELECT D,E,F FROM TABLEDEF > ORDER BY A,D > > Hamid Alavi > Office 818 737-0526 > Cell818 402-1987 > > The information contained in this message and any attachments is intended > only for the use of the individual or entity to which it is addressed, and > may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from > disclosure under applicable law. If you have received this message in error, > you are prohibited from copying, distributing, or using the information. > Please contact the sender immediately by return e-mail and delete the > original message from your system. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hamid Alavi > 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). -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 "Life is like a sewer, what you get out of it depends on what you put into it" - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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: UNION
You have to use ORDER BY column position as in SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY 1 HTH 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! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
Re: UNION
Hamid Alavi wrote:
>
> Hi,
>
> I try to use union and order by first column of first select statment and
> also first column of second select statment but get error, Any Idea how to
> do this??
>
> SELECT A,B,C FROM TABLEABC
> UNION
> SELECT D,E,F FROM TABLEDEF
> ORDER BY A,D
>
> Hamid Alavi
> Office 818 737-0526
> Cell818 402-1987
>
Hamid,
Remember that the purpose of a union is to bring back rows from
several tables as if they were coming from a single table - a bit like a
join returns columns from several tables as if they were coming from a
single table. By convention, the column names which are assigned come
from the first table in the union. In your example, columns will be
named (A,B,C) even if actually the first part of the UNION returns no
row. Syntactically, to order the output of a union you must specified
column by position number in the select list, not by name - on your
example, it will be 'order by 1' ('1' refering to A or D indistinctly).
If you always want rows from TABLEABC to be returned before rows from
TABLEDEF, you must cheat and add a dummy column :
SELECT 1 dummy, A, B, C FROM TABLEABC
UNION
SELECT 2, D, E, F FROM TABLEDEF
ORDER BY 1, 2
(you can make the dummy column disappear from the output with SQL*Plus
by defining
col dummy noprint
In a program, just ignore it).
Note that there is a drawback to the dummy column use: with a standard
UNION (as opposed to UNION ALL) if a row in the first table is strictly
identical to a row in another table from the UNION, it appears only once
(duplicates are eliminated). With a dummy column, only duplicates from
the same table can be removed.
HTH
Stephane Faroult
Oriole Ltd
--
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: UNION
Hamid, Look in the Sql manual. For UNION clauses, you must ORDER BY the item number: SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY 1 <= lookee here hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, January 17, 2002 1:31 PM To: Multiple recipients of list ORACLE-L Hi, I try to use union and order by first column of first select statment and also first column of second select statment but get error, Any Idea how to do this?? SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY A,D Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: UNION
select * from ( SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF) x ORDER BY A,D > -Original Message- > From: Hamid Alavi [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, January 17, 2002 12:31 PM > To: Multiple recipients of list ORACLE-L > Subject: UNION > > Hi, > > I try to use union and order by first column of first select statment and > also first column of second select statment but get error, Any Idea how to > do this?? > > SELECT A,B,C FROM TABLEABC > UNION > SELECT D,E,F FROM TABLEDEF > ORDER BY A,D > > > > > Hamid Alavi > Office 818 737-0526 > Cell818 402-1987 > > The information contained in this message and any attachments is intended > only for the use of the individual or entity to which it is addressed, and > may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from > disclosure under applicable law. If you have received this message in > error, > you are prohibited from copying, distributing, or using the information. > Please contact the sender immediately by return e-mail and delete the > original message from your system. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Hamid Alavi > 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: Mac Isaac, John 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: UNION
In the order by section use the relative column numbers. Plus, you can not individually order by a single column from each union. Its a comprehensive sort of the entire column. SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY 1 -Original Message- Sent: Thursday, January 17, 2002 12:31 PM To: Multiple recipients of list ORACLE-L Hi, I try to use union and order by first column of first select statment and also first column of second select statment but get error, Any Idea how to do this?? SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY A,D Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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).
