2

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

Dwza
  • 6,494
  • 6
  • 41
  • 73
  • @Simo no, that's not the problem, i've tested it right now. Adding AS results the error to the AS position ^^ – Dwza Nov 02 '15 at 11:40
  • @AndrewB, sadly not. I tried all in one row. Same result :( – Dwza Nov 02 '15 at 11:42
  • SQL is case sensitive right? You are using "EAN" and "ean" seemingly as table names and column names, in the first FROM part, you select from "EAN" and in the join you select from "ean". If they are different tables, for "GROUP BY ean", is ean not the table name you are selecting from rather than the column to group by? – AndrewB Nov 02 '15 at 11:51
  • 1
    @AndrewB: no. (standard) SQL is **not** case sensitive. `EAN`, `Ean` and `ean` are all the same identifiers. –  Nov 02 '15 at 11:55
  • 1
    trough testings there is no matter of CS. But good idea :) – Dwza Nov 02 '15 at 11:55

3 Answers3

3

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 )
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Some people seem to be not from this wold to know all this stuff :D The first one doesn't work but the second one does. This select takes a lot of time but actually results this what I want. Thank you :) – Dwza Nov 02 '15 at 12:01
  • by the way, of corse I know we should use a newer db, but in this case this is sadly not possible. Actually it's a running system and to build this new it would cost to much time :) – Dwza Nov 02 '15 at 12:45
0
SELECT * 
FROM EAN E 
INNER JOIN 
    (SELECT EA.EAN, COUNT(*) Cnt 
    FROM ean EA
    GROUP BY EA.ean 
    HAVING COUNT(*) > 1 
    ) R ON E.EAN = R.EAN;

I gave an alias Cnt to COUNT(*). This should make the error go away.

Adish
  • 709
  • 4
  • 12
0

Try this (adding aliases to the tables with AS keyword)

SELECT * FROM EAN AS E 
INNER JOIN 
(SELECT EAN, COUNT(*) FROM ean GROUP BY ean HAVING COUNT(*) > 1) AS R 
ON 
E.EAN = R.EAN;
MusicLovingIndianGirl
  • 5,909
  • 9
  • 34
  • 65