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
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |
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 > >
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |
<!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 ls_where_clause variable. At any time where you need to 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 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>
![]() |
0 |
![]() |
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 :-))
![]() |
0 |
![]() |
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 =======================================================
![]() |
0 |
![]() |
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
![]() |
0 |
![]() |
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 > >
![]() |
0 |
![]() |
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 > =======================================================
![]() |
0 |
![]() |