SQLinForm SQL FormatterCommunity Discussion Forum
SQLinForm SQL Formatter
Guido, how are keywords identified for each "flavor" of a language? My guess is there is some dictionary file that provides a listing of these.
My issue comes from how my user-defined "My Upper/Lower Case Keywords" section is used. I have the following config using Engine Release 6.24.07.27 of the Desktop App:
Complete SQL = No Change
Keywords = UPPERCASE
Non-Keywords = lowercase
My understanding is that the Non Keywords would include anything that is *not* a master keyword AND *not* in the list of user-defined keywords. This assumes anything in the list of user-defined words would be treated as Keyword and follow the Keywords config (UPPERCASE in my scenario).
When formatting the following statement, I see unexpected lowercasing of the user-defined keywords:
alter table sji_sku_ru_tmp add constraint "SJI_SKU_RU_TMP_PK" primary key ("ITEMNUM" , "SJG_FUNC_CLASS") using index pctfree 10 initrans 2 maxtrans 255 compute statistics storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default flash_cache default cell_flash_cache default)tablespace "USERS" enable;
I have defined the words "STATISTICS" and "PCTFREE" as keywords but they are not formatted as such.
ALTER TABLE sji_sku_ru_tmp ADD CONSTRAINT "SJI_SKU_RU_TMP_PK" PRIMARY KEY ( "ITEMNUM" , "SJG_FUNC_CLASS" ) USING INDEX pctfree 10 initrans 2 maxtrans 255 COMPUTE statistics STORAGE(INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645 PCTINCREASE 0 freelists 1 freelist groups 1 buffer_pool DEFAULT flash_cache DEFAULT cell_flash_cache DEFAULT)TABLESPACE "USERS" enable ;
As you can see, it looks like they user-defined Keywords are treated as Non-Keywords and left as lowercase instead of uppercasing them as I was hoping.
Am I offbase on thinking this is how that section works?
Also, Is there a way to add Master Keywords to the listing?
Thanks!
Hi @jasonccrane,
I am back from holiday and had a look to your post.
Currently the Upper/Lower Case Keywords are only respected inside SQL DML Statements (SELECT, UPDATE, INSERT, etc)
I think it is a good idea to apply these also for DDL statements.
Will do more investigations and come back to you soon.
Regards
Guido
Something I have also noticed with some of the keyword formatting is the MERGE statement does not have a section under the SQL>Ins/Upd/Del section. Ideally it could behave using those keywords for INSERT/UPDATE/DELETE, however, there are some options that aren't present, such as keeping the table on the same line as the MERGE and USING keywords. Also, the ON sections can't be formatted - just something to think about as options.
Thank you - I love this formatter.
Hi Jason,
I activated the lower/upper keywords for DDL Statements and will publish it within 2 or 3 days
I like the idea of adding a MERGE section. Will think about it.
Regards
Guido
Hi @jasonccrane
could you please post a few examples of the formatting rules you would like to get for the MERGE keyword?
Regards
Guido
@admin yes, give me a bit of time to come up with my most common examples. Thanks for help!
Guido,
The statement below serves as a good example:
MERGE INTO "SCHEMA"."CL_MAPPING" tgt USING ( SELECT '5' AS "COMPANY_ID" , '1' AS "MTRINST_FNCT_ID" , '382100' AS "MTRINST_PLANT_ID" , PROP_id_merge AS "MTRINST_PROP_ID" , '1' AS "UNIT_COST" , '1' AS "REG_FNCT_ID" , '383000' AS "REG_PLANT_ID" , '200058' AS "REG_PROP_ID" , '1' AS "ID" , '0' AS "PERCENT_SPLIT" , 'PWRPLANT' AS "CREATED_USER_ID" , TO_DATE('30-AUG-24','DD-MON-RR') AS "CREATED_TIME_STAMP" FROM cl_PROP_id_map where PROP_id_ecl = '205433' ) src ON (src."COMPANY_ID" = tgt."COMPANY_ID" AND src."MTRINST_FNCT_ID" = tgt."MTRINST_FNCT_ID" AND src."MTRINST_PLANT_ID" = tgt."MTRINST_PLANT_ID" AND src."MTRINST_PROP_ID" = tgt."MTRINST_PROP_ID" AND src."UNIT_COST" = tgt."UNIT_COST" AND src."REG_FNCT_ID" = tgt."REG_FNCT_ID" AND src."REG_PLANT_ID" = tgt."REG_PLANT_ID" AND src."REG_PROP_ID" = tgt."REG_PROP_ID") WHEN NOT MATCHED THEN INSERT (tgt."ID", tgt."COMPANY_ID", tgt."MTRINST_FNCT_ID", tgt."MTRINST_PLANT_ID", tgt."MTRINST_PROP_ID", tgt."REG_FNCT_ID", tgt."REG_PLANT_ID", tgt."REG_PROP_ID", tgt."UNIT_COST", tgt."PERCENT_SPLIT", tgt."CREATED_USER_ID", tgt."CREATED_TIME_STAMP") VALUES (src."ID", src."COMPANY_ID", src."MTRINST_FNCT_ID", src."MTRINST_PLANT_ID", src."MTRINST_PROP_ID", src."REG_FNCT_ID", src."REG_PLANT_ID", src."REG_PROP_ID", src."UNIT_COST", src."PERCENT_SPLIT", src."CREATED_USER_ID", src."CREATED_TIME_STAMP") WHEN MATCHED THEN UPDATE SET tgt."ID" = src."ID" ,tgt."PERCENT_SPLIT" = src."PERCENT_SPLIT" ,tgt."CREATED_USER_ID" = src."CREATED_USER_ID" ,tgt."CREATED_TIME_STAMP" = src."CREATED_TIME_STAMP" ;
My config is the following for the INSERT/UPDATE/DELETE options
The output for that (and other config that I can share via profile config file is this:
MERGE INTO
"SCHEMA"."CL_MAPPING" tgt
USING
(
SELECT
'5' AS "COMPANY_ID"
, '1' AS "MTRINST_FNCT_ID"
, '382100' AS "MTRINST_PLANT_ID"
, prop_id_merge AS "MTRINST_PROP_ID"
, '1' AS "UNIT_COST"
, '1' AS "REG_FNCT_ID"
, '383000' AS "REG_PLANT_ID"
, '200058' AS "REG_PROP_ID"
, '1' AS "ID"
, '0' AS "PERCENT_SPLIT"
, 'PWRPLANT' AS "CREATED_USER_ID"
, TO_DATE('30-AUG-24', 'DD-MON-RR')AS "CREATED_TIME_STAMP"
FROM cl_prop_id_map
WHERE prop_id_ecl = '205433'
)src
ON (src."COMPANY_ID" = tgt."COMPANY_ID"
AND src."MTRINST_FNCT_ID" = tgt."MTRINST_FNCT_ID"
AND src."MTRINST_PLANT_ID" = tgt."MTRINST_PLANT_ID"
AND src."MTRINST_PROP_ID" = tgt."MTRINST_PROP_ID"
AND src."UNIT_COST" = tgt."UNIT_COST"
AND src."REG_FNCT_ID" = tgt."REG_FNCT_ID"
AND src."REG_PLANT_ID" = tgt."REG_PLANT_ID"
AND src."REG_PROP_ID" = tgt."REG_PROP_ID")
WHEN NOT MATCHED THEN
INSERT (
tgt."ID"
, tgt."COMPANY_ID"
, tgt."MTRINST_FNCT_ID"
, tgt."MTRINST_PLANT_ID"
, tgt."MTRINST_PROP_ID"
, tgt."REG_FNCT_ID"
, tgt."REG_PLANT_ID"
, tgt."REG_PROP_ID"
, tgt."UNIT_COST"
, tgt."PERCENT_SPLIT"
, tgt."CREATED_USER_ID"
, tgt."CREATED_TIME_STAMP"
)
VALUES (
src."ID"
, src."COMPANY_ID"
, src."MTRINST_FNCT_ID"
, src."MTRINST_PLANT_ID"
, src."MTRINST_PROP_ID"
, src."REG_FNCT_ID"
, src."REG_PLANT_ID"
, src."REG_PROP_ID"
, src."UNIT_COST"
, src."PERCENT_SPLIT"
, src."CREATED_USER_ID"
, src."CREATED_TIME_STAMP"
)
WHEN MATCHED THEN
UPDATE
SET
tgt."ID" = src."ID"
, tgt."PERCENT_SPLIT" = src."PERCENT_SPLIT"
, tgt."CREATED_USER_ID" = src."CREATED_USER_ID"
, tgt."CREATED_TIME_STAMP" = src."CREATED_TIME_STAMP"
;
So, ideally, I'd like to be able to tailor the options to allow the following adjustments:
MERGE INTO "SCHEMA"."CL_MAPPING" tgt USING (
SELECT
'5' AS "COMPANY_ID"
, '1' AS "MTRINST_FNCT_ID"
, '382100' AS "MTRINST_PLANT_ID"
, prop_id_merge AS "MTRINST_PROP_ID"
, '1' AS "UNIT_COST"
, '1' AS "REG_FNCT_ID"
, '383000' AS "REG_PLANT_ID"
, '200058' AS "REG_PROP_ID"
, '1' AS "ID"
, '0' AS "PERCENT_SPLIT"
, 'PWRPLANT' AS "CREATED_USER_ID"
, TO_DATE('30-AUG-24', 'DD-MON-RR')AS "CREATED_TIME_STAMP"
FROM cl_prop_id_map
WHERE prop_id_ecl = '205433'
) src ON (src."COMPANY_ID" = tgt."COMPANY_ID" AND src."MTRINST_FNCT_ID" = tgt."MTRINST_FNCT_ID" AND src."MTRINST_PLANT_ID" = tgt."MTRINST_PLANT_ID" AND src."MTRINST_PROP_ID" = tgt."MTRINST_PROP_ID" AND src."UNIT_COST" = tgt."UNIT_COST" AND src."REG_FNCT_ID" = tgt."REG_FNCT_ID" AND src."REG_PLANT_ID" = tgt."REG_PLANT_ID" AND src."REG_PROP_ID" = tgt."REG_PROP_ID")
WHEN NOT MATCHED THEN
MERGE INTO
"SCHEMA"."CL_MAPPING" tgt
USING (
SELECT
'5' AS "COMPANY_ID"
, '1' AS "MTRINST_FNCT_ID"
, '382100' AS "MTRINST_PLANT_ID"
, prop_id_merge AS "MTRINST_PROP_ID"
, '1' AS "UNIT_COST"
, '1' AS "REG_FNCT_ID"
, '383000' AS "REG_PLANT_ID"
, '200058' AS "REG_PROP_ID"
, '1' AS "ID"
, '0' AS "PERCENT_SPLIT"
, 'PWRPLANT' AS "CREATED_USER_ID"
, TO_DATE('30-AUG-24', 'DD-MON-RR')AS "CREATED_TIME_STAMP"
FROM cl_prop_id_map
WHERE prop_id_ecl = '205433'
) src ON (src."COMPANY_ID" = tgt."COMPANY_ID" AND src."MTRINST_FNCT_ID" = tgt."MTRINST_FNCT_ID" AND src."MTRINST_PLANT_ID" = tgt."MTRINST_PLANT_ID" AND src."MTRINST_PROP_ID" = tgt."MTRINST_PROP_ID" AND src."UNIT_COST" = tgt."UNIT_COST" AND src."REG_FNCT_ID" = tgt."REG_FNCT_ID" AND src."REG_PLANT_ID" = tgt."REG_PLANT_ID" AND src."REG_PROP_ID" = tgt."REG_PROP_ID")
WHEN NOT MATCHED THEN
MERGE INTO "SCHEMA"."CL_MAPPING" tgt
USING
(
SELECT
'5' AS "COMPANY_ID"
, '1' AS "MTRINST_FNCT_ID"
, '382100' AS "MTRINST_PLANT_ID"
, prop_id_merge AS "MTRINST_PROP_ID"
, '1' AS "UNIT_COST"
, '1' AS "REG_FNCT_ID"
, '383000' AS "REG_PLANT_ID"
, '200058' AS "REG_PROP_ID"
, '1' AS "ID"
, '0' AS "PERCENT_SPLIT"
, 'PWRPLANT' AS "CREATED_USER_ID"
, TO_DATE('30-AUG-24', 'DD-MON-RR')AS "CREATED_TIME_STAMP"
FROM cl_prop_id_map
WHERE prop_id_ecl = '205433'
) src
ON (
src."COMPANY_ID" = tgt."COMPANY_ID"
AND src."MTRINST_FNCT_ID" = tgt."MTRINST_FNCT_ID"
AND src."MTRINST_PLANT_ID" = tgt."MTRINST_PLANT_ID"
AND src."MTRINST_PROP_ID" = tgt."MTRINST_PROP_ID"
AND src."UNIT_COST" = tgt."UNIT_COST"
AND src."REG_FNCT_ID" = tgt."REG_FNCT_ID"
AND src."REG_PLANT_ID" = tgt."REG_PLANT_ID"
AND src."REG_PROP_ID" = tgt."REG_PROP_ID"
)
SELECT col_1 , col_2 , col_3 , col_4 , col_5, col_6, col_7, col_8, col_9, col_10 , col_11, col_12, col_13, col_14, col_15 FROM dual ;
SELECT col_1 , col_2 , col_3 , col_4 , col_5, col_6, col_7, col_8, col_9, col_10, col_11, col_12, col_13, col_14, col_15 FROM dual ;
Please reach out to me if you have questions or need more info. Happy to help, and as always, thank you so much.
Hi Hi @jasonccrane ,
the formatting of keywords in DDL statments should now also work in the Desktop app
Regards
Guido
@admin Disregard my last comment - I downloaded and tested. Looks great. Thank you!
Hi @jasonccrane,
I am back from holiday and can start working on new features 🙂
For the number of columns per line I could add a "999" option to the dropdown list
Hi @jasonccrane,
I am back from holiday and can start working on new features 🙂
For the number of columns per line I could add a "999" option to the dropdown list
That would work just fine. Thank you!
