Home » Developer & Programmer » Reports & Discoverer » how to use union (report 6i)
| how to use union [message #444828] |
Wed, 24 February 2010 06:59  |
|
|
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 1680 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  |
cookiemonster
Messages: 13919
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 |
|
|
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 |
cookiemonster
Messages: 13919
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 |
|
|
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 |
cookiemonster
Messages: 13919
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 |
|
|
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 |
cookiemonster
Messages: 13919
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 11:23:17 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
