Is it possible to change the DW SQL statement at runtime?

I want to retrieve records according to a retrieval argument if the user supply
it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
from the SQL statement).

Thanks.
Michael

Michael, you can do what your asking two ways:

1) If there is not much data to retrieve, then retrieve it all then use the
SetFilter ( ) and Filter ( ) methods of the DataWindow control to narrow down
what's visible.  The nice thing about this technique is that you don't have to
go back to the database if the user wants to change the filter.

2) Set up your DataWindow object with a SQL statement with no WHERE clause as
the default.  If you need to add a WHERE clause at runtime, use:

dw_1.Object.DataWindow.Table.Select += ' WHERE {rest of WHERE clause}'

then do your Retrieve ( )



On Fri, 30 Jul 1999 13:17:42 -0400,
 in powersoft.public.powerbuilder.general
Michael <mfong@ultramar.ca> wrote: 
>Is it possible to change the DW SQL statement at runtime?
>
>I want to retrieve records according to a retrieval argument if the user supply
>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>from the SQL statement).
>
>Thanks.
>Michael

Thanks a lot. I`ll go for the 2nd solution.

I was trying with:
dw_1.Object.DataWindow.Table.SQLSelect (.SQLSelect instead of .Select)
even though it seems to display the same SQL statement when I put it in a
message box, I could not assign anything to it.

".Select" works fine. BTW do you know what is the difference between .SQLSelect
and .Select?

Regards,
Michael


On Fri, 30 Jul 1999 13:48:56 -0400,
 in powersoft.public.powerbuilder.general
Brad Ashton <bashton@centric-corp.com> wrote: 
>Michael, you can do what your asking two ways:
>
>1) If there is not much data to retrieve, then retrieve it all then use the
>SetFilter ( ) and Filter ( ) methods of the DataWindow control to narrow down
>what's visible.  The nice thing about this technique is that you don't have to
>go back to the database if the user wants to change the filter.
>
>2) Set up your DataWindow object with a SQL statement with no WHERE clause as
>the default.  If you need to add a WHERE clause at runtime, use:
>
>dw_1.Object.DataWindow.Table.Select += ' WHERE {rest of WHERE clause}'
>
>then do your Retrieve ( )
>
>
>
>On Fri, 30 Jul 1999 13:17:42 -0400,
> in powersoft.public.powerbuilder.general
>Michael <mfong@ultramar.ca> wrote: 
>>Is it possible to change the DW SQL statement at runtime?
>>
>>I want to retrieve records according to a retrieval argument if the user
supply
>>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>>from the SQL statement).
>>
>>Thanks.
>>Michael

You can setup your SQL something like this:
SELECT   "CUSTOMER"."CUSTOMERNAME" ,           
           "CUSTOMER"."AREACODE" ,
           "CUSTOMER"."TELEPHONE" ,
           "CUSTOMER"."FAXAREACODE" ,
           "CUSTOMER"."FAXNUMBER" ,
           "CUSTOMER"."EMAILADDRESS" 			    
          FROM "CUSTOMER"      
          WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )  and
          (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone or   (
:telephone = '*') ) and
          (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
(:customer_name = '*') ) 
Here I can either supply 1,2 or 3 arguments or no arguments. If I don't supply
an argument it will retrieve everything.

On Fri, 30 Jul 1999 13:17:42 -0400,
 in powersoft.public.powerbuilder.general
Michael <mfong@ultramar.ca> wrote: 
>Is it possible to change the DW SQL statement at runtime?
>
>I want to retrieve records according to a retrieval argument if the user supply
>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>from the SQL statement).
>
>Thanks.
>Michael

Could not make your idea work.

1. When I send only 2 arguments instead of 3, I get a message like expecting 3
arguments but got only 2.

2. Even if I send 3 arguments, it does not retrieve anything at all. Even tried
sending a NULL value.

Using PB6.5 and MS SQL Server 6.5

Michael
P.S. I think you can only supply more retrieval arguments than what the DW is
expecting, but not the other way around.


On Fri, 30 Jul 1999 14:05:56 -0400,
 in powersoft.public.powerbuilder.general
Mike <mdalsant@domdir.com> wrote: 
>You can setup your SQL something like this:
>SELECT   "CUSTOMER"."CUSTOMERNAME" ,           
>           "CUSTOMER"."AREACODE" ,
>           "CUSTOMER"."TELEPHONE" ,
>           "CUSTOMER"."FAXAREACODE" ,
>           "CUSTOMER"."FAXNUMBER" ,
>           "CUSTOMER"."EMAILADDRESS" 			    
>          FROM "CUSTOMER"      
>          WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )  and
>          (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone or
(
>:telephone = '*') ) and
>          (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
>(:customer_name = '*') ) 
>Here I can either supply 1,2 or 3 arguments or no arguments. If I don't supply
>an argument it will retrieve everything.
>
>On Fri, 30 Jul 1999 13:17:42 -0400,
> in powersoft.public.powerbuilder.general
>Michael <mfong@ultramar.ca> wrote: 
>>Is it possible to change the DW SQL statement at runtime?
>>
>>I want to retrieve records according to a retrieval argument if the user
supply
>>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>>from the SQL statement).
>>
>>Thanks.
>>Michael

Mike,
That's a flexible approach, but the ORs will cause a sequential scan of the
customer table.  Probably OK for a small table, but this will kill
performance for a really large table, even if you provide all 3 arguments.

Paul Horan
VCI
Springfield, MA

Mike <mdalsant@domdir.com> wrote in message
news:fSk6rYr2#GA.111@forums.sybase.com...
> You can setup your SQL something like this:
> SELECT   "CUSTOMER"."CUSTOMERNAME" ,
>            "CUSTOMER"."AREACODE" ,
>            "CUSTOMER"."TELEPHONE" ,
>            "CUSTOMER"."FAXAREACODE" ,
>            "CUSTOMER"."FAXNUMBER" ,
>            "CUSTOMER"."EMAILADDRESS"
>           FROM "CUSTOMER"
>           WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )
and
>           (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone
or   (
> :telephone = '*') ) and
>           (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
> (:customer_name = '*') )
> Here I can either supply 1,2 or 3 arguments or no arguments. If I don't
supply
> an argument it will retrieve everything.
>
> On Fri, 30 Jul 1999 13:17:42 -0400,
>  in powersoft.public.powerbuilder.general
> Michael <mfong@ultramar.ca> wrote:
> >Is it possible to change the DW SQL statement at runtime?
> >
> >I want to retrieve records according to a retrieval argument if the user
supply
> >it, otherwise I want to retrieve all records (i.e. removing the WHERE
clause
> >from the SQL statement).
> >
> >Thanks.
> >Michael

The PB 6.5 help files state:

"SQLSelect - The most recently executed SELECT statement. Setting this has no
effect. See Select in this table."

The key word here is "executed".  Since you can change the SELECT on the fly (as
you want to do) you could execute a SELECT, then change it, but the SQLSelect
attribute will still hold the one that you last executed.  I assume that if no
SELECT has been executed yet that PowerBuilder simply defaults this attribute
the SELECT coded into the DataWindow, which is why it appears to provide you
with the same data.

One word of caution - by setting the Select attribute directly as in the example
below, PowerBuilder DOES NOT VALIDATE the SQL.  So, if anything is wrong with
the SELECT you code (i.e. bad column/table name, etc) then you will only find
out when you go to Retrieve ( ).  

If you want the SQL to be validated before doing a Retrieve ( ), then use the
DataWindow SetSQLSelect ( ) method.  It will perform slower as it has to go to
the database and validate the SQL.  How much slower will depend on your
database, network, server, client, etc.


On Fri, 30 Jul 1999 14:04:29 -0400,
 in powersoft.public.powerbuilder.general
Michael <mfong@ultramar.ca> wrote: 
>Thanks a lot. I`ll go for the 2nd solution.
>
>I was trying with:
>dw_1.Object.DataWindow.Table.SQLSelect (.SQLSelect instead of .Select)
>even though it seems to display the same SQL statement when I put it in a
>message box, I could not assign anything to it.
>
>".Select" works fine. BTW do you know what is the difference between .SQLSelect
>and .Select?
>
>Regards,
>Michael
>
>
>On Fri, 30 Jul 1999 13:48:56 -0400,
> in powersoft.public.powerbuilder.general
>Brad Ashton <bashton@centric-corp.com> wrote: 
>>Michael, you can do what your asking two ways:
>>
>>1) If there is not much data to retrieve, then retrieve it all then use the
>>SetFilter ( ) and Filter ( ) methods of the DataWindow control to narrow down
>>what's visible.  The nice thing about this technique is that you don't have to
>>go back to the database if the user wants to change the filter.
>>
>>2) Set up your DataWindow object with a SQL statement with no WHERE clause as
>>the default.  If you need to add a WHERE clause at runtime, use:
>>
>>dw_1.Object.DataWindow.Table.Select += ' WHERE {rest of WHERE clause}'
>>
>>then do your Retrieve ( )
>>
>>
>>
>>On Fri, 30 Jul 1999 13:17:42 -0400,
>> in powersoft.public.powerbuilder.general
>>Michael <mfong@ultramar.ca> wrote: 
>>>Is it possible to change the DW SQL statement at runtime?
>>>
>>>I want to retrieve records according to a retrieval argument if the user
>supply
>>>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>>>from the SQL statement).
>>>
>>>Thanks.
>>>Michael

