Data360 Analyze

 View Only
  • 1.  Changing query

    Posted 05-29-2023 06:57

    Assume i have a SQL query in Oracle(dbnode).

    I have a table 'table_1' with a column named Status, it has 1 row that can have the values 'Done' or 'NotDone'.

    The values in the Status column are updated automatically in Oracle, depending on which time i look at it.

    What i would like is a query that checks table_1 lets say every 5 minutes, until the column Status has gotten the value 'Done', then i would like that as an output.

    If the Status column still has the value 'Notdone' i want to continue iterating until it has the value 'Done'

    In essence what i want to do is, when the query(status column) gets the value 'Done', i want to trigger an email node.

    If it still has the status 'Notdone' i want to continue running the query until the Status column gets the value 'Done'

    Should i do this with a query, do while conditional?

    Best regards

    Henrik B
    E.ON Sverige

  • 2.  RE: Changing query

    Posted 05-30-2023 01:59

    Hi Henrik,
    I have been able to use the snowflake version of an anonymous pl/sql block in the jdbc execute node. (needed as the exception handling is not helpful in Analyze). I would try if Oracle pl/sql works in the same way, (begin.. xzy... end). If so, there are many examples out there with running a query and using dbms_session.sleep to run it again after n seconds. Analyze should just patiently until the query returns, and then you can do what you want with the emailing there.


    Peter Sykes
    Vontobel Holding AG

  • 3.  RE: Changing query

    Posted 05-30-2023 02:48

    I think i found a solution for it, i define count:long
    0 in create data. 'count' < 5 in do while conditional.

    out1.count = in1.count + 1 in increment node.

    if in1.status =='DONE':
        out1.status = in1.status
        out2.status = in1.status

    in my transform node.

    And a meta check if i get an output >0 on out1 on my transform, that activates the mail node

    And a delay on the sleep timer.

    Henrik B
    E.ON Sverige