Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2010
    Posts
    2

    Unanswered: Problem with UNION QUERY

    Hi everybody.
    I'm having an error with this sentence:

    Code:
    SELECT DISTINCT h.BUKRS,h.LIFNR,h.BLART,h.BELNR,h.XBLNR,h.GJAHR,h.BLDAT,h.DOCTYPEID,'C' AS TIPO_LINEA,h.ZLABELID,h.SGTXT,h.ZTEXT1,h.ZTEXT2,'' AS BUZEI,'' AS HKONT,'' AS KOSTL,'' AS PROJK,'' AS WRBTR,'' AS PERNR
    FROM CMSOUTINVH AS h
    INNER JOIN CMSOUTINVD AS d ON  h.BUKRS = d.BUKRS AND
                                   h.LIFNR = d.LIFNR AND
                                   h.BLART = d.BLART AND
                                   h.BELNR = d.BELNR AND
                                   h.XBLNR = d.XBLNR AND
                                   h.GJAHR = d.GJAHR AND
                                   h.BLDAT = d.BLDAT AND
                                   h.DOCTYPEID = d.DOCTYPEID
                                                        
    FETCH FIRST 30 ROWS ONLY                                             
     
    UNION                                                                                       
                                            
    SELECT d2.BUKRS,d2.LIFNR,d2.BLART,d2.BELNR,d2.XBLNR,d2.GJAHR,d2.BLDAT,d2.DOCTYPEID,'L' AS TIPO_LINEA,'' AS ZLABELID,'' AS SGTXT,'' AS ZTEXT1,'' AS ZTEXT2,d2.BUZEI,d2.HKONT,d2.KOSTL,d2.PROJK,d2.WRBTR,d2.PERNR
    FROM CMSOUTINVD AS d2
    WHERE  d2.BUKRS || d2.LIFNR || d2.BLART || d2.BELNR || d2.XBLNR || d2.GJAHR || cast(d2.BLDAT as char(8)) || d2.DOCTYPEID IN 
    
    (SELECT DISTINCT h1.BUKRS || h1.LIFNR || h1.BLART || h1.BELNR || h1.XBLNR || h1.GJAHR || cast(h1.BLDAT as char(8)) || h1.DOCTYPEID
     FROM CMSOUTINVH AS h1
     INNER JOIN CMSOUTINVD AS d1 ON h1.BUKRS = d1.BUKRS AND
                                    h1.LIFNR = d1.LIFNR AND
                                    h1.BLART = d1.BLART AND
                                    h1.BELNR = d1.BELNR AND
                                    h1.XBLNR = d1.XBLNR AND
                                    h1.GJAHR = d1.GJAHR AND
                                    h1.BLDAT = d1.BLDAT AND
                                    h1.DOCTYPEID = d1.DOCTYPEID
     FETCH FIRST 30 ROWS ONLY)
    I'm getting this error:

    [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=SELECT DISTINCT h.BUKRS,h.LIFNR,h;BEGIN-OF-STATEMENT;<query_expr_body>, DRIVER=3.57.82

    If I execute both sentences separetely they work ok. I don't know why UNION is not working.

    How could I find it out?

    Thank u so much
    Regards
    Last edited by cachico; 11-29-10 at 11:31.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    FETCH FIRST applies to the entire result set produced by the SELECT statement. In other words, there can be only one such clause at the very end of the statement. Consider using search conditions (WHERE clauses) or ROW_NUMBER() if you need to limit the result set.

  3. #3
    Join Date
    Nov 2010
    Posts
    2
    Hi.
    Thanks a lot. It was a problem with a decimal field that I was inizialitating with ''. The query now is:

    Code:
    (SELECT DISTINCT h.BUKRS,h.LIFNR,h.BLART,h.BELNR,h.XBLNR,h.GJAHR,h.BLDAT,h.DOCTYPEID,'C' AS TIPO_LINEA,h.ZLABELID,h.SGTXT,h.ZTEXT1,h.ZTEXT2,0 AS BUZEI,'' AS HKONT,'' AS KOSTL,0 AS PROJK,0.00 AS WRBTR,0 AS PERNR
    FROM CMSOUTINVH AS h
    INNER JOIN CMSOUTINVD AS d ON  h.BUKRS = d.BUKRS AND
                                   h.LIFNR = d.LIFNR AND
                                   h.BLART = d.BLART AND
                                   h.BELNR = d.BELNR AND
                                   h.XBLNR = d.XBLNR AND
                                   h.GJAHR = d.GJAHR AND
                                   h.BLDAT = d.BLDAT AND
                                   h.DOCTYPEID = d.DOCTYPEID FETCH FIRST 2 ROWS ONLY)
                                                                                                    
    UNION                                                                                       
                                            
    (SELECT d2.BUKRS,d2.LIFNR,d2.BLART,d2.BELNR,d2.XBLNR,d2.GJAHR,d2.BLDAT,d2.DOCTYPEID,'L' AS TIPO_LINEA,'' AS ZLABELID,'' AS SGTXT,'' AS ZTEXT1,'' AS ZTEXT2,d2.BUZEI,d2.HKONT,d2.KOSTL,d2.PROJK,d2.WRBTR,d2.PERNR
    FROM CMSOUTINVD AS d2
    WHERE  d2.BUKRS || d2.LIFNR || d2.BLART || d2.BELNR || d2.XBLNR || d2.GJAHR || cast(d2.BLDAT as char(8)) || d2.DOCTYPEID IN 
    
    (SELECT DISTINCT h1.BUKRS || h1.LIFNR || h1.BLART || h1.BELNR || h1.XBLNR || h1.GJAHR || cast(h1.BLDAT as char(8)) || h1.DOCTYPEID
     FROM CMSOUTINVH AS h1
     INNER JOIN CMSOUTINVD AS d1 ON h1.BUKRS = d1.BUKRS AND
                                    h1.LIFNR = d1.LIFNR AND
                                    h1.BLART = d1.BLART AND
                                    h1.BELNR = d1.BELNR AND
                                    h1.XBLNR = d1.XBLNR AND
                                    h1.GJAHR = d1.GJAHR AND
                                    h1.BLDAT = d1.BLDAT AND
                                    h1.DOCTYPEID = d1.DOCTYPEID FETCH FIRST 2 ROWS ONLY))
    
    ORDER BY BUKRS,LIFNR,BLART,BELNR,XBLNR,GJAHR,BLDAT,DOCTYPEID,TIPO_LINEA
    The problem is that the UNION it doesn't work correctly. I think is becasuse of FETCH FIRST 2 ROWS.
    If I make the two sentences separetly it works fine but with the UNION I'm losing rows.
    I've attached a document with an example.

    Any idea?
    Thank u a lot.
    Attached Files Attached Files

Posting Permissions

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