tag:blogger.com,1999:blog-7631414.post109785408972645970..comments2023-05-31T04:00:19.993+00:00Comments on Tony Andrews on Oracle and Databases: "Pivot" QueriesTony Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.comBlogger18125tag:blogger.com,1999:blog-7631414.post-16859018880428976562011-02-10T14:29:29.124+00:002011-02-10T14:29:29.124+00:00Tony,
I simplified it even more (exclusing the joi...Tony,<br />I simplified it even more (exclusing the join) and it works.<br />So, the problem has to do with the join. I left out one join condition (equip) which caused it to aggregate across the site instead of just for the equipment.<br /><br />Thanks for all of your help!BrianSherwoodhttps://www.blogger.com/profile/18276132743598683779noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-14211943924232320702011-02-10T14:11:38.416+00:002011-02-10T14:11:38.416+00:00I'm still getting the same (total) value for a...I'm still getting the same (total) value for all pieces of equipment.<br /><br />Tony, again, thanks for your reply; I don't want to take up more of your time unless it is a problem with your code, which I don't think it is. I will work a bit more on the grouping problem and let you know when I find the solution.BrianSherwoodhttps://www.blogger.com/profile/18276132743598683779noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-46656535816134581442011-02-09T23:22:45.339+00:002011-02-09T23:22:45.339+00:00I can't see anything obvious. Try a simplifie...I can't see anything obvious. Try a simplified version, does that work as expected?<br /><br />select e.EQUIP_NAME <br />, sum(d.segment_mass_kg/1000)) <br />from wh_equip e, wh_emis_detail d <br />where e.site_name='Chaparral Plant'<br />AND d.Site_Name=e.Site_Name <br />AND d.Scenario='GHG' <br />AND d.Start_Date >= TO_DATE('1/1/2010','mm/dd/yyyy') <br />Tony Andrewshttps://www.blogger.com/profile/16750945985361011515noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-32971194501900053432011-02-09T21:38:04.837+00:002011-02-09T21:38:04.837+00:00Here is the SQL:
select e.EQUIP_NAME , sum(DECODE...Here is the SQL:<br /> select e.EQUIP_NAME , sum(DECODE(d.Calc_Material,'Carbon Dioxide', d.segment_mass_kg/1000)) as "Carbon Dioxide" , sum(DECODE(d.Calc_Material,'Nitrogen Oxide (N2O)', d.segment_mass_kg/1000)) as "Nitrogen Oxide (N2O)" , sum(DECODE(d.Calc_Material,'Methane', d.segment_mass_kg/1000)) as "Methane" from wh_equip e, BrianSherwoodhttps://www.blogger.com/profile/18276132743598683779noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-37999730086412093192011-02-09T21:30:20.339+00:002011-02-09T21:30:20.339+00:00Brian,
Can you post the SQL it generated?
TonyBrian,<br /><br />Can you post the SQL it generated?<br /><br />TonyTony Andrewshttps://www.blogger.com/profile/16750945985361011515noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-819612891009715112011-02-09T21:26:29.241+00:002011-02-09T21:26:29.241+00:00Tony,
First of all, thanks for your quick response...Tony,<br />First of all, thanks for your quick response!<br />Secondly, DOH! Sorry to waste your time with obvious errors (once you pointed them out to me...)<br />Thirdly, this gives me the same problem as when I created my own 'Decode' select statements: the Grouping on Equip_Name is not being 'honored' by the 'sum'; I'm getting a sum for all records in the Site, BrianSherwoodhttps://www.blogger.com/profile/18276132743598683779noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-40327954414033645302011-02-09T21:05:40.266+00:002011-02-09T21:05:40.266+00:00Brian,
There were a few mistakes in yours:
1) The...Brian,<br /><br />There were a few mistakes in yours:<br />1) The function version is called pivot_query not print_pivot_query<br />2) You missed the FROM clause of your select statement<br />3) You omitted the pivot_values parameter (and didn't use named notation to compensate)<br /><br />Try either:<br /><br />SELECT pivot.pivot_query(<br />'e.Equip_Name, e.Equip_sub_category',<br /Tony Andrewshttps://www.blogger.com/profile/16750945985361011515noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-33939670770775333122011-02-09T19:24:17.435+00:002011-02-09T19:24:17.435+00:00Hello Tony.
Brilliant piece of code.
I'm havin...Hello Tony.<br />Brilliant piece of code.<br />I'm having some difficulty using this for my purpose. I have two tables with the following data I'm trying to report on:<br />Tables <br />Equip e<br />Details d<br /><br />d.Site e.Equip d.Material d.Value<br />======== ======= ========= =======<br />Plant 1 E-1 CO2 74000<br />Plant 1 E-1 CH4 133<br />Plant BrianSherwoodhttps://www.blogger.com/profile/18276132743598683779noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-10533947238623621622010-01-21T12:55:40.792+00:002010-01-21T12:55:40.792+00:00@ARA, you would need to modify the package if you ...@ARA, you would need to modify the package if you want it to generate different aliases. But are the precise alias names really so important?Tony Andrewshttps://www.blogger.com/profile/16750945985361011515noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-76475712353323941072010-01-13T13:59:36.766+00:002010-01-13T13:59:36.766+00:00Hi Tony
I'd like to know if I can put an alia...Hi Tony<br /><br />I'd like to know if I can put an alias to a pivot column name. Is it possible?. For example change min_ANALYST_esal with ANALYST_salary or ANALYST_minimal_SALARY<br /><br />Thank you again<br /><br />Alberto RicoUnknownhttps://www.blogger.com/profile/01099179830580168866noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-91288364633465460932009-12-12T18:11:37.613+00:002009-12-12T18:11:37.613+00:00ARA,
Something like this:
declare
l_cur sys_r...ARA,<br /><br />Something like this:<br /><br />declare<br /> l_cur sys_refcursor;<br />begin<br /> l_cur := pivot.pivot_cursor<br />( 'x.ID_CENTRO, v.DESC_CENTRO, x.PERIODO, V.TOTAL '<br />, 'ID_PERFIL'<br />, 'v_tot_tx_user_perf_mens_ofic x, V_TOTAL_TX_MENS_OFICINA V'<br />, '1'<br />, agg_types => 'count'<br />, where_clause => 'x.id_centroTony Andrewshttps://www.blogger.com/profile/16750945985361011515noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-67397416321319215372009-12-11T14:28:28.059+00:002009-12-11T14:28:28.059+00:00Hi Tony
I didn`t find an example of pivot_cursor....Hi Tony<br /><br />I didn`t find an example of pivot_cursor.<br /><br />I'd like to execute :<br /><br />pivot.pivot_cursor<br /> ( 'x.ID_CENTRO, v.DESC_CENTRO, x.PERIODO, V.TOTAL '<br /> , 'ID_PERFIL'<br /> , 'v_tot_tx_user_perf_mens_ofic x, V_TOTAL_TX_MENS_OFICINA V'<br /> , '1'<br /> , agg_types => 'count'<br />Unknownhttps://www.blogger.com/profile/01099179830580168866noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-35165273868045049142009-05-25T15:15:25.024+00:002009-05-25T15:15:25.024+00:00Mitchell,
I'm not sure that that's feasible, sinc...Mitchell,<br /><br />I'm not sure that that's feasible, since the return type of the function would have to change according to the number of columns returned.Tony Andrewshttps://www.blogger.com/profile/16750945985361011515noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-32255267602802473992009-05-25T15:01:58.374+00:002009-05-25T15:01:58.374+00:00Can you show an example of how the pivot functions...Can you show an example of how the pivot functions can be called and return a recordset from a SELECT statement?<br /><br />For example (something like this):<br />select * from (select PIVOT.PIVOT_query('deptno', 'job', 'emp', '1', NULL, 'count') from dual) aUnknownhttps://www.blogger.com/profile/07169278071925606138noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-81983577270077934422009-02-09T13:46:00.000+00:002009-02-09T13:46:00.000+00:00Never mind figured it out, SQLDeveloper was confus...Never mind figured it out, SQLDeveloper was confusing me ;)<BR/><BR/>Was passing named parameters.<BR/><BR/>select PIVOT.PIVOT_QUERY(<BR/> 'DIM_GROUPING_FIELD',<BR/> 'FR_MONTH',<BR/> 'VW_TEST_PIVOT',<BR/> 'MEASURE',<BR/> NULL,<BR/> NULL,<BR/> NULL,<BR/> NULL<BR/> ) as resQuery from dual;Edhttps://www.blogger.com/profile/15931672374288039407noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-32058974166819644902009-02-09T09:07:00.000+00:002009-02-09T09:07:00.000+00:00This is by far the best pivot function I have foun...This is by far the best pivot function I have found. Just 1 question though, can you maybe show an example of how one would call this/select the output? Not used to ORACLE :\Edhttps://www.blogger.com/profile/15931672374288039407noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-1161199516082646222006-10-18T19:25:00.000+00:002006-10-18T19:25:00.000+00:00Brilliant !!!. The best I have seen. The packages ...<B>Brilliant !!!</B>. The best I have seen. The packages are very powerful, easy to use. Should be part of Oracle standard.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7631414.post-1134953050507864602005-12-19T00:44:00.000+00:002005-12-19T00:44:00.000+00:00This is fantastic .. keep the creative juices goin...This is fantastic .. keep the creative juices going..Anonymousnoreply@blogger.com