The datawindow expects the number of arguments that it was designed
with. The argument list is stored outside of the SQL statement, and I
don't *think* it is modifiable through a Modify() function. If you
want to change the number of arguments the datawindow requires, I'd
expect the only way to do this would be to get the syntax of the
entire datawindow with Describe(), modify the datawindow to the way
you want it (find the argument list, change it...) and Create() a
datawindow object with the new syntax. 

I think what the other Mike might have been suggesting was to create
your SQL in a way that you can pass it dummy values for arguments so
that the SQL ignores it. In his case, the dummy value for cust_id was
0, so that if 0 was passed, the DBMS wouldn't limit the data set based
on CUSTOMER.CUST_ID.

Good luck,

Terry [TeamSybase] and Sequel the techno-kitten

On Fri, 30 Jul 1999 14:57:17 -0400, "Michael" <mfong@ultramar.ca>
wrote:

>Could not make your idea work.
>
>1. When I send only 2 arguments instead of 3, I get a message like expecting 3
>arguments but got only 2.
>
>2. Even if I send 3 arguments, it does not retrieve anything at all. Even tried
>sending a NULL value.
>
>Using PB6.5 and MS SQL Server 6.5
>
>Michael
>P.S. I think you can only supply more retrieval arguments than what the DW is
>expecting, but not the other way around.
>
>
>On Fri, 30 Jul 1999 14:05:56 -0400,
> in powersoft.public.powerbuilder.general
>Mike <mdalsant@domdir.com> wrote: 
>>You can setup your SQL something like this:
>>SELECT   "CUSTOMER"."CUSTOMERNAME" ,           
>>           "CUSTOMER"."AREACODE" ,
>>           "CUSTOMER"."TELEPHONE" ,
>>           "CUSTOMER"."FAXAREACODE" ,
>>           "CUSTOMER"."FAXNUMBER" ,
>>           "CUSTOMER"."EMAILADDRESS" 			    
>>          FROM "CUSTOMER"      
>>          WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )  and
>>          (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone or
>(
>>:telephone = '*') ) and
>>          (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
>>(:customer_name = '*') ) 
>>Here I can either supply 1,2 or 3 arguments or no arguments. If I don't supply
>>an argument it will retrieve everything.
>>
>>On Fri, 30 Jul 1999 13:17:42 -0400,
>> in powersoft.public.powerbuilder.general
>>Michael <mfong@ultramar.ca> wrote: 
>>>Is it possible to change the DW SQL statement at runtime?
>>>
>>>I want to retrieve records according to a retrieval argument if the user
>supply
>>>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>>>from the SQL statement).
>>>
>>>Thanks.
>>>Michael

