SQL Query to return "something" IF the result is zero rows

[korns]

I am working on a workflow that uses WFA's "Incremental naming" feature for volume names. Even though the volume name the workflow is about to create will be automatically generated, I'd like the UserInput form to behave in certain ways based on what that new volume-name WILL be (when the workflow executes). One approach I found is that I could have a hidden userInput field (in an un-expanded UserInput group). I call this field $displayHighestVolume (example). I make it a type QUERY and have it search the storage.volumes table and return the highest volume-name is use (probably similar to how "WFA Incremental Naming" does it.) So I can tell it (example) to search for volumes with name = "volume%" and it returns "volume78". I can then presume the next volume name "WFA Incremental Naming" picks will be "volume79".

----- UserInput Field: $displayHighestVolume -> Type QUERY: SQL ----

SELECT

    storage.volume.name

FROM

    storage.volume

WHERE

     storage.volume.name LIKE "volume%"

ORDER BY

    storage.volume.name DESC LIMIT 1

----

So far this idea works great and it lets me do what I need to do in tests with my $displayHighestVolume field in the way that I need (ie; determine if it is even or odd is one thing). The trick is when there is no volume at all yet with the name prefix "volume". So, just like WFA Incremental Naming where you specify a value to use 'if nothing found' ... I need a similar way to return something, like "volume00" in the case where there is no volume yet. A quick google search point me to SQL functions IFNULL() and COALESCE() but I've been unable to crack the nut.

This link (http://stackoverflow.com/questions/12449899/returning-a-value-even-if-no-result)  gave me the impression I could do this ... basically wrapper the entire SQL sequence in an IFNULL() like:

---

IFNULL (

(SELECT

    storage.volume.name

FROM

    storage.volume

WHERE

    storage.volume.name LIKE "volume%"

ORDER BY

    storage.volume.name DESC LIMIT 1 ),

"volume00" );

---

But as I type IFNULL I notice that is doesn't highlight in blue like other recognized SQL verbs/functions (SELECT, etc). That make be assume it is not supported at all by WFA.

Any more seasoned SQL experts out there who might have an idea?

[korns]

I should add that I also tried COALESCE but I'm not sure it is appropriate and it didn't work as I needed ... and not sure I did it right (I think coalesce is for for "null" values, not a completely null set of rows??)

---

SELECT

    COALESCE(storage.volume.name,"volume00")

FROM

    storage.volume

WHERE

     storage.volume.name LIKE "volume%"

ORDER BY

    storage.volume.name DESC LIMIT 1

----

[ktim]

I think that you could do a UNION and have a second select statement that has the default return value, then do the sort and return the highest result

So something like:

(SELECT

    storage.volume.name AS "name"

FROM

    storage.volume

WHERE

     storage.volume.name LIKE "volume%"

)

UNION

(    

     SELECT "volume00"

)

ORDER by name DESC LIMIT 1

-Tim

Message was edited by: Tim Kleingeld-MacKenzie - added LIMIT 1 to ORDER BY clause

[korns]

Hey Tim, Thanks. That works perfect 'conceptually'. It also works perfect in the SQL [Test] button window provided to test SQL query code. However, when I click [OK] twice and then [Save] to save the workflow the clock cursor spins a few seconds (always a bad sign 🙂 then pops a window saying:

    java.lang.RuntimeException: An unexpected error has occurred while attempting to format SQL. It may be that the SQL provided is not well formed.

To make sure of what I was seeing I simplified my SQL to the exact cut/pasted example from above. With the UNION in there I get the 'not well formed' error when saving. Without the UNION the workflow saves fine. I also created a new workflow with a single step: 'Create Volume' cmd and the above SQL QUERY. It also [Test]'s Okay but gets the 'not well formed' error when I attempt to save the workflow. take out the UNION and it saves fine. I'm getting impression it 'should' work but doesn't'.

Do others see similar behavior?

I'm doing this in WFA 2.2RC1 and don't have ready access to test 2.1 or other versions.

[ktim]

Hmmm. Sounds like a bug in the WFA SQL parser. It might be caused by the parentheses

Here's an alternative approach that might get around that:

SELECT "volume00" AS "name"

FROM dual

WHERE NOT EXISTS (

   SELECT name from storage.volume where name LIKE "volume%"

)

UNION

SELECT storage.volume.name AS "name"

FROM storage.volume

WHERE storage.volume.name LIKE "volume%"

ORDER BY name DESC LIMIT 1

[korns]

Quick check says that compiles, works in [Test] mode, and a test workflow saves OKay. Good sign!! When I get more time I'll fully test it out in my real workflow