Re: Join , Where
Oracle 9i added SQL 1999 support, which includes OUTER JOIN. In SQL 92 syntax the where clause can have both the join relationships and filter conditions. In SQL 1999 syntax the where clause only contains filter conditions. Note: Oracle 9i allows both SQL92 and SQL 1999 syntax to peacefully coexist in the same statement (crazy to read). Using the Oracle 9i HR example schema this query works: SELECT country_name , postal_code , department_name , employee_id , job_title FROM countries LEFT OUTER JOIN locations ON countries.country_id = locations.country_id LEFT OUTER JOIN departments ON locations.location_id = departments.location_id LEFT OUTER JOIN employees ON employees.department_id = departments.department_id AND employees.manager_id = departments.manager_id LEFT OUTER JOIN jobs ON jobs.job_id = employees.job_id Along the way I noticed that changing LEFT to RIGHT on a line or two caused an intersection to occur in the result set (effectively becoming an INNER JOIN). Just wondering if this from clause works: FROM polinesrc LEFT OUTER JOIN poline ON polinesrc.company = poline.company AND polinesrc.po_code = poline.po_code AND polinesrc.po_number = poline.po_number AND polinesrc.po_release = poline.po_release AND polinesrc.line_nbr = poline.line_nbr LEFT OUTER JOIN popivuf ON poline.item = popivuf.item AND poline.procure_group = popivuf.procure_group AND poline.vendor = popivuf.vendor LEFT OUTER JOIN itemloc ON poline.company = itemloc.company AND poline.location = itemloc.location AND poline.item = itemloc.item LEFT OUTER itemmast ON itemloc.item = itemmast.item Have Fun :) Teresita Castro wrote: I run the query and it run even though in Rapid SQL send the next error: unexpected token:LEFT My questions is what is the difference between where an join in Oracle, what is better and why? [EMAIL PROTECTED] 07/02/03 06:55PM Hi !! (I am using Oracle 9.2) I am trying to run the next query but I received an error in the from statement. SELECT POLINE.ITEM AS CVEART, POLINESRC.QUANTITY AS CANT_PE, ROUND( ITEMLOC.SOH_QTY - ( ITEMLOC.ALLOC_QTY + ITEMLOC.IN_PROC_QTY), 2) AS EXIS_BOD, CASE WHEN POLINE.ENT_BUY_UOM='KG' THEN 'K' WHEN POLINE.ENT_BUY_UOM='UNID' THEN 'P' WHEN SUBSTR(POLINE.ENT_BUY_UOM,1,1)='C' THEN 'C' END UNIDAD, SUBSTR(POLINESRC.REQ_LOCATION,4,2) as TIENDA, CASE SUBSTR(POLINE.ENT_BUY_UOM,1,1) WHEN SUBSTR(ITEMMAST.ALT_UOM_01,1,1) THEN ITEMMAST.ALT_UOM_CONV_01 WHEN SUBSTR(ITEMMAST.ALT_UOM_02,1,1) THEN ITEMMAST.ALT_UOM_CONV_02 WHEN SUBSTR(ITEMMAST.ALT_UOM_03,1,1) THEN ITEMMAST.ALT_UOM_CONV_03 END EL_CANT_EM, POLINE.ENT_BUY_UOM AS EL_UNID_EM, POLINE.DESCRIPTION AS EL_DESCRIP, POLINE.PURCH_MAJCL AS EL_NO_FAM, CASE POLINE.TAX_CODE WHEN 'IVA 0%' THEN POLINE.TAXBL_UNT_CST WHEN 'IVA 10%' THEN POLINE.TAXBL_UNT_CST*1.1 WHEN 'IVA 15%' THEN POLINE.TAXBL_UNT_CST*1.15 ELSE ENT_UNIT_CST END EL_COSTO, POLINESRC.REQ_DEL_DATE AS FECHA, POLINE.VENDOR AS EL_NO_PROV, ITEMLOC.USER_FIELD_N2 AS FOR_SUR, ITEMLOC.USER_FIELD3 AS TIPO_ALMAC, POLINE.PO_NUMBER AS EL_NU_PEDI, CASE POLINE.COMPANY WHEN '2000' THEN POPIVUF.PIV_USR_FLD_01 WHEN '2001' THEN POPIVUF.PIV_USR_FLD_02 WHEN '2002' THEN POPIVUF.PIV_USR_FLD_03 ELSE '0' END EL_PAC_PRO FROM POLINESRC LEFT OUTER JOIN POPIVUF RIGHT OUTER JOIN POLINE ON POPIVUF.ITEM = POLINE.ITEM AND POPIVUF.PROCURE_GROUP = POLINE.PROCURE_GROUP AND POPIVUF.VENDOR = POLINE.VENDOR ON POLINESRC.COMPANY = POLINE.COMPANY AND POLINESRC.PO_CODE = POLINE.PO_CODE AND POLINESRC.PO_NUMBER = POLINE.PO_NUMBER AND POLINESRC.PO_RELEASE = POLINE.PO_RELEASE AND POLINESRC.LINE_NBR = POLINE.LINE_NBR LEFT OUTER JOIN ITEMLOC LEFT OUTER JOIN ITEMMAST ON ITEMLOC.ITEM = ITEMMAST.ITEM ON POLINE.COMPANY = ITEMLOC.COMPANY AND POLINE.LOCATION = ITEMLOC.LOCATION AND POLINE.ITEM = ITEMLOC.ITEM WHERE POLINE.PO_CODE='01' AND POLINESRC.REQ_DEL_DATE = sysdate ORDER BY POLINESRC.REQ_DEL_DATE What I was trying to do is made the next joins ( the first row are the tables, next rows are the fields of the join) POPOIVUF-POLINE ( all rows from POLINE) PROCURE_GROUP ITEM VENDOR POLINESRC-POLINE (all rows from POLINESRC) COMPANY PO_CODE PO_NUMBER PO_RELEASE LINE_NBR ITEMLOC-POLINE (all rows from POLINE) COMPANY LOCATION ITEM ITEMLOC-ITEMMAST (all rows from ITEMLOC) ITEM I tried to do this whit TOAD Modeler, but it used where instead of join. In Oracle are the same? WHERE ((poline.company = pol.company) AND (poline.po_code = pol.po_code) AND (poline.po_number = pol.po_number) AND (poline.po_release = pol.po_release) AND
Re: [PMX:##] Re: cannot disable or drop the ON DATABASE trigger !
D'oh! Shoulda thought of that. It's standard procedures on upgrades. Thanks Mladen. On Friday 04 July 2003 11:29, Mladen Gogala wrote: Parameter to disable database triggers is: _system_trig_enabled = FALSE This is 9i parameter. Alternatively, if you start the database up in the migrate mode, system triggers will be disable (startup migrate is what you need) On 2003.07.04 10:54, Jamadagni, Rajendra wrote: This will teach you that you should be _very_ careful when dealing with sys schema. what's that parameter to disable sys triggers ?? 1. Call OWS 2. If this is production DB, keep your resume ready. Raj -Original Message- Sent: Thursday, July 03, 2003 1:39 PM To: Multiple recipients of list ORACLE-L At first glance it appears that you have created a self referencing trigger. You can't drop it, because dropping it fires the trigger. Check metalink, open a TAR, etc. Jared rahul sharma [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/03/2003 03:40 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:cannot disable or drop the ON DATABASE trigger Help list... i have created the following in 8.1.5 instance CREATE OR REPLACE TRIGGER DDL_TRIGGER BEFORE CREATE OR ALTER OR DROP ON DATABASE BEGIN null; END; / and now i cannot drop or disable this trigger ERROR at line 1: ORA-04045: errors during recompilation/revalidation of SYS.DDL_TRIGGER i'm loggin into svrmgrl and connecting as internal to try drop or disable.. is this a bug ?? TIA Rahul.. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: rahul sharma 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: 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. *2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: sql loader question
Thursday, July 3, 2003, 5:40:37 AM, you wrote: bnini SQL*Loader reads a set of records from a file, generates INSERT bnini commands, This is the key right here. A conventional path load generates INSERT statements. A direct path load does not. A direct-path load passes preformatted blocks to the database. The term direct path comes from the fact that SQL*Loader (the client) formats a database block and sends it to the server to be written directly to the disk. No SQL processing is involved. bnini Answer : Direct-path load , ( It is said free blocks which implies bnini Direct-Path Load, because Conventional-Path Load also use bnini partially filled blocks. ) Was this from an Oracle exam? I hope not. It's ironic the person writing the test intended for you to notice the subtle mention of free blocks when they themselves didn't realize that direct path loads do not generate INSERT statements. bnini Shouldn't it be conventional path load, because insert commands are bnini being used . Yes. You are correct. 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).
Re: Performance
Dear Gaja, With your many splendid deeds in the name of performance management (my personal favorite being the fantastic "Compulsive Tuning Disorder" diagnosis) it's only fair that your name will - until something better comes along - be synonymous (spelling?) with the best RAID-technology available today... RAID-10... RAID-IO RAIDIO RADIO Gaja. Although it seems unlikely at the moment, I think there will be other musicals with you in the leading role. How about a musical about a finacially troubled radio station - the Hit Radio - and a man called Buster, who just doesn't quit, and decides to fight for the survival of real music? We could call it Buster Cash Hit Radio or just BCHR among friends. We could do musicals about "9i(nch), Rich and famous". About "United StatsPacks". About "Real Abbs Costner". And of course the most difficult of them all: "i Am Sorry". Mogens Gaja Krishna Vaidyanatha wrote: Dear Mogens, Wow!!! How creative you and Pete have gotten with my last name!!! Amazing indeed! I bet my Dad suffered from a few hiccups in the past few days. I am still cooking up something "big" on you my friend - Mogens (pronounced as Moens) Nrgaard (just skip the consanants in the middle and you will pronounce it as...something like Noa), which may sound about right. Anyways, there are not very many people whose firstname sound like faucets and whose last name require the use of "special characters" in the keyboard;-) You have that honor my friend from Denmark, oh Great Dane. At any rate, glad to be the source of some humor to the community at large;-). To the list: --- BTW, the rumours are true, I have agreed to "star" in the BAARF Musical. Yes, it is time I pursued my other career interests. It is kind of apt I think, given that I wrote a paper about the "specific application" of RAID 3 in a data warehouse environment, almost 3 years ago. It is only natural for us in the technical community to pretty much negate everything we say, and contradict ourselves once in 3 years...;-) Cheers, Gaja --- Mogens_Nrgaard [EMAIL PROTECTED] wrote: It's true. Gaja Vayanahneenahneeheyheyheyhey (sp?) will star in "BAARF. The Musical." Also - this might come as a surprise to Pete Sharman - the OakTable Choir will be part of the musical, and Pete (who, by the way, is a fantastic baryton singer) will sing one verse of a certain song (lyrics slightly re-written), while Lex de Haan (another excellent singer) will sing the second and Bjrn Engsig (if he participates) the third verse. Other participants include a group known as FEVER - Five Evil Vendor's Eternal Rotherhood - and of course the members of the BAARF Party, who teach Gaja Vahatneyhatneyhatney that Parity Is Pain. There'll be a sound-proofed special area for people playing air guitar, too. It will rock. James Morle and I are still working on the details, but Gaja has (very bravely) accepted the role in the musical. Respect to him. We're still looking for a heroine. Mogens Pete Sharman wrote: Surely not? I was told just last week by the inestimable Mr. Mogens Norgaard (who we all know as the source of truth) that Gaja's real name is Gaja Vaidyanathanathanatha, and he's about to take on a top-secret new career (details to be revealed at the DB Forum in Denmark in October) ... :) Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA. -Original Message- Rachel Carmichael Sent: Friday, June 27, 2003 6:19 PM To: Multiple recipients of list ORACLE-L Mladen, As one of the authors of DBA 101, I appreciate your plugging my book for me. But Gaja Vaidyanatha (correct spelling) and Kirtikumar Deshpande (both on this list) are the authors of the book I think you meant to talk about: Performance Tuning 101. Marlene (has she now moved onto single name status, like Cher and Elvis?) is my co-author, along with James Viscusi Rachel --- "Gogala, Mladen" [EMAIL PROTECTED] wrote: Have you set event 10046, lev 8 for the session? If not, try setting it and then use 9.2 tkprof to see what is the instance waiting for as well to analyze the execution plans and see how they differ. Also, during the execution, you can watch v$session_wait and see what the session is waiting for. If the event is something like 'db file scattered read', then it is the
