| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
03-01-10, 02:29
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 13
|
|
Sql0420
|
|
Hi,
I wrote a little SQL what worked fine:
Code:
SELECT
VHWHLO, PROD_DEPT, VHFACI, VHITNO, VHITDS, MO_FILTER
FROM ( SELECT VHWHLO, VHFACI, PROD_DEPT, VHMFNO, VHITNO, VHITDS, VHORTY,
trim ( char ( year (VHREFD))) || substr ( digits ( month (VHREFD)),9) as YMNTH,
MO_FILTER,
CAST (VHORQT as INTEGER ) as VHORQT,
CAST (VHMAQT as INTEGER ) as VHMAQT,
CAST (ReworkQty as INTEGER ) as SUM_REWORK,
CAST (ScrapQty as INTEGER ) as SUM_SCRAP,
CAST (RandSQty as INTEGER ) as SUM_RANDS,
CAST (NoneQty as INTEGER ) as SUM_NONE,
CAST (( COALESCE (MFCOST,0) * ( COALESCE (SCRAPQTY,0) + COALESCE (RANDSQTY,0) ) + COALESCE (RW_SPAREPARTS,0) + COALESCE (RW_LABOUR,0)) as FLOAT ) as SCRAP_COST,
CAST ( COALESCE (RW_SPAREPARTS, 0) + COALESCE (RW_LABOUR, 0) as FLOAT ) as REWORK_COST
FROM ( SELECT
VHCONO, VHWHLO, VHFACI,
substr (VODEPT,2) as PROD_DEPT,
trim (VHMFNO) as VHMFNO, VHORTY, trim (VHPRNO) as VHITNO, trim (MMITDS) as VHITDS,
VHWHST,
COALESCE (MMSPE1,0) as MO_FILTER,
VHORQT, VHMAQT, a.M9UCOS as MfCost, CCLOCD as Currency,
dec2date(VHRSDT) as STARTDT, MMCFI3,
dec2date(VHREFD) as VHREFD,
MMSTAT, case when VHRORN<>'' and VHRORC=3 then trim (VHRORN) || '/' || VHRORL else '' end as COReference,
trim (MMSPE5) as MMSPE5, trim (MMSPE1) as MMSPE1, trim(VHBANO) as VHBANO, MMITCL, MMITGR,
SUM (RW_SPAREPARTS) as RW_SPAREPARTS,
SUM (RACSHR) as RW_LABOUR,
SUM (coalesce(CEERQT, 0)) ErrorQty,
SUM ( case CEATRC when '1C' then CEERQT else 0 end ) NoneQty,
SUM ( case CEATRC when '2C' then CEERQT else 0 end ) ScrapQty,
SUM ( case CEATRC when '3C' then CEERQT else 0 end ) ReworkQty,
SUM ( case CEATRC when '4C' then CEERQT else 0 end ) RandSQty
FROM MVXCDTA.MWOHED
JOIN MVXCDTA.MITMAS ON (VHCONO=MMCONO AND VHPRNO=MMITNO)
JOIN MVXCDTA.MWOOPE ON (VHCONO=VOCONO AND VHFACI=VOFACI AND VHPRNO=VOPRNO AND VHMFNO=VOMFNO)
LEFT OUTER JOIN PFXCDTA.XQAC2E ON (CECONO=VOCONO AND CEFACI=VOFACI AND CEPRNO=VOPRNO AND CEMFNO=VOMFNO AND CEOPNO=VOOPNO)
JOIN MVXCDTA.MITFAC a ON (VHCONO=a.M9CONO AND a.M9FACI=VHFACI AND a.M9ITNO=VHPRNO)
JOIN MVXCDTA.CMNDIV ON (VHCONO=CCCONO AND CCDIVI!='' AND VHFACI=CCFACI)
left outer join PFXCDTA.XQARWA on (RAOBNO = CEOBID)
left outer join PFXCDTA.XQARSPV1 on (RSOBNO = CEOBID)
WHERE VHCONO = ?
AND VHFACI = ?
AND VHORTY = ?
AND VHWHLO = ?
AND substr (VODEPT,2) = ?
AND trim ( char ( year (VHREFD))) || substr ( digits ( month (VHREFD)),9) >= ?
AND trim ( char ( year (VHREFD))) || substr ( digits ( month (VHREFD)),9) <= ?
AND VHWHST > 79
GROUP BY VHCONO, VHWHLO, VHFACI, VODEPT, VHITNO, VHPRNO, MMITDS, VHMFNO, VHRORL, VHWHST, MMSPE1, VHORQT, VHMAQT, a.M9UCOS, CCLOCD, VHRSDT, VHREFD, VHORTY, MMSTAT,
VHRORN, VHRORL, VHRORC, MMCFI3, MMSPE1, MMSPE5, VHBANO, MMITCL, MMITGR
ORDER BY VHCONO, VHFACI, VHPRNO, VHREFD DESC ) AS TEMP
order by YMNTH, VHITNO, VHMFNO ) AS TEMP1
group by
VHITNO,
VHWHLO,
VHFACI,
PROD_DEPT,
VHITDS,
MO_FILTER
now if I add
Code:
,
SUM ( coalesce (VHORQT, 0) ) as VHORQT
to the SELECT term I get an SQL0420 error. I know that there should be a problem with my datatypes within the parameters, but I checked them and they should be fine.
Has anyone an idea why this happens?
Thank you very much,
best regards,
pilzbug
|
|
03-01-10, 04:05
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 13
|
|
What maybe is interesting too:
VHCONO is decimal
VHFACI, VHORTY, VHWHLO, substr (VODEPT,2), trim ( char ( year (VHREFD))) || substr ( digits ( month (VHREFD)),9), trim ( char ( year (VHREFD))) || substr ( digits ( month (VHREFD)),9) are strings
I tried to find a particular parameter what throws the error but it was not possible. What I saw was that if there are inputs for the last two parameters where there can be no data it really shows no data.
Thank you
|
|
03-01-10, 04:16
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,534
|
|
|
|
have you tried to include just VHORQT (without coalesce,sum) and check if you get any string values there ?
and btw, you do not need the coalesce in the sum ... any nulls will be ignored
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
03-01-10, 04:28
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 13
|
|
Hi sathyaram_s,
thanks or your reply.
Yes, I tried to do it without coalesce and there was the same error.
You are right about the need of coalesce!
But I don't understand how there could be string values because I did the CAST to integer in the first subselect.
|
|
03-01-10, 04:45
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,534
|
|
Quote:
|
Yes, I tried to do it without coalesce and there was the same error.
|
no, i meant, just select ....., VHORQT from .... to see if there is any non-numeric ..
Quote:
|
I don't understand how there could be string values because I did the CAST to integer in the first subselect.
|
yep ... it's bit strange ...
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
03-01-10, 05:42
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,194
|
|
Quote:
|
I don't understand how there could be string values because I did the CAST to integer in the first subselect.
|
But, you didn't reference to VHORQT in select list in outer most SELECT.
So, I thought that optimizer recognized unnecessariness of CAST to integer.
If you added "SUM ( coalesce (VHORQT, 0) )", it is neccesary to cast to integer.
|
|
03-01-10, 05:45
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 13
|
|
Hi,
@tonkuma: you're totally right but because I want to add the SUM I did add the CAST to prevent problems. The same problem appeared without all the CASTs.
@sathyaram_s: I tried also to select just VHORQT but it didn't work with the grouping, of course. But in the subselect everything worked fine and because of the cast everything is INT.
Thanks and regards
|
Last edited by pilzbug; 03-01-10 at 06:03.
|
03-01-10, 06:02
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,194
|
|
Please follow sathyaram_s advice.
Quote:
Quote:
|
Yes, I tried to do it without coalesce and there was the same error.
|
no, i meant, just select ....., VHORQT from .... to see if there is any non-numeric ..
|
Sometimes, blanks or zero length string were included in the column and couldn't cast to integer.
|
|
03-01-10, 06:26
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 13
|
|
Hi tonkuma,
thanks for this advice. But unfortunately it doesn't work. As our DB is wrote there can be no string or something like this but NULL is possible do I did an COALESCE there too. It doesn't work this way too.
Regards
|
|
03-01-10, 07:41
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,194
|
|
If you know exactly what data caused the message SQL0420,
you would be able to find the way to avoid SQL0420 error.
Following steps is a way to find the data which caused the message SQL0420.
(This is an example. There are many other ways.)
1) Here is an UDF to find non-numeric strings.
Code:
CREATE FUNCTION ISNUMERIC (Source VARCHAR(40))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
CASE
WHEN translate(source,'','0123456789.-+') <> '' THEN 0
WHEN posstr(ltrim(source),'-') > 1
OR posstr(ltrim(source),'+') > 1 THEN 0
WHEN length(rtrim(ltrim(translate(source,'','0123456789.')))) > 1
OR length(rtrim(ltrim(translate(source,'','0123456789-+')))) > 1 THEN 0
WHEN posstr(ltrim(rtrim(translate(source,'','-+'))),' ') > 0 THEN 0
WHEN translate(source,'','.-+') = '' THEN 0
WHEN source IS NULL THEN NULL
ELSE 1
END
;
By using ISNUMERIC function....
2) Execute your query by adding a predicate " AND (VHORQT IS NULL OR ISNUMERIC(VHORQT) = 1)" to confirm the usefulness of the function.
Code:
SELECT
VHWHLO, PROD_DEPT, VHFACI, VHITNO, VHITDS, MO_FILTER
, SUM ( coalesce (VHORQT, 0) ) as VHORQT
FROM (SELECT VHWHLO, VHFACI, PROD_DEPT, VHMFNO, VHITNO, VHITDS, VHORTY
, trim ( char ( year (VHREFD))) || substr ( digits ( month (VHREFD)),9) as YMNTH
, MO_FILTER
, CAST (VHORQT as INTEGER ) as VHORQT
, CAST (VHMAQT as INTEGER ) as VHMAQT
.....
FROM (SELECT
VHCONO, VHWHLO, VHFACI
, substr (VODEPT,2) as PROD_DEPT
, trim (VHMFNO) as VHMFNO
, VHORTY
, trim (VHPRNO) as VHITNO
, trim (MMITDS) as VHITDS
, VHWHST
, COALESCE (MMSPE1,0) as MO_FILTER
, VHORQT, VHMAQT, a.M9UCOS as MfCost, CCLOCD as Currency
.....
FROM MVXCDTA.MWOHED
JOIN MVXCDTA.MITMAS
ON VHCONO=MMCONO AND VHPRNO=MMITNO
.....
WHERE VHCONO = ?
AND VHFACI = ?
AND VHORTY = ?
AND VHWHLO = ?
AND substr (VODEPT,2) = ?
AND trim ( char ( year (VHREFD))) || substr ( digits ( month (VHREFD)),9) >= ?
AND trim ( char ( year (VHREFD))) || substr ( digits ( month (VHREFD)),9) <= ?
AND VHWHST > 79
AND (VHORQT IS NULL OR ISNUMERIC(VHORQT) = 1)
GROUP BY
VHCONO, VHWHLO, VHFACI, VODEPT, VHITNO, VHPRNO, MMITDS, VHMFNO, VHRORL, VHWHST, MMSPE1
, VHORQT, VHMAQT, a.M9UCOS, CCLOCD, VHRSDT, VHREFD, VHORTY, MMSTAT, VHRORN, VHRORL, VHRORC
, MMCFI3, MMSPE1, MMSPE5, VHBANO, MMITCL, MMITGR
ORDER BY
VHCONO, VHFACI, VHPRNO, VHREFD DESC
) AS TEMP
order by
YMNTH, VHITNO, VHMFNO
) AS TEMP1
group by
VHITNO, VHWHLO, VHFACI, PROD_DEPT, VHITDS, MO_FILTER
3) Select and see irregular data.
Code:
SELECT
VHWHLO, PROD_DEPT, VHFACI, VHITNO, VHITDS, MO_FILTER
/*
, SUM ( coalesce (VHORQT, 0) ) as VHORQT
*/
, VHORQT
FROM (SELECT VHWHLO, VHFACI, PROD_DEPT, VHMFNO, VHITNO, VHITDS, VHORTY
, trim ( char ( year (VHREFD))) || substr ( digits ( month (VHREFD)),9) as YMNTH
, MO_FILTER
/*
, CAST (VHORQT as INTEGER ) as VHORQT
*/
, VHORQT
, CAST (VHMAQT as INTEGER ) as VHMAQT
.....
FROM (SELECT
VHCONO, VHWHLO, VHFACI
, substr (VODEPT,2) as PROD_DEPT
, trim (VHMFNO) as VHMFNO
, VHORTY
, trim (VHPRNO) as VHITNO
, trim (MMITDS) as VHITDS
, VHWHST
, COALESCE (MMSPE1,0) as MO_FILTER
, VHORQT, VHMAQT, a.M9UCOS as MfCost, CCLOCD as Currency
.....
FROM MVXCDTA.MWOHED
JOIN MVXCDTA.MITMAS
ON VHCONO=MMCONO AND VHPRNO=MMITNO
.....
WHERE VHCONO = ?
AND VHFACI = ?
AND VHORTY = ?
AND VHWHLO = ?
AND substr (VODEPT,2) = ?
AND trim ( char ( year (VHREFD))) || substr ( digits ( month (VHREFD)),9) >= ?
AND trim ( char ( year (VHREFD))) || substr ( digits ( month (VHREFD)),9) <= ?
AND VHWHST > 79
AND (ISNUMERIC(VHORQT) = 0)
GROUP BY
VHCONO, VHWHLO, VHFACI, VODEPT, VHITNO, VHPRNO, MMITDS, VHMFNO, VHRORL, VHWHST, MMSPE1
, VHORQT, VHMAQT, a.M9UCOS, CCLOCD, VHRSDT, VHREFD, VHORTY, MMSTAT, VHRORN, VHRORL, VHRORC
, MMCFI3, MMSPE1, MMSPE5, VHBANO, MMITCL, MMITGR
ORDER BY
VHCONO, VHFACI, VHPRNO, VHREFD DESC
) AS TEMP
order by
YMNTH, VHITNO, VHMFNO
) AS TEMP1
group by
VHITNO, VHWHLO, VHFACI, PROD_DEPT, VHITDS, MO_FILTER
|
|
03-01-10, 09:52
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 13
|
|
Hi tonkuma,
thank you very much, I will try it an tell you the result.
|
|
03-02-10, 08:59
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 13
|
|
Hello again,
I tried everything to kill this error but nothing worked.
Now so you maybe see better where the problem is, I will give two codes. The first one works, the second one throws SQL0420.
I changed the SQL alot and did CASTs everywhere.
Here the first code, that works fine:
Code:
SELECT VHITNO, VHWHLO, VHFACI
FROM ( SELECT
CAST (VHCONO as DECIMAL(3,0)) as VHCONO,
CAST (VHWHLO as varchar(12)) as VHWHLO,
CAST (VHFACI as varchar(12)) as VHFACI,
CAST (VODEPT as varchar(12)) as PROD_DEP,
CAST (CCLOCD as varchar(12)) as CURR,
CAST ( trim (VHMFNO) as varchar(12)) as VHMFNO,
CAST (VHORTY as varchar(12)) as VHORTY,
CAST (trim (VHPRNO) as varchar(12)) as VHITNO,
CAST (trim (MMITDS) as varchar(64)) as VHITDS,
CAST ( COALESCE (VHWHST,0) as INTEGER ) as VHWHST,
CAST ( COALESCE (MMSPE1,0) as INTEGER ) as MO_FILTER,
CAST ( COALESCE (VHORQT,0) as INTEGER ) as VHORQT,
CAST ( COALESCE (VHMAQT,0) as INTEGER ) as VHMAQT,
CAST ( COALESCE (a.M9UCOS,0) as DECIMAL(10,3)) as MFCOST,
dec2date(VHREFD) as VHREFD,
CAST ( CONCAT ( trim ( char ( year (dec2date(VHREFD)))), substr ( digits ( month (dec2date(VHREFD))),9)) as char(6)) as YMNTH,
CAST ( SUM ( COALESCE (RW_SPAREPARTS,0)) as FLOAT ) as RW_SPAREPARTS,
CAST ( SUM ( COALESCE (RACSHR,0)) as FLOAT ) as RW_LABOUR,
CAST ( SUM ( COALESCE (CEERQT, 0)) as INTEGER ) as ERR_QTY,
CAST ( SUM ( case CEATRC when '1C' then CEERQT else 0 end ) as INTEGER ) as SUM_NONE,
CAST ( SUM ( case CEATRC when '2C' then CEERQT else 0 end ) as INTEGER ) as SUM_SCRAP,
CAST ( SUM ( case CEATRC when '3C' then CEERQT else 0 end ) as INTEGER ) as SUM_REWORK,
CAST ( SUM ( case CEATRC when '4C' then CEERQT else 0 end ) as INTEGER ) as SUM_RANDS,
CAST (( COALESCE (a.M9UCOS,0) * ( SUM ( case CEATRC when '2C' then CEERQT else 0 end ) + SUM ( case CEATRC when '4C' then CEERQT else 0 end )) + SUM ( COALESCE (RW_SPAREPARTS,0)) + SUM ( COALESCE (RACSHR,0))) as FLOAT ) as QCOST,
CAST (( COALESCE (a.M9UCOS,0) * ( SUM ( case CEATRC when '2C' then CEERQT else 0 end ) + SUM ( case CEATRC when '4C' then CEERQT else 0 end ))) as FLOAT ) as SCRAP_COST,
CAST (( SUM ( COALESCE (RW_SPAREPARTS, 0)) + SUM ( COALESCE (RACSHR, 0))) as FLOAT ) as REWORK_COST
FROM MVXCDTA.MWOHED
JOIN MVXCDTA.MITMAS ON (VHCONO=MMCONO AND VHPRNO=MMITNO)
JOIN MVXCDTA.MWOOPE ON (VHCONO=VOCONO AND VHFACI=VOFACI AND VHPRNO=VOPRNO AND VHMFNO=VOMFNO)
LEFT OUTER JOIN PFXCDTA.XQAC2E ON (CECONO=VOCONO AND CEFACI=VOFACI AND CEPRNO=VOPRNO AND CEMFNO=VOMFNO AND CEOPNO=VOOPNO)
JOIN MVXCDTA.MITFAC a ON (VHCONO=a.M9CONO AND a.M9FACI=VHFACI AND a.M9ITNO=VHPRNO)
JOIN MVXCDTA.CMNDIV ON (VHCONO=CCCONO AND CCDIVI!='' AND VHFACI=CCFACI)
left outer join PFXCDTA.XQARWA on (RAOBNO = CEOBID)
left outer join PFXCDTA.XQARSPV1 on (RSOBNO = CEOBID)
WHERE VHCONO = ?
AND VHFACI = ?
AND VHORTY = ?
AND VHWHLO = ?
AND substr (VODEPT,2) = ?
AND CONCAT ( trim ( char ( year (VHREFD))), substr ( digits ( month (VHREFD)),9)) >= ?
AND CONCAT ( trim ( char ( year (VHREFD))), substr ( digits ( month (VHREFD)),9)) <= ?
AND VHWHST > '79'
GROUP BY VHCONO, VHWHLO, VHFACI, VODEPT, VHITNO, VHPRNO, MMITDS, VHMFNO, VHRORL, VHWHST, MMSPE1, VHORQT, VHMAQT, a.M9UCOS, CCLOCD, VHREFD, VHORTY,
VHRORN, VHRORL, VHRORC, MMCFI3
ORDER BY YMNTH, VHITNO, VHMFNO, VHCONO, VHFACI, VHPRNO, VHREFD DESC ) AS TEMP
GROUP BY VHITNO, VHWHLO, VHFACI
ORDER BY VHITNO
I get everything I want so far and even the grouping is right. Now if I just ad VHORQT to the SELECT and GROUPING everything is fine but there is no grouping on VHITNO. So I do:
Code:
SELECT VHITNO, VHWHLO, VHFACI,
SUM (VHORQT) as VHORQT
FROM ( SELECT
CAST (VHCONO as DECIMAL(3,0)) as VHCONO,
CAST (VHWHLO as varchar(12)) as VHWHLO,
CAST (VHFACI as varchar(12)) as VHFACI,
CAST (VODEPT as varchar(12)) as PROD_DEP,
CAST (CCLOCD as varchar(12)) as CURR,
CAST ( trim (VHMFNO) as varchar(12)) as VHMFNO,
CAST (VHORTY as varchar(12)) as VHORTY,
CAST (trim (VHPRNO) as varchar(12)) as VHITNO,
CAST (trim (MMITDS) as varchar(64)) as VHITDS,
CAST ( COALESCE (VHWHST,0) as INTEGER ) as VHWHST,
CAST ( COALESCE (MMSPE1,0) as INTEGER ) as MO_FILTER,
CAST ( COALESCE (VHORQT,0) as INTEGER ) as VHORQT,
CAST ( COALESCE (VHMAQT,0) as INTEGER ) as VHMAQT,
CAST ( COALESCE (a.M9UCOS,0) as DECIMAL(10,3)) as MFCOST,
dec2date(VHREFD) as VHREFD,
CAST ( CONCAT ( trim ( char ( year (dec2date(VHREFD)))), substr ( digits ( month (dec2date(VHREFD))),9)) as char(6)) as YMNTH,
CAST ( SUM ( COALESCE (RW_SPAREPARTS,0)) as FLOAT ) as RW_SPAREPARTS,
CAST ( SUM ( COALESCE (RACSHR,0)) as FLOAT ) as RW_LABOUR,
CAST ( SUM ( COALESCE (CEERQT, 0)) as INTEGER ) as ERR_QTY,
CAST ( SUM ( case CEATRC when '1C' then CEERQT else 0 end ) as INTEGER ) as SUM_NONE,
CAST ( SUM ( case CEATRC when '2C' then CEERQT else 0 end ) as INTEGER ) as SUM_SCRAP,
CAST ( SUM ( case CEATRC when '3C' then CEERQT else 0 end ) as INTEGER ) as SUM_REWORK,
CAST ( SUM ( case CEATRC when '4C' then CEERQT else 0 end ) as INTEGER ) as SUM_RANDS,
CAST (( COALESCE (a.M9UCOS,0) * ( SUM ( case CEATRC when '2C' then CEERQT else 0 end ) + SUM ( case CEATRC when '4C' then CEERQT else 0 end )) + SUM ( COALESCE (RW_SPAREPARTS,0)) + SUM ( COALESCE (RACSHR,0))) as FLOAT ) as QCOST,
CAST (( COALESCE (a.M9UCOS,0) * ( SUM ( case CEATRC when '2C' then CEERQT else 0 end ) + SUM ( case CEATRC when '4C' then CEERQT else 0 end ))) as FLOAT ) as SCRAP_COST,
CAST (( SUM ( COALESCE (RW_SPAREPARTS, 0)) + SUM ( COALESCE (RACSHR, 0))) as FLOAT ) as REWORK_COST
FROM MVXCDTA.MWOHED
JOIN MVXCDTA.MITMAS ON (VHCONO=MMCONO AND VHPRNO=MMITNO)
JOIN MVXCDTA.MWOOPE ON (VHCONO=VOCONO AND VHFACI=VOFACI AND VHPRNO=VOPRNO AND VHMFNO=VOMFNO)
LEFT OUTER JOIN PFXCDTA.XQAC2E ON (CECONO=VOCONO AND CEFACI=VOFACI AND CEPRNO=VOPRNO AND CEMFNO=VOMFNO AND CEOPNO=VOOPNO)
JOIN MVXCDTA.MITFAC a ON (VHCONO=a.M9CONO AND a.M9FACI=VHFACI AND a.M9ITNO=VHPRNO)
JOIN MVXCDTA.CMNDIV ON (VHCONO=CCCONO AND CCDIVI!='' AND VHFACI=CCFACI)
left outer join PFXCDTA.XQARWA on (RAOBNO = CEOBID)
left outer join PFXCDTA.XQARSPV1 on (RSOBNO = CEOBID)
WHERE VHCONO = ?
AND VHFACI = ?
AND VHORTY = ?
AND VHWHLO = ?
AND substr (VODEPT,2) = ?
AND CONCAT ( trim ( char ( year (VHREFD))), substr ( digits ( month (VHREFD)),9)) >= ?
AND CONCAT ( trim ( char ( year (VHREFD))), substr ( digits ( month (VHREFD)),9)) <= ?
AND VHWHST > '79'
GROUP BY VHCONO, VHWHLO, VHFACI, VODEPT, VHITNO, VHPRNO, MMITDS, VHMFNO, VHRORL, VHWHST, MMSPE1, VHORQT, VHMAQT, a.M9UCOS, CCLOCD, VHREFD, VHORTY,
VHRORN, VHRORL, VHRORC, MMCFI3
ORDER BY YMNTH, VHITNO, VHMFNO, VHCONO, VHFACI, VHPRNO, VHREFD DESC ) AS TEMP
GROUP BY VHITNO, VHWHLO, VHFACI
ORDER BY VHITNO
Then there is the SQL0420 and I really don't know why... 
Best regards,
pilzbug
|
|
03-02-10, 09:34
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
I have several questions.
1) What data type is VHORQT?
2) What DB2 version and OS are you using?
3) What result do you get from this query:
Code:
SELECT VHITNO, VHWHLO, VHFACI
FROM ( SELECT
CAST (VHCONO as DECIMAL(3,0)) as VHCONO,
CAST (VHWHLO as varchar(12)) as VHWHLO,
CAST (VHFACI as varchar(12)) as VHFACI,
CAST (VODEPT as varchar(12)) as PROD_DEP,
CAST (CCLOCD as varchar(12)) as CURR,
CAST ( trim (VHMFNO) as varchar(12)) as VHMFNO,
CAST (VHORTY as varchar(12)) as VHORTY,
CAST (trim (VHPRNO) as varchar(12)) as VHITNO,
CAST (trim (MMITDS) as varchar(64)) as VHITDS,
CAST ( COALESCE (VHWHST,0) as INTEGER ) as VHWHST,
CAST ( COALESCE (MMSPE1,0) as INTEGER ) as MO_FILTER,
CAST ( COALESCE (VHORQT,0) as INTEGER ) as VHORQT,
CAST ( COALESCE (VHMAQT,0) as INTEGER ) as VHMAQT,
CAST ( COALESCE (a.M9UCOS,0) as DECIMAL(10,3)) as MFCOST,
dec2date(VHREFD) as VHREFD,
CAST ( CONCAT ( trim ( char ( year (dec2date(VHREFD)))), substr ( digits ( month (dec2date(VHREFD))),9)) as char(6)) as YMNTH,
CAST ( SUM ( COALESCE (RW_SPAREPARTS,0)) as FLOAT ) as RW_SPAREPARTS,
CAST ( SUM ( COALESCE (RACSHR,0)) as FLOAT ) as RW_LABOUR,
CAST ( SUM ( COALESCE (CEERQT, 0)) as INTEGER ) as ERR_QTY,
CAST ( SUM ( case CEATRC when '1C' then CEERQT else 0 end ) as INTEGER ) as SUM_NONE,
CAST ( SUM ( case CEATRC when '2C' then CEERQT else 0 end ) as INTEGER ) as SUM_SCRAP,
CAST ( SUM ( case CEATRC when '3C' then CEERQT else 0 end ) as INTEGER ) as SUM_REWORK,
CAST ( SUM ( case CEATRC when '4C' then CEERQT else 0 end ) as INTEGER ) as SUM_RANDS,
CAST (( COALESCE (a.M9UCOS,0) * ( SUM ( case CEATRC when '2C' then CEERQT else 0 end ) + SUM ( case CEATRC when '4C' then CEERQT else 0 end )) + SUM ( COALESCE (RW_SPAREPARTS,0)) + SUM ( COALESCE (RACSHR,0))) as FLOAT ) as QCOST,
CAST (( COALESCE (a.M9UCOS,0) * ( SUM ( case CEATRC when '2C' then CEERQT else 0 end ) + SUM ( case CEATRC when '4C' then CEERQT else 0 end ))) as FLOAT ) as SCRAP_COST,
CAST (( SUM ( COALESCE (RW_SPAREPARTS, 0)) + SUM ( COALESCE (RACSHR, 0))) as FLOAT ) as REWORK_COST
FROM MVXCDTA.MWOHED
JOIN MVXCDTA.MITMAS ON (VHCONO=MMCONO AND VHPRNO=MMITNO)
JOIN MVXCDTA.MWOOPE ON (VHCONO=VOCONO AND VHFACI=VOFACI AND VHPRNO=VOPRNO AND VHMFNO=VOMFNO)
LEFT OUTER JOIN PFXCDTA.XQAC2E ON (CECONO=VOCONO AND CEFACI=VOFACI AND CEPRNO=VOPRNO AND CEMFNO=VOMFNO AND CEOPNO=VOOPNO)
JOIN MVXCDTA.MITFAC a ON (VHCONO=a.M9CONO AND a.M9FACI=VHFACI AND a.M9ITNO=VHPRNO)
JOIN MVXCDTA.CMNDIV ON (VHCONO=CCCONO AND CCDIVI!='' AND VHFACI=CCFACI)
left outer join PFXCDTA.XQARWA on (RAOBNO = CEOBID)
left outer join PFXCDTA.XQARSPV1 on (RSOBNO = CEOBID)
WHERE VHCONO = ?
AND VHFACI = ?
AND VHORTY = ?
AND VHWHLO = ?
AND substr (VODEPT,2) = ?
AND CONCAT ( trim ( char ( year (VHREFD))), substr ( digits ( month (VHREFD)),9)) >= ?
AND CONCAT ( trim ( char ( year (VHREFD))), substr ( digits ( month (VHREFD)),9)) <= ?
AND VHWHST > '79'
GROUP BY VHCONO, VHWHLO, VHFACI, VODEPT, VHITNO, VHPRNO, MMITDS, VHMFNO, VHRORL, VHWHST, MMSPE1, CAST ( COALESCE (VHORQT,0) as INTEGER ), VHMAQT, a.M9UCOS, CCLOCD, VHREFD, VHORTY,
VHRORN, VHRORL, VHRORC, MMCFI3
ORDER BY YMNTH, VHITNO, VHMFNO, VHCONO, VHFACI, VHPRNO, VHREFD DESC ) AS TEMP
GROUP BY VHITNO, VHWHLO, VHFACI
ORDER BY VHITNO
Andy
|
|
03-02-10, 09:43
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 13
|
|
Hey Andy,
VHORQT is decimal and I cast it to Integer in my subselect.
My company is using Windows XP and an DB2 V8 I think.
The result is:
VHITNO (part nr.) VHWHLO (stock location) VHFACI (facility)
-----------------------------------------------------------------
000014 001 MA1
...
one row for each productnumber, even if there are more orders. Thats exactly what I want.
Regards,
pilzbug
|
|
03-02-10, 09:51
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
OK, The what do you get with this code?
Code:
SELECT VHITNO, VHWHLO, VHFACI,
SUM (VHORQT) as VHORQT
FROM ( SELECT
CAST (VHCONO as DECIMAL(3,0)) as VHCONO,
CAST (VHWHLO as varchar(12)) as VHWHLO,
CAST (VHFACI as varchar(12)) as VHFACI,
CAST (VODEPT as varchar(12)) as PROD_DEP,
CAST (CCLOCD as varchar(12)) as CURR,
CAST ( trim (VHMFNO) as varchar(12)) as VHMFNO,
CAST (VHORTY as varchar(12)) as VHORTY,
CAST (trim (VHPRNO) as varchar(12)) as VHITNO,
CAST (trim (MMITDS) as varchar(64)) as VHITDS,
CAST ( COALESCE (VHWHST,0) as INTEGER ) as VHWHST,
CAST ( COALESCE (MMSPE1,0) as INTEGER ) as MO_FILTER,
VHORQT,
CAST ( COALESCE (VHMAQT,0) as INTEGER ) as VHMAQT,
CAST ( COALESCE (a.M9UCOS,0) as DECIMAL(10,3)) as MFCOST,
dec2date(VHREFD) as VHREFD,
CAST ( CONCAT ( trim ( char ( year (dec2date(VHREFD)))), substr ( digits ( month (dec2date(VHREFD))),9)) as char(6)) as YMNTH,
CAST ( SUM ( COALESCE (RW_SPAREPARTS,0)) as FLOAT ) as RW_SPAREPARTS,
CAST ( SUM ( COALESCE (RACSHR,0)) as FLOAT ) as RW_LABOUR,
CAST ( SUM ( COALESCE (CEERQT, 0)) as INTEGER ) as ERR_QTY,
CAST ( SUM ( case CEATRC when '1C' then CEERQT else 0 end ) as INTEGER ) as SUM_NONE,
CAST ( SUM ( case CEATRC when '2C' then CEERQT else 0 end ) as INTEGER ) as SUM_SCRAP,
CAST ( SUM ( case CEATRC when '3C' then CEERQT else 0 end ) as INTEGER ) as SUM_REWORK,
CAST ( SUM ( case CEATRC when '4C' then CEERQT else 0 end ) as INTEGER ) as SUM_RANDS,
CAST (( COALESCE (a.M9UCOS,0) * ( SUM ( case CEATRC when '2C' then CEERQT else 0 end ) + SUM ( case CEATRC when '4C' then CEERQT else 0 end )) + SUM ( COALESCE (RW_SPAREPARTS,0)) + SUM ( COALESCE (RACSHR,0))) as FLOAT ) as QCOST,
CAST (( COALESCE (a.M9UCOS,0) * ( SUM ( case CEATRC when '2C' then CEERQT else 0 end ) + SUM ( case CEATRC when '4C' then CEERQT else 0 end ))) as FLOAT ) as SCRAP_COST,
CAST (( SUM ( COALESCE (RW_SPAREPARTS, 0)) + SUM ( COALESCE (RACSHR, 0))) as FLOAT ) as REWORK_COST
FROM MVXCDTA.MWOHED
JOIN MVXCDTA.MITMAS ON (VHCONO=MMCONO AND VHPRNO=MMITNO)
JOIN MVXCDTA.MWOOPE ON (VHCONO=VOCONO AND VHFACI=VOFACI AND VHPRNO=VOPRNO AND VHMFNO=VOMFNO)
LEFT OUTER JOIN PFXCDTA.XQAC2E ON (CECONO=VOCONO AND CEFACI=VOFACI AND CEPRNO=VOPRNO AND CEMFNO=VOMFNO AND CEOPNO=VOOPNO)
JOIN MVXCDTA.MITFAC a ON (VHCONO=a.M9CONO AND a.M9FACI=VHFACI AND a.M9ITNO=VHPRNO)
JOIN MVXCDTA.CMNDIV ON (VHCONO=CCCONO AND CCDIVI!='' AND VHFACI=CCFACI)
left outer join PFXCDTA.XQARWA on (RAOBNO = CEOBID)
left outer join PFXCDTA.XQARSPV1 on (RSOBNO = CEOBID)
WHERE VHCONO = ?
AND VHFACI = ?
AND VHORTY = ?
AND VHWHLO = ?
AND substr (VODEPT,2) = ?
AND CONCAT ( trim ( char ( year (VHREFD))), substr ( digits ( month (VHREFD)),9)) >= ?
AND CONCAT ( trim ( char ( year (VHREFD))), substr ( digits ( month (VHREFD)),9)) <= ?
AND VHWHST > '79'
GROUP BY VHCONO, VHWHLO, VHFACI, VODEPT, VHITNO, VHPRNO, MMITDS, VHMFNO, VHRORL, VHWHST, MMSPE1, VHORQT, VHMAQT, a.M9UCOS, CCLOCD, VHREFD, VHORTY,
VHRORN, VHRORL, VHRORC, MMCFI3
ORDER BY YMNTH, VHITNO, VHMFNO, VHCONO, VHFACI, VHPRNO, VHREFD DESC ) AS TEMP
GROUP BY VHITNO, VHWHLO, VHFACI
ORDER BY VHITNO
Andy
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Sql0420
