Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3357
  • Last Modified:

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.
0
WMIF
Asked:
WMIF
1 Solution
 
Kent OlsenData 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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now