PBDR.COM

About   -   Contact   -   Purchase   -   Search   -   What's New

 
 
A New Where Clause

This article was submitted James Stokes who is using PowerBuilder 6.5 and the PFC.

To keep pace with today’s coding needs, traditional methods for modifying a ‘WHERE’ clause may no longer be adequate. A more modern approach which could be simple and user friendly is outlined below for comparison.

From the help file of PB 6.5 :
Adding a WHERE clause: The following scripts dynamically add a WHERE clause to a Datawindow object that was created with a SELECT statement that did not include a WHERE clause. (Since this example appends a WHERE clause to the original SELECT statement, additional code would be needed to remove a where clause from the original SELECT statement if it had one.) This technique is useful when the arguments in the WHERE clause may change at execution time.

The original SELECT statement might be:
SELECT employee.emp_id, employee.l_name
FROM employee
Presumably, the application builds a WHERE clause based on the user's choices. The WHERE clause might be:
WHERE emp_id > 40000
The script for the window's Open event stores the original SELECT statement in original_select, an instance variable:
dw_emp.SetTransObject(SQLCA)
original_select = &
	dw_emp.Describe("DataWindow.Table.Select")
The script for a CommandButton's Clicked event attaches a WHERE clause stored in the instance variable where_clause to original_select and assigns it to the DataWindow's Table.Select property:
string rc, mod_string

mod_string = "DataWindow.Table.Select='" &
	+ original_select + where_clause + "'"

rc = dw_emp.Modify(mod_string)
IF rc = "" THEN
	dw_emp.Retrieve( )
ELSE
	MessageBox("Status", "Modify Failed" + rc)
END IF
Quotes inserted in the Datawindow painter For SQL Anywhere and ORACLE, the Datawindow painter puts double quotes around the table and column name (for example, SELECT "EMPLOYEE"."EMP_LNAME"). Unless you have removed the quotes, the sample WHERE clause must also use these quotes. For example:
where_clause = &
   " where ~~~"EMPLOYEE~~~".~~~"SALARY~~~" > 40000"
Consider the use of PFC tomodify a WHERE clause
integer li_rtn
string  ls_str, rc , ls_state
string ls_orig_sql, ls_adl_where,, mod_string
n_cst_string inv_string
n_cst_sql   inv_sql
n_cst_sqlattrib  lnv_sqlattrib[ ]

ls_state = "'PA','GA','MA'"

THIS.DataObject = 'd_custtab' // from the PFC examples

the SQL of which looks like this :
 SELECT "customer"."id",   
         "customer"."company_name",   
         "customer"."fname",   
         "customer"."lname",   
         "customer"."address",   
         "customer"."city",   
         "customer"."state",   
         "customer"."zip",   
         "customer"."phone"  
    FROM "customer"  
ORDER BY "customer"."id" ASC   

li_rtn = THIS.of_settransobject (sqlca)
		
ls_orig_sql = dw_1.Describe("Datawindow.Table.Select")

li_rtn =  inv_sql.of_parse(ls_orig_sql,lnv_sqlattrib)
If li_rtn > 0 then
	ls_str = lnv_sqlattrib[1].s_verb +'~n~r'+&
		lnv_sqlattrib[1].s_tables+'~n~r'+&
		lnv_sqlattrib[1].s_columns+'~n~r'+&
		lnv_sqlattrib[1].s_values+'~n~r'+ &
		lnv_sqlattrib[1].s_where+'~n~r'+ &
		lnv_sqlattrib[1].s_order+'~n~r'+ &
		lnv_sqlattrib[1].s_group+'~n~r'+&
		lnv_sqlattrib[1].s_having
					
//	messagebox('jas','of parse '+'~n~r'+ ls_str)
						
	lnv_sqlattrib[1].s_where = lnv_sqlattrib[1].s_where+ &
	" and a.state in (" + ls_state +")" 
			
End if
		
ls_str = inv_sql.of_Assemble(lnv_sqlattrib[])

// Dev Note : because of the problem with modify function looking for a 
//	parsed string of single quote + string + single quote
//	It was easier to to insert what we wanted and reset the DW SQL
// set the new one.. rtn code for success = 1
li_rc = dw_1.SetSQLSelect(UPPER(ls_str))		
IF li_rc = 1 THEN
	il_rowcount = dw_1.Retrieve()
	If il_rowcount = 0 Then
	    MessageBox("Status ", "SQL Select Failed" + string(ls_str))
	End if		
END IF
A caveat when checking code from any magazine, make sure you alias the table of the SQL as shown in this example:
 SELECT  a . id ,   a . company_name ,   
          a. fname ,  a . lname ,   a . address ,   
          a . city ,   a . state ,   a . zip ,   
          a . phone   
 FROM  customer   a
ORDER BY  a . id  ASC
James Stokes (BSEE from the Univ of MD) has with 4yrs in the aerospace electronics instrumentation area, before moving to programming. He has been working with PowerBuilder for 3 yrs building tracking systems for various government agencies throughout the Wash DC area.. When not PowerBuilding, he enjoys the frustration of golf. Questions and comments should be e-mailed : james.stokes@erols.com
 

Top of Page

Legal Notice

© Ken Howe 2011