Home » SQL & PL/SQL » SQL & PL/SQL » Need Query Result
Need Query Result [message #199649] Wed, 25 October 2006 10:30 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi all,
i have two tables. they are given below.
create table mns_file(cmf10 varchar2(10),col1 varchar2(10), col2 number);

create table chem_file(cmf10 varchar2(10), cmf10_eq varchar2(10));

insert all
into mns_file values('A','A_VALUE',123)
into mns_file values('B','B_VALUE',456)
into mns_file values('C','C_VALUE',789)
SELECT * FROM DUAL

insert all
into chem_file values('A','Ac1')
into chem_file values('A','Ac9')
into chem_file values('A','Ac5')
into chem_file values('A','Ac4')
into chem_file values('B','Bc1')
into chem_file values('B','Bc2')
into chem_file values('B','Bc8')
into chem_file values('B','Bc4')
into chem_file values('C','Cc1')
into chem_file values('C','Cc7')
into chem_file values('C','Cc3')
into chem_file values('D','Dc1')
SELECT * FROM DUAL

i want the output like given below.
A	A_VALUE		123
Ac1	A_VALUE		123
Ac9	A_VALUE		123
Ac5	A_VALUE		123
Ac4	A_VALUE		123
B	B_VALUE		456
Bc1	B_VALUE		456
Bc2	B_VALUE		456
Bc8	B_VALUE		456
Bc4	B_VALUE		456
C	C_VALUE		789
Cc1	C_VALUE		789
Cc7	C_VALUE		789
Cc3	C_VALUE		789

i hope the question is clear from output itself.
Thanks,
Thangam
Re: Need Query Result [message #199657 is a reply to message #199649] Wed, 25 October 2006 10:45 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you for sample data!

Would something like this be OK?
SELECT m.cmf10, m.col1, m.col2
  FROM mns_file m
UNION
SELECT c.cmf10_eq, m.col1, m.col2
  FROM mns_file m, chem_file c
  WHERE m.cmf10 = c.cmf10
ORDER BY cmf10;
Re: Need Query Result [message #199743 is a reply to message #199657] Thu, 26 October 2006 01:50 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi Littlefoot,
Thanks for your Reply!!!
but still i'm not getting the desired output.
i.e it is getting sorted based on cmf10 since you are using union. but i dont want to be happened.
the output of your query is
SQL> SELECT m.cmf10, m.col1, m.col2
  2    FROM mns_file m
UNION
SELECT c.cmf10_eq, m.col1, m.col2
  FROM mns_file m, chem_file c
  WHERE m.cmf10 = c.cmf10
ORDER BY cmf10;  3    4    5    6    7  

CMF10      COL1             COL2
---------- ---------- ----------
A          A_VALUE           123
Ac1        A_VALUE           123
Ac4        A_VALUE           123
Ac5        A_VALUE           123
Ac9        A_VALUE           123
B          B_VALUE           456
Bc1        B_VALUE           456
Bc2        B_VALUE           456
Bc4        B_VALUE           456
Bc8        B_VALUE           456
C          C_VALUE           789
Cc1        C_VALUE           789
Cc3        C_VALUE           789
Cc7        C_VALUE           789

14 rows selected.

I too, had come up with the same output with following query.
select C.final_cmf, M.col1,M.col2 from 
mns_file M, (select cmf10, cmf10 final_cmf from chem_file union select cmf10,cmf10_eq from chem_file) C
where
M.cmf10=C.cmf10

To be simpler, i want the cmf10 from mns_file then subsequent cmf10_eq from chem_file for corresponding cmf10 in the same order in chem_file.
please check my desired output and actual output that we got from our query.

Thanks,
Thangam
Re: Need Query Result [message #199747 is a reply to message #199743] Thu, 26 October 2006 02:02 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unless you explicitly specify in which order (i.e. using an ORDER BY clause) you want those records to be sorted, you can't expect Oracle to do it for you.

Regarding data you provided, it seems that there's nothing much left here; you can not rely on ROWID, for example, because it does not guarantee that selected records will be sorted the way you inserted them.

Therefore, if you include another column (and use a sequence to populate it, for example), then it would be possible. I wouldn't know how to do it with current sample data set.
Re: Need Query Result [message #199794 is a reply to message #199649] Thu, 26 October 2006 03:57 Go to previous messageGo to next message
mahendramahendra
Messages: 6
Registered: October 2006
Junior Member

my mistake.. not read the thread completly... removed.

[Updated on: Thu, 26 October 2006 04:39]

Report message to a moderator

Re: Need Query Result [message #199803 is a reply to message #199794] Thu, 26 October 2006 04:31 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Mahendramahendra, your query is just another example of plagiarism. Inline view is the same as my query, but you decided to select everything from it once again, ordered by the same column. No change in the output result at all. What was your point, anyway? Because it was all in vain; we both failed to produce output Gold_oracl wanted ./fa/450/0/
Previous Topic: Query output Exist / not exist
Next Topic: Table heading
Goto Forum:
  


Current Time: Thu Dec 08 20:06:36 CST 2016

Total time taken to generate the page: 0.08256 seconds