13 Replies Latest reply: Oct 4, 2012 10:23 AM by m.davide RSS

    Is this script acceptable?

    m.davide
      The following script does what I want but I want to know if it is and acceptable script. Thank you!
      -- SELECT membership for specific period-region-province
      SELECT 
      mc.municipality_city_name,
      to_char(m.emp_government, 'FM999,999,999') AS emp_government,
      to_char(m.emp_private, 'FM999,999,999') AS emp_private,
      to_char(m.lifetime, 'FM999,999,999') AS lifetime,
      to_char(m.self_employed, 'FM999,999,999') AS self_employed,
      to_char(m.overseas_worker, 'FM999,999,999') AS overseas_worker,
      to_char(m.spon_mayor, 'FM999,999,999') AS spon_mayor,
      to_char(m.spon_governor, 'FM999,999,999') AS spon_governor,
      to_char(m.spon_congressman, 'FM999,999,999') AS spon_congressman,
      to_char(m.spon_nhts, 'FM999,999,999') AS spon_nhts,
      to_char(m.spon_others, 'FM999,999,999') AS spon_others,
      to_char(m.municipality_population, 'FM999,999,999') AS municipality_population,
      to_char((m.emp_government + m.emp_private + m.lifetime + m.self_employed + m.overseas_worker + m.spon_mayor + m.spon_governor + m.spon_congressman + m.spon_nhts + m.spon_others),'FM999,999,999') AS total_registered,
      to_char(m.target_members, 'FM999,999,999'),
      CASE
        WHEN to_char(m.target_members) = '0' THEN
          '-' 
        ELSE 
          to_char(((m.emp_government + m.emp_private + m.lifetime + m.self_employed + m.overseas_worker + m.spon_mayor + m.spon_governor + m.spon_congressman + m.spon_nhts + m.spon_others)/m.target_members)*100, 'FM999.00') || '%'
      END AS percent_registered
      FROM membership m
      INNER JOIN municipality_city_period mcp ON mcp.municipality_city_period_id = m.municipality_city_period_id
      INNER JOIN municipality_city mc ON mc.municipality_city_id = mcp.municipality_city_id
      INNER JOIN province_period pp ON pp.province_period_id=mcp.province_period_id
      INNER JOIN province p ON p.province_id=pp.province_id
      INNER JOIN region_period rp ON rp.region_period_id=pp.region_period_id
      INNER JOIN region r ON r.region_id=rp.region_id
      INNER JOIN period per ON per.period_id=mcp.period_id
      WHERE rp.period_id = 1 AND r.region_id= 1;
        • 1. Re: Is this script acceptable?
          Ora
          "acceptable"?!
          • 2. Re: Is this script acceptable?
            m.davide
            is there any other way of doing it that is more efficient?
            • 4. Re: Is this script acceptable?
              m.davide
              What did I do wrong in posting my question? Isn't it a valid question?
              • 5. Re: Is this script acceptable?
                Billy~Verreynne
                m.davide wrote:
                What did I do wrong in posting my question? Isn't it a valid question?
                How can anyone comment on this without
                a) knowing the business requirement to meet?
                b) the underlying data model?
                c) the physical implementation of the model (tables and indexes)?
                d) the execution plan that results from the SQL?

                And then critical data such as the 4 digit Oracle version number is also missing.
                • 6. Re: Is this script acceptable?
                  m.davide
                  I'm sorry.
                  I just wanted to know if it was OK to do calculations and formatting in my database instead of my front-end.
                  • 7. Re: Is this script acceptable?
                    Sven W.
                    I would try to avoid coding the formating of your numbers into the select statement. But it is not wrong to do so.
                    If you run this sql as a data source for another program, then it would be wrong in most cases. If you have a datatype number then stay with that datatype as long as possible.

                    You mentioned it is a script. This indicates that the output is spooled to some file. In this case you have a solid reason to pretty print the numbers. And since there is no direct session wide format mask for numbers, you have to do it on each column. But I would do it only if I need to print it differently then with the nomal formating. Like it is with your percent_registered column.

                    The difference btw is that sql plus will print chars left aligned and numbers right aligned.

                    example
                    with testdata as (select 1234 num from dual
                            union all select   56 num from dual)
                    select num, to_char(num) txt
                    from testdata;
                    
                    NUM  TXT                                    
                    ---- ----------------------------------------
                    1234 1234                                     
                      56 56                                       
                    • 8. Re: Is this script acceptable?
                      Sven W.
                      m.davide wrote:
                      I'm sorry.
                      I just wanted to know if it was OK to do calculations and formatting in my database instead of my front-end.
                      Calculations yes, formatting no.

                      Basic MVC pattern.
                      • 9. Re: Is this script acceptable?
                        Frank Kulash
                        Hi,
                        Sven W. wrote:
                        m.davide wrote:
                        I'm sorry.
                        I just wanted to know if it was OK to do calculations and formatting in my database instead of my front-end.
                        Calculations yes, formatting no.
                        Well put!

                        In SQL*Plus, I would use
                        SET  NUMFORMAT  999,999,999
                        to make all the number columns appear in that format by default. If there are numbers (like ids) that should be formatted differently, then use COLUMN to specify their format.
                        I wouldn't use FM, just because I think most people would want the numbers right-justified.
                        • 10. Re: Is this script acceptable?
                          Billy~Verreynne
                          m.davide wrote:

                          I just wanted to know if it was OK to do calculations and formatting in my database instead of my front-end.
                          Then why not ask this upfront - short and easy to understand question. No need for a long code snippet. :-)

                          The answer is, it depends.

                          Typically, no. If you have an "intelligent" client then that client should man up and take responsibility for formatting and rendering.

                          However, if you are spooling the data directly to a file, or to some simplistic client that does a WYSIWYG type display of SQL projections, then formatting the SQL projection is a valid approach - but also an exception to what client-server responsibility areas should be.
                          • 11. Re: Is this script acceptable?
                            m.davide
                            Thank you everyone!

                            My current project only involves data presentation. I wanted to do everything on the database because this is a rush project and I've been putting off trying to learn Php since my college days. In short I'm not comfortable working on web-based projects :D
                            • 12. Re: Is this script acceptable?
                              Gaff
                              In that case you might take a look at APEX.

                              http://www.oracle.com/technetwork/developer-tools/apex/overview/index.html

                              http://www.oracle.com/technetwork/developer-tools/apex/overview/apex101-326750.ppt


                              SQL Plus is fine for looking at text reports but it isn't something I'd necessarily be wanting to give to an end user these days. Sometimes presentation does matter.

                              m.davide wrote:
                              Thank you everyone!

                              My current project only involves data presentation. I wanted to do everything on the database because this is a rush project and I've been putting off trying to learn Php since my college days. In short I'm not comfortable working on web-based projects :D
                              • 13. Re: Is this script acceptable?
                                m.davide
                                Hehe, made a website for it. I'd love to get in to APEX though. Maybe on my next project.