'Dynamic' Where (was Hey! Long time since...)

[Peter Bateman]

Hi Karen:
 
;If any of the fields are also leading edge segments of an index
;you may want to create a between predicate instead of a like.

;Our challenge then is to create versions of the cursor that supports

; exact match (PATH 3 ), like match(PATH 1 ) and between match (PATH 2) .

;
 For example:- if city is a leading edge segment we can substitute the
                    appropiate syntax into the cursor.

  DECLARE zyz cursor in mydb for select * &
              from EMPLOYEES &
              where    &
                     ( cust_name like :pht2 or ( :pht2 = '___________________' &
                                           and cust_name is null ) )           &
                 and ( state like :pht3 or ( :pht3 = '__'                      &
                                           and state is null ) )               &
                 and ( :pht1 = '___________________'                           &
                                           and city is null )                  &   
                   ::OR_WHERE                                        )         &
              etc
Temp I000 integer unsigned size 1
Temp I255 integer unsigned size 1 initial 255
Procedure SELECT
 Begin

      let PATH = 1
      prompt CITY 
       if prompok 
         then begin
              let pht1 = fieldtext
              ; Note we are using the SQL wild characters '%' and '_'
              if not matchpattern ( pht1 , '(@((!_)|(!%))@' ) ; we can do an equal predicate 
              then let PATH = 3
              ; This is a city field it should start with a letter i.e. ^ in the matchpattern

              ; if so we can do a between predicate and a like predicate.
              else if matchpattern ( pht1 , '(^@((!_)|(!%))@' )                    

                   then begin
                      let ix = min ( index ( (pht1 + '%') , '%' ), index ( (pht1 + '_' ), '_' ) )
                      let lpht1 = pht1 [1: ( IX - 1 ) ] + CHAR ( I000); 
                      let hpht1 = pht1[ 1: (IX - 1)  ] + CHAR ( I255)
                      let PATH = 2
                      end
                  else begin ; just a like predicate
                      let PATH = 1  ; we have no leading edge character and SQL wildcharacters
                      end

                end
         else  let pht1 = '___________________' ; user does not want to select on city

;
      prompt CUST_NAME  
      if prompok 
             then let pht2 = fieldtext
             else  let pht2 = '___________________'
      
      prompt STATE 
      if prompok 
             then let pht1 = fieldtext
             else  let pht1 = '__'
 
      etc
 
  CURSOR zyz PRIMARY KEY EMPLOYEE
  ACCESS OR_WHERE ( or city like :pht1 )
  ACCESS OR_WHERE ( or city between :lpht1 and :hpht1 and city like :pht1')
  ACCESS OR_WHERE ( or city = :pht1 )


Has anyone managed to get PowerHouse SQL to recognize the CONTAINS predicate?

It would be very useful if you have used FULLTEXT in MS SQL Server.

 

  
Regards,
Peter Bateman







From: kbarrett00 at hotmail.com
To: powerh-l at lists.sowder.com
Subject: 'Dynamic' Where (was Hey! Long time since...)
Date: Thu, 5 Feb 2009 10:35:56 -0800



Thanks Ken, that is a good tip I certainly can use sometime. I will see if I can work it in here. 
 
What I'm dealing with is 8 variables that the user can put values into. For each field that is not null I take the value and create "and city like :input1" to append to my exisiting where clause. They are not exclusive.  
I'm trying to avoid building a gigantic Case statement which is what I think using Darren's approach would require. His logoc has a very simple append to the declared SQL (::and_where_clause) but in the find procedure you then load the sql open statement as follows
 sql open xyz and_where_clause(and city like :input1)
 
I'm sort of hoping I can load up a temp input99 with inputs 1-8 and just do sql open xyz and_where_clause(:input99)
I'm trying several things today, will let the list know if I get them to work.
 
I've never built a search screen with Quick before so new territory even though I'm a 20 year PH vet.
 
Karen 

> I am not sure exactly what you are trying to achieve, but I have a screen
> were the user can change the way in which records are retrieved. I use a
> UNION ALL with a temporary variable to make this work.
> 
> Maybe this would work in your case.
> 
> Ken










Stay up to date on your PC, the Web, and your mobile phone with Windows Live. See Now


_________________________________________________________________
Share photos with friends on Windows Live Messenger
http://go.microsoft.com/?linkid=9650734
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20090304/19fe3937/attachment.htm