Ed,

You have a very valid point.

I do many of these queries using the ANSI SQL CASE statement against an SQL database where I have no write capabilities to get the "working subset." My choice is to generate my own crosstab in this way or bring back way to much data and do it locally.

I get around the length limitations by breaking up my strings into cMySelect, cMyFrom, cMyWhere, etc.

Not having write capabilities, even for a temp file, has led to some creative thinking on my part for more than just this situation.



** Air Code Warning! **
>>
>>

>>SELECT Batch#, Trans#, ;
>>    SUM(IIF(Category = "RegTime", GrandChild.Quantity, 0)) AS RegTime_amt, ;
>>    SUM(IIF(Category = "Absence", GrandChild.Quantity, 0)) AS Absence_amt, ;
>>    ... ;
>>    FROM < be sure to include the parent tables with the proper joins > ;
>>    INTO CURSOR MyCursor ;
>>    GROUP BY Batch#, Trans#
>>

>>
>
>This is one approach, but I don't see the virtue of a single SQL Select statement here; it's the perfect place for using SQL Select to filter a working subset of the data to (Parent INNER JOIN Child) INNER JOIN GrandChild, and generate a crosstab with end result (the Pivot Table Wizard in VFP6, or GENXTAB from earlier versions of FoxPro); it's especialy the case since you'd have to hard-code all the IIF()s in the Query to account for each category, and could easily end up running into the length of statement limitations. In addition, the IIF() isn't Rushmore-optimizabe, so you end up stepping through the tables IAC, doing the JOINs as Selects lets you create an intermediary result, and then crosstab the result of what can be optimized.
>
>IOW SELECT the working set and pivot the result, rather than hard-coding categories and trying to stuff the whole thing into a single statement.