Hi Nadya,

There is another factor in here, to create all of those small SQLs and
combining them may take also time. Like I should have some algorithm behind it
to make those decisions while with Macro there is no need to do any thing at all.

So I need to test it. But I think it would be faster then the macro.

Regards,

Doron

>>
>>One more thing Macro is OK but if can take all small SQLs into 1 prg
>>then I don't need to use any Macro. The question is if I save any
>>time by breaking it to small SQLs and combing it, versus this Macro
>>substitution.
>>
>>Regards,
>>
>>Doron
>
>If you're thinking about creating SQL statement as a prg on the fly, compile it and run it, then YES, this would be a better approach and should save time comparing with running macro substitution.
>>
>>>
>>>You can get around this limitation by using macro substitution. Something like

lcFiledLIst = "Filed1, IIF(filed2, ...), .."
>>>lcFrom = "From Mytable"
>>>lcJoin = "JOIN ..."
>>>lcWhere = "Where ...."
>>>lcGroupBy = ...
>>>lcOrderBy = ...
>>>
>>>SELECT &lcFiledLIst &lcFrom ;
>>>  &lcJoin ;
>>>  &lcWhere ;
>>>  &lcGroupBy ;
>>>  &lcOrderBy ;
>>>INTO CURSOR ResultCursor

AS you can see the select command is much shorter than 8,192 characters. Just remember that each macro substitution is limited to 8,192 characters also.
>>>
>>>>Hi Sergey,
>>>>
>>>>That was not for a specific SQL statement I have developed a query and report
>>>>and engine and trying to solve problems in general. Also you can check this
>>>>link to see how that query engine works: http://www.dfarber.com/query_engine.htm
>>>>
>>>>But if you have some ideas for SQL statement improvement please let me look at
>>>>that. In some cases instead of using long tables names it is possible to have
>>>>an alias with short name, but that’s may not be enough in some cases when there
>>>>are some expression against each field like IIF() and more.
>>>>
>>>< snip >