|
Using the Query Builder circumvents all form-level security and allows users to see all data in all tables in the Object List. |
|
In the Query Builder you can write code to create a custom query and then view, test and/or modify it.
To create a custom query:
On the Queries menu, click Query Builder.
From the list, select the FAST application you are creating the query for.
Select the Query
List tab and click 
Create New Query.
The Query Editor opens, with sections for comments and the code.
Adding Query Comments is optional, however, they are useful if you are going to use this query as a basis for a custom reporting page.
In the large text box, write your query.
Notes:
You can type the query directly in this text box or copy and paste a query that has been written for you.
If you are writing the query directly, you must put the schema or owner of the object that you are trying to query, for example, FASTMCSL.VIEWXYZ so that the system will know where to look for the object.
If a column in the query is using a function, that column needs to have an alias name. For example, SUM(realamount) AS realamount.
If you want the user to enter a value, add square brackets [ ]. For example, WHERE B.FCTRBUDGETSERIES = [Enter the Budget Series].
You can click Execute Query to test and view the results.
Note that the default is for only the first 100 records in the query will be returned. This is to limit the data in cases of large queries to a workable amount in the query builder. Use the results browser to ensure that you are getting the query results that you expect with the correct columns and data, and that there are no errors in the query. The number of results that are returned is a configurable setting.
When you are done, click Return Edit Query.
When you are finished, click Save Query.
Type a new Query Name.
Select the Private check box if you do not want other users to see the query in the Query Editor or Query Viewer.
Click Save Query and then Ok. The query is added to the list.
The schema (application) that is displayed is the object owner (application-level, not user level), as access to database objects via the Query Builder goes back to access at the application level, not the user level. It is possible to access external objects with the appropriate security grants in place to appear within a given application. For example, if you are building a query in FAST FINANCE and require an object from FAST AR, it is possible to be granted access. It does not matter if you (the developer) have access to an object, FAST assumes that if you are building something in the Query Builder, it is most likely being done for other users as well, so access needs to be granted at the object level, to the FAST application.
When you are writing queries in the Query Builder, they are executing in the query schema of the related application. So in FAST Finance Reporting, the query would need to execute in the FASTFINANCE_QUERY schema. Therefore, FASTFINANCE_QUERY will need to have "SELECT" privileges to all objects referenced in the query. If you want to access an object from the FAST AR application in FAST Finance Reporting, two things need to happen:
STEP 1: The person or group that is responsible for the FAST AR data must agree to allow this information to be access by FAST Finance. Once obtaining this permission,
STEP 2: Your DBA must grant "SELECT" privileges on this object to the FAST QUERY schema in the FAST Oracle database.
After completing these steps, you will be able to reference this object in the FAST Finance Reporting Query Builder, but you must fully qualify the object with the schema name.
Additionally, if the data is coming from across a DBLINK, you will also need to append @[DBLINK NAME] to the end of the object.
If this is your first time creating this type of Query, access to external objects can be set up by Millennium staff and we are happy to walk you through it. For support, please submit a support ticket to Millennium and we will grant access for the object to show up in FAST application required. This relates to objects being shared between FAST applications. Please first ensure that permission has been explicitly granted by the area(s) on campus responsible for the data to be shared.
