User defined SQL in Oracle Stage

[dsisbank]

When i run user defined sql run in Oracle stage, no data returns.
But when i run that sql in TOAD , i can see data.

In my sql, there is a case statment like that

Process_date=(case when to_char(sysdate,'D')=1 then trunc(sysdate-3) else trunc(sysdate-1) end) .

when i change the sql Process_date=trunc(sysdate-3), there is no problem in ds.

How can i solve this problem?

[ray.wurlod]

What problem? You assert that there is no problem in ds.

[dsisbank]

When i run that query in ds, I couldnt see data in datastage. but i run that query in toad, i could see the data.

[kduke]

Don't think the variables are allowed. I think you need a pure SQL statement which return rows.

[chulett]

Post your SQL.

[vrishabhsagar]

Another quick check : Please reconfirm if you are connecting to the same database while firing the query on TOAD and DS.

I burnt my fingers once ...

[dsisbank]

My sql is below

select a.SUBE_NO SUBE_NO,
a.KREDI_KODU KREDI_KODU,
a.KARTON_NO KARTON_NO,
ISLEM_SIRA_NO,
a.MUSTERI_NO MUSTERI_NO,
ISLEM_TARIHI,
VALOR_TARIHI,
BORC_ALACAK_KODU,
HAREKET_TURU,
ISLEM_SUBE_KODU,
GISE_NO,
FIS_NO,
KOMPLIMANTER_KODU,
IPTAL_KODU,
IPTAL_SIRA_NO,
ACIKLAMA,
ISLEM_TUTARI,
ISLEM_DOVIZ_KODU,
ISLEM_TL_KARSILIK,
ISLEM_USD_KARSILIK,
MASRAF_TURU,
TAHSILAT_SEKLI,
TAHSILAT_KAYNAK,
ICRA_DOSYA_NO,
PROTOKOL_ID,
TEMINAT_REFERANS,
KARSI_MUHASEBE_KODU,
KARSI_REFERANS_TIPI,
KARSI_REFERANS_NO,
KARSI_SUBE_NO,
KK_TAKSITLI_ISLEM_TUTARI,
KK_DIGER_ISLEM_TUTARI,
KUR_TARIHI,
TAHAK_ETMEYEN_FAIZ,
FIRMA_TELNO
from ACC.TOA_KARTON_BILGI b ,
evt.toa_hareket_mstr a
where islem_tarihi=(case when to_char(sysdate,'D')=1 then trunc(sysdate-3) else trunc(sysdate-1) end)
and a.SUBE_NO = b.SUBE_NO
and a.KREDI_KODU=b.KREDI_KODU
and a.KARTON_NO = b.KARTON_NO

[chulett]

Your sql is 'fine' and running it in DataStage v. Toad won't change the behaviour. I would guess 'no data' when viewing would mean a metadata problem. How did you import the metadata for these tables? As an experiment, if you set all fields to Varchar, can you then view the data?

[rleishman]

Beware of to_char(sysdate,'D')=1

Depending on your NLS settings, the numbering of days can vary. I once had this problem (and I think it was with DS) because DS set the NLS init params differently.

Try using to_char(sysdate, 'DY') = 'MON'

[chulett]

I imagine that could be if they are overridden in the dsenv file. Check the second log entry for the job, that will show all environment variables in play while the job was running.