>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
Right. It depends on complexity of SQL too...
>
>>>
>>>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 >
If it's not broken, fix it until it is.
My Blog