Home » SQL & PL/SQL » SQL & PL/SQL » Sql query (Oracle 10g)
Sql query [message #324609] Tue, 03 June 2008 05:39 Go to next message
someswar1
Messages: 53
Registered: January 2008
Member
Hi,
I have a table sdp with following field account_number,adjustment_date,adjustment_time,balance_before,balance_after,cdr_id

and value like this

Insert into sdp Values
('978362369', '20071210', '061413', '708.000000', '5708.000000', '45813627');

I need to finding
The minimum date time among the combination of (ADJUSTMENT_TIME, ADJUSTMENT_DATE) with respect to ACCOUNT_NUMBER is to be considered and the corresponding BALANCE_BEFORE is to be mapped to OPENING_BALANCE.

In case of multiple row with same min(ADJUSTMENT_TIME, ADJUSTMENT_DATE), the record with least ‘CDR_ID’ should be considered.

e.g if for ACCOUNT_NUMBER 97813437 the ADJUSTMENT_TIME, ADJUSTMENT_DATE, BALANCE_BEFORE is
i> 070332, 20080129, 0.123569
ii> 190356, 20080129, 1.236598
iii> 000029, 20080130, 2.569874

then OPENING_BALANCE will be 0.123569

I write the query but not working properly
CASE
    WHEN BALANCE_BEFORE IS not NULL
         THEN (SELECT   BALANCE_BEFORE
        FROM sdp 
        WHERE ACCOUNT_NUMBER IN ( SELECT a.ACCOUNT_NUMBER
                                 FROM sdp a
                                 WHERE A.ACCOUNT_NUMBER =B.ACCOUNT_NUMBER)
                            AND  CDR_ID IN (SELECT   Min (CDR_ID)
                                 FROM sdp
                                 GROUP BY ACCOUNT_NUMBER)
                                 GROUP BY ACCOUNT_NUMBER,
                                          BALANCE_BEFORE,
                                          ROWID,
                                          CDR_ID)
     ELSE (SELECT   BALANCE_BEFORE FROM sdp
                    WHERE ACCOUNT_NUMBER IN (SELECT a.ACCOUNT_NUMBER
                                              FROM sdp a
                                              WHERE ACCOUNT_NUMBER =b.ACCOUNT_NUMBER)
                                       AND  ROWID IN (SELECT   Min (ROWID)
                                                       FROM sdp
                                                   GROUP BY ACCOUNT_NUMBER)
                                           GROUP BY ACCOUNT_NUMBER,
                                                    BALANCE_BEFORE,
                                                    ROWID,
                                                    CDR_ID)
END                                                                             


can any one help me.
Its Urgent

Regards:
somu



[mod-edit: added code tags to case statement]

[Updated on: Tue, 03 June 2008 13:33] by Moderator

Report message to a moderator

Re: Sql query [message #324627 is a reply to message #324609] Tue, 03 June 2008 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and FOLLOW OraFAQ Forum Guide.

Regards
Michel
Re: Sql query [message #324636 is a reply to message #324627] Tue, 03 June 2008 07:24 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Come on Michael. You cannot expect the OP to follow the guidelines. OP has said Its Urgent. OP is not having any time to follow the guidelines.

Regards

Raj
Re: Sql query [message #324661 is a reply to message #324609] Tue, 03 June 2008 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Its Urgent

somu,
The lack of planning on your part, does not constitute an emergency for me or anyone else here.
Re: Sql query [message #324701 is a reply to message #324609] Tue, 03 June 2008 13:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8630
Registered: November 2002
Location: California, USA
Senior Member
GROUP BY is intended for use with aggregate functions, but have used it in select statements without aggregate functions, so your usage of it is incorrect.

[Updated on: Tue, 03 June 2008 13:38]

Report message to a moderator

Re: Sql query [message #324787 is a reply to message #324701] Wed, 04 June 2008 00:29 Go to previous messageGo to next message
someswar1
Messages: 53
Registered: January 2008
Member
How I do this instead of group by clause
Re: Sql query [message #324953 is a reply to message #324787] Wed, 04 June 2008 10:51 Go to previous message
Barbara Boehmer
Messages: 8630
Registered: November 2002
Location: California, USA
Senior Member
If you want help, then you need to read and follow the forum guidelines, as previously instructed, including posting create table and insert statements for sample data, the results that you want based on that data, and a copy and paste of what you have tried, which means a complete select statement, not just a case statement, including line numbers and results or error message.
Previous Topic: ORDER BY dilemma
Next Topic: PLSQL Procedure Error
Goto Forum:
  


Current Time: Mon Dec 05 06:33:52 CST 2016

Total time taken to generate the page: 0.14888 seconds