I have a problem need your help.When i use the Jsqlparser ,I find it can't parse the sql with 'top' keyword, For example "select top 5 * from people",the jsqlparser with throw a exception with following:
net.sf.jsqlparser.JSQLParserException
at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:40)
at net.sf.jsqlparser.test.select.SelectTest.testTop(SelectTest.java:63)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered "5" at line 1, column 12.
Was expecting one of:
"AS" ...
"INTO" ...
"FROM" ...
<S_IDENTIFIER> ...
<S_QUOTED_IDENTIFIER> ...
"," ...
"." ...
"(" ...
"*" ...
"/" ...
"+" ...
"-" ...
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:4600)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:4481)
at net.sf.jsqlparser.parser.CCJSqlParser.FromItemsList(CCJSqlParser.java:867)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:599)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:533)
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:520)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:101)
at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:38)
... 16 more
Caused by:
net.sf.jsqlparser.parser.ParseException: Encountered "5" at line 1, column 12.
Was expecting one of:
"AS" ...
"INTO" ...
"FROM" ...
<S_IDENTIFIER> ...
<S_QUOTED_IDENTIFIER> ...
"," ...
"." ...
"(" ...
"*" ...
"/" ...
"+" ...
"-" ...
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:4600)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:4481)
at net.sf.jsqlparser.parser.CCJSqlParser.FromItemsList(CCJSqlParser.java:867)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:599)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:533)
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:520)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:101)
at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:38)
at net.sf.jsqlparser.test.select.SelectTest.testTop(SelectTest.java:63)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
for example:
have a schedule table that looks like this :
name tday tstart tend
---------- ----------- ----------- -----------
ashok 1 900 1000
ashok 2 900 1000
ashok 3 900 930
ashok 4 1200 1300
jim 1 900 1000
jim 2 900 1000
jim 3 900 930
jim 4 1200 1300
jim 5 1200 1300
reeve 1 1200 1300
jim 5 1500 1700
jim 5 1300 1500
one report needed was the weekly classroom schedule for a student.
this query :
select count(name) as 'no_of_stud' ,sum(tday) as 'day_of_week',
case when (tstart >= 900 and tend <= 1100)
then 1 else 0 end as 'c900_1100',
case when (tstart >= 1100 and tend <= 1300)
then 1 else 0 end as 'c1100_1300',
case when (tstart >= 1300 and tend <= 1500)
then 1 else 0 end as 'c1300_1500',
case when (tstart >= 1500 and tend <= 1700)
then 1 else 0 end as 'c1500_1700'
from schedule
Where name='jim'
group by tday, tstart, tend
we use the sqlServer 'case' syntaxt to split the columns...
1 stands for yes & 0 stands for no...
for jim this would return :
USing version 0.4.3 and TOP doesn't seem to be working. For example:
select top 5 foo from bar
I get no parsing error and the getLimit method on PlainSelect returns a null. I also tried
select foo from bar limit 5
and that does work as expected (so my visitors are doing the right thing). However, I need to be able to parse TOP and turn it into whatever works on
one or more target DBs.
Should I be looking someplace else (than getLimit) for the "top"?
thanks,
Patrick
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I don't know how I did not see getTop() in there :-(
On a related topic, if I use the SelectDeParser to write out the parsed query as a string, it seems to lose the "TOP n". Looking in the source for that class:
public void visit(PlainSelect plainSelect) {
buffer.append("SELECT ");
if (plainSelect.getDistinct() != null) {
...
it appears to be missing. That doesn't matter so much for me (I am trying to
take a query with TOP in it and transform it to work on DB2) but I thought I would point it out anyway.
Patrick
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Actually, I think you were right to keep top and limit separate as my interpretation (upon closer reading of a few articles about them) is that LIMIT is a simple row count limit while TOP is to get the TOP N values of something including ties (so could return more than N values). Practically speaking, TOP and LIMIT would do the same thing if the select list was such that there were no ties (eg values were unique between row N and N+1).
It's a subtle difference.
Patrick
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have a problem need your help.When i use the Jsqlparser ,I find it can't parse the sql with 'top' keyword, For example "select top 5 * from people",the jsqlparser with throw a exception with following:
net.sf.jsqlparser.JSQLParserException
at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:40)
at net.sf.jsqlparser.test.select.SelectTest.testTop(SelectTest.java:63)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered "5" at line 1, column 12.
Was expecting one of:
"AS" ...
"INTO" ...
"FROM" ...
<S_IDENTIFIER> ...
<S_QUOTED_IDENTIFIER> ...
"," ...
"." ...
"(" ...
"*" ...
"/" ...
"+" ...
"-" ...
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:4600)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:4481)
at net.sf.jsqlparser.parser.CCJSqlParser.FromItemsList(CCJSqlParser.java:867)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:599)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:533)
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:520)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:101)
at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:38)
... 16 more
Caused by:
net.sf.jsqlparser.parser.ParseException: Encountered "5" at line 1, column 12.
Was expecting one of:
"AS" ...
"INTO" ...
"FROM" ...
<S_IDENTIFIER> ...
<S_QUOTED_IDENTIFIER> ...
"," ...
"." ...
"(" ...
"*" ...
"/" ...
"+" ...
"-" ...
at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:4600)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:4481)
at net.sf.jsqlparser.parser.CCJSqlParser.FromItemsList(CCJSqlParser.java:867)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:599)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:533)
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:520)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:101)
at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:38)
at net.sf.jsqlparser.test.select.SelectTest.testTop(SelectTest.java:63)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)
It only accepts the "LIMIT" keyword. With which db are you working with? Sqlserver? Couldn't you use LIMIT?
I user sqlserver,can't user limit,how to slove this problem?
thanks!
The parser has to be changed to accept "top". Please wait for some days, I think that by next week it will available.
thanks,By the way i find out the jsalparse can't support 'case when'.Can you add this function?
thanks!!!
I don't know that function. Can you point me to some documentation?
for example:
have a schedule table that looks like this :
name tday tstart tend
---------- ----------- ----------- -----------
ashok 1 900 1000
ashok 2 900 1000
ashok 3 900 930
ashok 4 1200 1300
jim 1 900 1000
jim 2 900 1000
jim 3 900 930
jim 4 1200 1300
jim 5 1200 1300
reeve 1 1200 1300
jim 5 1500 1700
jim 5 1300 1500
one report needed was the weekly classroom schedule for a student.
this query :
select count(name) as 'no_of_stud' ,sum(tday) as 'day_of_week',
case when (tstart >= 900 and tend <= 1100)
then 1 else 0 end as 'c900_1100',
case when (tstart >= 1100 and tend <= 1300)
then 1 else 0 end as 'c1100_1300',
case when (tstart >= 1300 and tend <= 1500)
then 1 else 0 end as 'c1300_1500',
case when (tstart >= 1500 and tend <= 1700)
then 1 else 0 end as 'c1500_1700'
from schedule
Where name='jim'
group by tday, tstart, tend
we use the sqlServer 'case' syntaxt to split the columns...
1 stands for yes & 0 stands for no...
for jim this would return :
no_of_classes day_of_week c900_1100 c1100_1300 c1300_1500 c1500_1700
------------- ----------- ----------- ----------- ----------- -----------
1 1 1 0 0 0
1 2 1 0 0 0
1 3 1 0 0 0
1 4 0 1 0 0
1 5 0 1 0 0
1 5 0 0 1 0
1 5 0 0 0 1
I see... never used...
I'll see if I can add it to the grammar.
PLEASE reply to the right message, otherwise followups will get screwed up...
OK, i will wait for your good news,thanks!
Version 0.3.11 supports the TOP clause.
USing version 0.4.3 and TOP doesn't seem to be working. For example:
select top 5 foo from bar
I get no parsing error and the getLimit method on PlainSelect returns a null. I also tried
select foo from bar limit 5
and that does work as expected (so my visitors are doing the right thing). However, I need to be able to parse TOP and turn it into whatever works on
one or more target DBs.
Should I be looking someplace else (than getLimit) for the "top"?
thanks,
Patrick
Well, since TOP is "different" from Limit, I used two different methods. You have to use:
((PlainSelect) select.getSelectBody()).getTop().getRowCount()
please look at SelectTest.testTop for an example.
I don't know how I did not see getTop() in there :-(
On a related topic, if I use the SelectDeParser to write out the parsed query as a string, it seems to lose the "TOP n". Looking in the source for that class:
public void visit(PlainSelect plainSelect) {
buffer.append("SELECT ");
if (plainSelect.getDistinct() != null) {
...
it appears to be missing. That doesn't matter so much for me (I am trying to
take a query with TOP in it and transform it to work on DB2) but I thought I would point it out anyway.
Patrick
Well, I wasn't really sure that it should be a different method (since a TOP is nothing else but a LIMIT...) so I can see why you couldn't see it :)
And yes, there is a bug in SelectDeParser: I'll fix it in the next release. Thank you.
Actually, I think you were right to keep top and limit separate as my interpretation (upon closer reading of a few articles about them) is that LIMIT is a simple row count limit while TOP is to get the TOP N values of something including ties (so could return more than N values). Practically speaking, TOP and LIMIT would do the same thing if the select list was such that there were no ties (eg values were unique between row N and N+1).
It's a subtle difference.
Patrick