1. Pete Yankovich
  2. PowerBuilder
  3. Monday, 11 February 2019

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.

mike S Accepted Answer Pending Moderation
0
Votes
Undo

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
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. Pete Yankovich
  2. Monday, 11 February 2019
This is in SQLPREVIEW event of the DW

  1. Pete Yankovich
  2. Monday, 11 February 2019
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. Pete Yankovich
  2. Monday, 11 February 2019
There are no comments made yet.
  1. Monday, 11 February 2019
  2. PowerBuilder
  3. # 1
Chris Pollach Accepted Answer Pending Moderation
0
Votes
Undo

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.
  1. Monday, 11 February 2019
  2. PowerBuilder
  3. # 2
  • Page :
  • 1


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