db2 sql stored procedure

[slaterjas]

I am having problems getting this sql update statement to work in a
db2 sql stored procedure or if I try to execute it as an input script
to db2 command line. It works fine if I execute it using a SQL editor
on another product we use (dbartisan). What happens is that the first
SET occurs successfully (GAP.STATUS = 'P'), but the 2nd set
GAP.DT_TM_PBLSHD does not work. Any ideas on this would be
appreciated.

Thanks,
Julie

UPDATE GWA.GWA_ACCTNG_PRD as GAP
SET GAP.STATUS = 'P',
GAP.DT_TM_PBLSHD = (SELECT TIMESTAMP(QFB.RUN_DATE,
QFB.RUN_TIME)
FROM STAR.VSTR_QFB as QFB, STAR.TSTR_PPADJ_CTL_165
as PPC,
GWA.GWA_ACCTNG_PRD as GAP1
WHERE CHAR(QFB.RUN_DATE) =
CHAR(SUBSTR(PPC.PUBL_MTS_DATE,1,4) || '-' ||
SUBSTR(PPC.PUBL_MTS_DATE,5,2) || '-' ||
SUBSTR(PPC.PUBL_MTS_DATE,7,2)) AND
QFB.REPORT_ID = 'STM0SMTS' AND
PPC.ACCTNG_DATE = GAP1.ACCTNG_PRD
and GAP1.STATUS = 'O'
AND (LTRIM(RTRIM(COALESCE(CHAR(GAP1.DT_TM_PBLSHD),'')) ) = '')
AND ((LTRIM(RTRIM(COALESCE((SELECT PPC.PUBL_MTS_DATE
FROM STAR.TSTR_PPADJ_CTL_165 as PPC
WHERE PPC.ACCTNG_DATE = GAP1.ACCTNG_PRD),'')))) <>
''))

WHERE GAP.STATUS = 'O' AND
(LTRIM(RTRIM(COALESCE(CHAR(GAP.DT_TM_PBLSHD),''))) = '') AND
((LTRIM(RTRIM(COALESCE((SELECT PPC.PUBL_MTS_DATE
FROM STAR.TSTR_PPADJ_CTL_165 as PPC
WHERE PPC.ACCTNG_DATE = GAP.ACCTNG_PRD),'')))) <> '')

;

[Serge Rielau]

Julie,

Please, please please, post at least the error message DB2 is raising.
"Does not work" will not give a whole lot of answers in this group....
Best of course is if yoy also post the underlying DDL statements so
interested parties can simply reproduce your problem.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Julie,

Please, please please, post at least the error message DB2 is raising.
"Does not work" will not give a whole lot of answers in this group....
Best of course is if yoy also post the underlying DDL statements so
interested parties can simply reproduce your problem.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Knut Stolze]

slaterjas wrote:

WHERE CHAR(QFB.RUN_DATE) =
CHAR(SUBSTR(PPC.PUBL_MTS_DATE,1,4) || '-' ||
SUBSTR(PPC.PUBL_MTS_DATE,5,2) || '-' ||
SUBSTR(PPC.PUBL_MTS_DATE,7,2)) AND

I'm curious about the data types for the columns QFB.RUN_DATE and
PPC.PUBL_MTS_DATE. Are both columns of type DATE? Or is PUBL_MTS_DATE a
varchar that contains a valid date string? If yes, then you might want to
compare the dates like this:

WHERE QFB.RUN_DATE = DATE(PPC.PUBL_MTS_DATE)

Or if PUBL_MTS_DATE is a TIMESTAMP, you can also use the DATE function as
shown above to just extract the DATE portion.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Knut Stolze]

slaterjas wrote:

WHERE CHAR(QFB.RUN_DATE) =
CHAR(SUBSTR(PPC.PUBL_MTS_DATE,1,4) || '-' ||
SUBSTR(PPC.PUBL_MTS_DATE,5,2) || '-' ||
SUBSTR(PPC.PUBL_MTS_DATE,7,2)) AND

I'm curious about the data types for the columns QFB.RUN_DATE and
PPC.PUBL_MTS_DATE. Are both columns of type DATE? Or is PUBL_MTS_DATE a
varchar that contains a valid date string? If yes, then you might want to
compare the dates like this:

WHERE QFB.RUN_DATE = DATE(PPC.PUBL_MTS_DATE)

Or if PUBL_MTS_DATE is a TIMESTAMP, you can also use the DATE function as
shown above to just extract the DATE portion.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena