Subselect in Select expression allowed?

[markmorgan]

In DB2 are subselects allowed in a select expression e.g.

select CourseCode, EventCode,
(SELECT Count(*) FROM CrseApplic CA
WHERE CA.CourseCode = CE.CourseCode
AND CA.EventCode = CE.EventCode
AND CA.CrsAppCancelledDt IS NULL) AS EventEnrolCount
from CrseEvent CE
where CourseCode = ?
order by CourseCode, EventCode

This works fine for me under Siemens SESAM (SQL99 standard), but not under MicroFocus XDB emulating DB2v6. I have checked the DB2 manuals, but it is not clear wether this is allowed or not and I don't have access to DB2 to try it.

I will also mail this direct to MicroFocus to see if it is a compatibility issue.

Mark.

 

[blom0344]

Hello Mark,

I just wonder. You use the correlation name CE within the subselect , but you declare it in the main select.
DB2 manual speaks of correlation names only valid within the context.

Could this be better:

select CourseCode, EventCode,
(SELECT Count(*) FROM CrseApplic CA
WHERE CA.CourseCode = CrseEvent.CourseCode
AND CA.EventCode = CrseEvent.EventCode
AND CA.CrsAppCancelledDt IS NULL) AS EventEnrolCount
from CrseEvent
where CourseCode = ?
order by CE.CourseCode, CE.EventCode

It's a bit of a wild guess, but I'm absolutely sure subselects are allowed in DB2 SQL, as well as using correlation names. Possibly also use the tablename in the order by clause


T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[markmorgan]

Yes as you have shown the table name can stand in place of the correclation name.

DB2 does allow subselects. But I have never coded one as a expression giving a single result column in the select list before. I have only ever coded them in a where or having clause before (a subquery).

I'm not sure wether DB2 allows this. According to the DB2 manual a select list is built of expressions, an expression is either an expression, function, constant, column-name, host-variable, special-register, labelled-duration, case, or cast, combined with operators. It does not mention subselect to either allow or disallow its use.

As I said it works in SESAM, maybe its part of the SQL99 standard that has not been implemented in DB2.

Mark.

 

[blom0344]

Hello Mark,

I just finished a SQL on my datawarehouse to see what happened with the structure you are running. Conclusion is quite definite, error is on context of the tables in join in subselect. I was running the SQL on DB2 UDB 7.1
No dice !! T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com