1 / 9

Database Functions

Database Functions. The art of saving time and code. Kevin Ford Services Consaultant Accela, Inc. The power of functions. Saves time in development Saves Code Saves Redundancy Reusable Ability to do things far beyond what standard SQL would allow you.

odeda
Download Presentation

Database Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Functions The art of saving time and code Kevin Ford Services Consaultant Accela, Inc. Insert Presentation Title Here

  2. The power of functions • Saves time in development • Saves Code • Saves Redundancy • Reusable • Ability to do things far beyond what standard SQL would allow you. • Usually fast and efficient execution Insert Presentation Title Here

  3. How do they work? • Functions themselves are SQL queries. • Called in line with queries to retrieve data while keeping the code of the query simple. • Functions use parameters as criteria for the things that need to be returned • Stored in the database and never have to be written again. • Function on both SQL Server and Oracle

  4. SQL without functions example. SELECT 'VALUE', ( SELECT BCB1.B1_CHECKLIST_COMMENT FROM BCHCKBOX BCB1 WHERE B.SERV_PROV_CODE = BCB1.SERV_PROV_CODE AND B.B1_PER_ID1 = BCB1.B1_PER_ID1 AND B.B1_PER_ID2 = BCB1.B1_PER_ID2 AND B.B1_PER_ID3 = BCB1.B1_PER_ID3 AND BCB1.B1_CHECKBOX_DESC = 'BOS District' ) FROM B1PERMIT B WHERE 1=1 AND B.B1_ALT_ID = 'CODX2009-00001' AND B.SERV_PROV_CODE = ‘Agency1'

  5. SQL with Function SELECT 'VALUE', DBO.FN_GET_APP_SPEC_INFO(B.SERV_PROV_CODE, B.B1_PER_ID1, B.B1_PER_ID2, B.B1_PER_ID3, 'BOS District') District_Name FROM B1PERMIT B WHERE 1=1 AND B.B1_ALT_ID = 'CODX2009-00001' AND B.SERV_PROV_CODE = 'SACCO'

  6. Demo and Explanation • Walk through the documentation that we have on hand and show how to look at the functions so that you can learn how they work to ensure that they are used properly.

  7. Loading Functions into the Database • In the above example you can see that we have drilled down in the database to “Scalar-valued Functions” this is where the Accela Automation functions get loaded. After selecting “New Scalar-valued Function” simply copy, paste, and execute the function that you desire to load into the database.

  8. Client Use • The list of functions that have been developed and will be supported by Accela is actually quite short 27 functions in all. • Do we or can we develop custom functions? • We have developed custom functions for end users so that portions of reports that had special needs could be completed. • Custom functions developed should be well documented and included with report deployment packages and instructions.

  9. Documentation • Found on sharepoint (oasis) at the following address: Accela > Services > Technical Services > Standard Reports > AA Standard Database Functions for Reporting Note: the functions haven’t needed an update since their last release because they are so versatile

More Related