Home » Developer & Programmer » Reports & Discoverer » SQL Union All (SQL*Plus: Release 8.0.5.0.0 )
SQL Union All [message #541612] Wed, 01 February 2012 05:43 Go to next message
Hometown
Messages: 35
Registered: October 2010
Location: India
Member
I have the following Union All query. It throws the following error in SQL plus
ERROR at line 27:
ORA-01789: query block has incorrect number of result columns


After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like
(Select distinct c.contact_code


Following is the SQL query

 Select
 tbl_contact.contact_code,
 contact_title
 ||'.'||contact_name contact_name,
 contact_address,
 contact_pcode,
 city_name city_name,
 country_name,
 contact_ph_mob,
 contact_ph_res,
 contact_ph_off,
 contact_email,
 contact_fax,
 donation_date,
 purpose_name,
 Decode(donation_payment_mode,'CH','Cheque','CA','Cash',
 'PO','Postal Order','CC','Credit/Debit Card','DD',
 'Deposit in Bank') payment_mode
 ,appeal_code
 ,donation_book_no
 ,donation_receipt_no
 ,donation_sr_no
 ,campaign_name
 ,tbl_category_detail.category_amount
 ,currency_symbol cur_symbol
From
(Select distinct
 c.contact_code
,c.contact_title||' '||c.contact_name
,c.contact_address
,c.contact_pcode
,ci.city_name 
,cy.country_name
,c.contact_ph_mob
,c.contact_ph_res
,c.contact_ph_off
,c.contact_email
,c.contact_fax
,d.donation_date
,cm.campaign_name
,d.appeal_code
,a.appeal_name
,p.purpose_name
,Decode(d.donation_payment_mode,'CH','Cheque','CA','Cash',
 'PO','Postal Order','CC','Credit/Debit Card','DD',
 'Deposit in Bank') payment_mode
,d.donation_book_no
,d.donation_receipt_no
,d.donation_sr_no
,cd.category_amount
,cu.currency_symbol
From
   tbl_donation d
 , tbl_category_detail cd
 , tbl_contact c
 , tbl_city ci
 , tbl_country cy
 , tbl_currency cu
 , tbl_purpose p
 , tbl_appeal a
 , tbl_campaign cm
 where 1 = 1
 and
 d.contact_code= c.contact_code(+)
 and
 d.donation_code = cd.donation_code(+)
 and
 d.currency_code= cu.currency_code(+)
 and
 c.city_code= ci.city_code(+)
 and
 ci.country_code= cy.country_code(+)
 and
 d.purpose_code = p.purpose_code(+)
 and
 d.appeal_code = a.appeal_code(+)
 and
 d.campaign_code = cm.campaign_code(+)
 UNION ALL
 Select distinct
 c.contact_code
,c.contact_title||' '||c.contact_name
,c.contact_address
,c.contact_pcode
,ci.city_name 
,cy.country_name
,c.contact_ph_mob
,c.contact_ph_res
,c.contact_ph_off
,c.contact_email
,c.contact_fax
,d.direct_debit_code
,dd.dd_start_date
,dd.dd_end_date
,dd.dd_detail_amount
,null donation_date
,cm.campaign_name
,null appeal_code
,null appeal_name
,vp.program_name
,null purpose_name
,null donation_payment_mode
,null donation_book_no
,null donation_receipt_no
,null donation_sr_no
,null category_amount
,cu.currency_symbol
From
 tbl_contact c
,tbl_city ci
,tbl_country cy
,tbl_direct_debit d
,tbl_dd_detail dd
,tbl_campaign cm
,tbl_currency cu
,vew_program vp
 Where 1=1
 and
 c.contact_code= d.contact_code(+)
 and
 d.direct_debit_code = dd.dd_detail_code(+)
 and
 dd.currency_code= cu.currency_code(+)
 and
 c.city_code= ci.city_code(+)
 and
 ci.country_code= cy.country_code(+)
 and
 dd.program_code = vp.program_code(+)
 and
 dd.campaign_code = cm.campaign_code(+) 
 and
 c.contact_code between 240 and 1003
)


Hope for some urgent hint/suggestion.

Thank you
Re: SQL Union All [message #541614 is a reply to message #541612] Wed, 01 February 2012 05:48 Go to previous message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you really need us to count the number of columns in the 2 select lists for you?
There's nothing complicated to working this out, you can and should do it yourself.
Previous Topic: how to change from Portrait to landscape report
Next Topic: unable to install oracle forms
Goto Forum:
  


Current Time: Mon Jul 28 03:38:24 CDT 2014

Total time taken to generate the page: 0.11844 seconds