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.