Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Sql0420

  1. #1
    Join Date
    Mar 2010
    Posts
    13

    Unhappy Unanswered: 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

  2. #2
    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

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.

  4. #4
    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.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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 ..

    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.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  7. #7
    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 07:03.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please follow sathyaram_s advice.

    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.

  9. #9
    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

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  11. #11
    Join Date
    Mar 2010
    Posts
    13
    Hi tonkuma,

    thank you very much, I will try it an tell you the result.

  12. #12
    Join Date
    Mar 2010
    Posts
    13

    Unhappy

    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

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  14. #14
    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

  15. #15
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •