Home » Developer & Programmer » Reports & Discoverer » how to use union (report 6i)
how to use union [message #444828] Wed, 24 February 2010 06:59 Go to next message
lacchhii
Messages: 136
Registered: May 2009
Location: bangalore
Senior Member

HI,

I have used the following query

select a.fin_yr,a.MON_YR,a.grnt_cd,A.DR_CR,a.bud_ori_amt,a.bud_supp_amt,
a.BUD_SURR_AMT,a.BUD_NET_AMT,B.BUD_AMT,b.ORD_NO,b.ORD_DT,
b.MJH_CD,b.SMJH_CD,b.MIH_CD,b.SBH_CD,b.DTLH_CD,b.OBJH_CD,
b.CV_CD,b.PNP_CD
from bud_temp_mon a,reapp_sm b
where a.fin_yr=b.fin_yr
and a.grnt_cd=b.grnt_cd
and a.mjh_cd=b.mjh_cd
and a.smjh_cd=b.smjh_cd
and a.mih_cd=b.mih_cd
and a.sbh_cd=b.sbh_cd
and a.dtlh_cd=b.dtlh_cd
and a.objh_cd=b.objh_cd
and A.CV_CD= B.CV_CD
and A.PNP_CD=B.PNP_CD
and A.DR_CR=B.DR_CR
and a.grnt_cd='03'
GROUP BY a.fin_yr,a.MON_YR,a.grnt_cd,A.DR_CR,a.bud_ori_amt,a.bud_supp_amt,
a.BUD_SURR_AMT,a.BUD_NET_AMT,B.BUD_AMT,b.ORD_NO,b.ORD_DT,
b.MJH_CD,b.SMJH_CD,b.MIH_CD,b.SBH_CD,b.DTLH_CD,b.OBJH_CD,
b.CV_CD,b.PNP_CD
order by grnt_cd,objh_cd


the result of this query is attached as a pdf file. what i get is whenever the column bud_amt has 2 values ( see the highlighted rows in pdf file) the values of other columns gets repeated again which results in the values being totalled twice. I would like to know if union can be used to filter the values .

I would be thankful if the query can be corrected accordingly.



CM: Added code tags, please do so yourself next time - see the orafaq forum guide if you're not sure how.
  • Attachment: reapp.pdf
    (Size: 25.07KB, Downloaded 558 times)

[Updated on: Wed, 24 February 2010 07:15] by Moderator

Report message to a moderator

Re: how to use union [message #444831 is a reply to message #444828] Wed, 24 February 2010 07:20 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
I doubt union would help here but I'm not really sure what you are trying to achieve. Some questions:
1) Why are you selecting far more columns than you are displaying?
2) Why does your query have a group by clause? You're not using any aggregate functions (count, min, sum etc) so you shouldn't need it.
Re: how to use union [message #444832 is a reply to message #444831] Wed, 24 February 2010 07:23 Go to previous messageGo to next message
lacchhii
Messages: 136
Registered: May 2009
Location: bangalore
Senior Member

hi,

i have selected only those columns which i need to display. all the columns when i converted to pdf has been in different pages.

secondly, i tried using group by just to check in case it doesn't duplicate the values of other columns
Re: how to use union [message #444836 is a reply to message #444828] Wed, 24 February 2010 07:46 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Oracle is duplicated nothing here as far as I can tell. You're just getting multiple rows where most (but not all) of the columns have the same values.

I think we're going to need a test case - create table statements, insert statements for data and details of expected output.

You should note that group by in the absence of any aggregate functions does exactly the same thing as distinct.
Re: how to use union [message #444837 is a reply to message #444836] Wed, 24 February 2010 07:58 Go to previous messageGo to next message
lacchhii
Messages: 136
Registered: May 2009
Location: bangalore
Senior Member

hi,

yes , all the column except bud_amt and ord_no has the same value and what i am trying to do is to have one value for all the other column since it adds the second value also.

I think we're going to need a test case - create table statements, insert statements for data and details of expected output.

how do we do this?
Re: how to use union [message #444846 is a reply to message #444828] Wed, 24 February 2010 08:19 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post create table statements for your two tables.
Post insert statements for the tables to give us enough data in each to replicate your problem.
Explain what you want the output to be like.

That said, if bud_amt and ord_no are the only values in reapp_sm that can differ for a given record in bud_temp_mon then this might be what you want:
select a.fin_yr,a.MON_YR,a.grnt_cd,A.DR_CR,a.bud_ori_amt,a.bud_supp_amt,
a.BUD_SURR_AMT,a.BUD_NET_AMT,sum(B.BUD_AMT),sum(b.ORD_NO),b.ORD_DT,
b.MJH_CD,b.SMJH_CD,b.MIH_CD,b.SBH_CD,b.DTLH_CD,b.OBJH_CD,
b.CV_CD,b.PNP_CD
from bud_temp_mon a,reapp_sm b
where a.fin_yr=b.fin_yr
and a.grnt_cd=b.grnt_cd
and a.mjh_cd=b.mjh_cd
and a.smjh_cd=b.smjh_cd
and a.mih_cd=b.mih_cd
and a.sbh_cd=b.sbh_cd
and a.dtlh_cd=b.dtlh_cd
and a.objh_cd=b.objh_cd
and A.CV_CD= B.CV_CD
and A.PNP_CD=B.PNP_CD
and A.DR_CR=B.DR_CR
and a.grnt_cd='03'
GROUP BY a.fin_yr,a.MON_YR,a.grnt_cd,A.DR_CR,a.bud_ori_amt,a.bud_supp_amt,
a.BUD_SURR_AMT,a.BUD_NET_AMT,b.ORD_DT,
b.MJH_CD,b.SMJH_CD,b.MIH_CD,b.SBH_CD,b.DTLH_CD,b.OBJH_CD,
b.CV_CD,b.PNP_CD
order by grnt_cd,objh_cd
Re: how to use union [message #444937 is a reply to message #444846] Wed, 24 February 2010 21:31 Go to previous messageGo to next message
lacchhii
Messages: 136
Registered: May 2009
Location: bangalore
Senior Member

hi,

i have attached the report which i have done as per the query . i cannot sum function for the column bud_amt since i need two seperate values for that column and thats why i am facing this problem.

When i use the query in toad it gives two values but repeats the values for other columns, but when i use it report, it gives an error " fetches more than one value".

Kindly advice me accordingly
Re: how to use union [message #445022 is a reply to message #444828] Thu, 25 February 2010 05:00 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
I still don't understand what you want as an end result.
The behaviour you describe is normal. If you've got two rows where all the columns have the same values apart from two then when you query them you'll see all the columns with duplicate values apart from the two because that's whats in the table.

Maybe you need to move BUD_AMT and ORD_NO into a lower level group than all the other columns so you can base a repeating frame on that. That should stop the other stuff being repeated. You might even need to move them into a seperate query

If that's not what you want then you need to give us a test case along with the expected result so we can work out what needs to be done.
Re: how to use union [message #445031 is a reply to message #444828] Thu, 25 February 2010 05:35 Go to previous message
sushant84vietstar
Messages: 1
Registered: February 2010
Junior Member
Hi,
do one thing , in the last repeating frame where the BUD_AMT field is, modify the break order to none of all colums.
Previous Topic: REP-1401 and rep-0619
Next Topic: Order By Clause use with formula column
Goto Forum:
  


Current Time: Fri Sep 30 19:18:03 CDT 2016

Total time taken to generate the page: 0.14979 seconds