1

I am trying to load to a table based on load types - Full or Incremental that is being passed as parameter in stored procedure. I was able to try with substitution variable with one line of code previously, but the below code doesn't seem to work -

Stored procedure possible arguments:

LOAD_TYPE=FULL
LOAD_TYPE=INCR

var incr_condition = (load_type=='INCR')?"INNER JOIN temp_table"

with temp_table(
select data
from table a
where dt between 01-01-2019 and 09-09-2020
)

select *
from table b
${incr_condition} -- execute only if load_type=INCR
INNER JOIN TABLE C ON B.ID = C.ID

Is there any way to restrict the with clause to execute only if the load_type==INCR? Please advice.

vvazza
  • 421
  • 7
  • 21
  • Is load_type being passed into the stored procedure? If so, you need to capitalize the variable. – Greg Pavlik Sep 09 '20 at 12:58
  • Yes, It has been capitalized. Just for example purpose, i have it here in small letters. I would want with clause to execute only if load_type = FULL. And with ternary shorthand, it is expecting me to have an else clause as well. Could you please help me with the approach? – vvazza Sep 09 '20 at 12:59

1 Answers1

1

I think the conditional operator (question mark) must have a false part in addition to the true part. Otherwise, it generates a syntax error when there's a semicolon ending the line. This example obviously doesn't run anything, but it will return the values assigned to the "out" variable, which would be run.

Since you're using a replacement variable ${incr_condition} be sure to use backticks to open and close your SQL string.

create or replace procedure foo(LOAD_TYP string)
returns string
language javascript
as
$$

var load_type = LOAD_TYP;

var incr_condition = (load_type === 'INCR') ? "INNER JOIN temp_table" : "";

var out = `
with temp_table(
select data
from table a
where dt between 01-01-2019 and 09-09-2020
)

select *
from table b
${incr_condition} -- execute only if load_type=INCR
INNER JOIN TABLE C ON B.ID = C.ID
`;

return out;

$$;

call foo('INCR'); --Adds the inner join
call foo('FULL'); --Does not add the inner join

I also recommend changing your comparison on strings from == to ===. For details on why, reference What is the correct way to check for string equality in JavaScript?.

Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
  • Perfect, Greg ! This is exactly what I tried few minutes ago and it worked. Thanks for your guidance again :) – vvazza Sep 09 '20 at 15:03