This discussion is archived
13 Replies Latest reply: Oct 4, 2012 8:23 AM by 902014 RSS

Is this script acceptable?

902014 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    "acceptable"?!
  • 2. Re: Is this script acceptable?
    902014 Newbie
    Currently Being Moderated
    is there any other way of doing it that is more efficient?
  • 4. Re: Is this script acceptable?
    902014 Newbie
    Currently Being Moderated
    What did I do wrong in posting my question? Isn't it a valid question?
  • 5. Re: Is this script acceptable?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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?
    902014 Newbie
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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?
    902014 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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?
    902014 Newbie
    Currently Being Moderated
    Hehe, made a website for it. I'd love to get in to APEX though. Maybe on my next project.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points