Force script to fail based on condition

[jduarte12]

Hello,

I have some dashboards and reports that are refreshed periodically by gathering data from aggregation tables in a PostgreSQL database. If the PostgreSQL aggregation jobs fail for some reason, the content in the qvws will be incomplete and, thus, I don't want the dashboards and reports to be reloaded.

The way I used to solve this issue was getting information from the database that allows me to know if the jobs ran ok or not.

So I created a simple script:

check_tmp:

select case when ok='TRUE' THEN 1 ELSE 0 end as case from aggregation_job_table;

check:

LOAD

if (case <>1,'notok','ok') as case_2

resident check_tmp;

store check into check.qvd;

Then I would only need a condition that forces the script to fail:

IF case_2 ='ok' then

else

//go to error;

end if

I have searched in the community, and it is not quite clear how can I do that: if I write something with a synthax error, the script will fail always, even when I don't want it to. On the other hand, using exit script will not produce an error, so I can't rely on that to get a failed task that I can use as a condition to disable sequential reload tasks in the QMC.

I guess the solution may be very simple, but I am running out of ideas. Can you please suggest something that will get me an error only if case_2 <>'ok'?

Thanks in advance,

João Duarte

[Gysbert_Wassenaar]

If you want to make the script fail the do something like loading data from a non-existent table:

LOAD * RESIDENT NoSuchTable;

[jduarte12]

Thank you, Gysbert.

However, the script runs normally and raises no error by using this load in the error condition.

[Gysbert_Wassenaar]

That's not possible unless you disabled error handling by setting ErrorMode to 0. Or if your script never encounters the condition where you want the script to fail on.

[jduarte12]

I have not disabled error handling.

So the script doesn't encounter the condition, but that's a bit odd: since I know case_2 is 'ok', I am testing the script with a twist in order to test forced failure:

IF case_2 <>'ok' then;

else

LOAD * RESIDENT NoSuchTable;

end if

Since case_2='ok', it should try to perform the load and get an error...

[Gysbert_Wassenaar · Accepted solution]

An if statement like you're using has no knowledge of which tables exist and which fields they contain and which record it should get the value from. So the if statement can't resolve case_2 to a value it can use.

Try this:

check_tmp:

select case when ok='TRUE' THEN 1 ELSE 0 end as case from aggregation_job_table;

check:

LOAD

if (case <>1,'notok','ok') as case_2

resident check_tmp;

store check into check.qvd;

LET vCase_2 = peek('case_2',0,'check');

If '$(vCase_2)' <> 'ok' Then

     LOAD * RESIDENT NoSuchTable;    

End if

[jduarte12]

Thanks, Gysbert.

I was not aware of that. It was very helpful. Now it works fine.