Problems in using parentheses with UNION ALL STATEMENTS

[smadin <ar...@gmail.com>]

HI All,

Please Have a look at the query below.

 ( SELECT NEWS_ID,1 AS  SECTION_TYPE  FROM NEWS WHERE //condition_A ORDER BY
S_ID DESC  FETCH FIRST 16 ROWS   ONLY)

 UNION ALL 

(SELECT NEWS_ID ,2 AS  SECTION_TYPE  FROM NEWS WHERE  //condition_B ORDER BY
S_ID DESC  FETCH FIRST 16 ROWS ONLY)

UNION ALL 

(SELECT NEWS_ID ,3 AS  SECTION_TYPE  FROM NEWS WHERE  //condition_C ORDER BY
S_ID DESC  FETCH FIRST 16 ROWS ONLY)

I am trying to execute set of select statements in a single table, provided
that those conditions are different. And i want to execute these set of
queries as a single statement in order to reduce the execution time and
virtual column SECTION_TYPE is used to separate the results from the
result-set. 

I use UNION ALL to join these statements. But my problem is, sql exception
is thrown saying that there is a problem in ORDER clause when statements are
separated using parentheses.
" java.sql.SQLSyntaxErrorException: Syntax error: Encountered "ORDER" at
line 1, column xxx." 

And if i remove the parentheses and execute. Again exception thrown.
"java.sql.SQLSyntaxErrorException: Syntax error: Encountered "UNION" at line
1, column xxx." 

Any help regarding solving this or other approach of doing this is highly
appreciated.

Regards.
smadin





--
View this message in context: http://apache-database.10148.n7.nabble.com/Problems-in-using-parentheses-with-UNION-ALL-STATEMENTS-tp130822.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Problems in using parentheses with UNION ALL STATEMENTS

[smadin <ar...@gmail.com>]

Hi,

Thank you very much for the quick response,  Knut Anders
<http://apache-database.10148.n7.nabble.com/template/NamlServlet.jtp?macro=user_nodes&user=77>  
. I was using derby version 10.9 and upgrading to derby 10.10.1.1 solved the
problem.

thanks and regards,
smadin.





--
View this message in context: http://apache-database.10148.n7.nabble.com/Problems-in-using-parentheses-with-UNION-ALL-STATEMENTS-tp130822p130861.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Problems in using parentheses with UNION ALL STATEMENTS

[Knut Anders Hatlen <kn...@oracle.com>]

smadin <ar...@gmail.com> writes:

> I use UNION ALL to join these statements. But my problem is, sql exception
> is thrown saying that there is a problem in ORDER clause when statements are
> separated using parentheses.
> " java.sql.SQLSyntaxErrorException: Syntax error: Encountered "ORDER" at
> line 1, column xxx." 

Hi smadin,

Which version of Derby are you using? ORDER BY in operands of UNION ALL
was not supported until very recently, in version 10.10.1.1.

-- 
Knut Anders