Home » SQL & PL/SQL » SQL & PL/SQL » SQL
SQL [message #224776] Thu, 15 March 2007 07:48 Go to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Hi,

CUSTOMER_ID Amount remark ENTDATE
5679991 200 First Call TopUp 16/08/2006
5679991 100.91 Roaming Rental 28/08/2006
5679991 -6.74 Roaming Rental 02/09/2006
5679991 -10.19 SMS Roaming 05/09/2006
5933365 100 First Call TopUp 11/10/2006
5933365 50.01 E-Topup Recharge 16/10/2006
5933365 -3.07 MAUJ-POLYPHONIC_WAP 20/10/2006
5933365 17.29 E-Topup Recharge 06/01/2007
5933365 0 No event 10/01/2007
5933365 .23 GPRS Charging 18/01/2007
5933365 -.07 GPRS Charging 18/01/2007
5933365 15.75 E-Topup Recharge 20/01/2007
5933365 -16.25 CallerTune 21/01/2007
5933365 0 No event 09/02/2007
5933365 24.7 E-Topup Recharge 12/02/2007
5933365 -29.3 CallerTune 20/02/2007
5933365 -14.37 E-Topup Recharge 05/03/2007

OUTPUT below of Last Positive
5679991 -6.74 Roaming Rental 02/09/2006
5679991 -10.19 SMS Roaming 05/09/2006
5933365 -29.3 CallerTune 20/02/2007
5933365 -14.37 E-Topup Recharge 05/03/2007


Please help for the same i need all rows after last positive amount.


Thanks
Arvind
Re: SQL [message #224791 is a reply to message #224776] Thu, 15 March 2007 08:39 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, format sample data using CODE tags.

Also, post questions in appropriate forum. This is about SQL, not Forms.
Re: SQL [message #224804 is a reply to message #224776] Thu, 15 March 2007 09:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
<Standard Gripe> No create table statments, no insert statements</Standard Gripe>
create table test_37 (CUSTOMER_ID number, Amount number ,remark varchar2(60), ENTDATE date);

alter session set nls_date_format='dd/mm/yyyy';

insert into test_37 values (5679991, 200    ,'First Call TopUp'    ,'16/08/2006');
insert into test_37 values (5679991, 100.91 ,'Roaming Rental'      ,'28/08/2006');
insert into test_37 values (5679991, -6.74  ,'Roaming Rental'      ,'02/09/2006');
insert into test_37 values (5679991, -10.19 ,'SMS Roaming'         ,'05/09/2006');
insert into test_37 values (5933365, 100    ,'First Call TopUp'    ,'11/10/2006');
insert into test_37 values (5933365, 50.01  ,'E-Topup Recharge'    ,'16/10/2006');
insert into test_37 values (5933365, -3.07  ,'MAUJ-POLYPHONIC_WAP' ,'20/10/2006');
insert into test_37 values (5933365, 17.29  ,'E-Topup Recharge'    ,'06/01/2007');
insert into test_37 values (5933365, 0      ,'No event'            ,'10/01/2007');
insert into test_37 values (5933365, .23    ,'GPRS Charging'       ,'18/01/2007');
insert into test_37 values (5933365, -.07   ,'GPRS Charging'       ,'18/01/2007');
insert into test_37 values (5933365, 15.75  ,'E-Topup Recharge'    ,'20/01/2007');
insert into test_37 values (5933365, -16.25 ,'CallerTune'          ,'21/01/2007');
insert into test_37 values (5933365, 0      ,'No event'            ,'09/02/2007');
insert into test_37 values (5933365, 24.7   ,'E-Topup Recharge'    ,'12/02/2007');
insert into test_37 values (5933365, -29.3  ,'CallerTune'          ,'20/02/2007');
insert into test_37 values (5933365, -14.37 ,'E-Topup Recharge'    ,'05/03/2007');

select customer_id
      ,amount
      ,remark
      ,entdate
from (select customer_id
            ,amount
            ,remark
            ,entdate
            ,max(sign(amount)) over (partition by customer_id order by entdate desc rows between unbounded preceding and current row) row_sign
      from   test_37)
where row_sign < 0;

CUSTOMER_ID     AMOUNT REMARK                                                       ENTDATE
----------- ---------- ------------------------------------------------------------ ---------
    5679991     -10.19 SMS Roaming                                                  05-SEP-06
    5679991      -6.74 Roaming Rental                                               02-SEP-06
    5933365     -14.37 E-Topup Recharge                                             05-MAR-07
    5933365      -29.3 CallerTune                                                   20-FEB-07
Re: SQL [message #224935 is a reply to message #224804] Fri, 16 March 2007 00:48 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Thanks its working fine
Re: SQL [message #228204 is a reply to message #224804] Mon, 02 April 2007 01:37 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Query is executed fine but if bind in PL/SQL block then get error please help for the same how can avoid the error in PL/SQL.


declare
begin
INSERT INTO MIS_PRE_FESS_NEGATIVE_FINAL
SELECT
CUSTOMER_ID,
ORIG_AMOUNT,
REMARK_AMOUNT,
REMARK,
ENTDATE
FROM
(SELECT
CUSTOMER_ID,
ORIG_AMOUNT,
REMARK_AMOUNT,
REMARK,
ENTDATE,
MAX(SIGN(REMARK_AMOUNT)) OVER (PARTITION BY CUSTOMER_ID ORDER BY ENTDATE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as ROW_SIGN
FROM
MIS_PRE_FESS_NEGATIVE)
WHERE ROW_SIGN < 0 AND
CUSTOMER_ID IN(5679991,5933365,4278999,4278998);
end;
Re: SQL [message #228210 is a reply to message #228204] Mon, 02 April 2007 01:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe it would help if you showed us WHAT error you got..
Re: SQL [message #228219 is a reply to message #228210] Mon, 02 April 2007 02:20 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps MAX() OVER(...) is supported in SQL*Plus, but not in PL/SQL of your Oracle DB version?
Re: SQL [message #228250 is a reply to message #228219] Mon, 02 April 2007 04:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
or ye ol' privilege error...
Re: SQL [message #228259 is a reply to message #228250] Mon, 02 April 2007 05:10 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd guess that the OP is using 8i, and can use analytics in SQL, but not PL/SQL.

The standard fix for this used to be to create a view based on your query, and reference this view in the PL.SQL
Previous Topic: execute procedure in SqlDeveloper which returns recordset
Next Topic: Finding timestamp and username info of a query
Goto Forum:
  


Current Time: Wed Dec 07 08:33:21 CST 2016

Total time taken to generate the page: 0.08857 seconds