what is wrong with the query?

dimas · **Posted:** Wed Oct 29, 2003 12:47 pm

"select count(*) from ( select distinct emp from Employee emp )"

Throws error:

[junit] in expected: select [select count(*) from ( select distinct emp from Employee emp ) ]

dimas · **Posted:** Wed Oct 29, 2003 12:49 pm

oops, I forgot the stack trace:

[junit] at net.sf.hibernate.hql.FromParser.token(FromParser.java:102)
[junit] at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
[junit] at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:120)
[junit] at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
[junit] at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:146)
[junit] at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:133)
[junit] at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:352)
[junit] at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:330)
[junit] at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1368)
[junit] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1332)
[junit] at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:76)
[junit] at TestTEMP.testX(TestTEMP.java:149)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
[junit] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

Obviously, I can make that exact query simplier, but I need a way to automatically build "count(*)" around any query...

emmanuel · **Posted:** Wed Oct 29, 2003 1:09 pm

select count(distinct emp) from Employee as emp

dimas · **Posted:** Wed Oct 29, 2003 1:15 pm

Thanks but I know how to make it working :)
I just do not understand why _original_ query does not work.

I need a common way to make any query countable and the simplest way is use "select count(*) from (ORIGINALQUERYGOESHERE)".
But this does not work for some reason even for simple original query...

emmanuel · **Posted:** Wed Oct 29, 2003 1:32 pm

select count(emp) from Employee as emp where cat in (select distinct emp2 from Employee as emp2)

emmanuel · **Posted:** Wed Oct 29, 2003 1:33 pm

Hum would say

select count(emp) from Employee as emp where emp in (select distinct emp2 from Employee as emp2)

dimas · **Posted:** Wed Oct 29, 2003 1:50 pm

Yes, your query works, but original query may be not about Employee but about Company for example. I only know this at runtime. How to make a common COUNT(*) wrapper?

My first though was to make query with java.lang.Object:

select count(*) from java.lang.Object o where o in ( select distinct emp from com.dataart.datatec.Employee emp )

But it sucks in entire database :)

emmanuel · **Posted:** Wed Oct 29, 2003 2:35 pm

Code:

String dynaQuery = "select count(distinct runtimeClass) from " + myRuntimeClass.getName() + " as runtimeClass";

dimas · **Posted:** Wed Oct 29, 2003 5:18 pm

I was not very clear: one of the functions receives text of query to be executed. Function must construct "counting" query for the same conditions. It is not known what kind of query will be passed. Ideally, function must be able to handle ANY type of query like:

from Employee emp
select emp from Employee emp
select distinct emp from Employee emp
emp from Employee emp

These queries require slightly different methods of building COUNT(*) queries...

It seems I have solved my problem because I wrote that function. But I clearly understand, this function sucks. There are tons of queries this function will not be able to handle. But it works for most of my queries...

Code:

    

    static Pattern pattern = null;

    static
    {
        Perl5Compiler compiler = new Perl5Compiler();

        try
        {
            pattern = compiler.compile("^ \\s* (select)? (.*?) (from .*) $",
                                       Perl5Compiler.EXTENDED_MASK | Perl5Compiler.READ_ONLY_MASK | Perl5Compiler.CASE_INSENSITIVE_MASK);
        }
        catch (MalformedPatternException exception)
        {
            throw new RuntimeException("Can'not initialize - pattern can not be compiled");
        }
    }

    public static String buildCountingQuery(String originalQuery)
    {
        // >> from Employee emp
        // << SELECT COUNT(*) from Employee emp
        // >> select emp from Employee emp
        // << SELECT COUNT(emp) from Employee emp
        // >> select distinct emp from Employee emp
        // << SELECT COUNT(DISTINCT emp) from Employee emp
        // >> emp from Employee emp
        // << SELECT COUNT(emp) from Employee emp

        String countWhat = "*";
        String queryTail = null;

        PatternMatcher matcher = new Perl5Matcher();
        MatchResult match;

        if (matcher.contains(originalQuery, pattern))
        {
            match = matcher.getMatch();

            String selectTokens = match.group(2);
            if (selectTokens != null)
            {
                selectTokens = selectTokens.trim();

                if (selectTokens.length() > 0)
                    countWhat = selectTokens;
            }

            queryTail = match.group(3);
        }
        else
            queryTail = originalQuery;


        return "SELECT COUNT(" + countWhat + ") " + queryTail;
    }

omisch · **Posted:** Mon Dec 06, 2004 11:43 am

I have a similar question. I do not need a general way to wrap a count(*) around every query but around a query with a group by clause.

For example a count(*) around this query:
select NAME from PERSON where NAME like '%XYZ%' group by NAME

This does not work (SQL semantics):
select count(*) from PERSON where NAME like '%XYZ%' group by NAME

In SQL I could use a subquery in the from clause:
select count(*) from (select NAME from PERSON where NAME like '%XYZ%' group by NAME)

Hibernate does not support sub queries in the from clause. Is there a possibility to get the number of rows that would be returned by the first query?