I should have given you a bit more info,sorry, in your pfc_retrieve event you'll
have to validate your arguments such as:

if isnull(il_custid) then
	       il_custid = -0
end if
		
if len(trim(is_telephone)) = 0 or isnull(is_telephone) then
	 is_telephone = '*'
end if

if len(trim(is_name)) > 0 or isnull(is_telephone) then
	is_name = is_name + '%'
else
	is_name = '*'
end if

return this.retrieve(il_custid,is_telephone,is_name)

If I don't pass any values in my arguments my arguments will still have values
(as per above) so the retrieve would look like : retrieve(0,'*','*'), this which
would retrieve everything in the customer table. If I only wanted to search by
name then I would pass a value to my name field then validation as above and the
retrieve would look like : retrieve(0,'*','Smith%'). Hope this clarifies things
a bit more.

On Fri, 30 Jul 1999 14:57:17 -0400,
 in powersoft.public.powerbuilder.general
Michael <mfong@ultramar.ca> wrote: 
>Could not make your idea work.
>
>1. When I send only 2 arguments instead of 3, I get a message like expecting 3
>arguments but got only 2.
>
>2. Even if I send 3 arguments, it does not retrieve anything at all. Even tried
>sending a NULL value.
>
>Using PB6.5 and MS SQL Server 6.5
>
>Michael
>P.S. I think you can only supply more retrieval arguments than what the DW is
>expecting, but not the other way around.
>
>
>On Fri, 30 Jul 1999 14:05:56 -0400,
> in powersoft.public.powerbuilder.general
>Mike <mdalsant@domdir.com> wrote: 
>>You can setup your SQL something like this:
>>SELECT   "CUSTOMER"."CUSTOMERNAME" ,           
>>           "CUSTOMER"."AREACODE" ,
>>           "CUSTOMER"."TELEPHONE" ,
>>           "CUSTOMER"."FAXAREACODE" ,
>>           "CUSTOMER"."FAXNUMBER" ,
>>           "CUSTOMER"."EMAILADDRESS" 			    
>>          FROM "CUSTOMER"      
>>          WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )
and
>>          (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone or
>(
>>:telephone = '*') ) and
>>          (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
>>(:customer_name = '*') ) 
>>Here I can either supply 1,2 or 3 arguments or no arguments. If I don't supply
>>an argument it will retrieve everything.
>>
>>On Fri, 30 Jul 1999 13:17:42 -0400,
>> in powersoft.public.powerbuilder.general
>>Michael <mfong@ultramar.ca> wrote: 
>>>Is it possible to change the DW SQL statement at runtime?
>>>
>>>I want to retrieve records according to a retrieval argument if the user
>supply
>>>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>>>from the SQL statement).
>>>
>>>Thanks.
>>>Michael

