I am creating hold tables with dates based on filters and then join the hold tables to the main table. Then I have requirements based on dates from 4 different hold tables to enter a value of Active, Inactive, Historical, or whatever else. Those names dont really matter for what Im asking, but the issue is comparing dates to determine which name goes in that field. In order to compare dates, there needs to at least be a date there. When I left outer join the tables back to main table, there are a lot of fields with blank dates. In SQL, those would be NULL. When doing date comparison in SQL I can use the ISNULL(date_field, '1900-01-01') to populate 1-1-1900 in a null value, which gives me a date to compare to. I know there are no loans in our system dated back to 1-1-1900 so it is a safe date to compare as if it didnt exist when figuring out the max date of the 4 hold tables for each loan. What is the function in WebFocus code to emulate the ISNULL function in MSSQL?

This message has been edited. Last edited by: <Kathryn Henning>,

Hi Mikey,

I have no knowledge of a function that does exactly the same as ISNULL does in SQL. You can, however, create a virtual field (DEFINE or COMPUTE) that does what you need:

SQLISNULL/YYMD= IF YourDateField IS MISSING THEN '1900-01-01' ELSE YourDateField;

And you replace YourDateField with SQLISNULL in your report.

Hope it helps.

I did something similar to what you wrote, but it does not work when coming from a hold talbe, I just get a blank.

INACT_DTTM/HYYMDS = IF J2.INACT.LOAN_INACTIVE_DTTM EQ MISSING THEN '1900010100000000' ELSE J2.INACT.LOAN_INACTIVE_DTTM;

Try this instead:

INACT_DTTM/HYYMDS = IF J2.INACT.LOAN_INACTIVE_DTTM EQ '' THEN '1900010100000000' ELSE J2.INACT.LOAN_INACTIVE_DTTM;

Have you thought about including SET HOLDMISS = ON in your profile or fex?

T

diogopc I tried the double ticks as well with no success.

Tona A I have tried SET HOLDMISS = ON based on examples I read and have not got that to work either.

If I use my formula from a real table that i left join to, I can get it to work. It is not working when I left outer join to a hold table for the missing rows.

quote:

If I use my formula from a real table that i left join to, I can get it to work. It is not working when I left outer join to a hold table for the missing rows.

Hi Mikey,

So a SQL table left joined to a SQL table and the null test works ok, but a SQL table to a HOLD file does not? Is that correct?

So Psuedo code would be something like (verb being SUM or PRINT etc.) -

TABLE sqltable
verb datefield1 BY keyfield
ON TABLE HOLD AS hldfile1 FORMAT FOCUS INDEX keyfield
END

TABLE sqltable
verb datefield2 BY keyfield
ON TABLE HOLD AS hldfile2 FORMAT FOCUS INDEX keyfield
END

JOIN CLEAR *
JOIN LEFT_OUTER keyfield IN sqltable TAG T1 TO keyfield IN hldfile1 TAG T2 AS J1
JOIN LEFT_OUTER keyfield IN sqltable        TO keyfield IN hldfile2 TAG T3 AS J2
JOIN ....

TABLE FILE sqltable
verb data fields
     COMPUTE status/A20 = IF T2.datefield1 IS-NOT MISSING THEN 'something'
                     ELSE IF T3.datefield2 IS-NOT MISSING THEN 'something else' etc.
                     ELSE 'default value';
.....

Hopefully you understand the "flow" and it maybe allows you to correlate the idea to your code.

In my ramblings that follow, I am not talking MYSQL specific as I am not familiar with that adapter (or are you using the ODBC adapter?). It is still mostly relevant though and may help you move forward.

The first thing that I would suggest is that joining SQL to non SQL (e.g. HOLD files) can be inefficient in certain coding. So I would not advocate that approach. Rather, get all your data in HOLD files before joining (open the flood gates for those who disagree ).

Secondly, unless you really need the DATETIME format dates in your output, use date formats as they can be "easier" to use in your code. I am guessing that your synonyms have the datetime columns with USAGE= HYYMDS, ACTUAL=HYYMDS or the equivalent. There is a trick known to many an old (and new ) coder to not have to DEFINE an alternate format to a particular datetime column such that you can reference a date equivalent in your code. At many Customer sites I will add additional date columns to SQL data sources to allow both datetime and date formats. All you have to remember with SQL data sources is that the ALIAS MUST be the data source column name, whereas the FIELDNAME can be anything within reason (for this see "Defining data" in a manual for your release).
So, you will often see this when I leave synonyms for Customers -

-* ATA 2014/05/14 Alternate date column to allow selection via YYMD etc.
FIELDNAME=datefield, ALIAS=sql_datefield_name, USAGE=HYYMDS, ACTUAL=HYYMDS, MISSING=ON, $
FIELDNAME=datefield_DMYY, ALIAS=sql_datefield_name, USAGE=DMYY, ACTUAL=DATE, MISSING=ON, $

The MISSING is important because it will reflect the sql_datefield_name columns definition as NULLABLE. If you do not have that MISSING=ON in your synonym then it is likely to be NOT NULLABLE.

If you do have the MISSING=ON against those dates columns in which you are interested, then adding SET HOLDMISS = ON as the first line of your code should have helped. If you do not then that could explain your problems.

I would also suggest that you revisit the code that you posted earlier -

INACT_DTTM/HYYMDS = IF J2.INACT.LOAN_INACTIVE_DTTM EQ MISSING THEN '1900010100000000' ELSE J2.INACT.LOAN_INACTIVE_DTTM;

as you are not comparing the value correctly (imho). You should convert the alpha string into the equivalent HYYMDS (using HINPUT I think). This is one reason that I would suggest using a date format instead of datetime

Anyway, it's been a long day, it's now getting dark here so I will let others continue as necessary - plus the grey matter is not performing as well at this time of night

Good luck!

T

When I said real table, I shouldve said an actual synonym and not a hold table. i was doing all of this in webfocus code.

thanks for the replies, but I have decided to go a different route for now.