Skip to Content
author's profile photo Former Member
Former Member

Options for setting global variables in a data flow

Hello experts,

I am new to BODS, but have used a couple other ETL/ELT tools. Please help me answer below questions:

#1. What are my options (if any) to set the values of global variables inside a data flow? Can I set it in a query transform either directly (preferably) or via a custom function, or any other transform?

#2. Can I have a data flow without a target table? ( I am trying to use a data flow to process only one row from a source table - just to set a bunch of global variables. If the answer is No; what is the easiest dummy target - a flat file? I dont want to use a template table as it adds an temp table to the database; whereas, with a flat file I am assuming it need not pre-exist (and will not be generated in my dataflow anyway). My thinking correct? Any more choices here?


#3. Inside a script, using the sql() call, can I get more than one column into variable(s). For instance, below statement gets one column into a global variable. However, I want to get multiple columns into separate variables - in one pass. Possible or not?


$G_Current_SQL_Text = sql('Datastore_Name','SELECT Column_1 FROM my_table WHERE SQL_NO = [$G_Current_SQL_No]');

Background (If interested):

<<Please look at the attachment - "Proof_Of_Concept.jpg">>

I am trying to define a data validation framework using BODS. I have a bunch of validation SQLs in a metadata table and having BODS loop through it using a while loop. I am processing only one row (containing the sql statement) at a time. I am using a global variable ($G_Current_SQL_Text) in the SQL transform to execute the sql and write the result into a target table. However, as a precursor to this, in the beginning part inside the while loop, I am using script to define below global variables in multiple passes. I want to replace the script with a data flow; hence the questions posted above

$G_Total_No_Of_SQLs

$G_Current_SQL_Text

$G_Current_SQL_Datastore

$G_Current_SQL_No_Of_Columns, etc

Thanks in advance,

Kaushik.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Oct 02, 2014 at 07:17 AM

    1) You can call Custom Function to assign Global Variable, In the function assign a value. But this function will be called for every record.

    2) When you have only one record, use Script.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 02, 2014 at 07:54 AM

    #2. Every dataflow needs at least one target, table or source. Even if the flow doesn't procude any output, the file and template table are created.

    #3. The sql() function returns a single text string only. If you want multiple columns, you'll need multiple calls.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 02, 2014 at 01:30 PM

    Balakrishna & Dirk,

    Thanks for the quick responses. Both of your answers were very helpful. A few follow-up questions:

    1. In a custom function, can we dynamically assign the value for the variable? In other words, is it possible to

    a) Create just one custom function with two input parameters - one for the global variable name and other for the assigned value?; or

    b) Do I need to create a custom function for every global variable that I have to assign a value to?

    2. With flat file as the target;

    a) Does the file need to pre-exist in the specified directory; or

    b) Will an empty file be created when the data flow is executed; or

    c) No file will be created as no rows will be written to the file.

    Thanks

    Kaushik.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.