Trying to force an error in script

[rittermd]

Although my code seems logical it is not working.  The field that I create in the SQL called vpublish is not passing its value to the If statement below it.  The Trace shows that it is blank. 

What I want is for the script to try to load the txt file (which does not exist) when vpublish = 'No' and to finish successfully if vpublish = 'Yes'.  My plan is to make my dashboard tasks dependent on this task so they don't run if there is no new data.

Do I need to add a Resident Load for the If statement to work?

last_update:
SQL
select
client_id
,dm_name
,last_dm_update_date
,convert(date,GETDATE()) as curdate
,case when datediff(dd,last_dm_update_date, convert(date,getdate())) <=1 then 'Yes'
else 'No' end [vpublish]
from dbReferences.dbo.t_clients
where client_id = 2;

TRACE vpublish=$(vpublish);

If (vpublish = 'No') THEN
LOAD *
FROM inexistent.txt(txt, codepage is 1252, no labels, delimiter is ',',msq);
END IF

[swuehl · Accepted solution]

I think after the SQL, you just have a field vpublish, no variable.

Add a Peek to create a variable from field:

last_update:
SQL
select
client_id
,dm_name
,last_dm_update_date
,convert(date,GETDATE()) as curdate
,case when datediff(dd,last_dm_update_date, convert(date,getdate())) <=1 then 'Yes'
else 'No' end [vpublish]
from dbReferences.dbo.t_clients
where client_id = 2;

Let vpublish = Peek('vpublish',-1,'last_update');

TRACE vpublish=$(vpublish);

If (vpublish = 'No') THEN
LOAD *
FROM inexistent.txt(txt, codepage is 1252, no labels, delimiter is ',',msq);
END IF

[maxgro]

small example, replace the bold with your sql load

T:

load

  'No' as  vpublish

AutoGenerate 1;

let vpublish = Peek('vpublish');

TRACE vpublish='$(vpublish)';

If ('$(vpublish)' = 'No') THEN

  trace NO;

  LOAD * FROM inexistent.txt(txt, codepage is 1252, no labels, delimiter is ',',msq);

END IF;