End of the CTE section should be on its own

[hayssams]

trafficstars

line 10 below should be:

 cte
)
/* ...*/
SELECT sl_ ...

MERGE INTO bq_test_ds.transactions_v3 sl_existing
    USING ( WITH sl_incoming AS (
                    WITH cte AS (
                            SELECT  '12345' AS transaction_id
                                    , TIMESTAMP '2021-03-01' AS transaction_date
                                    , 300 AS amount
                                    , 'CA' AS location_info
                                    , 'Dua' AS seller_info  )
                    SELECT *
                    FROM cte ) /* All line present in the source with their key */ SELECT   sl_incoming.transaction_id AS sl_pseudo_transaction_id
                                        , sl_incoming.*
            FROM sl_incoming
                LEFT JOIN bq_test_ds.transactions_v3 sl_existing
                    ON ( sl_incoming.`transaction_id` = sl_existing.`transaction_id`
                            AND sl_existing.end_ts IS NULL )
            WHERE sl_existing.`transaction_id` IS NULL
                OR sl_existing.transaction_date < sl_incoming.transaction_date
            UNION ALL  /* All line present in the source and target that are current in the target */
            SELECT  NULL AS sl_pseudo_transaction_id
                    , sl_incoming.*
            FROM sl_incoming
                INNER JOIN bq_test_ds.transactions_v3 sl_existing
                    ON ( sl_incoming.`transaction_id` = sl_existing.`transaction_id`
                            AND sl_existing.end_ts IS NULL )
            WHERE sl_existing.transaction_date < sl_incoming.transaction_date ) sl_incoming
        ON ( sl_incoming.sl_pseudo_transaction_id = sl_existing.transaction_id )
WHEN MATCHED THEN
    UPDATE SET  end_ts = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
    INSERT ( `transaction_id`
                , `transaction_date`
                , `amount`
                , `location_info`
                , `seller_info`
                , `start_ts`
                , `end_ts` )
    VALUES ( sl_incoming.`transaction_id`
                , sl_incoming.`transaction_date`
                , sl_incoming.`amount`
                , sl_incoming.`location_info`
                , sl_incoming.`seller_info`
                , CURRENT_TIMESTAMP()
                , NULL )

[manticore-projects]

Thank you for reporting! Can you please give me the complete original statement? Your text above is garbled unfortunately.

Also, on the Samples Page https://manticore-projects.com/JSQLFormatter/samples.html you can see what should be expected. Anything different from those patterns is a bug/issue I would need to fix.

[hayssams]

Great thank you. The samples page provides a great base for discussing:

In the SELECT section on line 10, 32, 53 ..., I suggest that the SELECT statement starts on the next line:

before:

 FROM (  SELECT ...

after:

FROM (
  SELECT ...

In the MERGE section line 3, keep USING on the same line as the MERGE keyword when possible:

before:

MERGE INTO ...
    USING ( 

after:

MERGE INTO ... USING ( 

Still in the MERGE section line 88, WITH should always be on the next line even outside a merge statement:

before:

MERGE INTO ...
     USING ( WITH x AS (

after:

MERGE INTO ... USING ( 
  WITH x AS (

P.S. I updated the issue to have the statement correctly rendered.