Home » SQL & PL/SQL » SQL & PL/SQL » Need Help for SQL Query
Need Help for SQL Query [message #202959] Mon, 13 November 2006 07:29 Go to next message
numan_ocp
Messages: 9
Registered: August 2006
Junior Member
Plz i need help on the following query

SELECT distinct vendor_id,to_char(invoice_date,'MON-YY')Month,to_number(to_char(to_date(to_char(invoice_date,'MON-YY'),'MON-YY'),'j'))sort
FROM ap_invoices_all aia
WHERE aia.invoice_date between :startdate and :enddate
and vendor_id like :vendorid

Now i want only single occurance of vendor id, say if vendor id exist in 3 months for which i m running query, i only need single line of vendor id.

hope u guys understand,
plz urgent help need
Re: Need Help for SQL Query [message #202964 is a reply to message #202959] Mon, 13 November 2006 07:50 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does it mean that in, let's say, 3 months, this vendor can have more than one records in a table but you'd like to select only one of them?

If so, which one? First or last (ordered by some_column)? Some other condition?
Re: Need Help for SQL Query [message #202971 is a reply to message #202964] Mon, 13 November 2006 08:03 Go to previous messageGo to next message
numan_ocp
Messages: 9
Registered: August 2006
Junior Member
May this example help to understand,

Actually i m trying to get supplier balance record.for this i m developing matrix report. Month on column side and Vendor name on Row side. Now the problem is if i include vendor id in main query and run report for JAN-06 to MAR-06 then if one vendor has invoice in each month the it'll give.

Vendor id Month
1 JAN-06
1 FEB-06
1 MAR-06
I need record like below..

Vendor id Month
1 JAN-06
FEB-06
MAR-06
Hope u understand now,...

by using this i can calculate vendor balance on each month.
Re: Need Help for SQL Query [message #202973 is a reply to message #202971] Mon, 13 November 2006 08:05 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Try break column command, which works in sql * plus.

By
Vamsi.
Re: Need Help for SQL Query [message #202980 is a reply to message #202973] Mon, 13 November 2006 08:14 Go to previous messageGo to next message
numan_ocp
Messages: 9
Registered: August 2006
Junior Member
can u plz give the example query and its result
Re: Need Help for SQL Query [message #202987 is a reply to message #202980] Mon, 13 November 2006 08:20 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
SQL> select deptno,ename from emp where deptno = 10;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER

SQL> break on deptno
SQL> /

    DEPTNO ENAME
---------- ----------
        10 CLARK
           KING
           MILLER


By
Vamsi
Re: Need Help for SQL Query [message #202988 is a reply to message #202987] Mon, 13 November 2006 08:21 Go to previous messageGo to next message
numan_ocp
Messages: 9
Registered: August 2006
Junior Member
Its better if use to only query the data, but i need such situation in the report main query...

??????????
Re: Need Help for SQL Query [message #202990 is a reply to message #202988] Mon, 13 November 2006 08:25 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Please elaborate your requirement.

By
Vamsi.
Re: Need Help for SQL Query [message #202991 is a reply to message #202990] Mon, 13 November 2006 08:28 Go to previous messageGo to next message
numan_ocp
Messages: 9
Registered: August 2006
Junior Member
plz give me ur hotmail address

i chat right now on this issue
Re: Need Help for SQL Query [message #202992 is a reply to message #202991] Mon, 13 November 2006 08:31 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Sorry! I'm rushing out for the day...

By
Vamsi

PS: Also you can see all mail ids at the top right of each message.

[Updated on: Mon, 13 November 2006 08:34]

Report message to a moderator

Re: Need Help for SQL Query [message #202994 is a reply to message #202992] Mon, 13 November 2006 08:34 Go to previous messageGo to next message
numan_ocp
Messages: 9
Registered: August 2006
Junior Member
ok,
but give me though for future, i'll contact u tomorrow
Re: Need Help for SQL Query [message #203127 is a reply to message #202959] Tue, 14 November 2006 00:08 Go to previous messageGo to next message
freethinking
Messages: 10
Registered: November 2006
Junior Member
hi buddy try this
SELECT CASE
WHEN EMP_ROW = 1 THEN
DEPTNO
ELSE
' '
END,
ENAME
FROM (SELECT DEPTNO, ENAME, ROWNUM AS EMP_ROW FROM EMP WHERE DEPTNO = 10)
Re: Need Help for SQL Query [message #205403 is a reply to message #202959] Fri, 24 November 2006 09:15 Go to previous messageGo to next message
pravisri
Messages: 10
Registered: March 2005
Location: Manchester
Junior Member
Just going forward from freethinking's query

Try this
SELECT CASE
WHEN EMP_ROW = 1 THEN
DEPTNO
ELSE
NULL
END,
ENAME
FROM (SELECT DEPTNO, ENAME, ROWNUM AS EMP_ROW FROM EMP)

well done freethinking
Re: Need Help for SQL Query [message #206247 is a reply to message #202959] Wed, 29 November 2006 05:39 Go to previous message
akshaye_2win
Messages: 2
Registered: November 2006
Junior Member
Just use break on statement

For example
if table contains

Dept Staff
---- ----
10 E78929
10 183575
10 E15591


syntax:
break on dept
then write your sql script as usual
Previous Topic: email attachment >32k in 10g
Next Topic: update flag with first occurence of a column
Goto Forum:
  


Current Time: Fri Dec 09 23:15:16 CST 2016

Total time taken to generate the page: 0.09244 seconds