query problem when running in informatica

[jlito]

Hi guys,


I have a query here that returns 2 rows in oracle. But when i run it in informatica ver 6.1, its returning me with 3 records which is shown below.

READER_1_1_1> RR_4029 SQ Instance [SQ_TGDW_GTFEXPMAS] User specified SQL Query [SELECT TGDW_GTFEXPMAS.EXPM_TXN_CCY_CD, TGDW_GTFEXPMAS.EXPM_GL_CORP_CD, TGDW_GTFEXPMAS.EXPM_LEGR_BAL_GL_CD, sum(TGDW_GTFEXPMAS.EXPM_LEGR_BAL_TXN), TGDW_CUR_STS.CUR_SNAP_DT
FROM
TGDW_GTFEXPMAS, TGDW_CUR_STS
WHERE
TGDW_GTFEXPMAS.EXPM_TXN_CCY_CD <> '???'
and TGDW_GTFEXPMAS.EXPM_GL_CORP_CD <> '???'
and TGDW_CUR_STS.LOAD_CTRY = 'SG'
AND
TGDW_GTFEXPMAS.LOAD_CTRY = TGDW_CUR_STS.LOAD_CTRY
and TGDW_GTFEXPMAS.SNAP_DT = TGDW_CUR_STS.CUR_SNAP_DT
GROUP BY EXPM_TXN_CCY_CD, EXPM_GL_CORP_CD, EXPM_LEGR_BAL_GL_CD, CUR_SNAP_DT]
READER_1_1_1> BLKR_16019 Read [3] rows, read [0] error rows for source table [TGDW_GTFEXPMAS] instance name [TGDW_GTFEXPMAS]
READER_1_1_1> BLKR_16008 Reader run completed.
TRANSF_1_1_1_1> CMN_1053 SQ_TGDW_GTFEXPMAS:Leaf Exchange Output: Rowdata: ( RowType=0 Src Rowid=1 Targ Rowid=1
EXPM_TXN_CCY_CD (EXPM_TXN_CCY_CD:Char.3: &quot;SGD&quot;
EXPM_LEGR_BAL_GL_CD (EXPM_LEGR_BAL_GL_CD:Char.10: &quot;1511111111&quot;
EXPM_GL_CORP_CD (EXPM_GL_CORP_CD:Char.4: &quot;???&quot;
EXPM_LEGR_BAL_TXN (EXPM_LEGR_BAL_TXNouble: &quot;150212023.0000000&quot;
CUR_SNAP_DT (CUR_SNAP_DTate: &quot;12/10/2002 00:00:00&quot;
)

TRANSF_1_1_1_1> CMN_1053 SQ_TGDW_GTFEXPMAS:Leaf Exchange Output: Rowdata: ( RowType=0 Src Rowid=2 Targ Rowid=2
EXPM_TXN_CCY_CD (EXPM_TXN_CCY_CD:Char.3: &quot;SGD&quot;
EXPM_LEGR_BAL_GL_CD (EXPM_LEGR_BAL_GL_CD:Char.10: &quot;1511111111&quot;
EXPM_GL_CORP_CD (EXPM_GL_CORP_CD:Char.4: &quot;1001&quot;
EXPM_LEGR_BAL_TXN (EXPM_LEGR_BAL_TXNouble: &quot;250212023.0000000&quot;
CUR_SNAP_DT (CUR_SNAP_DTate: &quot;12/10/2002 00:00:00&quot;
)

Is there any thing wrong with my query? I'm filtering the records with EXPM_GL_CORP_CD = '???'. But it is populating to the target. Why???

Regards,
jlito

 

[blom0344]

We are talking a CHAR datatype, so the filter should be:

EXPM_GL_CORP_CD = '??? '

instead of:

EXPM_GL_CORP_CD = '???'

CHAR datatypes have trailing spaces you have to reckon with...

T. Blom
Information analyst
tbl@shimano-eu.com