[Solved] Keywords and "My Upper/Lower Case Keywords"

 
Page 1 / 3
Ideas, Issues and Questions
Last Post by Guido 2 years ago
18 Posts
2 Users
0 Reactions
4,093 Views
Posts: 20
 jasonccrane
Topic starter
Aug 20, 2024 10:44 pm
(@jasonccrane)
Member
Joined: 3 years ago
[#125]

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!


17 Replies
Guido
Posts: 295
 Guido
Admin
Aug 24, 2024 9:25 pm
(@admin)
Member
Joined: 5 years ago

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


Reply
2 Replies
 jasonccrane
(@jasonccrane)
Joined: 3 years ago

Member
Posts: 20
Aug 24, 2024 10:45 pm
Reply toGuidoGuido

@admin thank you. Would this also include WITH statements?


Reply
 jasonccrane
(@jasonccrane)
Joined: 3 years ago

Member
Posts: 20
Sep 06, 2024 10:55 pm
Reply toGuidoGuido

@admin 

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.


Reply
Guido
Posts: 295
 Guido
Admin
Sep 07, 2024 11:09 pm
(@admin)
Member
Joined: 5 years ago

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


Reply
Guido
Posts: 295
 Guido
Admin
Sep 08, 2024 9:31 pm
(@admin)
Member
Joined: 5 years ago

Hi @jasonccrane

could you please post a few examples of the formatting rules you would like to get for the MERGE keyword?

Regards

Guido


Reply
1 Reply
 jasonccrane
(@jasonccrane)
Joined: 3 years ago

Member
Posts: 20
Sep 09, 2024 5:04 pm
Reply toGuidoGuido

@admin yes, give me a bit of time to come up with my most common examples.  Thanks for help!


Reply
Posts: 20
 jasonccrane
Topic starter
Sep 09, 2024 9:41 pm
(@jasonccrane)
Member
Joined: 3 years ago

@admin

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

image

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:

  1. Option to break or keep the table name and USING keyword on the same line as "MERGE" or "INSERT INTO" similar to the DELETE section for "AFTER DELETE."
  2. Add or modify the SQL.Select section to include or apply the break options to the MERGE statement for the "ON" syntax and can keep inline or break the join criteria "AND/OR" clause(s) of a MERGE.   
    1. Examples  
      1. 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
      2. 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
      3. 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"
)
  3. For the SQL.SQL Lists.SELECT Columns/Line option, add more multiples of 10 to the list - often times I have 20-40 columns that I want formatted on a single line but only have the option to break at 10, nothing beyond.
    1. Example (1=current vs 2=requested) 
      1. 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
;
      2. 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.


Reply
Page 1 / 3
Forum Jump:
  Previous Topic
Next Topic  
Share: