Home » SQL & PL/SQL » SQL & PL/SQL » EXTRACT DATA (Oracle, 8.1.7, Windows Server 2003)
EXTRACT DATA [message #325397] Fri, 06 June 2008 02:16 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi All,

I have two tables namely MRH, MRJ. I need to extract all datas from MRJ where MRH.RONO=MRJ.RON0 and MRJ.JCODE='T0107'.

I need the output like this

RONO LICNO JCODE PRICE
R001 ABC T0107 20
R001 ABC ZO300 20
R001 ABC V9901 30
R002 XYZ T0107 20
R002 XYZ Z0300 10

I need the output like this. I tried with my SQL knowledge but unable to extract. Can anyone help me? I have attached the table script with insert data as attachment RO_TKP.SQL.



  • Attachment: RO_TMP.SQL
    (Size: 1.81KB, Downloaded 98 times)
Re: EXTRACT DATA [message #325400 is a reply to message #325397] Fri, 06 June 2008 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you already tried.
Post it online and formatted not in an attached file.

Regards
Michel
Re: EXTRACT DATA [message #325402 is a reply to message #325397] Fri, 06 June 2008 02:23 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
where MRH.RONO=MRJ.RON0 and MRJ.JCODE='T0107'

But ... you've already done it, haven't you? It looks like an ordinary join, you know the WHERE clause, so - what's the problem? ./fa/1580/0/
Re: EXTRACT DATA [message #325403 is a reply to message #325397] Fri, 06 June 2008 02:25 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Try This Out:-

SELECT mrj.*,(SELECT licno FROM mrh WHERE   MRH.RONO=mrj.RONO) LICNumber FROM mrj 
Re: EXTRACT DATA [message #325405 is a reply to message #325403] Fri, 06 June 2008 02:33 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Dear All,

I need the data from MRJ for all the RONO only for the JCODE having T0107. Did you see my required output? I tried this method already and also using where clause but unable to get all the datas.

Please see my required output for your reference.
Re: EXTRACT DATA [message #325408 is a reply to message #325397] Fri, 06 June 2008 02:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thanks for posting the create & insert scripts - it helps.

The requirements you have specified do not produce the output you want from the data you have given us.

The requirements you gave give this query:
SQL> select mrj.* 
  2  from   mrj,mrh
  3  where  mrj.rono = mrh.rono
  4  and    mrj.jcode = 'T0107';

RONO       JCODE      PRICE
---------- ----- ----------
R001       T0107         20
R002       T0107         20


To get the output you want, a simple join should do:
SQL> select mrj.* 
  2  from   mrj,mrh
  3  where  mrj.rono = mrh.rono;

RONO       JCODE      PRICE
---------- ----- ----------
R001       T0107         20
R001       Z0300         20
R001       V9901         30
R001       J9901         10
R002       T0107         20
R002       Z0300         10
R003       Z0300         10
Re: EXTRACT DATA [message #325412 is a reply to message #325408] Fri, 06 June 2008 02:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ahhh - I think I have managed to work backwards from your results to derive the question you meant to ask.

I think you are asking: How can I get all the MRJ records that have a RONO for which there is a MRJ record with a code of 'T0107'

You can do this quite simply with a self join of MRJ to itself.
SQL> select mrj1.rono
  2        ,mrh.licno
  3        ,mrj1.jcode
  4        ,mrj1.price
  5  from   mrj mrj1
  6        ,mrj mrj2
  7        ,mrh
  8  where  mrj1.rono = mrh.rono
  9  and    mrj2.jcode = 'T0107'
 10  and    mrj1.rono = mrj2.rono;

RONO       LICNO      JCODE      PRICE
---------- ---------- ----- ----------
R001       ABC        J9901         10
R001       ABC        V9901         30
R001       ABC        Z0300         20
R001       ABC        T0107         20
R002       XYZ        Z0300         10
R002       XYZ        T0107         20

6 rows selected.
Previous Topic: Bug while using Between for Date Fields
Next Topic: Replace a String
Goto Forum:
  


Current Time: Mon Dec 05 07:14:28 CST 2016

Total time taken to generate the page: 0.09179 seconds