Unions in a query

system · October 1, 2008, 8:33am

Hi I am facing a problem where my result will come through Unions in a query.
The query is as follows:

select sum(call_r351_conc_xy_ind),
sum(CALL_R353_CONC_REP_IND),
Sum(nvl(CALL_R354_QSB_IND,0)),
Sum(nvl(CALL_C100_IND,0)),
Sum(nvl(CALL_C101_IND,0)),
Sum(nvl(CALL_C102_IND,0)),
call_app_type
from rcoll_data
where call_answer_office_name in (‘QS800’,‘ERIC’,‘RSC’,‘BSC’,‘RCOLL’,‘BCOLL’,‘PBAR’,‘VC’,‘CEPC’,‘BEPC’,‘RSC’)
AND CALL_ANSWER_OFFICE_LANGUAGE = ‘ENG’
and Call_app_type <> ‘DUMMY’
AND
(
CALL_START_DATETIME >= trunc(to_date(:data_date, ‘MM/DD/YYYY’))
and CALL_START_DATETIME < trunc(to_date(:data_date, ‘MM/DD/YYYY’) + 1)
)
group by call_app_type
union
select sum(call_r351_conc_xy_ind),
sum(CALL_R353_CONC_REP_IND),
Sum(nvl(CALL_R354_QSB_IND,0)),
Sum(nvl(CALL_C100_IND,0)),
Sum(nvl(CALL_C101_IND,0)),
Sum(nvl(CALL_C102_IND,0)),
call_app_type
from bcoll_data
where call_answer_office_name in (‘QS800’,‘ERIC’,‘RSC’,‘BSC’,‘RCOLL’,‘BCOLL’,‘PBAR’,‘VC’,‘CEPC’,‘BEPC’,‘RSC’)
AND CALL_ANSWER_OFFICE_LANGUAGE = ‘ENG’
and Call_app_type <> ‘DUMMY’
AND
(
CALL_START_DATETIME >= trunc(to_date(:data_date, ‘MM/DD/YYYY’))
and CALL_START_DATETIME < trunc(to_date(:data_date, ‘MM/DD/YYYY’) + 1)
)
group by call_app_type

For this i have included both the Tables RCOLL_Data and BCOLL_Data in my universe and have made the corresponding Objects also.
But when in the Reporter i drag these columns…i am not able to use union in the query…I don’t know the option to use unions…is it done at teh reporter level only or we do something at the designer level…Please help

smiling_puneet (BOB member since 2006-12-04)

system · October 1, 2008, 10:05am

Hi,

In query panel of reporter, you will find one icon to Combine Queries…use that to create union queries.

soni_ak (BOB member since 2008-09-15)

system · October 1, 2008, 10:31am

Thanks a lot…But now my problem is that i have a no of unions in my query around 12…but Business Objects doesn’t allow me to have more that eight Unions…anyhelp in this regard… any work around on this limitation of Business Objects

smiling_puneet (BOB member since 2006-12-04)

system · October 1, 2008, 8:04pm

You are right, You can get only 8 unions through the reporter.

I have not come across any situation where in I had to use more than 8 unions. If that is the case, Look into options of creating a view in the database, including it in the universe and creating objects, Or create a derived table in the universe and use it.

Hope that helps.

chander165 (BOB member since 2005-12-20)

system · October 3, 2008, 3:50am

thanx for the help…
Now when i try to create a derived tabel at the universe level…it throws me an error:

"Each Calculated column must have an explicit name "

I simply clicked on Insert Menu> derived Tables.
then i pasted my entire query in it which was:

(
SELECT
Sum(nvl(RCOLL_DATA.CALL_C102_IND,0)),
Sum(nvl(RCOLL_DATA.CALL_C101_IND,0)),
Sum(nvl(RCOLL_DATA.CALL_C100_IND,0)),
Sum(nvl(RCOLL_DATA.CALL_R354_QSB_IND,0)),
sum(RCOLL_DATA.CALL_R353_CONC_REP_IND),
sum(RCOLL_DATA.call_r351_conc_xy_ind),
TO_CHAR(TO_DATE(RCOLL_DATA.CALL_DATE, ‘YYYY-MM-DD’),‘MM/YYYY’),
RCOLL_DATA.CALL_APP_TYPE
FROM
RCOLL_DATA
WHERE
(
RCOLL_DATA.CALL_ANSWER_OFFICE_NAME IN (‘QS800’, ‘ERIC’, ‘RSC’, ‘BSC’, ‘RCOLL’, ‘BCOLL’, ‘PBAR’, ‘VC’, ‘CEPC’, ‘BEPC’, ‘RSC’)
AND RCOLL_DATA.CALL_ANSWER_OFFICE_LANGUAGE = ‘ENG’
AND RCOLL_DATA.CALL_APP_TYPE != ‘DUMMY’
AND TO_DATE((TO_CHAR(TO_DATE(RCOLL_DATA.CALL_DATE, ‘YYYY-MM-DD’),‘MM/YYYY’)),‘MM/YYYY’) BETWEEN @prompt(‘Enter Beginning Date (MM/YYYY):’,‘D’,free) AND @prompt(‘Enter Ending Date (MM/YYYY):’,‘D’,free)
)
GROUP BY
TO_CHAR(TO_DATE(RCOLL_DATA.CALL_DATE, ‘YYYY-MM-DD’),‘MM/YYYY’),
RCOLL_DATA.CALL_APP_TYPE
UNION
(
SELECT
Sum(nvl(BCOLL_DATA.CALL_C102_IND,0)),
Sum(nvl(BCOLL_DATA.CALL_C101_IND,0)),
Sum(nvl(BCOLL_DATA.CALL_C100_IND,0)),
Sum(nvl(BCOLL_DATA.CALL_R354_QSB_IND,0)),
sum(BCOLL_DATA.CALL_R353_CONC_REP_IND),
sum(BCOLL_DATA.call_r351_conc_xy_ind),
BCOLL_DATA.CALL_APP_TYPE,
TO_CHAR(TO_DATE(BCOLL_DATA.CALL_DATE, ‘YYYY-MM-DD’),‘MM/YYYY’)
FROM
BCOLL_DATA
WHERE
(
BCOLL_DATA.CALL_ANSWER_OFFICE_LANGUAGE IN (‘QS800’, ‘ERIC’, ‘RSC’, ‘BSC’, ‘RCOLL’, ‘BCOLL’, ‘PBAR’, ‘VC’, ‘CEPC’, ‘BEPC’, ‘RSC’)
AND BCOLL_DATA.CALL_ANSWER_OFFICE_NAME = ‘ENG’
AND BCOLL_DATA.CALL_APP_TYPE != ‘DUMMY’
AND TO_DATE((TO_CHAR(TO_DATE(BCOLL_DATA.CALL_DATE, ‘YYYY-MM-DD’),‘MM/YYYY’)),‘MM/YYYY’) BETWEEN @prompt(‘Enter Beginning Date (MM/YYYY):’,‘D’,free) AND @prompt(‘Enter Ending Date (MM/YYYY):’,‘D’,free)
)
GROUP BY
BCOLL_DATA.CALL_APP_TYPE,
TO_CHAR(TO_DATE(BCOLL_DATA.CALL_DATE, ‘YYYY-MM-DD’),‘MM/YYYY’)

Please let me know where i am wrong and what should i do to correct it.
Also here i have mentioned just one union…i have 12 unions to be done

smiling_puneet (BOB member since 2006-12-04)

system · October 3, 2008, 3:55am

Hi,

Just give an explicit column name for each of the calculated columns for example

Select Sum(ABC) as ABC
From Test

Thanks,
Pragna

Pragna (BOB member since 2007-07-25)

system · October 3, 2008, 7:03am

i have made a derived table Table 1 as follows:

(SELECT
Sum(nvl(RCOLL_DATA.CALL_C102_IND,0)) as SUM1,
Sum(nvl(RCOLL_DATA.CALL_C101_IND,0)) as SUM2,
Sum(nvl(RCOLL_DATA.CALL_C100_IND,0)) as SUM3,
Sum(nvl(RCOLL_DATA.CALL_R354_QSB_IND,0)) as SUM4,
sum(RCOLL_DATA.CALL_R353_CONC_REP_IND) as SUM5,
sum(RCOLL_DATA.call_r351_conc_xy_ind) as SUM6,
TO_CHAR(TO_DATE(RCOLL_DATA.CALL_DATE, ‘YYYY-MM-DD’),‘MM/YYYY’) as FormattedMonth,
RCOLL_DATA.CALL_APP_TYPE
FROM
RCOLL_DATA
WHERE
(
RCOLL_DATA.CALL_ANSWER_OFFICE_NAME IN (‘QS800’, ‘ERIC’, ‘RSC’, ‘BSC’, ‘RCOLL’, ‘BCOLL’, ‘PBAR’, ‘VC’, ‘CEPC’, ‘BEPC’,

‘RSC’)
AND RCOLL_DATA.CALL_ANSWER_OFFICE_LANGUAGE = ‘ENG’
AND RCOLL_DATA.CALL_APP_TYPE != ‘DUMMY’
AND TO_DATE((TO_CHAR(TO_DATE(RCOLL_DATA.CALL_DATE, ‘YYYY-MM-DD’),‘MM/YYYY’)),‘MM/YYYY’) BETWEEN @prompt('Enter Beginning

Date (MM/YYYY):’,‘D’,free) AND @prompt(‘Enter Ending Date (MM/YYYY):’,‘D’,free)
)
GROUP BY
TO_CHAR(TO_DATE(RCOLL_DATA.CALL_DATE, ‘YYYY-MM-DD’),‘MM/YYYY’),
RCOLL_DATA.CALL_APP_TYPE
)
UNION
(
SELECT
Sum(nvl(VC_DATA.CALL_C102_IND,0)) as SUM1,
Sum(nvl(VC_DATA.CALL_C101_IND,0)) as SUM2,
Sum(nvl(VC_DATA.CALL_C100_IND,0)) as SUM3,
Sum(nvl(VC_DATA.CALL_R354_QSB_IND,0)) as SUM4,
sum(VC_DATA.CALL_R353_CONC_REP_IND) as SUM5,
sum(VC_DATA.call_r351_conc_xy_ind) as SUM6,
VC_DATA.CALL_APP_TYPE,
TO_CHAR(TO_DATE(VC_DATA.CALL_DATE, ‘YYYY-MM-DD’),‘MM/YYYY’) as FormattedMonth
FROM
VC_DATA
WHERE
(
VC_DATA.CALL_ANSWER_OFFICE_LANGUAGE IN (‘QS800’, ‘ERIC’, ‘RSC’, ‘BSC’, ‘RCOLL’, ‘BCOLL’, ‘PBAR’, ‘VC’, ‘CEPC’, ‘BEPC’,

‘RSC’)
AND VC_DATA.CALL_ANSWER_OFFICE_NAME = ‘ENG’
AND VC_DATA.CALL_APP_TYPE != ‘DUMMY’
AND TO_DATE((TO_CHAR(TO_DATE(VC_DATA.CALL_DATE, ‘YYYY-MM-DD’),‘MM/YYYY’)),‘MM/YYYY’) BETWEEN @prompt('Enter Beginning

Date (MM/YYYY):’,‘D’,free) AND @prompt(‘Enter Ending Date (MM/YYYY):’,‘D’,free)
)
GROUP BY
VC_DATA.CALL_APP_TYPE,
TO_CHAR(TO_DATE(VC_DATA.CALL_DATE, ‘YYYY-MM-DD’),‘MM/YYYY’)
)

But how do i make objects on this?
i want the entire thing in my query.

also when i just export this universe…this derived table doesn’t come along and doesn’t appear in teh query panel .

Please help me out i have never evr worked on derived tables

smiling_puneet (BOB member since 2006-12-04)

system · October 3, 2008, 8:41am

Hi

Just name the derived table as per your requirement and drag the derived table to the Classes and objects pane in the Universe structure…The objects would get created in that way…Save the universe after this and export it to the repository…when you import the universe the Derived table should be in place

Pragna (BOB member since 2007-07-25)

system · October 3, 2008, 9:11am

hi i did what you have written…but that didn’t help… as you can see named the columns as Sum1 , Sum2…and so on…at fiorst i named every column in each query as Sum1, sum2 etc…this only gave me Sum1 - Sum6 in that table. then i renamed them differently…like for RCOll_Data i named the columns as Sum1,Sum2, Sum3…etc and For VC_Data i gave them the names as Sum7, Sum8…and so on…but when save it…it only gives me columns Sum1 to Sum6…
I think the union thing is not working here…
or let me know incase i am doing something wrong

smiling_puneet (BOB member since 2006-12-04)

system · October 3, 2008, 9:32am

Hi,

Union query combines data from respective columns of both the queries and displays in single column, for example- in your code it will combine SUM1 of Query 1 with SUM1 of Query 2 and display data of both the columns in a single column say SUM1.

Even if you give different names to columns in both queries is will return only one column each for respective matching columns of both the queries.

If you want to have different columns for each query then why are you applying union??

soni_ak (BOB member since 2008-09-15)