I need a Query that without any changes work on these three different database server : MySQL, MSSQL, PostgreSQL . In this query i have to calculate a column with the following expression that work correctly on MySQL :

The fields in the distinct are of different type :

The expression work correctly on MySQL but i get an error when i try to execute it on Sql Server or PostgreSQL (the problem is that the count function doesn't accept 3 arguments of different type on MSSQL/PostgreSQL), is there a way to achieve the same result with an expression that work in each of these database server (SQL Server, MySQL, PostgreSQL ) ?

I've tried also in another manner : including the count on a subquery, with this approach the query should work on every database server type, but the problem is that from the inner query i can't get the value of a field in the parent query, for example :

In the query above t0.depnum isn't recognized as a valid field in the subquery,
i get an error , how can i get the value of the parent query ? Is there a way ?


Thanks.

If you want a query to run on different dbms products, use standard (ISO/ANSI) SQL as far as possible.

The SQL Validator (online syntax validator) can save lots of time when writing portable SQL.
Mimer SQL Developers - Mimer SQL-2003 Validator

As you aleady have noticed, COUNT doesn't want more that one argument.

In this specific case you could try something like:
COUNT(DISTINCT field_char || cast(field_int as char(11)) || cast(field_date as char(16)))

Thank for the link of the validator, the only problem is that the double pipes string concatenation operator doesn't work in any database, for example doesn't work on Microsoft SQL Server because it require '+' puls operator for concatenation string .

Do you know any other way ?

Standard SQL uses || as string concatenation operator. If your system doesn't support that, it doesn't follow the standard in this respect. You may want to try the CONCAT operator instead.

However, you could avoid the casting and do something like this:

The problem is that the expression is inside a parent query select, so if i try to achieve the result with a sub query approach, i stumble in this situation :

This query give me an error because does not recognize the field t0.depnum of the parent query insiede the inner query.

Is there a way to get the value of the parent query within the inner/subquery?

What you try to do is basically right. However, there seems to be a comma missing after the "t0.depnum" expression, so you should get a syntax error.

I also recall that we had recently a discussion that DB2 somehow couldn't figure out the correlation over multiple subquery levels. This may be a problem in your case. You can work around that by using an uncorrelated subquery in the inner-most query block. The DISTINCT isn't influenced if you include the "depnum" column additionally. The filtering is then applied in the outer sub-select.

And the formatting also makes things rather hard to figure out. So I reformatted it a bit.