Dynamic Input Zero Row Error

[MarqueeCrew]

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:

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

[Claje]

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]

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]

Hey @SeanAdams,

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

cheers,

Mark

[fharper]

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.  

Failsuccess

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.