Help with Creating UNION with SQL from 2 report

[biandinfosec]

I have 2 reports that I have to merge as 1. I am using CR version 8, on Sybase Adaptive server anywhere v6.4, local PC database.

I am putting the entire sql here. can I get help combining these 2 SQl into one SQL using union All? or is Stored procedure recommended? or Should I create 2 VIEWS in the database, then create my report based on the 2 views?

Here is SQL from report 1:

select RepHeader.CUSTOMER_ID,
RepHeader.TELCO_ID,
RepHeader.REP_NAME,
RepHeader.REP_ID,
RepHeader.REP_PHONE,
RepHeader.REP_OFFICE_CODE,
RepHeader.REP_UNIT,
RepHeader.MAINE_IND,
RepHeader.STATUS,
RepHeader.CONTACT,
RepHeader.MARKET_NO,
RepHeader.POC_STREET_ADDRESS,
RepHeader.POC_COMMUNITY,
RepHeader.POC_STATE_ZIP,
RepHeader.CONTACT_NAME,
RepHeader.CONTACT_TN,
RepHeader.CONTACT_TITLE,
RepHeader.CUST_OPEN_DATE_TIME,
RepHeader.BILLING_NAME,
RepHeader.BILLING_COMMUNITY,
RepHeader.BILLING_ATTENTION,
RepHeader.BILLING_STREET_ADDRESS,
RepHeader.BILLING_STATE_ZIP,
RepHeader.BILLING_TN,
RepHeader.QC_ATTENTION,
RepHeader.QC_STREET_ADDRESS,
RepHeader.QC_COMMUNITY,
RepHeader.QC_STATE_ZIP,
RepHeader.MAIN_LISTED_TN,
RepHeader.MAIN_LISTED_NAME,
RepHeader.MAIN_LISTED_COMMUNITY,
RepHeader.MAIN_LISTED_STREET_ADDRESS,
RepHeader.MAIN_LISTED_STATE_ZIP,
RepHeader.SOHO_IND,
RepHeader.OMIT_ADDRESS_IND,
RepCntrctProdBill.DIRECTORY_CODE,
RepCntrctProdBill.DIRECTORY_ISSUE_NUM,
RepCntrctProdBill.ACCOUNT_ID,
RepCntrctProdBill.DIRECTORY_NAME,
RepCntrctProdBill.DIRECTORY_ISSUE_DATE,
RepCntrctProdBill.STATE_ABBREVIATION,
RepCntrctProdBill.BILLING_BASIS,
RepCntrctProdBill.REP_ID as C_ReP_ID,
RepCntrctProdBill.UNIT_ID,
RepCntrctProdBill.DIV_ID,
RepCntrctProdBill.CURR_REP_ID,
RepCntrctProdBill.CURR_REP_UNIT,
RepCntrctProdBill.CURR_REP_OFFICE_CODE,
RepCntrctProdBill.RETIREMENT_STATUS,
RepCntrctItem.DIRECTORY_CODE as I_DIRECTORY_CODE,
RepCntrctItem.DIRECTORY_ISSUE_NUMBER,
RepCntrctItem.ITEM_ID,
RepCntrctItem.ACCOUNT_ID as I_ACCOUNT_ID,
RepCntrctItem.BILLING_SUFFIX,
RepCntrctItem.HEADING_NAME,
RepCntrctItem.MAIN_ITEM_ID,
RepCntrctItem.UDAC_CODE,
RepCntrctItem.ITEM_ACTION_CODE,
RepCntrctItem.ITEM_PI_AMT,
RepCntrctItem.ITEM_NISD_AMT,
RepCntrctItem.TEXT_POINTER,
RepCntrctItem.TRADE_CAPTION_LEVEL_1,
RepCntrctItem.TRADE_CAPTION_LEVEL_2,
RepCntrctItem.TRADE_CAPTION_LEVEL_3,
RepCntrctItem.COOP_NISD_AMT,
RepCntrctItem.RELATED_TYPE,
RepCntrctItem.brand_name,
RepCntrctItem.SECTION_CODE,
RepCntrctItem.SPANISH_HEADING_NAME,
RepCntrctItem.CO_OP_NI_STATUS,
RepCntrctItem.FORCE_PRICE_IND,
RepCntrctItem.DISCOUNT_CODE,
RepCntrctItem.LANGUAGE_TYPE,
RepCntrctItem.HEADING_CODE,
RepCntrctItem.SUB1_HEADING_NAME,
RepCntrctItem.SUB2_HEADING_NAME,
RepCntrctItem.CPY_SHT_STS,
RepCntrctItem.DISC_NAME,
RepCntrctItem.IN_OUT_DATE,
RepCntrctItem.COOP_PLAN_NAME,
RepCntrctItem.OPEN_DATE,
RepCntrctItem.BARTERED_SEEDED_IND,
CANVASS.CANVASS_CODE,CANVASS.CANVASS_NAME,
DIRECTORY_SECTION.SECTION_CODE as D_SECTION_CODE,
DIRECTORY_SECTION.SECTION_FULL_NAME,
DIRECTORY_ISSUE.DIRECTORY_CODE as DI_DIRECTORY_CODE,
DIRECTORY_ISSUE.INFO_TEXT_LINE_1,
DIRECTORY_ISSUE.INFO_TEXT_LINE_2,
DIRECTORY_ISSUE.INFO_TEXT_LINE_3,
DIRECTORY_ISSUE.INFO_TEXT_LINE_4,
RepCntrctListing.LISTING_ID,
RepCntrctListing.LISTED_TN,
RepCntrctListing.LISTING_ALI_CODE,
RepCntrctListing.LISTED_NAME,
RepCntrctListing.LNI,
RepCntrctListing.SCOPING_TYPE,
RepCntrctListing.WIRELESS_IND,
UDAC.rate_ind
from
DBA.RepHeader as RepHeader,DBA.RepCntrctProdBill as RepCntrctProdBill left outer join DBA.DBA.RepCntrctItem as RepCntrctItem on RepCntrctProdBill.CUSTOMER_ID = RepCntrctItem.CUSTOMER_ID and RepCntrctProdBill.DIRECTORY_CODE = RepCntrctItem.DIRECTORY_CODE and RepCntrctProdBill.DIRECTORY_ISSUE_NUM = RepCntrctItem.DIRECTORY_ISSUE_NUMBER and RepCntrctProdBill.MAIN_LISTING_ID = RepCntrctItem.MAIN_LISTING_ID and RepCntrctProdBill.ACCOUNT_ID = RepCntrctItem.ACCOUNT_ID,DBA.RepCntrctProdBill as RepCntrctProdBill left outer join DBA.DBA.Canvass as Canvass on RepCntrctProdBill.Canvass_Code = Canvass.Canvass_Code and RepCntrctProdBill.Canvass_Issue_Number = Canvass.Canvass_Issue_Number,DBA.RepCntrctItem as RepCntrctItem left outer join DBA.RepCntrctListing as RepCntrctListing on RepCntrctItem.CUSTOMER_ID = RepCntrctListing.CUSTOMER_ID and RepCntrctItem.LISTING_ID = RepCntrctListing.LISTING_ID,DBA.RepCntrctItem as RepCntrctItem left outer join DBA.UDAC as UDAC on RepCntrctItem.UDAC_CODE = UDAC.UDAC_CODE,DBA.RepCntrctItem as RepCntrctItem left outer join DBA.DIRECTORY_SECTION as DIRECTORY_SECTION on directory_section.effective_date = (select Max(ds.effective_date) from DBA.DIRECTORY_SECTION as ds join DBA.RepCntrctItem as rci on ds.DIRECTORY_CODE = rci.DIRECTORY_CODE and ds.section_code = rci.SECTION_CODE and ds.effective_date <= now(*)) and RepCntrctItem.SECTION_CODE = DIRECTORY_SECTION.section_code and RepCntrctItem.DIRECTORY_CODE = DIRECTORY_SECTION.DIRECTORY_CODE,DBA.RepCntrctProdBill as RepCntrctProdBill left outer join DBA.DIRECTORY_ISSUE as DIRECTORY_ISSUE on RepCntrctProdBill.DIRECTORY_CODE = DIRECTORY_ISSUE.DIRECTORY_CODE and RepCntrctProdBill.DIRECTORY_ISSUE_NUM = DIRECTORY_ISSUE.DIRECTORY_ISSUE_NUMBER,DBA.RepCntrctItem as RepCntrctItem left outer join DBA.DISCOUNT_PERCENTAGE as DISCOUNT_PERCENTAGE on RepCntrctItem.DISCOUNT_CODE = DISCOUNT_PERCENTAGE.DISCOUNT_CODE and RepCntrctItem.DISCOUNT_PHASE = DISCOUNT_PERCENTAGE.NUMBER_OF_PHASES where
RepHeader.CUSTOMER_ID = RepCntrctProdBill.CUSTOMER_ID

