inline view - table reference with union all

ive got a larger query, but to cut it down its like this:

select count(col1) as reccount from table1
where col2 > 0 and col3 / col2 > 21
union all
select count(col1) as reccount from table1
where col2 = 0


in the first query i have to exclude records that are zero because as you can see i would end up dividing by zero.  i also need to include those records in the results though.  the query works alright when im getting the details, but not when using the count function.  im a sql guy and i would have done something like this to get a sum of the counts:

select sum(reccount) as sumcount from (
select count(col1) as reccount from table1
where col2 > 0 and col3 / col2 > 21
union all
select count(col1) as reccount from table1
where col2 = 0)



when i try to do that with the db2, it gives me:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0104 - Token <END-OF-STATEMENT> was not valid. Valid tokens: AS <IDENTIFIER>.



i have searched "db2 inline view" and "db2 table reference" and i keep finding articles that say its possible, but i cant get it to work.
LVL 22
WMIFAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:

Welcome to the world of IBM error messages.  :)   "General Failure to Do Right....."


Derived tables must be named.  Simply put a "as table3" after the closing ")".


Good Luck!
Kent
0
 
WMIFAuthor Commented:
awesome.  that worked perfectly.  



>>Welcome to the world of IBM error messages.  :)   "General Failure to Do Right....."

i am fairly familiar with the error messages because ive been accessing the db2 through asp for many years now.  its a pain to do sometimes, but its more of a pain to create copies of data into a sql server, besides being pointless as well.

thanks so much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.