Home » SQL & PL/SQL » SQL & PL/SQL » select then union with dummy column (merged 2)
select then union with dummy column (merged 2) [message #426453] Thu, 15 October 2009 08:55 Go to next message
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
Re: select then union with dummy column [message #426456 is a reply to message #426453] Thu, 15 October 2009 08:58 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Can't you just use an outer join?

And I'm not sure why you need to make the dummy column 16 chars long and why you're worried about size changes - can't you just use null?
Re: select then union with dummy column [message #426457 is a reply to message #426453] Thu, 15 October 2009 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you put a simple NULL in the dummy column?
And what is the problem with the length?

Regards
Michel
select then union with dummy column [message #426463 is a reply to message #426453] Thu, 15 October 2009 09:21 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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;
Re: select then union with dummy column [message #426468 is a reply to message #426465] Thu, 15 October 2009 10:07 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But one query takes its data from 2 tables and the other from only one.

Regards
Michel
Previous Topic: INSERT A RECORD
Next Topic: join function
Goto Forum:
  


Current Time: Tue Sep 27 04:15:31 CDT 2016

Total time taken to generate the page: 0.24124 seconds