-
Notifications
You must be signed in to change notification settings - Fork 43
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Format Merge #19
Comments
Hi, for my own curiosity I ran it against my own manticore JSQLFormatter. -- EXTERNAL
MERGE INTO DW_STG_USER.ACCOUNT_DIM target
USING ( SELECT COMMON_NAME m_commonName
, ORIGIN m_origin
, USAGE_TYPE m_usageType
, CATEGORY m_category
FROM MY_TABLE
WHERE USAGE_TYPE = :value ) source
ON ( source.m_usageType = target.USAGE_TYPE )
WHEN NOT MATCHED THEN
INSERT ( target.COMMON_NAME
, target.ORIGIN
, target.USAGE_TYPE
, target.CATEGORY )
VALUES ( source.m_commonName
, source.m_origin
, source.m_usageType
, source.m_category )
WHEN MATCHED THEN
UPDATE SET target.COMMON_NAME = source.m_commonName
, target.ORIGIN = source.m_origin
, target.USAGE_TYPE = source.m_usageType
, target.CATEGORY = source.m_category
WHERE ( ( source.m_commonName <> target.COMMON_NAME )
OR ( source.m_origin <> target.ORIGIN )
OR ( source.m_usageType <> target.USAGE_TYPE )
OR ( source.m_category <> target.CATEGORY ) )
; |
This seems to be fixed on current version. |
Simple statements look better now, but there are still a lot of issues with complex ones, e. g. -- MERGE 4
MERGE INTO cfe.instrument_import_measure imp
USING ( WITH x AS (
SELECT a.id_instrument
, a.id_currency
, a.id_instrument_type
, b.id_portfolio
, c.attribute_value product_code
, t.valid_date
, t.yield
FROM cfe.instrument a
INNER JOIN cfe.impairment b
ON a.id_instrument = b.id_instrument
LEFT JOIN cfe.instrument_attribute c
ON a.id_instrument = c.id_instrument
AND c.id_attribute = 'product'
INNER JOIN cfe.ext_yield t
ON ( a.id_currency = t.id_currency )
AND ( a.id_instrument_type LIKE t.id_instrument_type )
AND ( b.id_portfolio LIKE t.id_portfolio
OR ( b.id_portfolio IS NULL
AND t.id_portfolio = '%' ) )
AND ( c.attribute_value LIKE t.product_code
OR ( c.attribute_value IS NULL
AND t.product_code = '%' ) ) )
SELECT /*+ PARALLEL */ *
FROM x x1
WHERE x1.valid_date = ( SELECT max
FROM x
WHERE id_instrument = x1.id_instrument
AND valid_date <= to_date ) ) s
ON ( imp.id_instrument = s.id_instrument
AND imp.measure = 'YIELD_PP' )
WHEN MATCHED THEN
UPDATE SET imp.value = s.yield
; You can have a look at https://github.com/manticore-projects/jsqlformatter and also benchmark it against the interactive DEMO. Maybe the provided samples and test cases will be helpful. |
Btw, how do you plan to deal with comments? Example -- BOTH CLAUSES PRESENT 'with a string' AND "a field"
MERGE /*+ PARALLEL */ INTO test1 /*the target table*/ a
USING all_objects /*the source table*/
ON ( /*joins in()!*/ a.object_id = b.object_id )
-- INSERT CLAUSE
WHEN /*comments between keywords!*/ NOT MATCHED THEN
INSERT ( object_id /*ID Column*/
, status /*Status Column*/ )
VALUES ( b.object_id
, b.status )
/* UPDATE CLAUSE
WITH A WHERE CONDITION */
WHEN MATCHED THEN /* Lets rock */
UPDATE SET a.status = '/*this is no comment!*/ and -- this ain''t either'
WHERE b."--status" != 'VALID'
; Had a really hard time to figure that out. |
That's a good idea, but I think the advantage of this library is having no dependencies, as described in readme. |
Greetings!
On Thu, 2021-05-06 at 07:22 -0700, yohei yamana wrote:
That's a good idea, but I think the advantage of this library is
having no dependencies, as described in readme.
I get your point, but you will start to re-invent the wheel by writing
your own parser while there is already a parser available, which is
matured and can be bundled easily.
Even that matured JSQLParser had a few gaps I had to fix over the last
few weeks and there are still more gaps to fix. Developing all this
logic from scratch will take a few man years.
Another concern is speed: The REGEX will struggle with very long SQL statements (e.g. formatting a file of insert statements).
And about formatting, currently, my priority is to reproduce the
behavior of original library,
https://github.com/zeroturnaround/sql-formatter.
Can't argue about that.
In contrast I need a reliable Formatter for quite complex SQL
statements and I haven't found anything good. Still a long way to go,
but a lot of stuff is working quite well already.
But, there are a few bugs or issues in the original version, so I'm
now wondering if I should improve it independently...
That's how I started JSQLFormatter. I actually have had a look at your
project first in January and found that it failed on the `MERGE` and `WITH`
statements. So I checked the Tokens and REGEXs and while I was working
on that stuff, I figured that I was actually working on the parser, not
even on the formatter yet. This triggered my decision to restart from
scratch based on JSQLParser.
Anyways, nice to stay in touch! I would love to exchange ideas and
solutions in the future.
All the best and cheers!
Andreas
|
I almost completely agree with you, there is no doubt that JSQLFormatter is more reliable at formatting. And now, there is an issue about using external SQL parser created by maintainer of original. Thank you very much too. |
Please enhance the library to format this SQL.
The text was updated successfully, but these errors were encountered: