• 0 Vote(s) - 0 Average
  • 5
  • 4
  • 3
  • 2
  • 1
Thread Modes

Conditional Step SQL Issues
#1
I am looking to get some help in fixing an SQL query that is breaking a workflow conditional step. In essence, I am trying to test and implement a new workflow change for service requests that will bypass an approval step based on whether the requestor (not the recipient) is a VIP/VVIP. This will allow us to help expedite requests that are received if a higher-up submits the request on behalf of a subordinate. Here is the SQL I am attempting to run:

Code:
SELECT VIP_LEVEL_EN FROM AM_VIP_LEVEL WHERE VIP_LEVEL_ID = #[WF_TAGS.REQUESTOR_VIP_LEVEL]#

While this SQL checks out in MC_Monitoring (substituting the tag for the VIP level I am expecting), it continually fails when testing the workflow in service manager. I will receive a message stating "an error has occurred", and while it does generate the ticket, it is stuck on the conditional step. This indicates to me that the error is with the SQL and it's unable to proceed because it's not getting a valid returned value. 

I've even tried to create an Internal Update Step that pulls the requestor's VIP level and stores it in a workflow variable, but this too fails with the following SQL:

Code:
select AM_EMPLOYEE.VIP_LEVEL_ID from AM_EMPLOYEE where AM_EMPLOYEE.LAST_NAME =  #[CUSTOM_TAGS.Requesting Person]#

All of this being said, where am I going wrong? How should I correctly implement this kind of a conditional step? It would seem that the tags available in the workflow step editor don't work with SQL queries, or at least don't directly translate over to them. 

Any help is appreciated!

Thanks!
Tony Bozeglav
Systems Administrator
Hyatt Legal Plans, a MetLife Company


#2
(04-26-2019, 07:23 AM)tbozeglav Wrote: I am looking to get some help in fixing an SQL query that is breaking a workflow conditional step. In essence, I am trying to test and implement a new workflow change for service requests that will bypass an approval step based on whether the requestor (not the recipient) is a VIP/VVIP. This will allow us to help expedite requests that are received if a higher-up submits the request on behalf of a subordinate. Here is the SQL I am attempting to run:

Code:
SELECT VIP_LEVEL_EN FROM AM_VIP_LEVEL WHERE VIP_LEVEL_ID = #[WF_TAGS.REQUESTOR_VIP_LEVEL]#

While this SQL checks out in MC_Monitoring (substituting the tag for the VIP level I am expecting), it continually fails when testing the workflow in service manager. I will receive a message stating "an error has occurred", and while it does generate the ticket, it is stuck on the conditional step. This indicates to me that the error is with the SQL and it's unable to proceed because it's not getting a valid returned value. 

I've even tried to create an Internal Update Step that pulls the requestor's VIP level and stores it in a workflow variable, but this too fails with the following SQL:

Code:
select AM_EMPLOYEE.VIP_LEVEL_ID from AM_EMPLOYEE where AM_EMPLOYEE.LAST_NAME =  #[CUSTOM_TAGS.Requesting Person]#

All of this being said, where am I going wrong? How should I correctly implement this kind of a conditional step? It would seem that the tags available in the workflow step editor don't work with SQL queries, or at least don't directly translate over to them. 

Any help is appreciated!

Thanks!

The Tags return all values as strings so you'll need to include single quotes around the tags otherwise the SQL will error. This should address both statement issues, however, you may need to do an explicit conversion in the first statement if it doesn't work. This is because the field is an integer and the tag returns a string.
cmiller, proud to be a member of EV CONNECT FORUM since Oct 2015.

#3
(04-26-2019, 08:02 AM)cmiller Wrote: The Tags return all values as strings so you'll need to include single quotes around the tags otherwise the SQL will error. This should address both statement issues, however, you may need to do an explicit conversion in the first statement if it doesn't work. This is because the field is an integer and the tag returns a string.

I certainly feel silly for not realizing the single quotes. I've added them and changed the SQL match to look at the right columns. So far, so good... but a lot more testing to go. 

In regards to the tags available in the SQL editor - are there any limitations or known issues to make note of? As well, is there any documentation that state what type of result the various tags return? I've reviewed the tag list for what each tag returns, but I have not yet seen much in the way of information telling me the type of data I should expect to receive.
Tony Bozeglav
Systems Administrator
Hyatt Legal Plans, a MetLife Company


#4
(04-26-2019, 08:29 AM)tbozeglav Wrote:
(04-26-2019, 08:02 AM)cmiller Wrote: The Tags return all values as strings so you'll need to include single quotes around the tags otherwise the SQL will error. This should address both statement issues, however, you may need to do an explicit conversion in the first statement if it doesn't work. This is because the field is an integer and the tag returns a string.

I certainly feel silly for not realizing the single quotes. I've added them and changed the SQL match to look at the right columns. So far, so good... but a lot more testing to go. 

In regards to the tags available in the SQL editor - are there any limitations or known issues to make note of? As well, is there any documentation that state what type of result the various tags return? I've reviewed the tag list for what each tag returns, but I have not yet seen much in the way of information telling me the type of data I should expect to receive.


The only issue I'm aware of the QUESTION_RESULTS_HTML tag that returns the Q&A. Up until 305.2(I think?), if you tried to use this tag in an UPDATE statement and one of the questions or answers contained an apostrophe, the UPDATE would fail. Other than that, nothing else I can think of. The only tag documentation I know of can be found here.
cmiller, proud to be a member of EV CONNECT FORUM since Oct 2015.






Users browsing this thread: 1 Guest(s)