Where Clause for Update/Delete

Key Columns

This option is commonly used for single-user applications. It’s also used when working with “many-to-many” tables where the primary key consists of all the columns in the table

Key and Updateable Columns

This method provides maximum consistency

Key and Modified Columns

This option provides maximum concurrency

You should check the data model to make sure the update does not violate the business rules of the database. The value you are changing may depend on the value of another column that may have been changed.

Graphs

What is the difference between Value, Category and Series?

• Value is the dependent variables (y axis)
• Category is the by component (x axis)
• Series is the third component in a 3D graph (z axis)

How do I use the ObjectAtPointer( ) function?

The ObjectAtPointer( ) function will allow the user to drill down on clicked graph data. It has three arguments: GraphObject, SeriesNumber, and DataPoint.

The following script is for a datawindow control’s clicked event. It identifies what graph object was clicked, if it is a category or value, it retireves the data in a second DataWindow based o the passed argument.

GrObjectType    ClickedObject
Long        ll_dept_num
String        ls_deptnum
Integer        li_ret, li_series, li_category

ClickedObject = this.ObjectAtPointer(‘gr_1’, li_series, li_category)

//If the userr clicked data or category, find out which one and retrieve second dw  with argument
IF ClickedObject = TypeData! OR ClickedObject = TypeCategory! THEN
   Ls_deptnum = this.CategoryName(‘gr_1’, li_category)
   Ll_deptnum = long(ls_deptnum)
   Dw_detail.title = “Employees in dept “ + ls_deptnum
   Dw_detail.Retrieve(ll_deptnum)
   Dw_detail.show( )
ELSE
   MessageBox(parent.title, “Click a department to see employee names”)
END IF

Grid Style

PowerScript

You can prevent the user from moving columns in a grid

Dw_1.Object.DataWindow.Grid.ColumnMove = ‘no’

You can also control where grid lines are displayed

Dw_1.object.DataWindow.Grid.lines = 2 // 0 – always, 1 - never, 2 – display only, 3 print only

How do I get rid of the grid lines?

Starting with version 5, PB allows you to turn off the lines on a grid datawindow. To do this from the datawindow painter, set the datawindow property (click on the dw background, not on a column of text field) GRID and DISPLAY on the GENERAL tab.

To change the lines at run time, use the following

Composite DataWindow

What is a composite report?

A composite report contains other reports based on independent SQL statements. The composite report itself has no data source; it is just a container for the reports nested within it. The nested reports are not related to each other. However you can define retrieval arguments for the composite report and use them to control which rows each nested report retrieves.

Composite reports allow you to print the data for multiple DataWindows together.

To access data on a composite report use the GetChild function.

Datawindowchild ldwc_child
   
Dw_1.GetChild(“r_report”, ldwc_child)
Ldwc_child.function( )
   
Dw_1.object.r_report.object.emp_id[1]

What does the “Train the Footer” property of a nested report do?

This controls the placement of the footer for the last page of a nested report. When set, the footer will appear directly under the nested dw, not at the bottom of the page.

How do I modify an item on a composite window’s child?

In the following example, d_data is the name of the datawindow child as it appears in the composite datawindow, D_data is NOT a variable of type datawindowchild:

Dw_utilization_report.object.d_data.object.t_impressions.text = “xxx”

I have a composite DataWindow with three child DataWindows one of which is doing a ShareData with a datastore. When I retrieve, I get a GPF.

Do the ShareData after the Retrieve()

Nested Report

What is a nested report?

A report can nest another report as an object within it. This can be useful for representing master/detail information. For each master row, a separate retrieve is done for each detail row. So a report with 20 master rows involves 21 retrieves.

Detail rows can relate the nested report to the base report via retrieval arguments or retrieval criteria.

To access nested data use something like this:

Dw_1.object.base[1].object.nest[1]

The GetChild function will work for composite reports but not for nested ones.

