sqlsqlbasesqltalk

SQL-Select ends up in an Error when combined

I try to do a simple select for some duplicates but my sql statement always end up with this error:

Command not properly ended

What I have is a table like this

EAN              STR
=============    ====
8030524096397    AAAA
8030524096397    BBBB
8030524096731    XXXX
8030524096731    YYYY
8030524096324    CCCC

My select is actually simple

SELECT EAN, COUNT(*) FROM ean GROUP BY ean HAVING COUNT(*) > 1;

Reults:

EAN               COUNT(*)
=============     ========
8030524096397        2
8030524096731        2

Everything is fine until here! Now I want the STR of the duplicates and try this

SELECT * FROM EAN E 
    INNER JOIN ( SELECT EAN, COUNT(*) FROM ean GROUP BY ean HAVING COUNT(*) > 1 )  
R ON 
E.EAN = R.EAN;

But this results this error.

It exactly says this:

SELECT * FROM EAN E
INNER JOIN ( SELECT EAN, COUNT(*) FROM ean GROUP BY ean HAVING COUNT(*) > 1 )  R ON
^
Error: Command not properly ended

What am I doing wrong?

Information to DB: Gupta Centura SQLBase 7.0.1

Solution

  • I don't think SQLBase 7.01 supports proper ANSI JOIN syntax (aside: what a good reason to use a more modern product). The error indicates a problem on the INNER JOIN.

    Here are two possible solutions.

    First, yucky archaic join syntax:

    SELECT *
FROM EAN E,
     ( SELECT EAN, COUNT(*) as cnt FROM ean GROUP BY ean HAVING COUNT(*) > 1 )  
R 
WHERE E.EAN = R.EAN;

    Second, IN:

    SELECT *
FROM EAN E
WHERE E.EAN IN ( SELECT EAN FROM ean GROUP BY ean HAVING COUNT(*) > 1 )