Home » SQL & PL/SQL » SQL & PL/SQL » insert a dummy(?) row to the query result (10g on windows 2003 server)
insert a dummy(?) row to the query result [message #422186] Tue, 15 September 2009 00:22 Go to next message
kang
Messages: 89
Registered: November 2007
Member
with data as (
select 'ABC' key1, 'CDE' key2, '1234' val from dual union 
select 'ABC' key1, 'DEF' key2, '1234' val from dual union
select 'ABC' key1, 'XFF' key2, '1234' val from dual union
select 'BCD' key1, 'SDE' key2, '5678' val from dual union 
select 'BCD' key1, 'QEF' key2, '5678' val from dual union
select 'BCD' key1, 'RFF' key2, '5678' val from dual
) select * from data

ABC CDE 1234
ABC DEF 1234
ABC XFF 1234
BCD QEF 5678
BCD RFF 5678
BCD SDE 5678

key1 and key2 is the keys which values are equal.
I want to insert a grp1 row.
so only with key2 and val columns are enough.

ABC ABC 1234 <--
ABC CDE 1234
ABC DEF 1234
ABC XFF 1234
BCD BCD 5678 <--
BCD QEF 5678
BCD RFF 5678
BCD SDE 5678
Re: insert a dummy(?) row to the query result [message #422189 is a reply to message #422186] Tue, 15 September 2009 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use min or max aggregate function on key2 and group by the other columns.

Regards
Michel
Re: insert a dummy(?) row to the query result [message #422206 is a reply to message #422189] Tue, 15 September 2009 01:59 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'm not sure I follow Michel and I have a couple of questions:


  1. Is 'val' always the same for 'key1' or is this coincidence?
  2. Do you need to select an extra for each different value for key1 or only when it doesn't exist yet?


At first glance, I'd probably use a DISTINCT but it depends on the details.

MHE
Re: insert a dummy(?) row to the query result [message #422233 is a reply to message #422186] Tue, 15 September 2009 03:46 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
with data as (
select 'ABC' key1, 'CDE' key2, '1234' val from dual union 
select 'ABC' key1, 'DEF' key2, '1234' val from dual union
select 'ABC' key1, 'XFF' key2, '1234' val from dual union
select 'BCD' key1, 'SDE' key2, '5678' val from dual union 
select 'BCD' key1, 'QEF' key2, '5678' val from dual union
select 'BCD' key1, 'RFF' key2, '5678' val from dual
) 
select * from data
union
select key1,key1,val
from data

Smile

[Updated on: Tue, 15 September 2009 03:47]

Report message to a moderator

Re: insert a dummy(?) row to the query result [message #422236 is a reply to message #422233] Tue, 15 September 2009 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Question Question Question

What does you query returns and what is the relation with OP question?

Regards
Michel

[Updated on: Tue, 15 September 2009 04:23]

Report message to a moderator

Re: insert a dummy(?) row to the query result [message #422243 is a reply to message #422236] Tue, 15 September 2009 04:35 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
ABC ABC 1234 <--
ABC CDE 1234
ABC DEF 1234
ABC XFF 1234
BCD BCD 5678 <--
BCD QEF 5678
BCD RFF 5678
BCD SDE 5678


OP wanted those two extra rows..I thought

[Updated on: Tue, 15 September 2009 04:36]

Report message to a moderator

Re: insert a dummy(?) row to the query result [message #422247 is a reply to message #422243] Tue, 15 September 2009 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah ok! Really not clear as requirements.

Regards
Michel
Re: insert a dummy(?) row to the query result [message #422273 is a reply to message #422186] Tue, 15 September 2009 05:59 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
definitely.
thanks.
Re: insert a dummy(?) row to the query result [message #422287 is a reply to message #422273] Tue, 15 September 2009 06:36 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
did you get what you were looking for??
Previous Topic: System error 0
Next Topic: Dynamic bind variables
Goto Forum:
  


Current Time: Sun Sep 25 04:16:11 CDT 2016

Total time taken to generate the page: 0.05698 seconds