Skip to main content
Solved

SQL executor - continue translation without results?

  • September 4, 2025
  • 4 replies
  • 49 views
parke372
Contributor
Forum|alt.badge.img+10

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?

 

Best answer by parke372

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!

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

4 replies

crutledge
Influencer
Forum|alt.badge.img+43
  • crutledgeInfluencer
  • Influencer
  • 281 replies
  • September 4, 2025

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?

At your rest service ^B
parke372
Contributor
Forum|alt.badge.img+10
  • parke372Contributor
  • Author
  • Contributor
  • 18 replies
  • September 4, 2025

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?

parke372
Contributor
Forum|alt.badge.img+10
  • parke372Contributor
  • Author
  • Contributor
  • 18 replies
  • September 4, 2025

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.

parke372
Contributor
Forum|alt.badge.img+10
  • parke372Contributor
  • Author
  • Contributor
  • 18 replies
  • Best Answer
  • September 4, 2025

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!

Community Stats

32,099
Posts
121,889
Replies
39,743
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.