The response has actually been quite good. I use this routine on a small table
(couple of thousand records) and the retrieval is instantaneous. I also use it
on a table of almost 400,000 rows ,it takes about 5 secs, but that's acceptable
considering the users don't need to retrieve from that table constantly. But
you're right, it will do a table scan.
On Fri, 3
 1999 15:46:35 -0400,
 in powersoft.public.powerbuilder.general
Paul Horan <paulh@NOSPAMmindspring.com> wrote: 
>Mike,
>That's a flexible approach, but the ORs will cause a sequential scan of the
>customer table.  Probably OK for a small table, but this will kill
>performance for a really large table, even if you provide all 3 arguments.
>
>Paul Horan
>VCI
>Springfield, MA
>
>Mike <mdalsant@domdir.com> wrote in message
>news:fSk6rYr2#GA.111@forums.sybase.com...
>> You can setup your SQL something like this:
>> SELECT   "CUSTOMER"."CUSTOMERNAME" ,
>>            "CUSTOMER"."AREACODE" ,
>>            "CUSTOMER"."TELEPHONE" ,
>>            "CUSTOMER"."FAXAREACODE" ,
>>            "CUSTOMER"."FAXNUMBER" ,
>>            "CUSTOMER"."EMAILADDRESS"
>>           FROM "CUSTOMER"
>>           WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )
>and
>>           (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone
>or   (
>> :telephone = '*') ) and
>>           (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
>> (:customer_name = '*') )
>> Here I can either supply 1,2 or 3 arguments or no arguments. If I don't
>supply
>> an argument it will retrieve everything.
>>
>> On Fri, 30 Jul 1999 13:17:42 -0400,
>>  in powersoft.public.powerbuilder.general
>> Michael <mfong@ultramar.ca> wrote:
>> >Is it possible to change the DW SQL statement at runtime?
>> >
>> >I want to retrieve records according to a retrieval argument if the user
>supply
>> >it, otherwise I want to retrieve all records (i.e. removing the WHERE
>clause
>> >from the SQL statement).
>> >
>> >Thanks.
>> >Michael
>
>

Mike, I guess that it seems like a lot work to simply filter a list, and isn't
very intuative or understandable.  

