Aug 03 2020 01:50 PM
Aug 03 2020 01:50 PM
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")
Aug 03 2020 02:20 PM
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.
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"