I have the following SQL that I want to format but am having an issue with the REGEXP_REPLACE master keyword not being formatted properly. I have the following keywords setup the same to format on a single line "My Keywords"
trim
decode
nvl
instr
instrb
instrc
subst
substrb
substrc
replace
regexp_instr
regexp_replace
with # Vals on Line 1 = 999 and # Vals on Line > 1 = 999 with no comma alignment.
SELECT decode(ACTUALCOST,1,trim(0),1,2,3), replace(ACTUALDATE,"test","testc") , instr(ASSETNUM,"test"), instrb(BINNUM,"test"), trim(regexp_replace(ITEMNUM,'[^ -~]','')), trim(regexp_replace(WONUM,'[^ -~]','')), trim(regexp_replace(PARENT,'[^ -~]','')), trim(regexp_replace(JOBCODECOMP,'[^ -~]','')), trim(regexp_replace(WO_WONUM,'[^ -~]','')), trim(regexp_replace(sjgppauth,'[^ -~]','')), trim(regexp_replace(sjgdivision1,'[^ -~]','')) FROM processing_table a WHERE NOT EXISTS ( SELECT 1 FROM INTERFACE_STG b WHERE a.transid <= b.transid ) AND NOT EXISTS ( SELECT 1 FROM interface_STG_ARC b WHERE a.transid <= b.transid );
Here is the output:
SELECT DECODE(actualcost , 1 , TRIM(0) , 1 , 2 , 3) , REPLACE(actualdate , "test" , "testc") , INSTR(assetnum , "test") , INSTRB(binnum , "test") , TRIM(REGEXP_REPLACE ( itemnum , '[^ -~]' , '')) , TRIM(REGEXP_REPLACE ( wonum , '[^ -~]' , '')) , TRIM(REGEXP_REPLACE ( parent , '[^ -~]' , '')) , TRIM(REGEXP_REPLACE ( jobcodecomp , '[^ -~]' , '')) , TRIM(REGEXP_REPLACE ( wo_wonum , '[^ -~]' , '')) , TRIM(REGEXP_REPLACE ( sjgppauth , '[^ -~]' , '')) , TRIM(REGEXP_REPLACE ( sjgdivision1 , '[^ -~]' , '')) FROM processing_table a WHERE NOT EXISTS ( SELECT 1 FROM interface_stg b WHERE a.transid <= b.transid ) AND NOT EXISTS ( SELECT 1 FROM interface_stg_arc b WHERE a.transid <= b.transid ) ;
As you can see, everything looks to have worked correctly except the REGEXP_REPLACE keyword.
Hi @jasonccrane,
as always many thanks for your valuable feedback 🙂
I fixed this issue.
Are you working with the N++ Plugin? 32 or 64 bit?
Regards
Guido
New Release is now available for download
Hi @jasonccrane,
as always many thanks for your valuable feedback 🙂
I fixed this issue.
Are you working with the N++ Plugin? 32 or 64 bit?
Regards
Guido
My pleasure! This was using the 64 bit Desktop App, no N++ plug-in. Very much appreciate the quick turnaround!