Excel Formula help on getting the syntax right

0
votes

Excel Version: 2016

I've asked the main question here, this is the extension of that question.

Building SQL SELECT statement within EXCEL enter image description here

I need to extend the logic for all the columns whenever there is a value in that in the sql "AND" clause as well.

eg. AND TAB1.COLUMNA = TAB2.TCOLUMNA AND TAB1.COLUMNB = TAB2.COLUMNB and so on, only if there is a value in the DIM columns.

I am not sure how to embed it dynamically using the SUBSTITUTE and TRIM functions.

Edit: Formula

="select * from (SELECT RET_ID,RET_NM,"&SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2)," ",",")&" FROM 
TABLEX ORDER BY "&SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2)," ",",")&") TAB1 FULL OUTER JOIN   (SELECT RET_ID,RET_NM,"&SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2)," ",",")&" FROM 
TABLEX ORDER BY "&SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2)," ",",")&") TAB2 ON TAB1.RET_ID = TAB2.RET_ID AND TAB1.RET_NM = TAB2.RET_NM  "
1
sqlexcelexcel-formula

1 Answers

0
votes

Solved, By this piece of code

"&IF(LEN(D2)>0,CONCATENATE("AND COALESCE(TAB1.",D2,",'')","="," COALESCE(TAB2.",D2,",'')"),"")&"