| select then union with dummy column (merged 2) [message #426453] |
Thu, 15 October 2009 08:55  |
adev
Messages: 2
Registered: October 2009
Location: south africa
|
Junior Member |
|
|
Hi
The below query works, but is this the best way of doing SQL to get data from the same table but where some transactions must be summarised by po_key by po_account and other transactions you want all the records? Also the use of dummy column 16 char's in length I find restrictive to when column size change could occur.
select tab1.po_key, po_sub_sys as t_type,
tab2.po_account from tab1
join tab2 on tab1.po_key = tab2.po_key
where tab1.po_posted = 'Y' and tab1.po_sub_sys = 'GL'
group by tab1.po_key, po_account
UNION
select tab1.po_key, po_sub_sys as t_type, &
'1234567890123456' as t_account from tab1
where tab1.po_posted = 'Y' and tab1.po_sub_sys = 'PD'
group by tab1.po_key &
order by t_type, tab1.po_key
Thanks
|
|
|
|
|
|
|
|
| select then union with dummy column [message #426463 is a reply to message #426453] |
Thu, 15 October 2009 09:21  |
adev
Messages: 2
Registered: October 2009
Location: south africa
|
Junior Member |
|
|
Hi
The below query works, but is this the best way of doing SQL to get data from the same table but where some transactions must be summarised by po_key by po_account and other transactions you want all the records? Also the use of dummy column as used restricts that when that column size change occurs, this code will need to be altered:
select tab1.po_key, po_sub_sys as t_type,
tab2.po_account from tab1
join tab2 on tab1.po_key = tab2.po_key
where tab1.po_posted = 'Y' and tab1.po_sub_sys = 'GL'
group by tab1.po_key, po_account
UNION
select tab1.po_key, po_sub_sys as t_type, &
'1234567890123456' as t_account from tab1
where tab1.po_posted = 'Y' and tab1.po_sub_sys = 'PD'
group by tab1.po_key &
order by t_type, tab1.po_key
Thanks
|
|
|
|
| Re: select then union with dummy column [message #426465 is a reply to message #426463] |
Thu, 15 October 2009 09:51 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Use a Case statement in your group by clause (and obviously in your select list too).
Here's an example:create table test_089 (gp varchar2(1), acc number, val number);
insert into test_089 values ('G',1,100);
insert into test_089 values ('G',2,110);
insert into test_089 values ('G',3,120);
insert into test_089 values ('S',4,130);
insert into test_089 values ('S',5,140);
select case when gp = 'G' then 'Total' else to_char(acc) end acc
,sum(val)
from test_089
group by case when gp = 'G' then 'Total' else to_char(acc) end; |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
