Qdabra qRules

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

SOMHRS — Thu, 02 Jan 2014 18:41:15 GMT

I'm getting results now. I looked at the SQL trace and that was valid fine so then I recreated my data connection just to be sure and voila. I can't believe how well this command works. What a find! Thank you! I appreciate your help.

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

Hilary Stoupa — Thu, 02 Jan 2014 16:00:43 GMT

So, that could mean that the data connection is returning no results.... Now the question is why. :)

So - I would probably make sure I knew exactly what the where clause is in this case. Do you know you have results for the selected Department? Are you able to run SQL trace to see the query being executed?

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

SOMHRS — Thu, 02 Jan 2014 15:36:44 GMT

I tried that and I don't get any results for the data connection. The success box is checked though and there aren't any error messages. That must mean my formula is wrong even though the syntax verifies?

This will work on a secondary data connection, correct? And I should be able to run it locally (in an unpublished form)?

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

ErnestoM — Thu, 02 Jan 2014 15:13:23 GMT

Hi-

You should try setting the value of Command to your formula above, then checking the data connection secondary data source for results.

I don't believe there's a need to separately query the data connection.

Hope this helps.

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

SOMHRS — Tue, 31 Dec 2013 15:20:19 GMT

I'm currently testing out a trial version of qrules and am running into the some of same issues described in this post. I've put my concat statement into the command line formula and it verifies correctly. My statement is:

concat("ModifySqlQuery /dsname=VW_DIM_INF_APPR_CURRENT /where=where DEPT = '", DEPT, "'")

My rule is based on a field change and when it does the first action sets the command field value to the above formula and the second action queries the data connection (which is a secondary data connection). There is no error message and the success box is checked. I had originally included only the command line action in the rule, but I could never get any values to be returned for the connection. Once I added the action in the rule to query the data connection I did get values, but they were not filtered per the WHERE statement. Nothing shows up in my result box either so I'm wondering how that relates to this particular command. The command seems to be executing, but my data connection results are not being filtered. Can anyone help?

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

Hilary Stoupa — Thu, 08 Dec 2011 22:22:50 GMT

Well, the command node is always cleared after the command runs, so you wouldn't see a value there. For this command, the error node should return any error that is returned to the data connection when it is executed, but it sounds like this issue doesn't actually return an error from the data connection.

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

Keadlard — Thu, 08 Dec 2011 22:12:24 GMT

Yes, it was blank. Current was view 1. I did a pause after the bad command, but it showed no info. I did get it figured out. But I would love to figure out why the command didn't appear and why the error node was blank.

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

Hilary Stoupa — Thu, 08 Dec 2011 22:08:28 GMT

Did you check the qRules Error node?

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

Keadlard — Thu, 08 Dec 2011 21:54:51 GMT

I always know if I post a note, than I will figure it out. I used another query that worked: concat("ModifySqlQuery /dsname=REVENUE /where=where Forecaster='", QueryChange, "'") not sure why the N'" doesn't work in the revenue query but does in the main, but thanks anyway. :)

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

Keadlard — Thu, 08 Dec 2011 21:40:59 GMT

Hi. I am at it again, and now I am trying to add another query. The original Query: concat("ModifySqlQuery /dsname=Main /where=where (Forecaster = N'", QueryChange, "')") works fine. But the following query brings no resulta and checking the command and success of qrules shows no blank after it runs. I have tried it several ways.

concat("ModifySqlQuery /dsname=REVENUE /where=where (Forecaster = N'", QueryChange, "')")

concat("ModifySqlQuery /dsname=PB_VW_Inprogress_Revenue /where=where (Forecaster = N'", QueryChange, "')")

Here is the SQL connection info. I renamed the connection to REVENUE to make it work. I also renamed it PB_VW_Inprogress_Revenue.

select "Part#" as "Part_","Forecaster","Division","Region","Company","Type","ForecastDate","M1","M2","M3","M4","M5","M6","M7","M8","M9","M10","M11","M12" from "dbo"."PB_VW_Inprogress_Revenue" as "PB_VW_Inprogress_Revenue"

I've tried it several ways and can't figure out why it's not working.

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

Hilary Stoupa — Wed, 21 Sep 2011 19:31:40 GMT

Your syntax looks incorrect there. Remember, we need to wrap our field values in single quotes. Also, there was no space before your "and".

/where=Where StateID = '" , Field1, "' and CityID = '", Field2, "'")

So, that would be:

StateID = SingleQuoteDoubleQuote, Field1, DoubleQuoteSingleQuote and CityID = SingleQuoteDoubleQuote, Field2, DoubleQuoteSingleQuoteDoubleQuote

we want our result to look like:

StateID = 'WA' and CityID = 'Sea'

yours would have resulted in:

StateID = 'WAand CityID = 'Sea'

You can always paste your concat into a calculated value to easily check what your syntax is going to look like....

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

Keadlard — Wed, 21 Sep 2011 18:55:25 GMT

Hi, can you tell me if this query can be used for multiple selects, for example dsname=Genstates /where=Where StateID = '" , Field1, "and CityID = '", Field2, "'")

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

Keadlard — Tue, 20 Sep 2011 21:54:04 GMT

LOL, I am also a bit OCD, which is why I have tried it all ways. But it looks like what I missed before was the quotes around the ' because when I do it like "'" it now works. I have several data connections, the main connectioned query worked perfectly, but the secondary one was where I was having issues. I'm am moving my secondary info to a main connection so I can use the page buttons I received from Patrick last year. Everything seems to be working now that I added the extra quote.

Thank you.

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

Hilary Stoupa — Tue, 20 Sep 2011 21:26:38 GMT

I'm a little OCD, so I just tried this. And my concat looks like:

concat("ModifySqlQuery /dsname=GenStates /where=where StateId = '", field1, "'")

As you can see, I'm not using parenthesis around my where clause, and I don't have double quotes around my column name. This worked in my environment.

However, I am wondering about one thing - your main data connection - your form is database bound? Why wouldn't you just use the built in query fields?

Re: Qrules ModifySQLQuery and ExecuteSQLQuery commands

Keadlard — Tue, 20 Sep 2011 21:06:25 GMT

ok, thank you.