Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

Dynamic Input Zero Row Error

I am using a Dynamic Input within a Batch Macro to allow the user to read (dump) data from multiple Oracle tables with varying schemas.  If a table read has 0 rows output, then an error message like the following is displayed at the end of the job execution:

 

capture.jpg

 

Because it is always possible to return 0 rows from the read, I'd like to mute the error message.  If this message is present, I don't want to STOP the macro/application.  I do however want to stop the application if an ERROR Message that I care about is encountered (e.g. Output file is not defined).

 

Cheers,

 

Mark

 

5 Comments
Claje
14 - Magnetar

Out of curiosity, if you try running this with a column specified for the select statement on the table that errored out, eg: DM_INTL.V_FD_ROSTER.TERRITORY_VOD__C (code below), does it return this error still?

 

Select DM_INTL.V_FD_ROSTER.TERRITORY_VOD__C FROM DM_INTL.V_FD_ROSTER
Where RowNum <= 10
Order By DM_INTL.V_FD_ROSTER.TERRITORY_VOD__C


If the above code works, and If you include the Select * in this statement as well, what happens?

 

Select DM_INTL.V_FD_ROSTER.TERRITORY_VOD__C, DM_INTL.V_FD_ROSTER.* FROM DM_INTL.V_FD_ROSTER
Where RowNum <= 10
Order By DM_INTL.V_FD_ROSTER.TERRITORY_VOD__C
SeanAdams
17 - Castor
17 - Castor

Fully agree with your thought here @MarqueeCrew - I posted the same concept a few weeks ago here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Defect-Short-circuit-evaluation-needed...

 

In essence the change required is very minor - just short-circuit the evaluation of any down-stream tools and formulae if there are no rows coming from a dynamic tool.

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

Hey @SeanAdams,

 

i have ave another idea. I should ask you about certifying macros. Give me a call bud!

 

cheers,

 

Mark

fharper
12 - Quasar

I use dynamic inputs both in regular and macro workflows and do not have an error generated by zero results from a query.  

 

I think the issue is not that you have a zero result set from the query but rather it is that because you have nothing flowing into the dynamic query where you probably do string replacement.  The text string to be replaced is not being changed dynamically since it has nothing to operate on and so the actual query fails on syntax which shows the message

-->  Error opening "----sql---" No Columns Returned.

 

I solve this upstream by evaluating if there is no data and if so I force a default value I am sure won't return rows but will allow the string substitution to work.  you can do this with dummy record or a few other ways.

 

I have uploaded pics showing a flow with zero result on a working query and the same flow where I remove all data from the input stream, using a sample tool, and you see the failure.  

example, dynamic input.PNGFailFailsuccesssuccess

 

here is the failed query message...if you look near the bottom you see the text string that would normally be replaced by a list of CIMs wrapped in "'" and separated by comma wasn't since there was no input to the tool...thus the query is not correct from a syntax view.

 

Error: Dynamic Input (100): Error opening "--set current query acceleration eligible;;

WITH CASE1 AS(
SELECT

GH4.LIST_BILL_CASE_NUM as GH4_LIST_BILL_CASE_NUM
,GH4.LIST_BILL_CIM as GH4_LIST_BILL_CIM
,CM4.PROD_LVL_OWNING_CARRIER as CARRIER
,GH5.LIST_BILL_CASE_NUM as GH5_LIST_BILL_CASE_NUM
,GH5.LIST_BILL_CIM as GH5_LIST_BILL_CIM
,LEFT(MAX(CHAR(BG.BILL_PERIOD) || CHAR(BG.RUN_NUMBER)) OVER (PARTITION BY GH5.LIST_BILL_CASE_NUM),10) AS LAST_BILLPERIOD

FROM DB2PROD.GRP_LISTBILL_HIER GH

JOIN DB2PROD.CASE_MASTER CM4
ON CM4.CASENAME#CIM = GH4.LIST_BILL_CIM

JOIN DB2PROD.GRP_LISTBILL_HIER GH4
ON GH4.HIGHEST_LVL_CIM = GH.HIGHEST_LVL_CIM
AND GH4.CASE_USE_IND IN ('INDIV-LB')

JOIN DB2PROD.GRP_LISTBILL_HIER GH5
ON GH5.HIGHEST_LVL_CIM = GH.HIGHEST_LVL_CIM
AND GH5.CASE_USE_IND IN ('DTLCASE')

LEFT JOIN DB2PROD.BLRGCASE BG 
ON BG.CASE_NUMBER = GH5.LIST_BILL_CASE_NUM

WHERE GH.LIST_BILL_CIM IN (CIMListSelection) 
-- OR GH.LIST_BILL_CASE_NUM IN (CaseListSelection)
) 
SELECT 
GH4_LIST_BILL_CASE_NUM
,GH4_LIST_BILL_CIM
,CARRIER
,GH5_LIST_BILL_CASE_NUM
,GH5_LIST_BILL_CIM
,LAST_BILLPERIOD

FROM CASE1

GROUP BY 
GH4_LIST_BILL_CASE_NUM
,GH4_LIST_BILL_CIM
,CARRIER
,GH5_LIST_BILL_CASE_NUM
,GH5_LIST_BILL_CIM
,LAST_BILLPERIOD": No Columns Returned.

AlteryxCommunityTeam
Alteryx Community Team
Alteryx Community Team
Status changed to: Accepting Votes