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)
mike S
mike S Accepted Answer Pending Moderation
  1. Monday, 11 February 2019 21:49 PM UTC
  2. PowerBuilder
  3. # 1

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.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 11 February 2019 21:28 PM UTC
  2. PowerBuilder
  3. # 2

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.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.