SQL Statements

SQL statements are used to simulate information categories. Any valid SQL select statement can be used in sub reports, except "order by" statements (CDD will prompt for sort order). SQL statements are managed from the Sub Report Definition window.

Add

• Click Add SQL. The General window is displayed.
• Enter a name and description to the View you are creating.
• Click Next. The SQL window is displayed.
• The SQL window allows you to write an SQL statement directing the system to retrieve specific data from a selected table or tables. For example, if for expenditure transaction purposes you want to select the net of credits minus debits from a particular budget table, the SQL statement you would create might look like this: select glt_cr - glt_dr net from glt_trns_dtl. This statement directs the system to let "net" be the result of the general ledger credit value (glt_cr) minus the general ledger debit value (glt_dr) and instructs that this data is to be retrieved from the glt_trns_dtl table. You can enter this information by either typing the entire statement in the text field or by using the SQL statement keys.
• Click the Syntax button. The system will check the statement to be sure that it conforms to SQL syntax requirements. If there are errors in the statement, the system will display these in the Database Error message window. Note that, if the syntax is correct, nothing will happen. An error message will only appear if there is a problem with your SQL syntax.
• Click Next. The Items window is displayed.
• Check the columns that will be returned based on the view you are creating. To edit a column, select the item and click Edit. The Items Properties window will be displayed and the item properties such as font, color and point size can be altered. Click OK to accept the changes and return to the Items window.
• Click Next. The Sort window is displayed.
• Select the fields on which you want to base sorting of data in the report. Use the sort order buttons to rearrange the data elements.
• Click Finish.
• The new SQL statement is added to the Subreport Data section in the Sub Report Definition window.
• Click Finish to return to the report design window.

Update

• Select the SQL statement in the Subreport Data section.
• Click Edit. The Edit Sub Report SQL window is displayed.
• Use the four tabs (General, SQL, Items, Sort) to make the necessary changes.
• Click OK.

Remove

• Select the SQL statement in the Subreport Data section.
• Click Delete.
• The item is removed from the Subreport Data section.