The big pain about nested reports is that they have no current row, so a GetRow() will always return 0. So getting information can not be pinpointed with a mouse click. The row in the clicked event will refer to the container’s row, not the nested report’s row.

How do I determine if a datawindow contains nested Reports, i.e. is a Base Report (container datawindow)?

dw_control.Object.DataWindow.Nested

or

“DataWindow.Nested”

Which presentation styles are used for reporting only?

Reporting Only	Reporting and Updating
Composite	Freeform
Crosstab	Grid
Graph	Group
Label	Tabular
OLE 2.0	RichText
	N-up

How do I call a Function in an Ancestor?

Functions default behavior is to Override any existing function with the same name and signature. If you need to call the ancestor function, use the SUPER pronoun.

boolean lb_rc

// do some stuff

lb_rc = Super::of_Filter_others(as_err_msg)

// do some stuff

Return lb_rc

SQL Painter

How can I use Retrieval Arguments to make my where clause more flexible?

Here is a solution I came up with to avoid the need to dynamically modify the Where Clause, or creating multiple datawindows with slightly different SQL. The JOB_QUEUE_RULE table has IDs which must be a positive number ( a good place for a check constraint in the table’s DDL). If you want to see all the rules, set the retrieval argument to 0, otherwise set the ret arg to the ID you want to see.

SELECT R.ID,
       R.DESCRPTION
  FROM dbo.JOB_QUEUE_RULE   R
 WHERE ((R.ID = :ra_id) OR (:ra_id = 0 ))

How do I limit the number of tables to those with a certain prefix?

There exists a parameter to limit the tables you view in the database painter. Here’s the language you need.

• Click the DB profile tool bar button
• Select the profile you want and click EDIT (this action displays the DATABASE PROFILE SETUP dialogue)
• Select the SYSTEM tab
• Go to the TABLE CRITERIA section and type ,W11559,”TABLE” (or replace W11559 with whichever account you want)
• Click OK (this action returns you to the DATABASE PROFILES dialogue)
• Click CONNECT
• Help can be found under TABLECRITERIA (one word)

How can I use Dual (Oracle) to populate my initial search screen?

SELECT '' as secretary_code,
'' as LOG_SEQ_NO,
'' as governor_log_no,
'' as SUBJECT_DESC,
' as BUREAU_REGION_CODE,
to_date('' ) as FR_received_on_date,
to_date('' ) as TO_received_on_date,
to_date('' ) as FR_DUE_DATE,
to_date('' ) as TO_DUE_DATE,
to_date('' ) as FR_letter_out_date,
to_date('' ) as TO_letter_out_date,
to_date(to_date(to_char(add_months(sysdate,-12),'mm/dd/yyyy' ), 'mm/dd/yyyy' )) as FR_date,
to_date(to_char(sysdate,'mm/dd/yyyy' ),'mm/dd/yyyy' ) as TO_date,
'' as leg_cust_last_name,
'' as leg_cust_first_name,
to_date('' ) as fr_letter_date,
to_date('' ) as to_letter_date
FROMDUAL

Magic where clause for datawindow receiving a date retrieval argument

WHERE “WM_HUNTERS_CHOICE_APPLICANT”.”DOB” = to_date(:as_dob, ‘mm/dd/yyyy’ )

When I try to switch from Syntax mode to Graphic mode, PowerBuilder gives me a message saying that it can’t.

Tables associated with your SQL can not be accessed from your current database profile. Switch to a database profile with the correct server and system login ID and password.

DataWindow Bands

Header

GroupHeader

Detail

Group Trailer

Footer (end of each page)

Summary

Suppress Repeating Values in detail band

There is a selection under the Rows menu heading for this

Newspaper Columns in detail band

This option can be specified in the Print Specifications tab of the DataWindow Object. The newspaper column effect appears only when the report is printed or in print preview mode

Note, this will also display header information in newspaper column. Since you probably will not want this text repeated, the display can be limited by checking the Suppress Print After First Newspaper Column check box in the column’s general property page.

How come my groups are goofy

The data must be sorted in the same way as the group.