SQL executor - continue translation without results?
i’m not even sure how to title this - i have a workspace that looks like this:
in general, the process is looking at input user parameters from an upstream workspace via FME server automation, the first step is the SQL executor to see if the current date is a holiday, if so, then that dates gets flagged as “Holiday Exception - YES” otherwise i want it to continue to process to the attribute creater that appends some numbers and makes some features from user parameters there is then a series of filters to route the email different places depending on time,day and holiday status, then ultimately they end up at one of the FME automation writers.
the SQL exe looks at a table of holiday dates and then uses a tester to see if the current date matches a holiday:
the problem i’m having is that if i use a separate SQL reader to check for the holiday, then when holiday = YES i get 2 features that are processed through the filtering when there should only be one. I want to append the attribute “Holiday_Exception” to the single incoming email as yes or no, not have 2 features when Holiday_exception = YES. i am unsure how to accomplish that?
Page 1 / 1
Hi @parke372 the problem i’m having is that if i use a separate SQL reader to check for the holiday, then when holiday = YES i get 2 features that are processed through the filtering when there should only be one. I want to append the attribute “Holiday_Exception” to the single incoming email as yes or no, not have 2 features when Holiday_exception = YES.
Can you use a join?
Can you use a join?
Crutledge,
thanks for the response! i was just looking at that, actually. i think that might the next step, however, i’m still a little stuck.
so - i have 48 dates in the table, with the SQL executor, i get the 48 results:
i’m then taking an attribute manager to get today’s date, and compare that to the holiday exception date list:
so if it IS a holiday, i get 47 “No”s and 1 “Yes” i can then send that to a feature joiner that looks at the date from the holiday exception feature on the left, with the date in the user parameters on the right:
i can use a tester for Holiday_Exception = YES, and then the joiner gives me what i’m hoping for:
however, if it is NOT a holiday exception day, i have no “YES” results, and the processing stops. i need it to process either way.
So i was thinking about having the failed tester results go to a sampler and sample the top 1 lines, so if it is NOT a holiday, it’ll still feed to the joiner and (ideally) join - however, since there are 47 NOs even with a YES, i then get 2 lines from the joiner, since 1 is YES and the top 1 sample is NO.
so still unsure how to proceed with an either “it is YES and process” with just 1 result or “it is NO and process” with just 1 result.
Thoughts?
I guess to simplify the question - i need to combine the result and initiator of the SQL executor into a single feature, regardless of if there is a value in results or not.
i think i got it. the problem with not having a result if today's date doesn’t match just means i need to always have a match - i changed my SQL query to provide today’s date as a 3rd column in the query, so, now there is always that result. I can then use the merged feature output with attributemanager to have a conditional value that sets holiday exception to Yes or No based on Date_HT being empty or not.
SELECT
h.Date_HT,
h."Holiday Name",
t.Today
FROM
(SELECT strftime('%m-%d-%Y', 'now', 'localtime') AS Today) AS t
LEFT JOIN
HolidayTable h
ON
h.Date_HT = t.Today;
this is how i ended up:
the initiator has the upstream user parameters including today’s date, and the SQL “Today” will return today’s date in the same format, so, i merge based on those date(s) and it works nicely!
