[BUG] JSQLParser Version : RDBMS : failing feature description

[HoHnerF]

Always check against the Latest SNAPSHOT of JSQLParser and the Syntax Diagram

Failing SQL Feature:

net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "WITH" "WITH" at line 66, column 1.

Was expecting one of:

"("
"VALUES"
<K_SELECT>

SQL Example:

SELECT sum_name,accumulated_this_month from (WITH aa AS ( SELECT hafs.NAME AS asset_resource, hd.NAME dept, CASE

		WHEN aac.admin_dept != 606 THEN
		(
		SELECT ID 
		FROM
			account_asset_category aac 
		WHERE
			parent_left <= ( SELECT parent_left FROM account_asset_category WHERE ID IN ( aaa.category_id ) ) 
			AND parent_right >= ( SELECT parent_right FROM account_asset_category WHERE ID IN ( aaa.category_id ) ) 
			AND parent_id IS NULL 
		) ELSE aac.ID 
	END AS top_asset_category_id,
	had.depreciation_amount 
FROM
	hrp_asset_distribution had
	CROSS JOIN
                        (
                        SELECT
                            *
                        FROM
                            account_period
                        WHERE
                            asset_period_status = 'open'
                        AND STATE = 'draft'
                    ) ap
	LEFT JOIN account_asset_asset aaa ON aaa.ID = had.account_asset_id
	LEFT JOIN hr_department hd ON hd.ID = aaa.administrative_department
	LEFT JOIN account_asset_category aac ON aac.ID = aaa.category_id
	LEFT JOIN hrp_asset_funding_source hafs ON hafs.ID = had.funds_source_id
	LEFT JOIN account_analytic_account aaa2 ON aaa2.ID = had.expense_cost_center_id
	LEFT JOIN hrp_asset_transaction hat ON hat.account_asset_id = had.account_asset_id 
	AND hat.transaction_type = 'retirement' 
WHERE
	aaa.asset_type IN ( 'capitalization') 
	AND aaa.date_in_service <= ( SELECT date_stop FROM account_period WHERE ID = ap. ID ) 
	AND (
		aaa.STATE = 'open' 
		OR (
			aaa.STATE = 'close' 
			AND hat.transaction_date >= ( SELECT date_start FROM account_period WHERE ID = ap. ID ) 
			AND hat.transaction_date <= ( SELECT date_stop FROM account_period WHERE ID = ap. ID ) 
		) 
	) and ap.ID=(SELECT id from account_period WHERE  date_start>= '2024-05-01 00:00:00' and date_stop<= '2024-05-31 23:59:59')

	AND had.depreciation_amount != 0 
) SELECT
asset_resource || '-' || dept || '-' || aac.NAME sum_name,
round( SUM ( depreciation_amount ) :: NUMERIC, 2 ) AS accumulated_this_month 

FROM aa LEFT JOIN account_asset_category aac ON aac.ID = aa.top_asset_category_id GROUP BY asset_resource, dept, aac.NAME

UNION ALL

( WITH aa as ( SELECT hafs. NAME AS asset_resource, hd.name dept, case when aac.admin_dept !=606 then ( SELECT ID FROM account_asset_category aac WHERE parent_left <= ( SELECT parent_left FROM account_asset_category WHERE ID IN (aaa.category_id) ) AND parent_right >= ( SELECT parent_right FROM account_asset_category WHERE ID IN (aaa.category_id) ) AND parent_id IS NULL ) else aac.id end as top_asset_category_id, has.depreciation_amount FROM hrp_asset_snapshot has LEFT JOIN account_asset_asset aaa ON aaa. ID = has.asset_id LEFT JOIN hr_department hd on hd.id=aaa.administrative_department LEFT JOIN account_asset_category aac on aac.id=aaa.category_id

                                LEFT JOIN hrp_asset_funding_source hafs ON hafs. ID = has.funding_source_id
                                LEFT JOIN account_analytic_account aaa2 ON aaa2. ID = has.cost_center_id
                                WHERE
                                    has.period_id = (SELECT id from account_period WHERE  date_start>= '2024-05-01 00:00:00' and date_stop<= '2024-05-31 23:59:59')

                and aaa.asset_type='capitalization' and has.depreciation_amount !=0)
      SELECT asset_resource||'-'||dept||'-'||aac.name sum_name,
      round(SUM (depreciation_amount)::numeric,2) AS accumulated_this_month 
      from  aa 
      LEFT JOIN account_asset_category aac on aac.id=aa.top_asset_category_id
      GROUP BY asset_resource,dept,aac.name))t

Software Information:

• JSqlParser version: 4.1
• Database :PostgreSQL

Tips: