db2 stored proc. problem -

[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),'')))) <> '')

[Knut Stolze]

slaterjas wrote:

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.

UPDATE GWA.GWA_ACCTNG_PRD as GAP
SET GAP.STATUS = 'P',
GAP.DT_TM_PBLSHD = (SELECT TIMESTAMP(QFB.RUN_DATE,
QFB.RUN_TIME)

[...]

What's the error that you got?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Knut Stolze]

slaterjas wrote:

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.

UPDATE GWA.GWA_ACCTNG_PRD as GAP
SET GAP.STATUS = 'P',
GAP.DT_TM_PBLSHD = (SELECT TIMESTAMP(QFB.RUN_DATE,
QFB.RUN_TIME)

[...]

What's the error that you got?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Serge Rielau]

"does not work"
Any way to be more specific? Like a dump of the error message....

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

"does not work"
Any way to be more specific? Like a dump of the error message....

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[slaterjas]

I'm sorry for the confusion. It doesn't receive an error or a dump,
it just doesn't do the update of the 2nd column. It updates the
gap.status = P, but it does not update the gap.dt_tm_pblshd = (select
timestamp....). The correct result is received if I execute the
update query in dbartisan isql editor.

Thanks,
Julie

[slaterjas]

I'm sorry for the confusion. It doesn't receive an error or a dump,
it just doesn't do the update of the 2nd column. It updates the
gap.status = P, but it does not update the gap.dt_tm_pblshd = (select
timestamp....). The correct result is received if I execute the
update query in dbartisan isql editor.

Thanks,
Julie

[Knut Stolze]

slaterjas wrote:

I'm sorry for the confusion. It doesn't receive an error or a dump,
it just doesn't do the update of the 2nd column. It updates the
gap.status = P, but it does not update the gap.dt_tm_pblshd = (select
timestamp....). The correct result is received if I execute the
update query in dbartisan isql editor.

So what IS put in the 2nd column? A NULL, the wrong value, the same value
as before?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Knut Stolze]

slaterjas wrote:

I'm sorry for the confusion. It doesn't receive an error or a dump,
it just doesn't do the update of the 2nd column. It updates the
gap.status = P, but it does not update the gap.dt_tm_pblshd = (select
timestamp....). The correct result is received if I execute the
update query in dbartisan isql editor.

So what IS put in the 2nd column? A NULL, the wrong value, the same value
as before?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Serge Rielau]

I see. Is the value of GAP.DT_TM_PBLSHD NULL before and after?
In that case the update will have happened it just so happend that the
select return no row. I.e. the where clause didn't find a match. So DB2
followed "null on empty" semantics for scalar subqueries.
I am also suspicious of GAP1.
Am I right that this is a SQL Server or Sybase UPDATE FROM which you
ported to DB2?
Can you post the original? It may be easier to just start from scratch
than trying to reverse engineer the original intent :-)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

I see. Is the value of GAP.DT_TM_PBLSHD NULL before and after?
In that case the update will have happened it just so happend that the
select return no row. I.e. the where clause didn't find a match. So DB2
followed "null on empty" semantics for scalar subqueries.
I am also suspicious of GAP1.
Am I right that this is a SQL Server or Sybase UPDATE FROM which you
ported to DB2?
Can you post the original? It may be easier to just start from scratch
than trying to reverse engineer the original intent :-)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[slaterjas]

The value of GAP.DT_TM_PBLSHD is NULL before and after the update.
I'm curious why you asked if this was ported from SQL Server to DB2??
It was developed in DB2 but it was developed by someone with SQL
Server background. I'm rather new to stored procedures so I'm not
sure what indicated it was something other than a DB2 stored
procedure. The UPDATE that I posted earlier was actually an attempt
to rewrite it to fix the problem of the GAP.DT_TM_PBLSHD not being
updated. The original UPDATE was:
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
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 = GAP.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),'')))) <> '')
;

Thanks,
Julie

[Serge Rielau]

W.r.t. teh correlations to GAP the original actiually makes mre sense.
I'm qyuite certian that your problems do not stem from the stored procedure.
Have you tried running the statement from the command line?

I see soem serious issues with your schema here which convolute the
query and, even if you get this statement to work will wreck performance.

Can you post the column datatypes involved? They don't seem to match up
which is part of the problem.

E.g. I conclude that:
GAP.DT_TM_PBLSHD is a Timestamp?
Then the comparison to '' makes no sense, ever. If TS is NOT NULL then
it will not be ''.
This means:
(LTRIM(RTRIM(COALESCE(CHAR(GAP.DT_TM_PBLSHD),''))) = '')
== (GAP.DT_TM_PBLSHD IS NULL)

Maybe it would be best to explain in plain English what the query is
supposed to do and give soem sampel input and expected output.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[slaterjas]

The tables PPC and QFB are retrieved from a legacy database and loaded
to temporary tables just to process and populate the dt_tm_pblshd in
the gap table. We have to take the date from the PPC to get the
run_date and run_time from the QFB table. The legacy system has
definitely stored dates in an inconsistent manner (some char format...
some are date format).

SCHEMA from LEGACY database

PPC.PUBL_MTS_DATE PPC.ACCTNG_DATE
CHAR(8) CHAR(6)
---------------------------------------
20040309 200402
20040409 200403
20040510 200404
QFB.RUN_DATE QFB.RUN_TIME QFB.REPORT_ID
DATE TIME CHAR(8)
-----------------------------------------------------
3/9/2004 6:26:29 STM0SMTS
4/9/2004 6:43:21 STM0SMTS
5/10/2004 7:06:50 STM0SMTS
SCHEMA from NEW database

