single row query Return More than One Row

Question

4.00/5 (2 votes)

See more:

Hello friends...here Is My Query

SQL

select TRAN_AMOUNT AS CLOSING_BAL 
  From LOANTRAN 
 WHERE TRAN_DATE IN ( select AC_CLOSEDT from LNMASTER 
                       where AC_NO BETWEEN 100 AND 200 ) 
   AND ROWID IN ( SELECT MAX(ROWID) FROM LOANTRAN 
                   WHERE TRAN_ACNO BETWEEN 100 AND 200)

Actually problem is, I want to create a crystal report with some account no and their closing balance.

Here is Demo Of LOANTRAN Tbl...

TRAN_ACNO    TRAN_DATE    TRAN_AMOUNT
1             2/2/2001       2000
2             2/2/2001       1500
3             2/2/2001       3000
2             2/2/2001       3700
3             2/2/2001       4500

I want to select last transection of One Perticular TRAN_ACNO .. in Above Example there are two times entry of Ac.No 2 in a single day..That means The Amount 3700 Is a closing Balence Of Acount No 2...(Closing Balance Means Last transection of any Account on that Single Day.. Ac.No 3 has Closing Balance 4500) i want to select such Closing Balance from LOANTRAN table of Account no Between 100 AND 200...

Please help Me.....
Kumar
Oracle Developer
[DELETED]@yahoo.co.in

[edit]Never post your email address in any forum, unless you really like spam! If anyone replies to you, you will receive an email to let you know - OriginalGriff[/edit]
[edit2]Just cleaned up the sql so that it's easier to parse visually - Marcus[/edit2]

Posted 28-Jan-11 3:36am

kumar_jadhav

Updated 28-Jan-11 5:13am

fjdiewornncalwe

v5

Add a Solution

Comments

ravitechguy 28-Jan-11 9:46am

Do you have any other columns in LOANTRAN table which will record date entry into the table.

Indivara 28-Jan-11 9:46am

How do you figure that 3700 is the closing balance? Do you have the time as well? (your example only has date)

ravitechguy 28-Jan-11 9:52am

Try Below:
Select a.tran_amount, a.rowid
from
(select TRAN_AMOUNT AS CLOSING_BAL,
ROWID As Rownumber
From LOANTRAN
WHERE TRAN_DATE IN (select AC_CLOSEDT from LNMASTER where AC_NO BETWEEN 100 AND 200)
Order by ROWID) a
where a.rowid in (SELECT MAX(ROWID) FROM LOANTRAN WHERE TRAN_ACNO BETWEEN 100 AND 200)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

fjdiewornncalwe · Accepted Answer · 2011-01-28T06:31:00

Solution 1

As long as you have the WHERE ... BETWEEN 100 AND 200 clauses in your sql, you are potentially going to get multiple rows returned. You'll need to find a way to narrow these blocks down to single row returns in order to get the entire query to return one row. I can't tell you what that should look like unless you provide a bit more info on the LNMASTER table.

Posted 28-Jan-11 6:31am

fjdiewornncalwe

Comments

kumar_jadhav 29-Jan-11 0:00am

Thank You Marcus.....

fjdiewornncalwe 31-Jan-11 16:28pm

Hey Kumar.. I think you "accidentally" hit 1 instead of 5 on the vote. (5 is good, BTW, 1 is not so good).. :)