Focal Point
Passing Nulls to Sql Server

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/5887013516

January 11, 2012, 01:31 PM
ray_In_Vancouver
Passing Nulls to Sql Server
In My synonym I have

DEFINE MYDATE/MDYY MISSING ON=IF LENV( TRIMV('T', DLVY, 20, ' ', 1, 'A20V'), 'I8') NE 0 THEN
DATECVT(EDIT( TRIM('T', DLVY, 20, ' ', 1, 'A20'), '99$99$9999' ), 'A8MDYY', 'MDYY')
ELSE
'';

But when the flows run it inserts 1900-12-31 00:00:00 where ever a date was not given. I thought 'MISSING ON' passed Null to the targate sql


IWay SM 5.6, 6.01, 6.1, 6.9, 7.0
EDI, XML, XSD, WF 8.0, DM 77,8.0 asp.net, SQL Server
January 12, 2012, 06:09 AM
j.gross
Try
ELSE MISSING;
in place of
ELSE '';

'' represents the date value that WebFocus displays as blank (and 12/31/1900 is reserved for that purpose); MISSING, the null value, is something else.
January 12, 2012, 10:06 AM
Clif
Instead of ELSE '' use ELSE MISSING.


N/A
January 12, 2012, 04:10 PM
Dan Satchell
If ELSE MISSING doesn't solve the problem, you may need to SET DATEDISPLAY = OFF at the top of your program to prevent the base date from populating null dates.


WebFOCUS 7.7.05
January 13, 2012, 04:07 AM
Madasabear
Also include SET HOLDMISS = ON at the top of your code , this will ensure
that when you issue the ON TABLE HOLD AS something FORMAT SQLORA any missing/nulls
will not get populated with a default date.


Developer Studio Release : 7.6.11
January 13, 2012, 10:48 AM
ray_In_Vancouver
ELSE MISSING Got it.

thanks everyone


IWay SM 5.6, 6.01, 6.1, 6.9, 7.0
EDI, XML, XSD, WF 8.0, DM 77,8.0 asp.net, SQL Server