{"action":"created","issue":{"url":"https://api.github.com/repos/JSQLParser/JSqlParser/issues/301","repository_url":"https://api.github.com/repos/JSQLParser/JSqlParser","labels_url":"https://api.github.com/repos/JSQLParser/JSqlParser/issues/301/labels{/name}","comments_url":"https://api.github.com/repos/JSQLParser/JSqlParser/issues/301/comments","events_url":"https://api.github.com/repos/JSQLParser/JSqlParser/issues/301/events","html_url":"https://github.com/JSQLParser/JSqlParser/issues/301","id":162944474,"number":301,"title":"Failed to parse properly formatted MSQL syntax.","user":{"login":"gir489","id":7742541,"avatar_url":"https://avatars.githubusercontent.com/u/7742541?v=3","gravatar_id":"","url":"https://api.github.com/users/gir489","html_url":"https://github.com/gir489","followers_url":"https://api.github.com/users/gir489/followers","following_url":"https://api.github.com/users/gir489/following{/other_user}","gists_url":"https://api.github.com/users/gir489/gists{/gist_id}","starred_url":"https://api.github.com/users/gir489/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gir489/subscriptions","organizations_url":"https://api.github.com/users/gir489/orgs","repos_url":"https://api.github.com/users/gir489/repos","events_url":"https://api.github.com/users/gir489/events{/privacy}","received_events_url":"https://api.github.com/users/gir489/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2016-06-29T14:46:12Z","updated_at":"2016-06-29T18:18:43Z","closed_at":null,"body":"I am trying to parse the following SQL select with the SQL parser:\r\n\r\n`String stmt = \"SELECT CAST((MAX(CAST(IIF(isnumeric(license_no) = 1, license_no, 0) AS INT )) + 2) as varchar) FROM lcps.t_license WHERE profession_id = 60 and license_type = 100 and YEAR(issue_date) % 2 = case when YEAR(issue_date) % 2 = 0 then 0 else 1 end and ISNUMERIC(license_no) = 1\";\r\nStatements stmts = CCJSqlParserUtil.parseStatements(stmt);`\r\n\r\nThe exception is given:\r\n\r\n`Caused by: net.sf.jsqlparser.parser.ParseException: Encountered \" \"(\" \"( \"\" at line 1, column 12.\r\nWas expecting one of:\r\n    <EOF> \r\n    \"AS\" ...\r\n    \"DO\" ...\r\n    \"ANY\" ...\r\n    \"KEY\" ...\r\n    \"PERCENT\" ...\r\n    \"INTO\" ...\r\n    \"FROM\" ...\r\n    \"OPEN\" ...\r\n    \"WHERE\" ...\r\n    \"FOR\" ...\r\n    \"XML\" ...\r\n    \"GROUP\" ...\r\n    \"VALUE\" ...\r\n    \"HAVING\" ...\r\n    \"VALUES\" ...\r\n    \"REPLACE\" ...\r\n    \"TRUNCATE\" ...\r\n    \"CAST\" ...\r\n    \"OVER\" ...\r\n    \"PARTITION\" ...\r\n    \"EXTRACT\" ...\r\n    \"MATERIALIZED\" ...\r\n    \"START\" ...\r\n    \"CONNECT\" ...\r\n    \"PRIOR\" ...\r\n    \"SIBLINGS\" ...\r\n    \"COLUMN\" ...\r\n    \"NULLS\" ...\r\n    \"FIRST\" ...\r\n    \"LAST\" ...\r\n    \"ROWS\" ...\r\n    \"RANGE\" ...\r\n    \"FOLLOWING\" ...\r\n    \"ROW\" ...\r\n    \"SEPARATOR\" ...\r\n    \"CASCADE\" ...\r\n    \"NO\" ...\r\n    \"ACTION\" ...\r\n    <S_IDENTIFIER> ...\r\n    <S_QUOTED_IDENTIFIER> ...\r\n    \";\" ...\r\n    \",\" ...\r\n    \"+\" ...\r\n    \"||\" ...\r\n    \"|\" ...\r\n    \"&\" ...\r\n    \"-\" ...\r\n    \"^\" ...\r\n    \"::\" ...\r\n    \"::\" ...\r\n    \"^\" ...\r\n    \"+\" ...\r\n    \"-\" ...\r\n    \"||\" ...\r\n    \"|\" ...\r\n    \"&\" ...\r\n    \"AS\" ...\r\n    <S_IDENTIFIER> ...\r\n    <S_QUOTED_IDENTIFIER> ...\r\n    \"CAST\" ...\r\n    \"DO\" ...\r\n    \"EXTRACT\" ...\r\n    \"FIRST\" ...\r\n    \"FOLLOWING\" ...\r\n    \"LAST\" ...\r\n    \"MATERIALIZED\" ...\r\n    \"NULLS\" ...\r\n    \"PARTITION\" ...\r\n    \"RANGE\" ...\r\n    \"ROW\" ...\r\n    \"ROWS\" ...\r\n    \"SIBLINGS\" ...\r\n    \"VALUE\" ...\r\n    \"XML\" ...\r\n    \"COLUMN\" ...\r\n    \"REPLACE\" ...\r\n    \"TRUNCATE\" ...\r\n    \"KEY\" ...\r\n    \"ANY\" ...\r\n    \"OPEN\" ...\r\n    \"OVER\" ...\r\n    \"VALUES\" ...\r\n    \"PERCENT\" ...\r\n    \"PRIOR\" ...\r\n    \"SEPARATOR\" ...\r\n    \"NO\" ...\r\n    \"ACTION\" ...\r\n    \"CASCADE\" ...\r\n    \",\" ...\r\n    \"INTO\" ...\r\n    \"FROM\" ...\r\n    \"WHERE\" ...\r\n    \"START\" ...\r\n    \"CONNECT\" ...\r\n    \"GROUP\" ...\r\n    \"HAVING\" ...\r\n    \"FOR\" ...\r\n    \r\n\tat net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:13996)\r\n\tat net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:13852)\r\n\tat net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:236)\r\n\tat net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:122)\r\n\t... 1 more\r\nCaused by:\r\nnet.sf.jsqlparser.parser.ParseException: Encountered \" \"(\" \"( \"\" at line 1, column 12.\r\nWas expecting one of:\r\n    <EOF> \r\n    \"AS\" ...\r\n    \"DO\" ...\r\n    \"ANY\" ...\r\n    \"KEY\" ...\r\n    \"PERCENT\" ...\r\n    \"INTO\" ...\r\n    \"FROM\" ...\r\n    \"OPEN\" ...\r\n    \"WHERE\" ...\r\n    \"FOR\" ...\r\n    \"XML\" ...\r\n    \"GROUP\" ...\r\n    \"VALUE\" ...\r\n    \"HAVING\" ...\r\n    \"VALUES\" ...\r\n    \"REPLACE\" ...\r\n    \"TRUNCATE\" ...\r\n    \"CAST\" ...\r\n    \"OVER\" ...\r\n    \"PARTITION\" ...\r\n    \"EXTRACT\" ...\r\n    \"MATERIALIZED\" ...\r\n    \"START\" ...\r\n    \"CONNECT\" ...\r\n    \"PRIOR\" ...\r\n    \"SIBLINGS\" ...\r\n    \"COLUMN\" ...\r\n    \"NULLS\" ...\r\n    \"FIRST\" ...\r\n    \"LAST\" ...\r\n    \"ROWS\" ...\r\n    \"RANGE\" ...\r\n    \"FOLLOWING\" ...\r\n    \"ROW\" ...\r\n    \"SEPARATOR\" ...\r\n    \"CASCADE\" ...\r\n    \"NO\" ...\r\n    \"ACTION\" ...\r\n    <S_IDENTIFIER> ...\r\n    <S_QUOTED_IDENTIFIER> ...\r\n    \";\" ...\r\n    \",\" ...\r\n    \"+\" ...\r\n    \"||\" ...\r\n    \"|\" ...\r\n    \"&\" ...\r\n    \"-\" ...\r\n    \"^\" ...\r\n    \"::\" ...\r\n    \"::\" ...\r\n    \"^\" ...\r\n    \"+\" ...\r\n    \"-\" ...\r\n    \"||\" ...\r\n    \"|\" ...\r\n    \"&\" ...\r\n    \"AS\" ...\r\n    <S_IDENTIFIER> ...\r\n    <S_QUOTED_IDENTIFIER> ...\r\n    \"CAST\" ...\r\n    \"DO\" ...\r\n    \"EXTRACT\" ...\r\n    \"FIRST\" ...\r\n    \"FOLLOWING\" ...\r\n    \"LAST\" ...\r\n    \"MATERIALIZED\" ...\r\n    \"NULLS\" ...\r\n    \"PARTITION\" ...\r\n    \"RANGE\" ...\r\n    \"ROW\" ...\r\n    \"ROWS\" ...\r\n    \"SIBLINGS\" ...\r\n    \"VALUE\" ...\r\n    \"XML\" ...\r\n    \"COLUMN\" ...\r\n    \"REPLACE\" ...\r\n    \"TRUNCATE\" ...\r\n    \"KEY\" ...\r\n    \"ANY\" ...\r\n    \"OPEN\" ...\r\n    \"OVER\" ...\r\n    \"VALUES\" ...\r\n    \"PERCENT\" ...\r\n    \"PRIOR\" ...\r\n    \"SEPARATOR\" ...\r\n    \"NO\" ...\r\n    \"ACTION\" ...\r\n    \"CASCADE\" ...\r\n    \",\" ...\r\n    \"INTO\" ...\r\n    \"FROM\" ...\r\n    \"WHERE\" ...\r\n    \"START\" ...\r\n    \"CONNECT\" ...\r\n    \"GROUP\" ...\r\n    \"HAVING\" ...\r\n    \"FOR\" ...\r\n    \r\n\tat net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:13996)\r\n\tat net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:13852)\r\n\tat net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:236)\r\n\tat net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:122)\r\n\tat com.sa.v4.business.process.LicenseNumberGenerator.main(LicenseNumberGenerator.java:2462)`\r\n\r\nBut if I run the query in SQL Server Management Studio, I get a return of 30000 from the server.``"},"comment":{"url":"https://api.github.com/repos/JSQLParser/JSqlParser/issues/comments/229442923","html_url":"https://github.com/JSQLParser/JSqlParser/issues/301#issuecomment-229442923","issue_url":"https://api.github.com/repos/JSQLParser/JSqlParser/issues/301","id":229442923,"user":{"login":"wumpz","id":1569337,"avatar_url":"https://avatars.githubusercontent.com/u/1569337?v=3","gravatar_id":"","url":"https://api.github.com/users/wumpz","html_url":"https://github.com/wumpz","followers_url":"https://api.github.com/users/wumpz/followers","following_url":"https://api.github.com/users/wumpz/following{/other_user}","gists_url":"https://api.github.com/users/wumpz/gists{/gist_id}","starred_url":"https://api.github.com/users/wumpz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wumpz/subscriptions","organizations_url":"https://api.github.com/users/wumpz/orgs","repos_url":"https://api.github.com/users/wumpz/repos","events_url":"https://api.github.com/users/wumpz/events{/privacy}","received_events_url":"https://api.github.com/users/wumpz/received_events","type":"User","site_admin":false},"created_at":"2016-06-29T18:18:43Z","updated_at":"2016-06-29T18:18:43Z","body":"If I remember correctly,  there is an open issue regarding if or boolean conditions as function parameters.  That would be my first guess,  why the parse failed. Tobias"}}
{"actor":{"display_login":"wumpz"}}