255-char limit in listbox sql... 1

[thatguy]

Hey there folks--

I've been away from foxpro for awhile.. too long, I guess.. I'm trying to pull some data into a listbox, but the 255-char limit on the SQL statement is getting in the way. I've tried basing the listbox on a query, but can't figure out how to add a WHERE clause programmatically. I've tried SET FILTER on the cursor, then requery on the listbox, but that just pulls the full dataset. I've tried a local view, but that's the same thing/result as the query. Can someone lend a hand? Here's the SQL...

SELECT Invoices.pkey, Invoices.dstartdate, Invoices.cinvoiceno, Clients.cname, TRANSFORM(NTOM(SUM(Items.rtotal)),"$9999.99") AS totalcost FROM clients INNER JOIN invoices INNER JOIN items ON Invoices.pkey = Items.ninvoicekey ON Clients.pkey = Invoices.nclientkey
 GROUP BY Invoices.pkey, Invoices.dstartdate, Invoices.cinvoiceno, Clients.cname ORDER BY Invoices.cinvoiceno INTO CURSOR curlst

Thanks
-- frank~

 

[craigsboyd]

Sometimes it is necessary to bust the SQL up into sections and add them together into a variable that can then be used:

Local lcSQL

lcSQL = [SELECT Invoices.pkey, Invoices.dstartdate, Invoices.cinvoiceno, Clients.cname, TRANSFORM(NTOM(SUM(Items.rtotal)),"$9999.99") AS totalcost FROM clients INNER JOIN invoices INNER JOIN items ON ]
lcSQL = lcSQL + [Invoices.pkey = Items.ninvoicekey ON Clients.pkey = Invoices.nclientkey GROUP BY Invoices.pkey, Invoices.dstartdate, Invoices.cinvoiceno, ]
lcSQL = lcSQL + [Clients.cname ORDER BY Invoices.cinvoiceno INTO CURSOR curlst]

...then the variable can be used instead.

http://www.sweetpotatosoftware.com

 

[jimstarr]

Hi Frank,

In this particular case you can greatly shorten your statement by abbreviating the file name "Invoices." For example:

SELECT I.pkey, I.dstartdate FROM invoices I

Jim

 

[rgbean]

Another simple option is to put this code in a separate .PRG, and then choose RowSourceType = 4 - Query and put the .PRG name into RowSource.

Rick

 

[DSummZZZ]

You can also start with just breaking up the lines so it isn't one long line, and gain the added benefit of making it more readable:

SELECT Invoices.pkey, Invoices.dstartdate, ;
       Invoices.cinvoiceno, Clients.cname, ;
   TRANSFORM(NTOM(SUM(Items.rtotal)),"$9999.99") ;
      AS totalcost;
   FROM clients ;
   INNER JOIN invoices ;
   INNER JOIN items ON Invoices.pkey = Items.ninvoicekey ;
   ON Clients.pkey = Invoices.nclientkey;
   GROUP BY Invoices.pkey, Invoices.dstartdate, ;
      Invoices.cinvoiceno, Clients.cname ;
   ORDER BY Invoices.cinvoiceno ;
   INTO CURSOR curlst

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[thatguy]

Wow.. well thanks for the responses everyone, but does this mean there's no easy way around the 255-char limit?

I've tried Craig's suggestion with inconsistent results. I built the string up from 3 different pieces and assigned it to the listbox (thisform.lstInvoices.rowsource = lclsqst). It works once, then I change something to make it functional (adding a WHERE clause based on a combobox or two), then suddenly, no more results. I'm still playing with this...

Jim- Unfortunately, after adding a WHERE clause (sometimes with two expressions) the length of the SQL gets to be up around 450 chars, but I'll check [when I get back to my PC] if shortening the table names could get it below 254.

Rick- How would adding a WHERE clause work with the .PRG solution? Would SET FILTER work differently (I tried FILTERing a query and requerying the listbox, but that just pulls the full dataset)? Thinking out loud -- maybe build the SQL on the fly in the PRG using iif()'s to check if the comboboxes have selections? hmm...

Dave- Yeah, the statement is actually broken up in the code window - I'm rather picky about things looking nice. I just clumped it together in here to try and not take up too much space.. *shrug*

Thanks
-- frank~

 

[rgbean]

Frank,
Actually the restrictions on a RowSourceType "Query" .PRG are very few. Any variables used in the SELECT either need to be local or scoped from the outside so they are visible. All you have to do is create a Cursor to be used. Note: Like any program, you can't use ThisForm (properties or methods) and I've never found a way to pass parameters.

Of course if you want to build an SQL SELECT in code based on Form data, then just do it in a Form method, create a cursor and use it as the RowSource with RowSourceType = 2 - Alias.

Rick

 

[thatguy]

Yes!! That's it!! The Star goes to Rick for "Build the SQL in a form method into a cursor and base the listbox on the alias"

Thank you Rick!

 

[oldbowler]

If you are using VFP7 or newer try

TEXT [TO VarName [ADDITIVE] [TEXTMERGE] [NOSHOW]]
TextLines
ENDTEXT

I have SELECT statements with well over 600 characters and have no problem.

 

[rgbean]

oldbowler,
Actually using this technique you can create any command up to the 8,192 character limit!

Rick