What if you want to provide a window with a number of SLEs, one for each field
in your table, then have the code dynamically check what was entered and filter
the DataWindow?

With your method you have a heavy database hit each and every query because of
the size of your WHERE clause.  That may not be all that bad in a small
database, but once you get several hundred thousand records in the table most
databases will simply give up any pretense of using an index and do a table scan
anyway.

The goal should be to pass to the database only what it needs to return to you
the correct data.  Anything else will slow it down.


On Fri, 30 Jul 1999 17:03:53 -0400,
 in powersoft.public.powerbuilder.general
Mike <mdalsant@domdir.com> wrote: 
>I should have given you a bit more info,sorry, in your pfc_retrieve event
you'll
>have to validate your arguments such as:
>
>if isnull(il_custid) then
>	       il_custid = -0
>end if
>		
>if len(trim(is_telephone)) = 0 or isnull(is_telephone) then
>	 is_telephone = '*'
>end if
>
>if len(trim(is_name)) > 0 or isnull(is_telephone) then
>	is_name = is_name + '%'
>else
>	is_name = '*'
>end if
>
>return this.retrieve(il_custid,is_telephone,is_name)
>
>If I don't pass any values in my arguments my arguments will still have values
>(as per above) so the retrieve would look like : retrieve(0,'*','*'), this
which
>would retrieve everything in the customer table. If I only wanted to search by
>name then I would pass a value to my name field then validation as above and
the
>retrieve would look like : retrieve(0,'*','Smith%'). Hope this clarifies things
>a bit more.
>
>On Fri, 30 Jul 1999 14:57:17 -0400,
> in powersoft.public.powerbuilder.general
>Michael <mfong@ultramar.ca> wrote: 
>>Could not make your idea work.
>>
>>1. When I send only 2 arguments instead of 3, I get a message like expecting 3
>>arguments but got only 2.
>>
>>2. Even if I send 3 arguments, it does not retrieve anything at all. Even
tried
>>sending a NULL value.
>>
>>Using PB6.5 and MS SQL Server 6.5
>>
>>Michael
>>P.S. I think you can only supply more retrieval arguments than what the DW is
>>expecting, but not the other way around.
>>
>>
>>On Fri, 30 Jul 1999 14:05:56 -0400,
>> in powersoft.public.powerbuilder.general
>>Mike <mdalsant@domdir.com> wrote: 
>>>You can setup your SQL something like this:
>>>SELECT   "CUSTOMER"."CUSTOMERNAME" ,           
>>>           "CUSTOMER"."AREACODE" ,
>>>           "CUSTOMER"."TELEPHONE" ,
>>>           "CUSTOMER"."FAXAREACODE" ,
>>>           "CUSTOMER"."FAXNUMBER" ,
>>>           "CUSTOMER"."EMAILADDRESS" 			    
>>>          FROM "CUSTOMER"      
>>>          WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )
>and
>>>          (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") = :telephone or
>>(
>>>:telephone = '*') ) and
>>>          (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name) or
>>>(:customer_name = '*') ) 
>>>Here I can either supply 1,2 or 3 arguments or no arguments. If I don't
supply
>>>an argument it will retrieve everything.
>>>
>>>On Fri, 30 Jul 1999 13:17:42 -0400,
>>> in powersoft.public.powerbuilder.general
>>>Michael <mfong@ultramar.ca> wrote: 
>>>>Is it possible to change the DW SQL statement at runtime?
>>>>
>>>>I want to retrieve records according to a retrieval argument if the user
>>supply
>>>>it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
>>>>from the SQL statement).
>>>>
>>>>Thanks.
>>>>Michael

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
A simple and worked out way goes like this - what I tried.
<p>Create the dw with the basic SQL (typically without any where clause)
which you need as the SQL to build all the where clause at any time. Now
before the first retrieval store the dw sql in an instance variable. typically
you could use either dw.Descibe("DataWindow.Table.Select") and get the
sql stored in LS_ORIGINAL_SQL.
<p>Now you have the base SQL available at any time will not bother you.
At any instance where you need to retrieve with any number or specific
SQL create the string variable - only the WHERE clause, typically " WHERE
CUST_ID = " + String(li_no) + " AND ..." so on... in&nbsp; ls_where_clause
variable. At any time where you need to&nbsp; change the where criteria,
just change this variable and do a dw.Modify("DataWindow.Table.Select=
' " + LS_ORIGINAL_SQL + ls_where_clause + " '") and do the Retrieve().
No need to pass the args!
<p>Note that if your base dw sql uses the '~' characters&nbsp; in table
names and column names, then you have to take care of that while building
the where clause.
<p>All the best.
<p>Suresh
<p>Michael wrote:
<blockquote TYPE=CITE>Is it possible to change the DW SQL statement at
runtime?
<p>I want to retrieve records according to a retrieval argument if the
user supply
<br>it, otherwise I want to retrieve all records (i.e. removing the WHERE
clause
<br>from the SQL statement).
<p>Thanks.
<br>Michael</blockquote>
</html>

Brad Ashton <bashton@centric-corp.com> wrote in message
news:ZXp3kUt2#GA.302@forums.sybase.com...
> What if you want to provide a window with a number of SLEs, one for each
field
> in your table,

No, no, not SLEs again :-))

