Home » SQL & PL/SQL » SQL & PL/SQL » Help in this SQLl (Oracle, 8.1.7, Windows Server 2003)
Help in this SQLl [message #348527] Wed, 17 September 2008 02:25 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Dear All,

I have a sql like this

SELECT INV_NO, INV_DATE, GROSS, MRQ_NO, MRQ_DATE, MRQ_AMT,MRQNO
FROM mps_v_inv_gst;

My current and required output have attached in text format.

Can anyone help me in this regard? Thanks

  • Attachment: OUTPUT.txt
    (Size: 1.65KB, Downloaded 112 times)
Re: Help in this SQLl [message #348534 is a reply to message #348527] Wed, 17 September 2008 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Explain your output.
Provide a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Help in this SQLl [message #348546 is a reply to message #348527] Wed, 17 September 2008 03:32 Go to previous messageGo to next message
Modaya
Messages: 10
Registered: September 2008
Location: Colombo
Junior Member
hi...

try changing your line size. for example SET LINEZISE 120

then try your sql..

regards,
Asela B Razz
Re: Help in this SQLl [message #348556 is a reply to message #348546] Wed, 17 September 2008 04:10 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Create & Insert file as attachment.
Re: Help in this SQLl [message #348560 is a reply to message #348556] Wed, 17 September 2008 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good, now the other points.

Regards
Michel
Re: Help in this SQLl [message #348565 is a reply to message #348527] Wed, 17 September 2008 05:04 Go to previous messageGo to next message
Modaya
Messages: 10
Registered: September 2008
Location: Colombo
Junior Member
hi Kumar..

your sql is wrong i guess...

your select statement :
SELECT INV_NO, INV_DATE, GROSS, MRQ_NO, MRQ_DATE, MRQ_AMT,MRQNO
FROM mps_v_inv_gst;

but your create table statement says..

SQL> desc mps_v_inv_gst;
Name Null? Type
----------------------------------------- -------- -----------

INV_NO VARCHAR2(3)
INV_DATE DATE
GROSS NUMBER
MRQ_NO VARCHAR2(3)
MRQ_DATE DATE
INVNO VARCHAR2(3)
MRQ_GROSS NUMBER
Re: Help in this SQLl [message #348751 is a reply to message #348565] Wed, 17 September 2008 19:40 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi,

Sorry my sql is

SELECT INV_NO, INV_DATE, GROSS, MRQ_NO, MRQ_DATE, MRQ_GROSS,INVNO
FROM mps_v_inv_gst;

The current output and my required output is in the text file attached.
  • Attachment: OUTPUT.txt
    (Size: 1.65KB, Downloaded 101 times)
Re: Help in this SQLl [message #348822 is a reply to message #348751] Thu, 18 September 2008 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still didn't explain your output.

Regards
Michel
Re: Help in this SQLl [message #348837 is a reply to message #348822] Thu, 18 September 2008 01:21 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
hi

I have already attached the current output and my required output in the attached txt file. Can you please see it?
  • Attachment: OUTPUT.txt
    (Size: 1.65KB, Downloaded 71 times)
Re: Help in this SQLl [message #348853 is a reply to message #348837] Thu, 18 September 2008 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 18 September 2008 07:41
You still didn't explain your output.

You post it but you don't EXPLAIN it.
Why each line?
Why this format?
How could we generates this output if we don't understand where it comes from and what are the rules to generate it?

Regards
Michel
Re: Help in this SQLl [message #348861 is a reply to message #348853] Thu, 18 September 2008 02:53 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hello,

When I run the sql I am getting the output like i mentioned in the txt file current output.

I want to have it in one line for each date against the matching inv_no and invno.

If I run the sql even by using order by also it is not coming. Can you help me?
Re: Help in this SQLl [message #348864 is a reply to message #348861] Thu, 18 September 2008 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have at least 2 ways: self-join the table or group by (no,date).

Regards
Michel
Re: Help in this SQLl [message #348868 is a reply to message #348864] Thu, 18 September 2008 03:18 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi,

I tried group by but it asking to group all the columns.

The sql command I gave was

SELECT
INV_NO,INV_DATE,GROSS,MRQ_NO,MRQ_DATE,INVNO,MRQ_GROSS
FROM MPS_V_INV_GST group by inv_no,invno.


Re: Help in this SQLl [message #348874 is a reply to message #348868] Thu, 18 September 2008 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For instance:
SQL> select a.inv_no, a.inv_date, a.gross, b.mrq_no, b.mrq_date, b.mrq_gross
  2  from MPS_V_INV_GST a, MPS_V_INV_GST b
  3  where a.mrq_no is null
  4    and b.inv_no (+) is null
  5    and ( b.invno (+) = a.inv_no and b.mrq_date (+) = a.inv_date )
  6  order by 2, 1
  7  /
INV INV_DATE         GROSS MRQ MRQ_DATE     MRQ_GROSS
--- ----------- ---------- --- ----------- ----------
MOC 01-Sep-2008       5040
MOI 01-Sep-2008     3031.6
MOS 01-Sep-2008    1820.63
MOC 02-Sep-2008     9562.8
MOI 02-Sep-2008     7906.8 MRQ 02-Sep-2008      316.4
MOS 02-Sep-2008   13256.21 MRQ 02-Sep-2008       40.8
MOC 03-Sep-2008    2900.85
MOI 03-Sep-2008    9069.95
MOS 03-Sep-2008    5000.58
MOC 04-Sep-2008     1380.7
MOI 04-Sep-2008     4686.7
MOS 04-Sep-2008    5947.14
MOC 05-Sep-2008     5152.6
MOI 05-Sep-2008     4011.4
MOS 05-Sep-2008    9661.72
MOC 06-Sep-2008     1955.3
MOI 06-Sep-2008       3161
MOS 06-Sep-2008   10711.05
MOC 08-Sep-2008    8467.35
MOI 08-Sep-2008     7799.4
MOS 08-Sep-2008    1463.01
MOC 09-Sep-2008    1406.55
MOI 09-Sep-2008    16019.4
MOS 09-Sep-2008   17397.74 MRQ 09-Sep-2008        136
MOC 10-Sep-2008    2779.75
MOI 10-Sep-2008    7985.14 MRQ 10-Sep-2008     2194.3
MOS 10-Sep-2008   27124.68
MOC 11-Sep-2008     1527.2
MOI 11-Sep-2008     5994.8 MRQ 11-Sep-2008      702.4
MOS 11-Sep-2008   13321.26 MRQ 11-Sep-2008    6455.63
MOC 12-Sep-2008     651.35
MOI 12-Sep-2008   12524.95
MOS 12-Sep-2008   19748.25
MOC 13-Sep-2008     700.25
MOI 13-Sep-2008       1125 MRQ 13-Sep-2008        732
MOS 13-Sep-2008    13415.1
MOC 15-Sep-2008    6358.65
MOI 15-Sep-2008     4545.5
MOS 15-Sep-2008    4376.44 MRQ 15-Sep-2008         63
MOC 16-Sep-2008     2925.8
MOI 16-Sep-2008    2088.45
MOS 16-Sep-2008   10280.16 MRQ 16-Sep-2008    7131.64

42 rows selected.

Regards
Michel
Re: Help in this SQLl [message #348885 is a reply to message #348874] Thu, 18 September 2008 03:59 Go to previous message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Thanks Mr.Michel it works, sorry for any inconvenience caused.
Previous Topic: spool
Next Topic: sql query
Goto Forum:
  


Current Time: Sat Dec 03 22:11:11 CST 2016

Total time taken to generate the page: 0.07533 seconds