Rows Not Match (Transformer)

[imfarhan]

hi ,
I'm quite new using Datastage,
Give you background ...I'm only using DB2, Lookup and Tranformer for this job. I have number of IF THN ELSE conditions which I have used in my last stage of Transformer and all are working ok.
Only two conditions are not matching against my SQL which i'm running on IBM DataStudio
I''m checking each condition by using constraint the following IF conditoin work ok for required value

The following condition running ok and match again my SQL

Code: Select all

OP_Appt_hrg_Src.PROCEDURE_FLAG = 0 and (OP_Appt_hrg_Src.CARE_PROF_NC_CONSULTANT [1,1] ='N' or OP_Appt_hrg_Src.CARE_PROF_NC_CONSULTANT [1,1] ='M' or OP_Appt_hrg_Src.CARE_PROF_NC_CONSULTANT [1,1] ='H'  )

as soon I add one more OR condition "OR OP_Appt_hrg_Src.PROVIDERS_REFERENCE_NO = 'NCL')"
I'm getting less rows

Code: Select all

OP_Appt_hrg_Src.PROCEDURE_FLAG = 0 and (OP_Appt_hrg_Src.CARE_PROF_NC_CONSULTANT [1,1] ='N' or OP_Appt_hrg_Src.CARE_PROF_NC_CONSULTANT [1,1] ='M' or OP_Appt_hrg_Src.CARE_PROF_NC_CONSULTANT [1,1] ='H'  OR OP_Appt_hrg_Src.PROVIDERS_REFERENCE_NO = 'NCL')

This new OR condition is coming from different table , but shouldn't be a problem as all other condition showing the right results using the same columns


Many thanks
Farhan

[ArndW]

Is OP_Appt_hrg_Src.PROVIDERS_REFERENCE_NO a nullable field?

[imfarhan]

I've run the distinct on that column found Nulls and ' ' as shown below
Thanks for your help

Code: Select all

SELECT PROVIDERS_REFERENCE_NO, LENGTH(PROVIDERS_REFERENCE_NO),COUNT(*) FROM DV_RDS.CONTRACT
GROUP BY PROVIDERS_REFERENCE_NO

PROVIDERS_REFERENCE_NO 2 3
---------------------- ---- ------

BOWEL
CARDDA
CARDDIAG
CYST_FIB
DAYCARE
EXCL_BREAST
EXCL_CHEMO
EXCL_MH
EXCL_URO
NCL
OPHTHDIAG
ORTHOTICS
PAEDPHYSIO
PRE-ASSESSMENT
REABLE
SOPHIE
WHEELCHAIR
NULL

[ArndW]

OK, so you need to add a NullToValue() or otherwise handle those nulls and then you can mark the topic as resolved.

[imfarhan]

Thanks a lot first Time I have used this forum quite good

Kind Regards
Farhan