Repeated Sub-Queries 1

[BrianTyler]

I have several objects which are the components of an address (e.g. ADDR1, ADDR2, ADDR3, POSTCODE). The address table includes a record type: 1=CURRENT 2=TEMPORARY.

I now wish to design objects which will give me fields from the temporary address, but if there is no temporary address to return current address details. I do not really want the user to have to remember to test for the record type as there is a possibility of duplicates if thy forget.

I can do this by including 'where ADDRTYPE = (select max(ADDRTYPE) from CUSTADDR x where x.CUSTNO = CUSTADDR.CUSTNO)' in the object definitions for TEMPORARY address.

When the SQL is generated,the subquery will be repeated in the SQL once for each object, which could destroy performance.

Has anyone any ideas how to stop the repeated generation of the subquery.

(DB2 on AIX + BO 6.5)

Thanks

Brian

 

[blom0344]

I suppose the CUSTADDR table will have a composite primary key?
I can imagine a derived table or database view that will store the address data together with an OLAP based field that will rank() over the addresstype. (sort of dummy rank , cause it does not relate to a numerical field)
In that case you should be able to pick the record that contains the value 1.
If both temporary and current exist temp = 1, current = 2.
If only current exist , it will automatically have the value 1. (No subquery needed at all)

Am I making sense? Perhaps I could be more to the point if I know the way your address data is stored

Ties Blom

 

[BrianTyler]

Hi Ties,

There is a single address table. Each address row contains the customer number,the address type (1=current, 2=temporary), status (1=live) and a number of address lines.

In designer I have created objects for each element of the CURRENT address by including a 'where clause' in the design object window.

e.g. custaddr.ca_addr_type = 1 and custaddr.ca_status = 1

Creating similar objects for the latest known address the 'where clause' is extended

e.g. custaddr.ca_addr_type = (select max(x.ca_addr_type) from custaddr x where x.ca_cust_no = custaddr.ca_cust_no and x.ca_status = 1) and custaddr.ca_status = 1

When I include several elements in the string, the complex 'where clause' is repeated for each one:

SELECT
{fn rtrim(CUSTADDR.CA_ADDR1)},
{fn rtrim(CUSTADDR.CA_ADDR2)},
{fn rtrim(CUSTADDR.CA_ADDR3)},
{fn rtrim(CUSTADDR.CA_ADDR4)},
CUSTADDR.CA_PCODE
FROM
CUSTADDR
WHERE
( custaddr.ca_addr_type = (select max(x.ca_addr_type) from custaddr x where x.ca_cust_no = custaddr.ca_cust_no and x.ca_status = 1) and custaddr.ca_status = 1 )
AND ( custaddr.ca_addr_type = (select max(x.ca_addr_type) from custaddr x where x.ca_cust_no = custaddr.ca_cust_no and x.ca_status = 1) and custaddr.ca_status = 1 )
AND ( custaddr.ca_addr_type = (select max(x.ca_addr_type) from custaddr x where x.ca_cust_no = custaddr.ca_cust_no and x.ca_status = 1) and custaddr.ca_status = 1 )
AND ( custaddr.ca_addr_type = (select max(x.ca_addr_type) from custaddr x where x.ca_cust_no = custaddr.ca_cust_no and x.ca_status = 1) and custaddr.ca_status = 1 )
AND ( custaddr.ca_addr_type = (select max(x.ca_addr_type) from custaddr x where x.ca_cust_no = custaddr.ca_cust_no and x.ca_status = 1) and custaddr.ca_status = 1 )

I assume that the repeated sub-query will seriously impact performance.

As far as I can see, the easiest way to stop the sub-query being repeated is to only include it in the POSTCODE object, and to hope that the users always include it in their queries (a bit dangerous).

I cannot see any reliable way to avoid the subquery being repeated, so I am looking for advice.

Brian

 

[blom0344]

Hardcoding where-clauses in BO universes is a definite no-no. You have no control over the resulting SQL once users start mixing objects in query panel.

The usual strategy is to wrap things into CASE statements, which may not be applicable in your case.

From your original post I deduced that for any given customer there may either 1 (current) or 2 entries (temporary / current)

In case no temporary record exists you would need to fetch the current record in your join?

My idea was to create a derived table / view that already stores just one record for a given customer.

If that is the case, then you can build such an object and do away with the where clause.

Ties Blom

 

[BrianTyler]

Thanks again Ties,

Your idea of a view seems to be the most appropriate in this case. It will certainly avoid the repeated sub-query problem.

I'll have a word with the DBA and try to get him to allow one.

Brian