At 10:48 AM 21/10/2007, you wrote:

>Hello Helen,
>
>> Don't you need left joins to the lookup tables? Your query returns
>> results only where ALL of the inner joins have matches, otherwise nothing.
>
>yes, certainly... Thanks a lot :)
>
>and one more question - what is better way:
>
>to use left join view
>or
>use stored procedure, where get need data from tables ?

The "better way" is the one that satisfies your requirements better, e.g., you need speed? Test both ways and determine which way is faster for retrieving the set you want.

Examples of requirements:

You need to filter? For a SP, you need to know which fields will need to be filtered, and provide input parameters for the filtered fields. The PSQL is then more complicated (although it need not slow down the execution).

You need to sort (ORDER BY or GROUP BY)? Sorting ability with the SP is static (has to be done at compile time: it cannot be parameterised.) You *could* use EXECUTE STATEMENT for this, however.

You want to join your set to another set? Joining to an SP, while allowed by the engine, involves a lot of resources and is terribly slow.

A select on a view can be filtered or sorted. A view can be joined to another set. But views have no keys or indexes of their own, so can be slow if the underlying structure is not well optimised (the case with your view).

As an alternative to using left joins for your conditional lookups, you might like to experiment with the stopwatch on using correlated subqueries to get the names of the customer entities. Under some conditions, this can be more efficient than the left joins.

./heLen