syntax error on or about "union all" in crystal reports 2011

[shelby101]

Hi,

I have the following union query that I am using to access data from a Progress Open Edge using Crystal Reports 2011 and I am getting the error: No of expressions projected on either side of set-op dont match (7582). Both queries work fine independently and I can't see what is wrong with this. Plese help

SELECT "ap_invoice1"."kco", "ap_invoice1"."gan_code", "ap_invoice1"."ain_posted", "ap_invoice1"."avm_num", "ap_invoice1"."ain_amount", "ap_invoice1"."ain_retain", "ap_invoice1"."ain_balance", "ap_invoice1"."job_num", "ap_vendor1"."avm_name", "ap_invoice1"."ain_idate", "ap_invoice1"."ain_supref", "ap_invoice1"."ain_hold", "ap_invoice1"."ain_hreason", "ap_invoice1"."ain_hcode", "ap_invoice1"."ain_void", "ap_invdist1"."job_num", "ap_invdist1"."aid_amount"
FROM {oj ("COINS"."PUB"."ap_invoice" "ap_invoice1" LEFT OUTER JOIN "COINS"."PUB"."ap_vendor" "ap_vendor1" ON ("ap_invoice1"."kco"="ap_vendor1"."kco") AND ("ap_invoice1"."avm_num"="ap_vendor1"."avm_num")) LEFT OUTER JOIN "COINS"."PUB"."ap_invdist" "ap_invdist1" ON (("ap_invoice1"."ain_inv"="ap_invdist1"."ain_inv") AND ("ap_invoice1"."avm_num"="ap_invdist1"."avm_num")) AND ("ap_invoice1"."kco"="ap_invdist1"."kco")}
WHERE "ap_invoice1"."ain_hold"=1 AND "ap_invoice1"."ain_posted"=1 AND "ap_invoice1"."ain_void"=0


UNION ALL

SELECT "sc_certificate1"."cob_line", "sc_certificate1"."cob_num", "sc_certificate1"."gan_code", "sc_certificate1"."job_num", "sc_certificate1"."kco", "sc_certificate1"."sbm_code", "sc_certificate1"."sbp_balance", "sc_certificate1"."sbp_certdate", "sc_certificate1"."sbp_hold", "sc_certificate1"."sbp_hold2", "sc_certificate1"."sbp_ref", "sc_mast1"."sbm_name", "sc_certificate1"."sbp_hreason", "sc_certificate1"."sbp_hreason2"
FROM {oj "COINS"."PUB"."sc_certificate" "sc_certificate1" LEFT OUTER JOIN "COINS"."PUB"."sc_mast" "sc_mast1" ON ("sc_certificate1"."kco"="sc_mast1"."kco") AND ("sc_certificate1"."sbm_code"="sc_mast1"."sbm_code")}
WHERE ("sc_certificate1"."sbp_hold"=1 OR "sc_certificate1"."sbp_hold2"=1)

 

[kray4660]

When you do a union you have to have the same number of fields (preferably the same data type, or other errors are possible)

 

[shelby101]

Ok, I have checked the queries, and added columns where there needed to be a filler, still not working, any thoughts?

SELECT "ap_invoice1"."kco",
"ap_invoice1"."gan_code",
"ap_invoice1"."ain_posted",
"ap_invoice1"."avm_num",
"ap_invoice1"."ain_amount",
"ap_invoice1"."ain_retain",
"ap_invoice1"."ain_balance",
"ap_invoice1"."job_num",
"ap_vendor1"."avm_name",
"ap_invoice1"."ain_idate",
"ap_invoice1"."ain_supref",
"ap_invoice1"."ain_hold",
"ap_invoice1"."ain_hold",
"ap_invoice1"."ain_hreason",
"ap_invoice1"."ain_hcode",
"ap_invoice1"."ain_hcode",
"ap_invoice1"."ain_void",
"ap_invdist1"."job_num",
"ap_invdist1"."aid_amount",
FROM {oj ("COINS"."PUB"."ap_invoice" "ap_invoice1" LEFT OUTER JOIN "COINS"."PUB"."ap_vendor" "ap_vendor1" ON ("ap_invoice1"."kco"="ap_vendor1"."kco") AND ("ap_invoice1"."avm_num"="ap_vendor1"."avm_num")) LEFT OUTER JOIN "COINS"."PUB"."ap_invdist" "ap_invdist1" ON (("ap_invoice1"."ain_inv"="ap_invdist1"."ain_inv") AND ("ap_invoice1"."avm_num"="ap_invdist1"."avm_num")) AND ("ap_invoice1"."kco"="ap_invdist1"."kco")}
WHERE "ap_invoice1"."ain_hold"=1 AND "ap_invoice1"."ain_posted"=1 AND "ap_invoice1"."ain_void"=0

UNION ALL

SELECT "sc_certificate1"."kco",
"sc_certificate1"."gan_code",
"sc_certificate1"."sbp_posted",
"sc_certificate1"."sbm_code",
"sc_certificate1"."sbp_grosstp",
"sc_certificate1"."sbp_claimamt",
"sc_certificate1"."sbp_balance",
"sc_certificate1"."job_num",
"sc_mast1"."sbm_name",
"sc_certificate1"."sbp_certdate",
"sc_certificate1"."sbp_ref",
"sc_certificate1"."sbp_hold",
"sc_certificate1"."sbp_hold2",
"sc_certificate1"."sbp_hreason",
"sc_certificate1"."sbp_hcode",
"sc_certificate1"."sbp_hcode2",
"sc_certificate1"."sbp_void",
"sc_certdist1"."job_num",
"sc_certdist1"."cdb_amount",
FROM {oj "COINS"."PUB"."sc_certificate" "sc_certificate1" LEFT OUTER JOIN "COINS"."PUB"."sc_mast" "sc_mast1" ON ("sc_certificate1"."kco"="sc_mast1"."kco")
AND ("sc_certificate1"."sbm_code"="sc_mast1"."sbm_code")}
WHERE ("sc_certificate1"."sbp_hold"=1 OR "sc_certificate1"."sbp_hold2"=1)

 

[kray4660]

Some that stands out is that you need to remove the comma before the FROM (both queries). Also, but not 100% sure. I think your parenthesis are unbalanced.

 

[hilfy]

Also, you need to make sure that not only do the field counts match across the queries but also that the data types of the corresponding fields match across the queries.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com