Michael wrote:
> 
> Is it possible to change the DW SQL statement at runtime?
> 
> I want to retrieve records according to a retrieval argument if the user supply
> it, otherwise I want to retrieve all records (i.e. removing the WHERE clause
> from the SQL statement).
> 
> Thanks.
> Michael

Michael,

If I understand it correctly, what you need is 

1) a datawindow in query mode that accepts user's input of the retrieval
argument

and 2) another datawindow, shared data with the first one, that display
the retrieved data.

HTH,

-- 
Lijun

=======================================================
Lijun Yang  mailto:Lijun.Yang@bigfoot.com
=======================================================

Thanks Mike.

I misunderstood your initial posting. I was concentrating on the "or
(:telephone = '*')" and "or (:customer_name = '*')" and thought that "or
 :cust_id =  -0)" was a mistype. I was wondering how that thing would solve
my problem :)

BTW is that a minus zero (-0) or a zero (0)? I would think it is a zero, but
then in a later posting by you, you put minus zero again.

puzzled :)
Michael


Mike <mdalsant@domdir.com> wrote in message
news:mFAcH8s2#GA.339@forums.sybase.com...
> I should have given you a bit more info,sorry, in your pfc_retrieve event
you'll
> have to validate your arguments such as:
>
> if isnull(il_custid) then
>        il_custid = -0
> end if
>
> if len(trim(is_telephone)) = 0 or isnull(is_telephone) then
> is_telephone = '*'
> end if
>
> if len(trim(is_name)) > 0 or isnull(is_telephone) then
> is_name = is_name + '%'
> else
> is_name = '*'
> end if
>
> return this.retrieve(il_custid,is_telephone,is_name)
>
> If I don't pass any values in my arguments my arguments will still have
values
> (as per above) so the retrieve would look like : retrieve(0,'*','*'), this
which
> would retrieve everything in the customer table. If I only wanted to
search by
> name then I would pass a value to my name field then validation as above
and the
> retrieve would look like : retrieve(0,'*','Smith%'). Hope this clarifies
things
> a bit more.
>
> On Fri, 30 Jul 1999 14:57:17 -0400,
>  in powersoft.public.powerbuilder.general
> Michael <mfong@ultramar.ca> wrote:
> >Could not make your idea work.
> >
> >1. When I send only 2 arguments instead of 3, I get a message like
expecting 3
> >arguments but got only 2.
> >
> >2. Even if I send 3 arguments, it does not retrieve anything at all. Even
tried
> >sending a NULL value.
> >
> >Using PB6.5 and MS SQL Server 6.5
> >
> >Michael
> >P.S. I think you can only supply more retrieval arguments than what the
DW is
> >expecting, but not the other way around.
> >
> >
> >On Fri, 30 Jul 1999 14:05:56 -0400,
> > in powersoft.public.powerbuilder.general
> >Mike <mdalsant@domdir.com> wrote:
> >>You can setup your SQL something like this:
> >>SELECT   "CUSTOMER"."CUSTOMERNAME" ,
> >>           "CUSTOMER"."AREACODE" ,
> >>           "CUSTOMER"."TELEPHONE" ,
> >>           "CUSTOMER"."FAXAREACODE" ,
> >>           "CUSTOMER"."FAXNUMBER" ,
> >>           "CUSTOMER"."EMAILADDRESS"
> >>          FROM "CUSTOMER"
> >>          WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id
=  -0) )
> and
> >>          (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") =
:telephone or
> >(
> >>:telephone = '*') ) and
> >>          (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name)
or
> >>(:customer_name = '*') )
> >>Here I can either supply 1,2 or 3 arguments or no arguments. If I don't
supply
> >>an argument it will retrieve everything.
> >>
> >>On Fri, 30 Jul 1999 13:17:42 -0400,
> >> in powersoft.public.powerbuilder.general
> >>Michael <mfong@ultramar.ca> wrote:
> >>>Is it possible to change the DW SQL statement at runtime?
> >>>
> >>>I want to retrieve records according to a retrieval argument if the
user
> >supply
> >>>it, otherwise I want to retrieve all records (i.e. removing the WHERE
clause
> >>>from the SQL statement).
> >>>
> >>>Thanks.
> >>>Michael

