Display N/A for date column where the data is grouped in DB

[jsttuchm3]

Hi Experts,

I have a view created in DB, columns in the view are based on grouping and not individual values for columns.
The problem is when there are null vlaues, want them to be replaced by "N/A" value. The data type of the column is date.

Tried all validations of IsNull, DatePart, InStr, Replace, Used Length function to check if the column has any data or not and mask it with "N/A" value. Nothing worked out for me.

Is there any way to mask null values for date field with "N/A" value in CRXI

DB view is very simple like this

SELECT
pln.PLAN_ID
,COUNT(SHPM.SHPM_ID)"TOTAL_SHIPMENTS"
,MIN(SHPM.CRTD_DTT) "MIN_CREATED_DATE"
FROM
Shiping SHPM
,Planing PLN
WHERE
SHPM.PLAN_ID (+)= PLN.PLAN_ID
GROUP BY pln.PLAN_ID
ORDER BY pln.PLAN_ID

I want "N/A" value to be displayed when there is no date value.

Code tried out way

Local StringVar y;
y := cstr({PLAN_ID_SUMRY.CRTD_DATE});

If InStr(y,'/') <> 0 THEN
y
ELSE
If InStr(y," ") = 0 THEN
"N/A"
ELSE
If IsNull({PLAN_ID_SUMRY.CRTD_DATE}) THEN
"N/A"
ELSE
y;
--------------------------------

Local datetimeVar a := {PLAN_ID_SUMRY.CRTD_DATE};
Local datetimeVar b := currentdatetime;
If (DatePart("d",a)) - (DatePart("d",b)) < 0 THEN
"N/A"
ELSE
cstr(a,"MM/dd/yyyy HH:mm")
-----------------------------------

If (DatePart("d",a)) - (DatePart("d",b)) = 0 THEN
"N/A"
ELSE
If InStr(y," ") = 0 THEN
"N/A"
ELSE
cstr(a,"MM/dd/yyyy HH:mm")
Replace(y," ","N/A")

---------------------------------

Is it possible to replace date with "N/A" when there are null values.

Thanks,
RP

 

[lbass]

You need a formula like this:

if isnull({table.date}) then
"N/A" else
totext({table.date},"MM/dd/yyyy HH:mm")

You don't need a variable--in fact the problem with your earlier attempts is that you referenced the date field (setting it to a variable) before checking it for nulls. The null check must always be first.

-LB

 

[jsttuchm3]

It works fine.
Looks like the convertion of DB field using variable was not required.

Thanks LB.

-RP