1. Andrew Davis
  2. PowerBuilder
  3. Tuesday, 15 May 2018 19:56 PM UTC

Hi All,

Hopefully you can help, where do i find any documentation on using a datawindow in querymode - I know how to put it into querymode - add do simple query

What i mean is i can do for example (  when a date field ) <2018/01/01 or a number field =1000

how would you do for example between dates. 

How would you say left( 'field') , 3 ) 

or pos("field")="The"

thanks in advance

Andrew

 

 

Accepted Answer
mike S Accepted Answer Pending Moderation
  1. Tuesday, 15 May 2018 20:45 PM UTC
  2. PowerBuilder
  3. # Permalink

the following is the 'how to use query mode' from our application's help:

Query Operators

Like

Use like to search for values using either a wildcard or bracketed [] list of values.  If you don't use either of these, then the like keyword works just like =

 

Example:  LIKE A%= Find all items beginning with A

NOT Like

Use not like to search for values not matching using either a wildcard or bracketed [] list of values. If you use neither wildcard nor the brackets, then not like works just like <>

 

Example:  NOT LIKE A% = Find all items that do not begin with an A

%  Wild card

Designates a wild card. It requires the LIKE keyword.   A wild card character could also be used as a prefix or suffix in conjunction with the Like operator and a known character.

 

Example: Like A%C will return ABC as well as ABBC and AC

_ (underscore) wildcard

The underscore character is also a wild card.  However, unlike the % wildcard, it will only match to 0 or 1 characters.  It too requires the Like keyword.

 

Example:  Like A_C will return ABC, but would not return ABBC

=   Equal to

This function includes any values equal to the search criteria.  If no operator is entered, the = is assumed.

If = is entered and no value is entered along with it, then the system will look for missing values (NULL values or an empty string if the field is a string).

 

Examples:  

= 1000 searches for the value of 1000.  Just entering 1000 works the same

= searches for missing entries (null)

 

>  Greater than

This function includes any item in the search criteria greater than the stated values. This operator can be used in conjunction with the AND operator to accomplish a between operator for both number and date fields.

 

Example:  

> 100    searches for anything greater than 100

>          searches for anything that has any value entered (not empty or null)

<   Less than

This function includes any item in the search criteria less than the stated values. This operator can be used in conjunction with the AND operator to accomplish a between operator for both number and date fields.

 

For example:  < 100

 

< >

Not equal To

This function includes all items in the search criteria other than the stated value.

 

For example:  <> 100  (Any value other than 100)

 

> =  Greater than, or equal to

This function includes all items greater than or equal to the stated search criteria.

 

For example:  >= 100  (any value 100 or greater)

< =   Less than, or equal to

This function includes all items less than or equal to the stated search criteria.

 

For example:  <= 100  (any value 100 or less)

AND

Setting parameters on subsequent rows with the prefix “and” designates an AND operator. Both conditions have to be true for an item to populate the window. A BETWEEN function is accomplished by using > or < in conjunction with the AND operator.

OR

Setting parameters on subsequent rows without a prefix designates an OR operator. Either parameter could be true for an item to populate the window.

IN

List of values.  Works like OR but within same cell.

 

Example:

IN ('ABC', 'DEF') is the same as (value = ABC OR value = DEF)

IN (1001,1002)

note that numbers do not need quotes, but string/characters do

[] Brackets

The use of brackets allows the user to specify that one of the values within the brackets must match.  It must be used with the Like Keyword.

 

Example:  Like A[BC]C

Returns ABC and ACC but not AAC

 

Example:  like [6,7]%

Returns everything starting with 6 or 7.  

 

Example:  like [6-9]%

Returns everything starting with 6 through 9.  Starts with 6,7,8 or 9 and all other parts are a wildcard.  

 

Example:  not like [6,7]%

Returns everything that does not start with either 6 or 7.  

 

 

Note that this will not work with the Oracle database.

 

 

 

 

Comment
  1. Andrew Davis
  2. Tuesday, 29 May 2018 10:19 AM UTC
awesome thanks

  1. Helpful
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Tuesday, 15 May 2018 20:42 PM UTC
  2. PowerBuilder
  3. # 1

use LIKE with and without wildcards ( % and _ )

IMHO things like left/pos etc are too complex for a non-developer to use.  you get similar results with LIKE

________________

a logical OR is implied when you use multiple rows for query mode.  works best with grid datawindows.

AND can be entered as a value and it ands the queries in rows instead of ORing them.  That is how you do date ranges: 

> 1/1/2018 AND

< 12/31/2018

again, works best with grid datawindows.

______________

you can use LIKE with any variations that your database supports such as the use of brackets:  MSSQL/ASE supports:   like [ABC]% 

but not oracle 

________________

you CAN code your program to automatically prefix entered text with LIKE and postfix with % - which makes it easier for users that don't get the concept of LIKE.  

 

NOTE:  querymode hasn't been updated since forever - some sql that should work does not. 

Comment
  1. Andrew Davis
  2. Tuesday, 29 May 2018 10:11 AM UTC
Thanks, really helpful



 

  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 15 May 2018 20:46 PM UTC
  2. PowerBuilder
  3. # 2

Hi Andrew;

   FWIW: QueryMode is not designed for generating complex Where clauses. That is what the SQLPreview Event would be for when using your own QBE dialogue that prompts the App user for DB search criteria in a more humane way. You can also use the GetSQLSelect and SetSQLSelect methods as well to assist your easy to use QBE search dialogue as well. Otherwise, the QueryMode approach will require the business users to understand DML syntax in depth.

   Food for thought.

Regards ... Chris

Comment
  1. Andrew Davis
  2. Tuesday, 29 May 2018 10:19 AM UTC
this is actually for a handful of people to do the odd one off search -

  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.