Help converting from oracle query 
Author Message
 Help converting from oracle query

I am having some trouble converting a query from Oracle to DB2 and thought
mabey someone could help.  The first query is from Oracle, that one runs
fine, the second is the one I am trying to run on DB2 (Mainframe) but I get
errors.

ORACLE

SELECT COUNT(DISTINCT USER_ACCESS_LOG.USER_ID),
       COUNT(DISTINCT USERS.COMPANY_ID),
       COUNT(USER_ACCESS_LOG.ACCESS_DESCRIPTION),
       COUNT(DISTINCT USERS.USER_ID)
FROM  USERS, USER_ACCESS_LOG
WHERE USERS.USER_ID  =  USER_ACCESS_LOG.USER_ID
  AND USERS.COMPANY_ID Not In (-1,0,1000)

DB2

SELECT COUNT(DISTINCT TAC00T.VUSER_ACCESS_LOG.USER_ID),
       COUNT(DISTINCT TAC00T.VUSERS.COMPANY_ID),
       COUNT(TAC00T.VUSER_ACCESS_LOG.USER_ACCESS_DESC),
       COUNT(DISTINCT TAC00T.VUSERS.USER_ID)
FROM   TAC00T.VUSER_ACCESS_LOG, TAC00T.VUSERS
WHERE  TAC00T.VUSER_ACCESS_LOG.USER_ID  =  TAC00T.VUSERS.USER_ID
  AND  TAC00T.VUSERS.COMPANY_ID Not In (-1,0,1000);

Error 1:

SQL0409N  The operand of a COUNT function is not valid.  SQLSTATE=42607
State:42607,Native:-409,Origin:[IBM][CLI Driver][DB2]

I think this is from not having the DISTINCT clause in the count function.  I
can solve that by putting a count(*) instead and getting the correct result.

That gives me the following query...

SELECT COUNT(DISTINCT TAC00T.VUSER_ACCESS_LOG.USER_ID),
       COUNT(DISTINCT TAC00T.VUSERS.COMPANY_ID),
       COUNT(*),
       COUNT(DISTINCT TAC00T.VUSERS.USER_ID)
FROM   TAC00T.VUSER_ACCESS_LOG, TAC00T.VUSERS
WHERE  TAC00T.VUSER_ACCESS_LOG.USER_ID  =  TAC00T.VUSERS.USER_ID
  AND  TAC00T.VUSERS.COMPANY_ID Not In (-1,0,1000);

and I get this error:

Error 2:

SQL0127N  DISTINCT is specified more than once.  SQLSTATE=42905
State:42905,Native:-127,Origin:[IBM][CLI Driver][DB2]

Any help would be appreciated.

Thanks!

John

 -----  Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web  -----
  http://www.***.com/ ,000+ groups
   NewsOne.Net prohibits users from posting spam.  If this or other posts



Sat, 23 Aug 2003 23:33:08 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Help need converting ACCESS query to Oracle

2. HELP: must convert IIF( ) from Access queries to T-SQL

3. Convert access query to a stored proc - Syntax help

4. Need help converting MS ACCESS query to SQL 6.5

5. help.. converting query from MS Access to SQL

6. Newbie ...need help converting update query

7. Need help for converting query result to list of dictoinary

8. Tool to convert SQl queries to oracle or vice versa

9. How to convert Access queries into Oracle

10. Tool to convert SQl queries to oracle or vice versa


 
Powered by phpBB® Forum Software