Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved Modify DW select in query mode

  1. How-to
0
Votes
Undo
  1. Pete Yankovich
    Pete Yankovich
  2. PowerBuilder
  3. Monday, 11 February 2019 21:10 PM UTC

In query mode, when user enters null in a date field, I want to change the DW select from date = 'null' to date is null.  The former give a db errror.  I've tried modify('DataWindow.Table.Select=..., but before the actual retrieval, date = 'null' is still in the select.

Any help is appreciated.

Select a printer tray using code
SMtP 64 BITS
Responses (2)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 11 February 2019 21:28 PM UTC
  2. PowerBuilder
  3. # 1

Hi Pete;

    I would suggest letting the SQL query mode process normally ... for example: QueryMode='NO' and then apply the Retrieve(). Then handle the SQL change from date = 'null' to date is null in the SQLPreview event.

Food for thought. HTH

Regards ... Chris

Comment
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Monday, 11 February 2019 21:49 PM UTC
  2. PowerBuilder
  3. # 2

what chris said.  This is the only way to handle it.

you can get fancy and have users enter just '=' and '<>' and change it to is null and is not null.  We allow this; we change what they entered during itemchanged to make it easier to find in the generated sql (we change '=' to '' to make finding it easier  )  

these types of changes make using querymode much easier for the typical user.  

 

Comment
Pete Yankovich
  1. Pete Yankovich
  2. Monday, 11 February 2019 22:03 PM UTC
Here's what worked for me, in case anybody else is working with this:



string ls_select

int li_pos, li_return



// Modify Select when user enters null in process date

ls_select = sqlsyntax



li_pos = pos(ls_select,"(((mic_dt_process = 'null')))")

if li_pos > 0 then

ls_select = replace(ls_select, li_pos, 29, "mic_dt_process is null")

li_return = this.SetSQLPreview(ls_select)

end if



return 0
  1. Helpful
Pete Yankovich
  1. Pete Yankovich
  2. Monday, 11 February 2019 22:04 PM UTC
This is in SQLPREVIEW event of the DW

  1. Helpful
Pete Yankovich
  1. Pete Yankovich
  2. Monday, 11 February 2019 22:11 PM UTC
One further refinement, remove parentheses in case other fields are filtered:



li_pos = pos(ls_select,"mic_dt_process = 'null'")

if li_pos > 0 then

ls_select = replace(ls_select, li_pos, 23, "mic_dt_process is null")

li_return = this.SetSQLPreview(ls_select)

end if

  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.