Tuesday, September 25, 2007

SQL Coding Standards

This is Vijay Bhaskar Reddy, Working as a Oracle Apps Sr. Technical Consultant. I want to share my experience with all of you through this blog(www.smartoracle.blogspot.com). If you are having any queries feel free to mail me at reddyvijayk@gmail.com

SQL Coding Standards

  1. Standard keywords, standard or custom package/function names, Table alias, operators are in upper case (e.g. SELECT, DBMS_OUTPUT etc.)
  2. Column names, column alias, table names, view names are in lower case.
  3. SQL statements are indented/aligned as per coding standards doc.(e.g given below) Each ‘Clause’ of SQL statement starts in a new line.
    e.g.
    SELECT RCT.trx_number invoice_number
    ,RCT.trx_date invoice_date
    ,SUM(APS.amount_due_original) invoice_amount
    .........
    FROM ra_customer_trx RCT
    ,ar_payment_schedules APS
    WHERE RCT.customer_trx_id = APS.customer_trx_id
    AND APS.class = ‘INV’
    AND RCT.completed_flag = ‘Y’
    AND RCT.customer_trx_id IN (
    SELECT DISTINCT ARA.customer_trx_id
    FROM ar_receivable_applications ARA
    WHERE apply_date BETWEEN ADD_MONTHS(SYSDATE,-1) AND SYSDATE
    )
    GROUP BY RCT.trx_number
    ,RCT.trx_date
    ORDER BY RCT.trx_number;
  4. Only one column/expression, table or selectivity condition appears on one line.
  5. Comma separating the columns, tables or parameters comes at beginning of lines, not at end.
  6. SELECT clause of sub-query starts on a new line.
  7. No more than 3 parameters of a function (standard or custom) per line.
  8. If parameter is an expression or nested function, then each expression or nested function appears on new lines.
  9. Only one join condition appears in one line.
  10. Keywords: AND or OR appears at the beginning of line (and not at the end).
  11. The column names are not unnecessarily abbreviated.
  12. Column name are inter-connected using ‘_’ (e.g. error_code instead of errorcode)
  13. Each table (except for dual) has a table alias.
  14. The table alias is formed using the first characters of ‘parts’ of table name.

4 comments:

oakleyses said...

nike air max, gucci handbags, ugg boots, louis vuitton outlet, polo ralph lauren, christian louboutin outlet, christian louboutin uk, cheap oakley sunglasses, nike free, tory burch outlet, burberry pas cher, polo outlet, jordan shoes, sac longchamp pas cher, oakley sunglasses, replica watches, louis vuitton, longchamp outlet, oakley sunglasses, nike free run, christian louboutin shoes, ray ban sunglasses, oakley sunglasses wholesale, louis vuitton outlet, nike air max, louis vuitton, louboutin pas cher, tiffany jewelry, prada handbags, nike roshe, oakley sunglasses, air max, prada outlet, ugg boots, nike outlet, tiffany and co, ray ban sunglasses, longchamp outlet, chanel handbags, michael kors pas cher, longchamp pas cher, jordan pas cher, replica watches, polo ralph lauren outlet online, uggs on sale, longchamp outlet, kate spade outlet, christian louboutin

oakleyses said...

ray ban uk, coach purses, polo lacoste, sac vanessa bruno, nike tn, hogan outlet, michael kors, michael kors outlet online, converse pas cher, nike air max uk, kate spade, michael kors outlet, hollister pas cher, uggs outlet, coach outlet, north face, michael kors outlet, north face uk, nike air max uk, ray ban pas cher, uggs outlet, nike air force, ralph lauren uk, mulberry uk, lululemon canada, nike free uk, true religion jeans, guess pas cher, replica handbags, timberland pas cher, nike blazer pas cher, coach outlet store online, michael kors outlet online, burberry handbags, abercrombie and fitch uk, michael kors, nike air max, michael kors outlet, vans pas cher, oakley pas cher, true religion outlet, hollister uk, nike roshe run uk, sac hermes, true religion outlet, new balance, michael kors outlet online, burberry outlet, true religion outlet

oakleyses said...

timberland boots, instyler, wedding dresses, mcm handbags, beats by dre, ghd hair, converse, oakley, herve leger, nike huaraches, hollister, valentino shoes, nfl jerseys, mac cosmetics, vans, abercrombie and fitch, insanity workout, mont blanc pens, hollister clothing, reebok outlet, bottega veneta, louboutin, lululemon, p90x workout, baseball bats, nike roshe run, celine handbags, vans outlet, converse outlet, north face outlet, hermes belt, new balance shoes, north face outlet, ray ban, longchamp uk, jimmy choo outlet, babyliss, ralph lauren, gucci, chi flat iron, soccer jerseys, asics running shoes, iphone cases, ferragamo shoes, hollister, nike trainers uk, soccer shoes, nike air max, giuseppe zanotti outlet, nike air max

oakleyses said...

canada goose, moncler outlet, canada goose outlet, toms shoes, montre pas cher, louis vuitton, moncler, canada goose, swarovski, juicy couture outlet, louis vuitton, replica watches, links of london, moncler, canada goose uk, moncler uk, hollister, wedding dresses, thomas sabo, karen millen uk, canada goose outlet, barbour uk, louis vuitton, ugg uk, ugg,ugg australia,ugg italia, moncler outlet, doudoune moncler, ugg,uggs,uggs canada, canada goose, supra shoes, coach outlet, canada goose outlet, ugg pas cher, ugg, pandora uk, louis vuitton, canada goose jackets, juicy couture outlet, moncler, marc jacobs, louis vuitton, pandora jewelry, lancel, swarovski crystal, pandora jewelry, pandora charms, barbour, moncler