field with no data insert 0's

[Mike Hughes]

When the IVA_MEMBER_ID field has no data I would like this query to return : 000000000,
is this possible and if so could you show me how? Thanks

SELECT DISTINCT
NOLDBA_INT_MEMBER_DEMOGRAPHIC.IVA_MEMBER_ID,
NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEM_SSN,
NOLDBA_LOG_DISBURSEMENT_DETAIL.AMT_DISBURSE,
NOLDBA_LOG_DISBURSEMENT_DETAIL.CD_TYPE_DISBURSE,
NOLDBA_LOG_DISBURSEMENT_DETAIL.SEQ_ORDER,
NOLDBA_LOG_DISBURSEMENT_DETAIL.SEQ_OBLIGATION,
NOLDBA_LOG_DISBURSEMENT_DETAIL.ID_CASE INTO [A TBL]

FROM NOLDBA_LOG_DISBURSEMENT_DETAIL INNER JOIN
(NOLDBA_INT_CASE_MEMBER INNER JOIN
NOLDBA_INT_MEMBER_DEMOGRAPHIC ON
NOLDBA_INT_CASE_MEMBER.MEMBER_ID = NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID) ON NOLDBA_LOG_DISBURSEMENT_DETAIL.ID_CHECK_RECIPIENT = NOLDBA_INT_CASE_MEMBER.MEMBER_ID

WHERE (((NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEM_SSN)>"0") AND ((NOLDBA_INT_CASE_MEMBER.RELATION_CODE)="C") AND ((NOLDBA_LOG_DISBURSEMENT_DETAIL.CD_CHECK_RECIPIENT)="1") AND ((NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_DISBURSE) Between [START DATE] And [ENTER END DATE]))

ORDER BY NOLDBA_INT_MEMBER_DEMOGRAPHIC.IVA_MEMBER_ID;

 

[Laoujin]

You can use the IIF clause

Usage:
IIF(Condition, True, False)
IIF(FieldName IS NULL, "0", FieldName)

Or you can use a UNION clause to select the normal fields with a WHERE clause field IS NOT NULL UNION "000000000" with a where clause field IS NULL.

 

[Laoujin]

In your case this could be something like:

IIF(NOLDBA_INT_MEMBER_DEMOGRAPHIC.IVA_MEMBER_ID IS NULL, "000000000", NOLDBA_INT_MEMBER_DEMOGRAPHIC.IVA_MEMBER_ID)

Note that it is not possible to select only "00000000" when IVA_MEMBER_ID is empty and multiple fields when it does have a value but you can select "000000" and NULL values for the other fields in that case.

 

[Mike Hughes]

Got it! Thanks!