0

I'd like to extend the JSQLParser so that it accepts the IIF command of oracle. The following stmt produces an exception because the function uses a expression:

SELECT LNNVL(A > 10, 1, 0) FROM TBL_NAME;

Do you mind giving me hints how to change the jj-file?

Changes

IIF replaced with LNNVL. The LNNVL is only valid in WHERE conditions but does not throw an exception.

Markus
  • 763
  • 7
  • 24
  • why didn't you use case instead: `select case when a > 10 then 1 else 0 end from tbl_name;` – Pham X. Bach May 24 '16 at 15:45
  • there is no `iif` in Oracle –  May 24 '16 at 16:47
  • There is a `iif` function: https://docs.oracle.com/cd/E12032_01/doc/epm.921/html_techref/maxl/dml/funcs/iif.htm – Markus May 25 '16 at 05:40
  • @PhamX.Bach: We have a very, very length SQL stmt that cannot be longer than 4000 char (Oracle limit of varchar). `IIF` is a nice way to shorten the stmt. – Markus May 25 '16 at 05:42
  • @Markus: Oraces does not have an `iif` function (and it neither has a `lnnvl` function) The link you added in your comment is **not** the manual for the Oracle database, it's the manual for Hyperion - a completely different thing. You need to read the correct manual: https://docs.oracle.com/database/121/SQLRF/functions.htm#SQLRF006 –  Jul 27 '16 at 10:27
  • Plus: an Oracle SQL statement can absolutely be longer then 4000 characters - you just can't have a varchar _constant_ that is longer then that. –  Jul 27 '16 at 10:28

1 Answers1

0

Using JSqlParser at github your start would be production

function()

Here you have to modify the jjt - File because this version of JSqlParser does some kind of ASTNode building. The jj - File is derived.

At the moment there is a SimpleExpressionList accepted

"(" [ [<K_DISTINCT> { retval.setDistinct(true); } | <K_ALL> { retval.setAllColumns(true); }] (expressionList=SimpleExpressionList() | "*" { retval.setAllColumns(true); }) ] ")"

This accepts only expressions but no conditions. You could make a variant of SimpleExpressionList (maybe SimpleExpressionOrConditionList) and 

ExpressionList SimpleExpressionConditionList():
{
    ExpressionList retval = new ExpressionList();
    List<Expression> expressions = new ArrayList<Expression>();
    Expression expr = null;
}
{
    ( expr=SimpleExpression()  | expr=Condition() )

    { expressions.add(expr); } ("," ( expr=SimpleExpression()  | expr=Condition() ) { expressions.add(expr); })*
    {
        retval.setExpressions(expressions);
        return retval;
    }
}

This is not tested!

But it should be a possibility. Pull requests are welcome: https://github.com/JSQLParser/JSqlParser

wumpz
  • 8,257
  • 3
  • 30
  • 25
  • Thank you for your answer. I will have a look at it the upcoming week. At the moment it looks very promising. – Markus May 26 '16 at 06:04
  • In `test/resources/net/sf/jsqlparser/test/oracle-tests/function05.sql` there is a sql script containing `LNNVL` that also expects a comparison – Markus May 27 '16 at 07:47
  • There are a lot of failing tests using this oracle SQL set. At the moment there are 135 parsed successfully. But if someone would implement like I suggested, this test should succeed. – wumpz May 27 '16 at 08:12