trigger sqlpage.run_sql() with conditions

[ttoorree]

Hi,

I want to run this:

SELECT 'dynamic' AS component,
       sqlpage.run_sql('order_receipt.sql',
                       JSON_OBJECT('id_participant', $id_participant,
                                   'order_id', $order_id)) AS properties
WHERE $should_send_receipt = 1;

But the run_sql() is running even when the $should_send_receipt variable is <> 1

Do you have a suggestion on this?

[lovasoa]

Hello and welcome to sqlpage !

The only way to create branching logic in sqlpage is by using the redirect component. In general, SQL excells at declarative statements and complex data extraction and transformation, but is not a great language to express imperative logic.

If your logic is very simple, you can do

select 'redirect' as component, 'send_receipt.sql?id=' || $id as link where $should_send_receipt;

-- the following is executed only when a receipt should not be sent
select 'redirect' as component, 'terminate_order.sql?id=' || $id as link;

As soon as your business logic becomes more complex, I would run a custom script with sqlpage.exec:

select sqlpage.exec('handle_order.py', JSON_OBJECT('order_id', $order_id));

[ttoorree]

Thank you for the reply.
I wanted to continue to run the original file from the point where the sqlpage.run_sql() was invoked.

i used this method instead

-- 1) Set flag (1/0)
SET should_send_receipt_flag =
  CASE
    WHEN $curr_status = 3
     AND $net_paid_amount > 0  
     AND $prev_status <> 3
    THEN 1 ELSE 0
  END;

-- 2) Which file to run
SET receipt_script =
  CASE WHEN $should_send_receipt_flag = 1
       THEN 'order_receipt.sql'
       ELSE 'noop.sql'
  END;

-- 3) Run file
SELECT 'dynamic' AS component,
       sqlpage.run_sql($receipt_script,
                       JSON_OBJECT('id_participant', $id_participant,
                                   'order_id', $order_id)) AS properties;

The noop.sql file returns nothing.