Meaning of YEAR in script

Ignition
#1

I am trying to figure out what a few lines of this code means. It was written by someone else and I can’t make it out. It is used as a basic SQL query within a reporting data source. Here is the code:

SELECT 
Alloy,
OperationID,
Username,
FurnaceBTNum,
r_LagTime,
r_ReqTotNegTime,
r_ReqTotPosTime,
r_ReqProcessTime1,
r_ReqProcessTemp1,
r_ReqTempTol1,
r_ReqProcessTime2,
r_ReqProcessTemp2,
r_ReqTempTol2,
r_ReqProcessTime3,
r_ReqProcessTemp3,
r_ReqTempTol3,
e_RecipeDownloaded_ts,
e_RecipeDownloaded_temp,
IF(YEAR(e_PreheatBegin_ts)>2,e_PreheatBegin_ts,NULL) AS e_PreheatBegin_ts,
IF(e_PreheatBegin_temp,e_PreheatBegin_temp,NULL) AS e_PreheatBegin_temp,
IF(YEAR(e_PreheatComplete_ts)>2,e_PreheatComplete_ts,NULL) AS e_PreheatComplete_ts,
IF(e_PreheatComplete_temp,e_PreheatComplete_temp,NULL) AS e_PreheatComplete_temp,
IF(YEAR(e_DoorOpenedBeingLoaded_ts)>2,e_DoorOpenedBeingLoaded_ts,NULL) AS e_DoorOpenedBeingLoaded_ts,
IF(e_DoorOpenedBeingLoaded_temp,e_DoorOpenedBeingLoaded_temp,NULL) AS e_DoorOpenedBeingLoaded_temp,
IF(YEAR(e_DoorClosedLoaded_ts)>2,e_DoorClosedLoaded_ts,NULL) AS e_DoorClosedLoaded_ts,
IF(e_DoorClosedLoaded_temp,e_DoorClosedLoaded_temp,NULL) AS e_DoorClosedLoaded_temp,
IF(YEAR(e_FurnaceAtTempLoaded_ts)>2,e_FurnaceAtTempLoaded_ts,NULL) AS e_FurnaceAtTempLoaded_ts,
IF(e_FurnaceAtTempLoaded_temp,e_FurnaceAtTempLoaded_temp,NULL) AS e_FurnaceAtTempLoaded_temp,
IF(YEAR(e_LagTimeComplete_ts)>2,e_LagTimeComplete_ts,NULL) AS e_LagTimeComplete_ts,
IF(e_LagTimeComplete_temp,e_LagTimeComplete_temp,NULL) AS e_LagTimeComplete_temp,
IF(YEAR(e_ProcessTimeComplete_ts)>2,e_ProcessTimeComplete_ts,NULL) AS e_ProcessTimeComplete_ts,
IF(e_ProcessTimeComplete_temp,e_ProcessTimeComplete_temp,NULL) AS e_ProcessTimeComplete_temp,
IF(YEAR(e_DoorOpenedUnload_ts)>2,e_DoorOpenedUnload_ts,NULL) AS e_DoorOpenedUnload_ts,
IF(e_DoorOpenedUnload_temp,e_DoorOpenedUnload_temp,NULL) AS e_DoorOpenedUnload_temp,
IF(e_total_process_time,e_total_process_time,NULL) AS e_total_process_time
FROM rpt_furnace_cycles
WHERE id={FurnaceCycleID}

An example of the lines I am trying to understand are below:

IF(YEAR(e_DoorOpenedUnload_ts)>2,e_DoorOpenedUnload_ts,NULL) AS e_DoorOpenedUnload_ts,
IF(e_total_process_time,e_total_process_time,NULL) AS e_total_process_time

I am thinking the first line says look at the timestamp value from the DB. If the value of the year within the timestamp is greater than 2, then return the timestamp value, else return NULL. And return this value as e_DoorOpenedUnload_ts

The second line is similar except it has no value with which to compare to.

Can someone give me a quick idea of how this code works? Thanks.

#2

YEAR will pull the year from the date as YYYY. Not sure why the >2 as this will always be >2 for any date.

The second condition will be true when the total process time is not 0 (or negative? Not sure how negatives are interpreted tbh)

#3

Actually, it will evaluate as True unless the value is 0, False. Negative values are not zero, so they are True. Also, I verified the expression function does not accept NULL/null/None nor strings, lest the expression fault. So that value would only be parsed as a boolean or numeric value.

1 Like
#4

Gotcha. Well the timestamp and temperatures are all set to default of NULL until they are populated throughout the cycle so they could be NULL.

I am trying to run this script using an Oracle DB instead. Not much different but running it through as a Named Query for testing.

I am getting an error telling me it is missing a right parenthesis and I don’t see where I am missing one.

image
image1693×1136 179 KB

When I try and execute to test I get the following:

image

#5

Are you sure that Oracle supports that IF statement format within a SELECT query?

I suspect you’ll need to use a CASE, but I haven’t used Oracle before.
E.g.

CASE WHEN YEAR(E_PREHEATBEGIN_TS) > 2 THEN E_PREHEATBEGIN_TS ELSE NULL END AS E_PREHEATBEGIN_TS

https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

Although I’m still not sure what the purpose of that whole expression is to be honest. Comparing the year to >2 seems redundant, as it’ll either always be >2 for any date, or if the date is NULL, then it’ll return NULL? probably which means you could just return the field itself without any expressions

#6

It was the expression after all. Used your suggestion and modified it and it works!

Revised code:

CASE WHEN (EXTRACT(YEAR FROM E_PREHEATBEGIN_TS)) > 2 THEN E_PREHEATBEGIN_TS ELSE NULL END AS E_PREHEATBEGIN_TS,
CASE WHEN (E_PREHEATBEGIN_TEMP) > -1 THEN E_PREHEATBEGIN_TEMP ELSE NULL END AS E_PREHEATBEGIN_TEMP,