Problem with UNION QUERY

cachico · #1 (**permalink**) 11-29-10, 10:17

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

n_i · #2 (**permalink**) 11-29-10, 12:19

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.

cachico · #3 (**permalink**) 11-30-10, 04:33

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech