Levels of nested table expression allowed

[redbadgers]

I used to have a dynamic query within a SQL Procedure that used three levels of nested table expression:
SELECT C.col1, C.col2 FROM (
SELECT B.col1, B.col2 FROM (
SELECT A.col1, A.col2 FROM (
SELECT ... FROM xxx
ORDER BY...
) A
ORDER BY...
) B
ORDER BY...
) C
ORDER BY...;

It compiled fine but the performance was poor. So I rewrote it as static SQL.
But now DB2 complained syntax error immediately after the outmost (first) nested table expression:
SQL0104N An unexpected token "(" was found following "C.COL2 FROM".
Expected tokens may include: ",". LINE NUMBER=132. SQLSTATE=42601

When there was only one level of nested table expression it was fine. I could not find in any DB2 documentation that said nested table expression can or cannot have multiple nesting levels. I tried TABLE optional keyword in between FROM and the first open paren but that didn't help.

The entire SQL SELECT statement was about 8K in length.

Both my DB2 client and server are at DB2 V8.1 FP9.
Any tip or reference is appreciated.
Regards, Nancy

 

[PruSQLer]

I doubt it's a problem with too many nested sub queries, which is really what you have here. I'd guess it's a syntax error of some kind but it's difficult to tell from the SQL you present in your post.

From what I've read, sub queries can have unpredictable effects on the optimizer, specially if you're using non indexed fields.

I recommend a staged approach using Table Expressions, something like this below: (filched from Graeme Birchall's DB2 Cookbook)

WITH ANSWER
rows_wanted AS ================================
(SELECT * ID NAME SALARY SUM_SAL PCT
FROM staff -- ------- -------- -------- ---
WHERE id < 100 70 Rothman 16502.83 34504.58 47
AND UCASE(name) LIKE ’%T%’ 90 Koonitz 18001.75 34504.58 52
),
sum_salary AS
(SELECT SUM(salary) AS sum_sal
FROM rows_wanted)
SELECT id
,name
,salary
,sum_sal
,INT((salary * 100) / sum_sal) AS pct
FROM rows_wanted
,sum_salary
ORDER BY id;

I think your performance has a better chance of being efficient if you take that approach as opposed to nesting all those sub queries. But, Version 8 is not good at handling large amounts of data with Table Expressions so if your tables are very big, this approach might have performance issues also. Then Global Temp Tables might provide a good alternative.

 

[redbadgers]

Thank you! Wished I had read Graeme Birchall's DB2 SQL Cookbook before I had started this venture! Thank you for this excellent pointer!

Regards, Nancy