Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Question Unanswered: Distinct on multiple field, looking for a standard way

    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 :

    Code:
    COUNT(DISTINCT field_char,field_int,field_date) AS costumernum
    The fields in the distinct are of different type :

    Code:
    field_char = character
    field_int  = integer
    field_date = datetime
    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 :

    Code:
    SELECT t0.description,t0.depnum
    (select count(*) from (
      select distinct f1, f2, f3 from salestable t1
      where t1.depnum = t0.depnum
    ) a) AS numitems
    FROM salestable t0
    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.

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    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)))

  3. #3
    Join Date
    Sep 2011
    Posts
    3
    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 ?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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:
    Code:
    SELECT COUNT(*)
    FROM ( SELECT DISTINCT field_char, field_int, field_data FROM ... ) AS t
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2011
    Posts
    3
    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 :

    Code:
    SELECT t0.description,t0.depnum
    (select count(*) from (
      select distinct field_char, field_int, field_data from salestable t1
      where t1.depnum = t0.depnum
    ) a) AS numitems
    FROM salestable t0
    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?

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Code:
    SELECT t0.description, t0.depnum,
           ( SELECT COUNT(*)
             FROM   ( SELECT DISTINCT t1.depnum, field_char, field_int, field_data
                      FROM   salestable AS t1 ) AS a
             WHERE  a.depnum = t0.depnum ) AS numitems
    FROM   salestable t0
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •