<html>
<head>
<style>
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Verdana
}
</style>
</head>
<body class='hmmessage'>
Hi Karen:<BR> <BR>;If any of the fields are also leading edge segments of an index<BR>;you may want to create a between predicate instead of a like.<BR>
;Our challenge then is to create versions of the cursor that supports<BR>
; exact match (PATH 3 ), like match(PATH 1 ) and between match (PATH 2) .<BR>
;<BR> For example:- if city is a leading edge segment we can substitute the<BR> appropiate syntax into the cursor.<BR><BR> DECLARE zyz cursor in mydb for select * &<BR> from EMPLOYEES &<BR> where &<BR> ( cust_name like :pht2 or ( :pht2 = '___________________' &<BR> and cust_name is null ) ) &<BR> and ( state like :pht3 or ( :pht3 = '__' &<BR> and state is null ) ) &<BR> and ( :pht1 = '___________________' &<BR> and city is null ) & <BR> ::OR_WHERE ) &<BR> etc<BR>Temp I000 integer unsigned size 1<BR>Temp I255 integer unsigned size 1 initial 255<BR>Procedure SELECT<BR> Begin<BR>
let PATH = 1<BR> prompt CITY <BR> if prompok <BR> then begin<BR> let pht1 = fieldtext<BR> ; Note we are using the SQL wild characters '%' and '_'<BR> if not matchpattern ( pht1 , '(@((!_)|(!%))@' ) ; we can do an equal predicate <BR> then let PATH = 3<BR> ; This is a city field it should start with a letter i.e. ^ in the matchpattern<BR>
; if so we can do a between predicate and a like predicate.<BR> else if matchpattern ( pht1 , '(<A href="mailto:^@((!_)|(!%))@'">^@((!_)|(!%))@'</A> ) <BR>
then begin<BR> let ix = min ( index ( (pht1 + '%') , '%' ), index ( (pht1 + '_' ), '_' ) )<BR> let lpht1 = pht1 [1: ( IX - 1 ) ] + CHAR ( I000); <BR> let hpht1 = pht1[ 1: (IX - 1) ] + CHAR ( I255)<BR> let PATH = 2<BR> end<BR> else begin ; just a like predicate<BR> let PATH = 1 ; we have no leading edge character and SQL wildcharacters<BR> end<BR>
end<BR> else let pht1 = '___________________' ; user does not want to select on city<BR>
;<BR> prompt CUST_NAME <BR> if prompok <BR> then let pht2 = fieldtext<BR> else let pht2 = '___________________'<BR> <BR> prompt STATE <BR> if prompok <BR> then let pht1 = fieldtext<BR> else let pht1 = '__'<BR> <BR> etc<BR> <BR> CURSOR zyz PRIMARY KEY EMPLOYEE<BR> ACCESS OR_WHERE ( or city like :pht1 )<BR> ACCESS OR_WHERE ( or city between :lpht1 and :hpht1 and city like :pht1')<BR> ACCESS OR_WHERE ( or city = :pht1 )<BR>
<BR>Has anyone managed to get PowerHouse SQL to recognize the CONTAINS predicate?<BR>
It would be very useful if you have used FULLTEXT in MS SQL Server.<BR>
<BR>
<BR>Regards,<BR>Peter Bateman<BR><BR>
<HR id=stopSpelling>
<BR>
<BR>From: kbarrett00@hotmail.com<BR>To: powerh-l@lists.sowder.com<BR>Subject: 'Dynamic' Where (was Hey! Long time since...)<BR>Date: Thu, 5 Feb 2009 10:35:56 -0800<BR><BR>
<STYLE>
.ExternalClass .EC_hmmessage P
{padding:0px;}
.ExternalClass body.EC_hmmessage
{font-size:10pt;font-family:Verdana;}
</STYLE>
Thanks Ken, that is a good tip I certainly can use sometime. I will see if I can work it in here. <BR> <BR>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. <BR>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<BR> sql open xyz and_where_clause(and city like :input1)<BR> <BR>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)<BR>I'm trying several things today, will let the list know if I get them to work.<BR> <BR>I've never built a search screen with Quick before so new territory even though I'm a 20 year PH vet.<BR> <BR>Karen <BR><BR>> I am not sure exactly what you are trying to achieve, but I have a screen<BR>> were the user can change the way in which records are retrieved. I use a<BR>> UNION ALL with a temporary variable to make this work.<BR>> <BR>> Maybe this would work in your case.<BR>> <BR>> Ken<BR><BR><BR><BR><BR>
<HR>
<BR>
<BR>Stay up to date on your PC, the Web, and your mobile phone with Windows Live. <A href="http://clk.atdmt.com/MRT/go/msnnkwxp1020093185mrt/direct/01/">See Now</A><BR><BR><br /><hr />Tell the whole story with photos, right from your Messenger window. <a href='http://go.microsoft.com/?linkid=9650732' target='_new'>Learn how!</a></body>
</html>