Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need a where clause that in the load I filter out a date but still retain the null data.
You want Jan 2017 and forward and you don't want to bring in nulls? May be this
DischargeDt as DateField
FROM "Pat"."dbo"."Patients"
Where COALESCE(DischargeDt, CONVERT(DATETIME, '2000-01-01')) >= CONVERT(DATETIME, '2017-01-01');
This will now bring everything 01/01/2017 and forward (just as you asked)
I only want to pull in DischargeDt of 01/01/2017 and forward.
and won't bring the nulls....
May be like this
LOAD *
FROM ...
Where If(Len(Trim(DateField)) = 0, MakeDate(2017, 1, 1), DateField) <= MakeDate(2017, 1, 1);
There are two things to consider
a) you want to combine different conditions in a WHERE clause
You can use logical operators to achieve this:
The OR operator would be useful in your case
b) you want to check for NULL
There are two common approaches to check for NULL, IsNull(FIELD) checks for NULL and returns true if FIELD is NULL, Len(Trim(FIELD)) returns zero for NULL or if the FIELD value is empty or blank.
In total
LOAD
DateField,
...
FROM ....
WHERE DateField <= '2017-01-01' OR Len(Trim(DateField));
Or can I just auto populate the nulls with a date of 2099-01-01?
Or can a us a measure to exclude all dates of 2016-12-31 and keep the nulls?
Seems like you performing this in the SQL SELECT Statement instead of load statement... would you be able to share the SELECT Statement you have?
I get an error
FROM "Pat"."dbo"."Patients"
Where If(Len(Trim(DischargeDt)) = 0, MakeDate(2017, 1, 1), DischargeDt) <= MakeDate(2017, 1, 1);
Or can I just auto populate the nulls with a date of 2099-01-01?
Try this
SQL SELECT *,
NVL(DischargeDt, TO_DATE('01JAN2099', 'DDMONYYYY')) as DateField
FROM "Pat"."dbo"."Patients"
Where NVL(DischargeDt, TO_DATE('01JAN2017', 'DDMONYYYY')) <= TO_DATE('01JAN2017', 'DDMONYYYY');
[Patients]:
LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';
// LIB CONNECT TO 'pharmoresql (pharmore_dewing)';
LOAD
[FacID] as PFacID,
[PatID] as PPatID,
[PatLName],
[PatFName],
[PatStatus],
[AdmDate],
// 01/01/0001 as Blankdate,
DischargeDt,
// isNull(DischargeDt, 00/00/0000),
[DefaultExempt340B];
SQL SELECT "FacID",
"PatID",
"PatLName",
"PatFName",
"PatStatus",
"AdmDate",
"DischargeDt",
"DefaultExempt340B"
FROM "Pat"."dbo"."Patients"
Where If(Len(Trim(DischargeDt)) = 0, MakeDate(2017, 1, 1), DischargeDt) <= MakeDate(2017, 1, 1);
yes it was in the select but this is what happens in load
// 01/01/0001 as Blankdate,
// DischargeDt,
If(Len(Trim(DischargeDt)) = 0, MakeDate(2017, 1, 1), DischargeDt) <= MakeDate(2017, 1, 1),