What if instead of
("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id =  -0) )
we have
( ( :cust_id =  -0) or "CUSTOMER"."CUST_ID" = :cust_id )

Any difference in table scan?

Michael




Paul Horan <paulh@NOSPAMmindspring.com> wrote in message
news:71ydzMs2#GA.302@forums.sybase.com...
> Mike,
> That's a flexible approach, but the ORs will cause a sequential scan of
the
> customer table.  Probably OK for a small table, but this will kill
> performance for a really large table, even if you provide all 3 arguments.
>
> Paul Horan
> VCI
> Springfield, MA
>
> Mike <mdalsant@domdir.com> wrote in message
> news:fSk6rYr2#GA.111@forums.sybase.com...
> > You can setup your SQL something like this:
> > SELECT   "CUSTOMER"."CUSTOMERNAME" ,
> >            "CUSTOMER"."AREACODE" ,
> >            "CUSTOMER"."TELEPHONE" ,
> >            "CUSTOMER"."FAXAREACODE" ,
> >            "CUSTOMER"."FAXNUMBER" ,
> >            "CUSTOMER"."EMAILADDRESS"
> >           FROM "CUSTOMER"
> >           WHERE ("CUSTOMER"."CUST_ID" = :cust_id   or  ( :cust_id
=  -0) )
> and
> >           (("CUSTOMER"."AREACODE" || "CUSTOMER"."TELEPHONE") =
:telephone
> or   (
> > :telephone = '*') ) and
> >           (upper("CUSTOMER"."CUSTOMERNAME") like upper(:customer_name)
or
> > (:customer_name = '*') )
> > Here I can either supply 1,2 or 3 arguments or no arguments. If I don't
> supply
> > an argument it will retrieve everything.
> >
> > On Fri, 30 Jul 1999 13:17:42 -0400,
> >  in powersoft.public.powerbuilder.general
> > Michael <mfong@ultramar.ca> wrote:
> > >Is it possible to change the DW SQL statement at runtime?
> > >
> > >I want to retrieve records according to a retrieval argument if the
user
> supply
> > >it, otherwise I want to retrieve all records (i.e. removing the WHERE
> clause
> > >from the SQL statement).
> > >
> > >Thanks.
> > >Michael
>
>

Errr... I think you misunderstood my initial posting

Say I have a DropDownListBox with department names. (the retrival argument)
If the user select a deparment name, then I'll retrieve data for that
particular department.
If the user does not select any department, then I'll retrieve data for ALL
departments.

The problem was the "WHERE" clause.

Thanks for your posting.
Michael


Lijun Yang <Lijun.Yang@bigfoot.com> wrote in message
news:37A32667.80F3DD2E@bigfoot.com...
> Michael wrote:
> >
> > Is it possible to change the DW SQL statement at runtime?
> >
> > I want to retrieve records according to a retrieval argument if the user
supply
> > it, otherwise I want to retrieve all records (i.e. removing the WHERE
clause
> > from the SQL statement).
> >
> > Thanks.
> > Michael
>
> Michael,
>
> If I understand it correctly, what you need is
>
> 1) a datawindow in query mode that accepts user's input of the retrieval
> argument
>
> and 2) another datawindow, shared data with the first one, that display
> the retrieved data.
>
> HTH,
>
> --
> Lijun
>
> =======================================================
> Lijun Yang  mailto:Lijun.Yang@bigfoot.com
> =======================================================