How to assign a value to a blank(empty or null) field for a joined table?

[#1]

Hi community,

       I joined two tables from two different data sources - one Oracle and one SQL Server. The join was successful after getting help here. The current problem is that I have difficulty in assigning a value to a blank fields in the join table. I need use that field as a filter. I have tried ISNULL, ='' and Len=0, but no succcess. Here is the example:

Table 1				Table2
MRN	NAME	AdmitDate	DischargeDate		MRN	FlushotTIME
1	Name1	1/1/2013	1/10/2013		1	1/1/2013
2	Name2	1/2/2013	1/11/2013		2	1/2/2013
3	Name3	1/3/2013	1/12/2013		3	1/3/2013
4	Name4	1/4/2013	1/13/2013
5	Name5	1/5/2013	1/14/2013

After join the Flushot for MRN 4, 5 should be blank (empty). I would like to CREATE a new field blnFlushot in JOINED table as new field to indicate the status (1, 0). I tried many different ways, all failed. Please help. Thanks a million!!!

Longmatch

[maxgro · Accepted solution]

perhaps this?

Table1:

LOAD * INLINE [

MRN, NAME, AdmitDate, DischargeDate

1, Name1, 1/1/2013, 1/10/2013

2, Name2, 1/2/2013, 1/11/2013

3, Name3, 1/3/2013, 1/12/2013

4, Name4, 1/4/2013, 1/13/2013

5, Name5, 1/5/2013, 1/14/2013

];

Left Join (Table1)

LOAD * INLINE [

MRN, FlushotTIME

1, 1/1/2013

2, 1/2/2013

3, 1/3/2013

];

Left Join (Table1) load MRN, if(IsNull(FlushotTIME),1,0) as FlagNull Resident Table1;

Regards

[#3]

Your example is working the way I wanted and it is what I need. Thanks.

However I have not figured out how to implement the same logics in my project. I need detailed explaination. Here is my code which does  not work.

OLEDB SQL server connection string

Asthmap_patients:
load *;
sql
select *
from cp_medicine.zFluShot;

OLEDB oracle connection sttring

ImmunizationTemp:
LOAD *;
SQL
select distinct case when d.name like '%INFLUENZA%' then 'Y' else 'N' end as FluVaccine, c.immune_date as IMMUNE_DATE,
to_char(c.immunization_time,'MM/DD/YYYY HH24:MI:SS') as immune_time
,b.pat_name as Patient, b.pat_mrn_id AS MRN, c.DOSE, C.IMMNZTN_STATUS_C, C.DEFER_REASON_C, d.name as Vaccine
,a.hsp_account_id as HospitalAccountID
from pat_enc_hsp a
inner join patient b on a.pat_id = b.pat_id
left join Immune c on c.pat_id = a.Pat_id
left join clarity_immunzatn d on d.IMMUNZATN_ID = c.IMMUNZATN_ID
where C.IMMUNZATN_ID in (select IMMUNZATN_ID from CLARITY_IMMUNZATN where name like 'INFLUENZA%')
and C.immune_date > to_date('09/17/2013', 'mm/dd/yyyy') and c.IMMNZTN_STATUS_C <> 2;

left join (Asthmap_patients) load
HOSPITALACCOUNTID,
admitdts,
DischargeDTS,
admitdt,
DischargeDT,
YearMonth,
patient
Resident ImmunizationTemp;

left join (Asthmap_patients)
load HOSPITALACCOUNTID, if(isnull(IMMUNE_DATE),1,0) as Flag Resident Asthmap_patients;


Immunization:
Load
HOSPITALACCOUNTID,
IMMUNE_DATE,
FLUVACCINE,
if(FLUVACCINE='Y',1,0) as BlnFluShot,
//if(IMMUNE_DATE <admitdts or IMMUNE_DATE > DischargeDTS, 0, 1)as InHospital,
if(IMMUNE_DATE <admitdt, 'Before', if(IMMUNE_DATE<=DischargeDT and IMMUNE_DATE>admitdt, 'InHospital','After'))as FlushotTime
Resident ImmunizationTemp
where not isnull(admitdts);
//where IMMUNE_DATE >=admitdts and IMMUNE_DATE <=DischargeDTS;

drop table ImmunizationTemp;

[maxgro]

Explanation of what I did

1) read from sql server (simulated with inline load) Table1

2) read from Oracle (simulated) table 2 

Left Join (Table1)

......

At this point I only have 1 table, Table1

3) add the flag field joining Table1 with Table1

Left Join (Table1) load MRN, if(IsNull(FlushotTIME),1,0) as FlagNull Resident Table1;

If I'm not wrong, at the end of your script you have 2 table (not 1 table as me),  Asthmap_patients and Immunization (join fields HOSPITALACCOUNTID and IMMUNE_DATE?) and what's your problem? Binflushot?