Alternatives to using Concat for SQL queries

%3CLINGO-SUB%20id%3D%22lingo-sub-1563718%22%20slang%3D%22en-US%22%3EAlternatives%20to%20using%20Concat%20for%20SQL%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1563718%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20find%20alternative%20ways%20to%20using%20Excel's%20concate%20function%20for%20a%20SQL%20Query%20and%20any%20help%20would%20be%20greatly%20appreciated.%20Specifically%20if%20anyone%20knows%20of%20a%20way%20to%20rewrite%20this%20using%20Textjoin%20that%20would%20be%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20current%20formula%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%3DCONCAT(%22select%20count(1)%20as%20rec_cnt%2C'%22%2CA3%2C%22'%20as%20tbl_nm%2C%20'%22%2CB3%2C%22'%20as%20col_nm%2C%20case%20when%20length(nvl(%22%2CB3%2C%22%2C0))%20%26gt%3B%207%20then%20'GT%207'%20else%20'LTE%207'%20end%20as%20col_len%20from%20dwh02.%22%2CA3%2C%22%20group%20by%20case%20when%20length(nvl(%22%2CB3%2C%22%2C0))%20%26gt%3B%207%20then%20'GT%207'%20else%20'LTE%207'%20end%20union%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1563718%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1563809%22%20slang%3D%22en-US%22%3ERe%3A%20Alternatives%20to%20using%20Concat%20for%20SQL%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1563809%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F748339%22%20target%3D%22_blank%22%3E%40nshahid70%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETextJoin()%20is%20used%20to%20combine%20contents%20from%20different%20cells.%20You%20can%20specify%20a%20delimiter%20and%20you%20can%20ignore%20empty%20cells.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20use%20TextJoin%2C%20then%20all%20the%20elements%20need%20to%20be%20in%20cells.%20Your%20current%20formula%20has%20only%20a%20few%20elements%20of%20the%20resulting%20text%20string%20in%20cells%2C%20and%20other%20elements%20are%20hard%20coded.%20This%20cannot%20be%20used%20with%20TextJoin.%20So%2C%20you%20would%20need%20to%20enter%20the%20text%20constants%20into%20cells%2C%20too.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-08-04_9-17-55.png%22%20style%3D%22width%3A%20605px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F210088i8C6D01B1FE25E864%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222020-08-04_9-17-55.png%22%20alt%3D%222020-08-04_9-17-55.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20cell%20E5%20is%26nbsp%3B%26nbsp%3B%3DTEXTJOIN(%2CTRUE%2CA5%2CA3%2CA6%2CB3%2CA7%2CB3%2CA8%2CA3%2CA9%2CB3%2CA10)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20you%20don't%20really%20need%20Concat()%20either.%20You%20can%20use%20the%20ampersand%20character%20to%20append%20different%20text%20strings.%20The%20formula%20in%20cell%20E4%20produces%20the%20same%20result%20with%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D%22select%20count(1)%20as%20rec_cnt%2C'%22%26amp%3BA3%26amp%3B%22'%20as%20tbl_nm%2C%20'%22%26amp%3BB3%26amp%3B%22'%20as%20col_nm%2C%20case%20when%20length(nvl(%22%26amp%3BB3%26amp%3B%22%2C0))%20%26gt%3B%207%20then%20'GT%207'%20else%20'LTE%207'%20end%20as%20col_len%20from%20dwh02.%22%26amp%3BA3%26amp%3B%22%20group%20by%20case%20when%20length(nvl(%22%26amp%3BB3%26amp%3B%22%2C0))%20%26gt%3B%207%20then%20'GT%207'%20else%20'LTE%207'%20end%20union%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Deleted
Not applicable

I am trying to find alternative ways to using Excel's concate function for a SQL Query and any help would be greatly appreciated. Specifically if anyone knows of a way to rewrite this using Textjoin that would be helpful.

 

The current formula is as follows:

=CONCAT("select count(1) as rec_cnt,'",A3,"' as tbl_nm, '",B3,"' as col_nm, case when length(nvl(",B3,",0)) > 7 then 'GT 7' else 'LTE 7' end as col_len from dwh02.",A3," group by case when length(nvl(",B3,",0)) > 7 then 'GT 7' else 'LTE 7' end union")

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 Reply
Highlighted

Hello @Deleted 

 

TextJoin() is used to combine contents from different cells. You can specify a delimiter and you can ignore empty cells.

 

If you want to use TextJoin, then all the elements need to be in cells. Your current formula has only a few elements of the resulting text string in cells, and other elements are hard coded. This cannot be used with TextJoin. So, you would need to enter the text constants into cells, too.

 

2020-08-04_9-17-55.png

The formula in cell E5 is  =TEXTJOIN(,TRUE,A5,A3,A6,B3,A7,B3,A8,A3,A9,B3,A10)

 

But you don't really need Concat() either. You can use the ampersand character to append different text strings. The formula in cell E4 produces the same result with

 

="select count(1) as rec_cnt,'"&A3&"' as tbl_nm, '"&B3&"' as col_nm, case when length(nvl("&B3&",0)) > 7 then 'GT 7' else 'LTE 7' end as col_len from dwh02."&A3&" group by case when length(nvl("&B3&",0)) > 7 then 'GT 7' else 'LTE 7' end union"