Can you use Global Variables in a SQL query node?

Yes and here is an example: My processflow variable is s_FName as String. Enclose this variable with single quote outside the <!-- and --> <.! and >sign


Another is type integer. Example i_Employee

where EMPLOYEE= <.!i_Employee>

remove the period in the < sign. I only added it to show here.

Jude

Thanks Jude.

I appreciate your response. However, I have to admit, I'm still a little confused. The variable I am referring to is the global one within process flow defining the product line . In my case, the product line is the same as the database table name I am hitting in my query. So, to make the flow work in either production or test, I wnated to use the global variable instead of hard coding the database name in the from clause of my sql statement.

For example, what I have now is: FROM dev.dbo.REVIEW
where, "dev" is both the database name I am using and the name of my Lawson product line.

What I thought I could use, but doesn't work was: FROM .dbo.REVIEW

Are you able to provide any further direction/assistance?

The global variable that defines the product line is appProdline.
You can use appProdline in a SQL query using the construct JudeBac said earlier:

Example:
select distinct EMPLOYEE
from .bnben

That was: less than exclaimation point appProdLine greater than dot table

See if this works, note remove the period between less than sign and exclamation point

FROM dev.dbo.REVIEW

FROM <.!appProdline>.dbo.REVIEW

This is exactly what I am trying, , but when I try to execute the statement and the pop-up comes up asking you to provide values for variables what do I put in there? (Or, is this only coming up becasue I am executing it manually?)

That doesn't happen to us. As soon as the start node of the flow executes, it shows the global variable values and ours is always the default product line (even though we have multiple product lines). Do you have your default product line defined in the pf config? Do you have more than one product line? If so, how are you telling your flow which product line to run it?
(Pop-up? Maybe you should explain more about what you are doing and how)

I hope you don't mind my asking, but I do have another question with regards to the SQL query node usage. In a typical SQL query hitting multiple tables, we use aliases. However, another thing I've noticed when using it in p-flow is that it is also throwing ewrrors wherever I've used an alias. The error is as follows, failing on the alias "a." as shown here:

ErrorLog in Process : MVN_Rvws_SQL_V02 Workunit : 762, ActivityType=ASSGN, ActivityName=Assign1150, ActivityId=ASSGN3 Date:10/12/2012 Time:09:25:57.830
Exception while evaluating expression: RVWSCHDT_MM = Review_a.SCHED_DATE.substring(0,2);
ReferenceError: "Review_a" is not defined. (threadScript#1)

Do you know how I can address this issue too? Thanks for any further insight you may provide!

Do you have your product line set-up in the designer preferences?

This is a known issue. I reported it to Lawson over a year ago, but they have declined to correct it. I have found that if I refer to any SQL returns in a certain way, pf will recognize them.
For example, the variable list might show:
SQL_TO_CHAR(emp.term_date,'MM/DD/YYYY') as term_date
But it works in the flow if I hard-type the variable as:
SQL_TERM_DATE

Thanks Barb. To answer the question from your initial post, yes I have the default product line defined in the config file for each of the two product lines. (Each resides in it's own environment on a separate server, so that part is pretty straight forward.)

As far as what I am doing & seeing now, here's the process I am referring to...
1. Select the SQL query node
2. Hit the 'Build' button as if I was going to modify the statement
3. Select the SQL statement tab
4. Modify the FROM clause in the statement to "FROM .dbo.REVIEW a" (same syntax is also used in a join to a second table)
5. Hit the 'Execute Statement' button

...This is where the Pop-Up comes up (basically a two column table), asking me to provide values in the value column, for the two variables which are both listed in the variable column...

6a. If I do nothing and just hit ok to continue execution of the statement, it returns the following error:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '<'.
6b. If I provide the value ("dev"), and hit ok to continue execution of the statement, it returns the query results. However, this seems to me to defeat the purpose of using a variable.
6c. Alternatively, if I change the syntax in the from clause to actually hard code the product line in, it also successfully executes the query. But, again, this defeats the purpose of utilization of the global variable.

Barb,

With regard to you second post, what do you mean by hard coding the variable in the flow? Because I'm hitting two tables and therefore have aliases assigned to each column in my select statement, how would this work, through an assign node?

With all these issue I am encountering, I am not really seeing the benefit of having SQL nodes available to us in p-flow, since they don't seem to work with basic SQL syntax & methodology? Am I wrong, or am I just missing something here? Please help me to see the light if you can. Thank you.

Got it - you are in a SQL node build, and testing the build - not running the whole flow from a trigger or from within the designer.
It sounds like you are editing a statement that was previously built. I usually just overtype what I want (or place my cursor where I want a variable to be inserted and select the variable from the list), never going back into Build since I seldom want to start completely over.

In a new build, when you get to the final screen where you can test the statement, you'll see the statement in the upper frame with ???? in the places where entries need to be made. To test the build, you have to hardcode the variables, e.g. PROD in product line, 1000 in Company, 12345 in Employee, then test to see if the results are correct.
Once you copy then paste and exit the build screens, just put your cursor where the hard-coded things are (PROD, 1000, 12345) and click to enter the variable names appProdline, Companyname, Employee, etc.) and delete the hard-coded things.

If you are talking about this post:
For example, the variable list might show:
SQL_TO_CHAR(emp.term_date,'MM/DD/YYYY') as term_date
But it works in the flow if I hard-type the variable as:
SQL_TERM_DATE

All I meant was that you can't select the variable name from the list because of how Lawson represents it - I didn't mean hard CODE, I meant hard TYPE the variable into your assign or other statement (SQL_TERM_DATE).

We haven't found any issues with SQL nodes and have found them immensely valuable. Have you been to Process Flow training? I would definetly recommend it. All of what you are asking about is covered well in the class.

Got it. Thank you Barb! I didn't realize that I could still modify the statement right in the node properties box after having completed the "build" of the statement itself upon creation of the node. I apologize for my stupidity.

Are you able to provide any further insight into how to work around the use of aliases in the sql statement, which Lawson/Process Flow does not seem to recognize?

Thanks.

See the post just before your latest reply.
Instead of selecting Lawson's dumb-looking variable name from the list:
Example:
NODENAME_TO_CHAR(emp.term_date,'MM/DD/YYYY') as term_date
Type in a correct variable name yourself where you want to use it:
Example: NODENAME_TERM_DATE