GAP.ACCTNG_PRD GAP.STATUS GAP.DT_TM_PBLSHD
CHAR(6) CHAR(1) TIMESTAMP
------------------------------------------------------------------
200402 P 3/9/2004 6:26:29.000000 PM
200403 P 4/9/2004 6:43:21.000000 PM
200404 P 5/10/2004 7:06:50.000000 PM
200405 O null
I tried to run it in Command Center, but it only updates the
GAP.STATUS field to 'P' but the GAP.DT_TM_PBLSHD remains null. But I
paste that same update query in dbartisan isql editor and it updates
both GAP.STATUS to 'P' and populates the GAP.DT_TM_PBLSHD with the
proper timestamp from the concatenation of QFB.RUN_DATE and
QFB.RUN_TIME.

Thanks,
Julie

[Serge Rielau]

OK, I have no clue what magic trick dbartisan is pulling. A cli-trace
would likely show what is different.
Anyway. Presuming you are on V8 try one of these two statements.
Note that in V8 you can cast between integer and date.
In V7 you will need the UPDATE statement and most of the substr you did.

MERGE INTO GWA.GWA_ACCTNG_PRD as GAP
USING (SELECT *
FROM STAR.TSTR_PPADJ_CTL_165 as PPC,
STAR.VSTR_QFB as QFB
WHERE DATE(INTEGER(PPC.PUBL_MTS_DATE)) = QFB.RUN_DATE
AND QFB.REPORT_ID = 'STM0SMTS') AS S
ON S.ACCTNG_DATE = GAP.ACCTNG_PRD
AND GAP.STATUS = 'O'
WHEN MATCHED THEN UPDATE
SET STATUS = 'P',
DT_TM_BPLSHD = TIMESTAMP(S.RUN_DATE, S.RUN_TIME);

or

UPDATE GWA.GWA_ACCTNG_PRD as GAP
SET (STATUS,
DT_TM_BPLSHD)
= (SELECT 'P', TIMESTAMP(QFB.RUN_DATE, QFB.RUN_TIME)
FROM STAR.TSTR_PPADJ_CTL_165 as PPC,
STAR.VSTR_QFB as QFB
WHERE DATE(INTEGER(PPC.PUBL_MTS_DATE)) = QFB.RUN_DATE AS S
AND PPC.ACCTNG_DATE = GAP.ACCTNG_PRD
AND QFB.REPORT_ID = 'STM0SMTS')
WHERE EXISTS(SELECT 1 FROM STAR.TSTR_PPADJ_CTL_165 as PPC,
STAR.VSTR_QFB as QFB
WHERE DATE(INTEGER(PPC.PUBL_MTS_DATE)) = QFB.RUN_DATE AS S
AND PPC.ACCTNG_DATE = GAP.ACCTNG_PRD
AND QFB.REPORT_ID = 'STM0SMTS')
AND GAP.STATUS = 'O'

It seem like in teh MERGE statement you woudl want to add
GAP.STATUS
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[slaterjas]

We are running V8.FP4. Once again....I was able to get the following
SQL to work when I execute it using dbartisan. When I put it in a db2
stored procedure or just try to execute it through db2 command center;
I receive the following error:

SQL0100W No row was found for FETCH, UPDATE or DELETE;
or the result of a query is an empty table. SQLSTATE=02000

I couldn't use the date(integer(ppc.publ_mts_date) from your message
because it kept giving me the following error so I reverted back to
CHAR:

SQL0183N A datetime arithmetic operation or a datetime scalar
function has a result that is not within the valid range of dates.
SQLSTATE=22008

MERGE INTO GWA.GWA_ACCTNG_PRD as GAP
USING (SELECT *
FROM STAR.TSTR_PPADJ_CTL_165 as PPC,
STAR.VSTR_QFB as QFB 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') AS S
ON S.ACCTNG_DATE = GAP.ACCTNG_PRD
AND GAP.STATUS = 'O'
WHEN MATCHED THEN UPDATE
SET STATUS = 'P',
DT_TM_PBLSHD = TIMESTAMP(S.RUN_DATE, S.RUN_TIME);
Thanks,
Julie

[Serge Rielau]

Hah! Your date formats are different between dbartisan and that used
in stored procedures :-) See the small, but important "ISO" I added below.
You also might want to verify your data in ppc.publ_mts_date.
Looks like you got some bad values in there.
The substr will work around the error, but not make it work :-)

That was birth by cesarian section....
MERGE INTO GWA.GWA_ACCTNG_PRD as GAP
USING (SELECT *
FROM STAR.TSTR_PPADJ_CTL_165 as PPC,
STAR.VSTR_QFB as QFB WHERE
CHAR(QFB.RUN_DATE, ISO) = 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') AS S
ON S.ACCTNG_DATE = GAP.ACCTNG_PRD
AND GAP.STATUS = 'O'
WHEN MATCHED THEN UPDATE
SET STATUS = 'P',
DT_TM_PBLSHD = TIMESTAMP(S.RUN_DATE, S.RUN_TIME);
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[slaterjas]

Serge... Thank you so much!! It is working now. That thing was
driving me crazy. I really appreciate your help on this.

Julie