• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1301
  • Last Modified:

SQL Question On Paradox Tables

Hi All,

I have two Paradox Tables:

1- Cust.db  //  Customers File

CustNo CustName     FirstDebit    FirstCredit
  1      John                    200                0  
  2      Peter                  120                 0
  3      Sam                      0              130
  4      Sara                  500                 0
  5      Nicol                     0              600
  6      Mickel                   0                 0
  7     Alexender               0                0
  8     X1                         0                0
  9     X2                       15                0



2- AccDetails.db   // Transaction File for Customers

CustNo     Debit      Credit
3              250           0
9                  0       350
4               100           0
1                 50           0


The Result Must Be Like This :


CustNo    Debit       Credit
  1             250         0
  2             120         0
  3             120         0
  4              600         0
  5                0        600
  9                0        335
                        
I Try This Statement

Select C.CustNo, C.FirstDebit -  C.FirstCredit + Sum(D.Debit)  - Sum(D.Credit) As Result
From Cust C, AccDetails D
Where C.CustNo = D.CustNo
Group By CustNo      

But the Result Appears Like This


CustNo   Result
  1             250
  3            120
  4            600
  9           -335

It Hide's the Accounts that there have no transactions, and also collect the result in one field

So How Can I make One SQL Statement to appear the right Result??

Best Regards,

Osama

0
oyg
Asked:
oyg
  • 5
  • 4
1 Solution
 
imrancsCommented:
Select C.CustNo, C.FirstDebit -  C.FirstCredit + Sum(D.Debit)  - Sum(D.Credit) As Result
From Cust C Left Outer Join  AccDetails D On (C.CustNo = D.CustNo)
Group By CustNo  


Imran
0
 
oygAuthor Commented:
Thank You Imran,

You are Great The Result is 100% but I need to split the Result To Positive Result and Negative Resul Like This:

CustNo    Debit       Credit
  1             250         0
  2             120         0
  3             120         0
  4              600         0
  5                0        600
  9                0        335

Not Like This:

CustNo   Result
  1             250
  3            120
  4            600
  9           -335

and Please Tell me where r u From ??

Best Regards,

Osama
0
 
oygAuthor Commented:
Sorry,

 Like This:

CustNo    Debit       Credit
  1             250         0
  2             120         0
  3             120         0
  4              600         0
  5                0        600
  9                0        335

Not Like This:

CustNo    Result
  1             250
  2             120
  3             120
  4              600
  5             - 600
  9             - 335

Best Regards,

Osama
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
oygAuthor Commented:

and I will accept your Answer for all conditions

0
 
imrancsCommented:
if the version of Paradox you are using supports CASE statement then it should be look like this

Select C.CustNo, Case When C.FirstDebit -  C.FirstCredit + Sum(D.Debit)  - Sum(D.Credit) >= 0 Then C.FirstDebit -  C.FirstCredit + Sum(D.Debit)  - Sum(D.Credit) Else 0  As Debit,
Case When C.FirstDebit -  C.FirstCredit + Sum(D.Debit)  - Sum(D.Credit) < 0 Then C.FirstDebit -  C.FirstCredit + Sum(D.Debit)  - Sum(D.Credit) Else 0  As Credit
From Cust C Left Outer Join  AccDetails D On (C.CustNo = D.CustNo)
Group By CustNo  


>>and Please Tell me where r u From ??
hmmm, I am from Pakistan > Lahore


Imran
0
 
vadim_tiCommented:
Cannot test it,
but i think something like it


Select C.CustNo, C.FirstDebit -  C.FirstCredit + Sum(D.Debit)  - Sum(D.Credit) As Debit, 0 as Credit
From Cust C Left Outer Join  AccDetails D On (C.CustNo = D.CustNo)
Group By CustNo  
Having Debit > 0

UNION

Select C.CustNo, 0 as Debit, -(C.FirstDebit -  C.FirstCredit + Sum(D.Debit)  - Sum(D.Credit)) As  Credit
From Cust C Left Outer Join  AccDetails D On (C.CustNo = D.CustNo)
Group By CustNo  
Having Credit > 0
0
 
oygAuthor Commented:
Thank U Imran and Vadim,

The last comment from Imran does not work in Local SQL, maybe with Oracle the comment from Vadim is worked good but it needs some modifications and I will Fix it .

Thank u for All

Osama
0
 
imrancsCommented:
Osama if Vadim's comment also helped you out in solving you problem then you also should award points to, I mean you may have to spilt the point between us.


Imran
0
 
oygAuthor Commented:
Thank U Imran for your honest, But How can I split the points between you and him??

Best Regards,

Osama
0
 
imrancsCommented:
post the question (0 points) in the community support to reopen the question and in question paste the URL of this question. And when the question will be re-opend  then you can spil the points.

here is the link for Community Support

http://www.experts-exchange.com/Community_Support/


Imran
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now