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

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

Syntax problem Informix with COUNT and HAVING

Hi,

I try to do the following query (which I thought is plain SQL), but it results in an error:

SELECT person_id
FROM job_function
GROUP BY person_id
HAVING COUNT(person_id) > 1

(IN NL: Give me all person_id's of those rows in the table that have this person_id more than once, which means, "Give me all persons with more than one function").

The database complains that the syntax is not OK. It isn't usefull, but when I put a distinct inside the COUNT, the answer of course is wrong, but the syntax seems right.... It seems the problem is a COUNT(field) is not supported, whereas a COUNT(*) is supported.

The database used is a very stripped Informix that was delivered with the Netscape Enterprise 2.0 webserver, with LiveWire Pro....

I think it's just a difference in syntax....

Cheers,


    George
0
georgedb
Asked:
georgedb
1 Solution
 
csalvesCommented:
Hello,

This another way to do it.

SELECT a.person_id
  FROM job_function a
  WHERE a.rowid <> ANY (SELECT b.rowid
                          FROM job_function b
                          WHERE a.ROWID <> b.ROWID
                            AND a.person_id = b.Person_id)
GROUP BY a.person_id

This statement will return all the persons with more than one job function.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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