JSqlParser will support WITH statements ? This will be very useful for us, because we use WITH to create dinamics views. See this reduced example:
WITH V_TRIBUTOS AS (
SELECT
ID_TRIBUTO_IMP
FROM
DBSM.TRIBUTOS_IMP
WHERE
COD_TRIBUTO = 'IPTU '
UNION ALL
SELECT
ID_TRIBUTO_DA AS ID_TRIBUTO_IMP
FROM
DBSM.TRIBUTOS_IMP
WHERE
COD_TRIBUTO = 'IPTU '
)
SELECT
DISTINCT CC.ANO_EXERCICIO
FROM
DBSM.CCORRENTE_FISCAL CC
WHERE
CC.ID_UNIDADE_IMOB = 1
AND CC.ID_TRIBUTO_IMP IN (
SELECT
ID_TRIBUTO_IMP
FROM
V_TRIBUTOS
)
I see that, and I don't think it wuold be too complicated to do.
But I'm swamped in work right now.
I will try and give it a look next week.
But if you find the way of doing it yourself go on and then share your code!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
it's incorrect because there's no "ON" clause in the "INNER JOIN" form, right?
I tried to avoid putting too many cases in the join (that is, defining "tab1, tab2" as "tab1 INNER JOIN tab2"), but I completely forgot about the ON clause (which is not present in the "tab1, tab2" format).
If that's what you saying, I think I can fix it in a matter of minutes.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
but only putting ON will not be a solution. You can create a flag to indicate that this Join isn't a join =/
I prefered maintain a list of from itens :)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
tomorrow i will test this version, but i don't understand why you need to support "select * from a,b LEFT OUTER JOIN c, d". This doesn't a correct SQL.
Example 1 (one dynamic view, RPL, with a union inside)
WITH RPL (PART, SUBPART, QUANTITY) AS
( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
FROM PARTLIST ROOT
WHERE ROOT.PART = '01'
UNION ALL
SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
FROM RPL PARENT, PARTLIST CHILD
WHERE PARENT.SUBPART = CHILD.PART
)
SELECT DISTINCT PART, SUBPART, QUANTITY
FROM RPL
ORDER BY PART, SUBPART, QUANTITY;
--------------------
Example 2 (two dynamic views, DINFO and DINFOMAX)
WITH
DINFO (DEPTNO, AVGSALARY, EMPCOUNT) AS
(SELECT OTHERS.WORKDEPT, AVG(OTHERS.SALARY), COUNT(*)
FROM EMPLOYEE OTHERS
GROUP BY OTHERS.WORKDEPT
),
DINFOMAX AS
(SELECT MAX(AVGSALARY) AS AVGMAX FROM DINFO)
SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY,
DINFO.AVGSALARY, DINFO.EMPCOUNT, DINFOMAX.AVGMAX
FROM EMPLOYEE THIS_EMP, DINFO, DINFOMAX
WHERE THIS_EMP.JOB = 'SALESREP'
AND THIS_EMP.WORKDEPT = DINFO.DEPTNO
I'm reading your .jj file to collaborate with this project ;)
Thanks
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
hello, Leonardo
congrats to JSqlParser.
JSqlParser will support WITH statements ? This will be very useful for us, because we use WITH to create dinamics views. See this reduced example:
WITH V_TRIBUTOS AS (
SELECT
ID_TRIBUTO_IMP
FROM
DBSM.TRIBUTOS_IMP
WHERE
COD_TRIBUTO = 'IPTU '
UNION ALL
SELECT
ID_TRIBUTO_DA AS ID_TRIBUTO_IMP
FROM
DBSM.TRIBUTOS_IMP
WHERE
COD_TRIBUTO = 'IPTU '
)
SELECT
DISTINCT CC.ANO_EXERCICIO
FROM
DBSM.CCORRENTE_FISCAL CC
WHERE
CC.ID_UNIDADE_IMOB = 1
AND CC.ID_TRIBUTO_IMP IN (
SELECT
ID_TRIBUTO_IMP
FROM
V_TRIBUTOS
)
See DB2 manual too.
http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/r0000879.htm
Thanks
WITH will be like a UNION, with many plainSelects inside
WITH VIEW_1 AS (selec ...), VIEW_2 AS (selec ...), VIEW_3 AS (selec ...) select ... from VIEW_1, VIEW_2 ...
I see that, and I don't think it wuold be too complicated to do.
But I'm swamped in work right now.
I will try and give it a look next week.
But if you find the way of doing it yourself go on and then share your code!
Could you please give me some other examples? The more the better.
That would really help the testing.
Please give 0.6.0 a try.
I got 0.6 and plainSelect.getFromItems() doesn't exists anymore. It's ok ?
To solve another problem I changed the list of fromitems into a single fromitem. All other tables are joins.
For example:
select * from a,b,c,d
Only a is fromitem; b,c,d are joins.
This is because sql such as:
select * from a,b LEFT OUTER JOIN c, d
was not parsable in the old way.
See also
https://sourceforge.net/forum/forum.php?thread_id=2164386&forum_id=360150
I found a problem because 'All other tables are joins':
SELECT *
FROM TABLE_A A, TABLE_B B
WHERE A.ID = B.ID
generated
SELECT *
FROM TABLE_A AS A
INNER JOIN TABLE_B AS B
WHERE A.ID = B.ID
this is incorrect.
it's incorrect because there's no "ON" clause in the "INNER JOIN" form, right?
I tried to avoid putting too many cases in the join (that is, defining "tab1, tab2" as "tab1 INNER JOIN tab2"), but I completely forgot about the ON clause (which is not present in the "tab1, tab2" format).
If that's what you saying, I think I can fix it in a matter of minutes.
Right
but only putting ON will not be a solution. You can create a flag to indicate that this Join isn't a join =/
I prefered maintain a list of from itens :)
'select * from a,b LEFT OUTER JOIN c, d" does not exist
see http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/r0000875.htm#jointbl
in 'joined-table' section
try 0.6.1
tomorrow i will test this version, but i don't understand why you need to support "select * from a,b LEFT OUTER JOIN c, d". This doesn't a correct SQL.
see 'joined-table' section
http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/r0000875.htm#jointbl
thx
I tested 0.6 and jsqlparser worked fine. Thank you very much.
hello, in the site
http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/r0000879.htm
i see that with may contain a list of columns too =/
although we don't use this list
Example 1 (one dynamic view, RPL, with a union inside)
WITH RPL (PART, SUBPART, QUANTITY) AS
( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
FROM PARTLIST ROOT
WHERE ROOT.PART = '01'
UNION ALL
SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
FROM RPL PARENT, PARTLIST CHILD
WHERE PARENT.SUBPART = CHILD.PART
)
SELECT DISTINCT PART, SUBPART, QUANTITY
FROM RPL
ORDER BY PART, SUBPART, QUANTITY;
--------------------
Example 2 (two dynamic views, DINFO and DINFOMAX)
WITH
DINFO (DEPTNO, AVGSALARY, EMPCOUNT) AS
(SELECT OTHERS.WORKDEPT, AVG(OTHERS.SALARY), COUNT(*)
FROM EMPLOYEE OTHERS
GROUP BY OTHERS.WORKDEPT
),
DINFOMAX AS
(SELECT MAX(AVGSALARY) AS AVGMAX FROM DINFO)
SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY,
DINFO.AVGSALARY, DINFO.EMPCOUNT, DINFOMAX.AVGMAX
FROM EMPLOYEE THIS_EMP, DINFO, DINFOMAX
WHERE THIS_EMP.JOB = 'SALESREP'
AND THIS_EMP.WORKDEPT = DINFO.DEPTNO
I'm reading your .jj file to collaborate with this project ;)
Thanks
It doesn't look that complicated, it's something like
WITH name (ColumnList) AS (PlainSelect) [, WITH name (ColumnList) AS (PlainSelect)]* Select
I think it should be ready before the end of the week.