How can I make compatible SQL query between Oracle and SQL Server?

Question

The following query refuses to work on Sql server whereas it runs fine on Oracle 10gR2.

SELECT COUNT(*)
FROM (
   SELECT count(*)
   FROM MYTABLE
   WHERE id IS NOT NULL
   GROUP BY id
)

It ends up with this message :

Microsoft OLE DB Provider for SQL Server error '80040e14'

Ligne 7 : syntaxe incorrecte vers ')'.

How can I make it compatible for both DBMS ?

Umm... TABLE is a reserved word in both DBMSs. Are you sure that exact query gives this error? — Mark Byers
– Mark Byers, Commented Apr 27, 2012 at 16:33
Are there any other changes you made to the query before posting it? Can we see the original query? — Mark Byers
– Mark Byers, Commented Apr 27, 2012 at 16:37
@MarkByers the query performed is exactly like the one in the post. The table name changes only. — Stephan
– Stephan, Commented Apr 27, 2012 at 16:43

Dave Carlile · Accepted Answer · 2012-04-27 16:35:11Z

4

SQL Server requires an alias on derived tables, so...

SELECT COUNT(*) 
FROM ( 
   SELECT count(*) 
   FROM TABLE 
   WHERE id IS NOT NULL 
   GROUP BY id 
)  a

I'm not sure how to do the alias in Oracle. Also, you'll need to provide a column name for count(*) in the derived table.

answered Apr 27, 2012 at 16:35

Dave Carlile

7,4771 gold badge23 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Byers · Accepted Answer · 2012-04-27 16:35:32Z

1

This should work in both:

SELECT COUNT(DISTINCT id)
FROM yourtable
WHERE id IS NOT NULL

answered Apr 27, 2012 at 16:35

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

1 Comment

Stephan Over a year ago

If I multiple fields, let's say id,foo and bar fields, how can I do ?

Justin Cave · Accepted Answer · 2012-04-27 16:35:52Z

1

If I understand what you are trying to accomplish, I think you could simplify this to

SELECT COUNT( DISTINCT id )
  FROM someTable
 WHERE id IS NOT NULL

which should work on both databases.

answered Apr 27, 2012 at 16:35

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

1 Comment

Stephan Over a year ago

If I multiple fields, let's say id,foo and bar fields, how can I do ?

Pawel · Accepted Answer · 2012-04-27 16:35:45Z

0

In Sql Server this should work:

SELECT count(id)
FROM TABLE
WHERE id IS NOT NULL
GROUP BY id

Not sure if it will work in Oracle though

answered Apr 27, 2012 at 16:35

Pawel

31.7k4 gold badges76 silver badges106 bronze badges

Collectives™ on Stack Overflow

How can I make compatible SQL query between Oracle and SQL Server?

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related