SQL from report 2:

select REPORT_HEADER.CUSTOMER_ID,
REPORT_HEADER.BUSINESS_TN,
REPORT_HEADER.BUSINESS_NAME,
REPORT_HEADER.ZIP_CODE,
REPORT_HEADER.COMMUNITY_NAME,
REPORT_HEADER.STATE,
REPORT_HEADER.STREET_NAME,
REPORT_HEADER.HOUSE_NO,
REPORT_HEADER.EMPLOYEE_NAME,
REPORT_HEADER.EMPLOYEE_ID,
REPORT_HEADER.HOUSE_NUMBER_SUFFIX,
REPORT_HEADER.STREET_DIRECTIONAL,
REPORT_HEADER.POST_DIRECTIONAL,
REPORT_HEADER.PO_BOX,
REPORT_HEADER.APARTMENT,
REPORT_HEADER.CONTACT_NAME,
REPORT_HEADER.MAIN_HEADING,
REPORT_HEADER.canvass_code,
REPORT_HEADER.MAINE_IND,
REPORT_HEADER.CANVASS_NAME,
DIRECTORY_SUMMARY_REPORT.DIRECTORY_NAME,
DIRECTORY_SUMMARY_REPORT.LISTED_TN,
DIRECTORY_SUMMARY_REPORT.NISD_AMT,
DIRECTORY_SUMMARY_REPORT.PUB_DATE,
DIRECTORY_SUMMARY_REPORT.TELCO_CLOSE_DATE,
DIRECTORY_SUMMARY_REPORT.BOTS_AMT,
DIRECTORY_SUMMARY_REPORT.DIRECTORY_CODE,
DIRSUM_FOOTER_REPORT.AUTHORIZED_BY,
DIRSUM_FOOTER_REPORT.SIGNER_NAME,
DIRSUM_FOOTER_REPORT.SIGNER_TITLE,
DIRSUM_FOOTER_REPORT.OWNER,
DIRSUM_FOOTER_REPORT.CONTRACT_DATE,
DIRSUM_FOOTER_REPORT.CONTACT_TN,
DIRSUM_FOOTER_REPORT.REP_TN,
DIRSUM_FOOTER_REPORT.REP_OFFICE,
DIRSUM_FOOTER_REPORT.SIGNER_VERIFICATION
from
DBA.REPORT_HEADER as REPORT_HEADER,
DBA.DIRECTORY_SUMMARY_REPORT as DIRECTORY_SUMMARY_REPORT,
DBA.DIRSUM_FOOTER_REPORT as DIRSUM_FOOTER_REPORT
where
REPORT_HEADER.CUSTOMER_ID = DIRECTORY_SUMMARY_REPORT.CUSTOMER_ID and
DIRECTORY_SUMMARY_REPORT.CUSTOMER_ID = DIRSUM_FOOTER_REPORT.CUSTOMER_ID

 

[MisterMo]

If you are getting different fields from the two queries then it might be best to use subreports each with one SQL statement.


Mo

 

[biandinfosec]

That is what my thoughts has been, but I have read through several threads on this site that seems to bash sub reports in favor of union or stored proceedure. I was hoping someone can help me out towards that direction. In the meantime I have created 2 views from these queries and used that as 2 tables to merge the 2 reports.

-b

 

[MisterMo]

There is nothing wrong in using supreports you can use your views with them too.

you can't use unions because you are getting more fields from the first query and the union need the same number of fields and of the same type.

a stored procedure will have to return an exact number of fields, so you will encounter the same problem because the 2 query have different number of fields returned.

if you want to use these option I suggest that you first sort out what must be returned from your 2 queries even if is just blank fields or null values from the query with less fields.

Mo