RE: Query question
asktom.oracle.com
http://asktom.oracle.com/pls/ask/f?p=4950:8:2542717627406446060::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:124812348063,
or
http://tinyurl.com/r3lk
pivot a result set
-Original Message-
Teresita Castro
I have the next query:
SELECT COMPANY, ITEM,
OEBASE.CUR_PRICE_01
FROM OEBASE
WHERE OEBASE.COMPANY IN (2000,2001,2002) AND OEBASE.BASE_NAME IN ('BASE-OCJ',
'BASE-OSI','BASE-OCR')
This returns for each item three lines
2000 0010041 12.34
2001 0010041 12.74
2002 0010041 11.99
I want one row per item, how can I do that?
0010041 12.3412.74 11.99
Put the price of each company on three different fields
--
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: Query question
Title: RE: Query question -Original Message- From: Mukesh Ghildiyal [mailto:[EMAIL PROTECTED]] I am running following query This takes forever to complete. select pay.gl_date from noetix_sys.noetix_current_period off 2 ,gl.gl_sets_of_books sob, gl.gl_periods per, 3 ar.ar_payment_schedules_all pay 4 where 5 pay.gl_date between per.start_date and per.end_date 6 and per.period_set_name||'' =sob.period_set_name 7 and per.period_type = sob.accounted_period_type 8 and sob.set_of_books_id=26 9 and off.application_id=222 10 and off.period_type=sob.accounted_period_type 11* and off.set_of_books_id=26 Now if I takeout following line and off.period_type=sob.accounted_period_type it works the column off.period_type and sob.acoount_period_type are both not null varchar2(15) type. Could anybody has some idea, why its doing that. Have you looked at the explain plan for the two queries? If not, you should probably do so.
RE: Query question
Mukesh, did you check the explain plan for both queries? -Original Message- Sent: Thursday, April 05, 2001 3:26 PM To: Multiple recipients of list ORACLE-L Hello gurus please help I am running following query This takes forever to complete. select pay.gl_date from noetix_sys.noetix_current_period off 2 ,gl.gl_sets_of_books sob, gl.gl_periods per, 3 ar.ar_payment_schedules_all pay 4 where 5 pay.gl_date between per.start_date and per.end_date 6 and per.period_set_name||'' =sob.period_set_name 7 and per.period_type = sob.accounted_period_type 8 and sob.set_of_books_id=26 9 and off.application_id=222 10 and off.period_type=sob.accounted_period_type 11* and off.set_of_books_id=26 Now if I takeout following line and off.period_type=sob.accounted_period_type it works the column "off.period_type" and "sob.acoount_period_type" are both not null varchar2(15) type. Could anybody has some idea, why its doing that. thank you very much Please help mcg __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mukesh Ghildiyal 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: Bala, Prakash INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Query question
I presume, your query is running out of indexes. Run the explain plan and check if Oracle performs any full scan into a table. Then, create an index for that particular case. If for any reason, Oracle doesn't use the index created, apply a hint ( /+ /) into your SQL, meaning that your going to force the engine to use it as a part of your execution plan. If the above still without working, I suggest to check your query, you might be able to reformulate your query by analyzing the product Cartesian that would return to you. HTH, Jordi -Original Message- From: Mukesh Ghildiyal [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 05, 2001 02:26 PM To: Multiple recipients of list ORACLE-L Subject:Query question Hello gurus please help I am running following query This takes forever to complete. select pay.gl_date from noetix_sys.noetix_current_period off 2 ,gl.gl_sets_of_books sob, gl.gl_periods per, 3 ar.ar_payment_schedules_all pay 4 where 5 pay.gl_date between per.start_date and per.end_date 6 and per.period_set_name||'' =sob.period_set_name 7 and per.period_type = sob.accounted_period_type 8 and sob.set_of_books_id=26 9 and off.application_id=222 10 and off.period_type=sob.accounted_period_type 11* and off.set_of_books_id=26 Now if I takeout following line and off.period_type=sob.accounted_period_type it works the column "off.period_type" and "sob.acoount_period_type" are both not null varchar2(15) type. Could anybody has some idea, why its doing that. thank you very much Please help mcg __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mukesh Ghildiyal 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: Jordi Sanmarti 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).
