Solved

SQL Question On Paradox Tables

Posted on 2004-09-17
10
1,248 Views
Last Modified: 2008-01-09
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
Comment
Question by:oyg
  • 5
  • 4
10 Comments
 
LVL 10

Accepted Solution

by:
imrancs earned 250 total points
ID: 12082702
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
 

Author Comment

by:oyg
ID: 12083581
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
 

Author Comment

by:oyg
ID: 12083600
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
 

Author Comment

by:oyg
ID: 12083604

and I will accept your Answer for all conditions

0
 
LVL 10

Expert Comment

by:imrancs
ID: 12085653
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 6

Expert Comment

by:vadim_ti
ID: 12085710
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
 

Author Comment

by:oyg
ID: 12090800
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
 
LVL 10

Expert Comment

by:imrancs
ID: 12090849
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
 

Author Comment

by:oyg
ID: 12100562
Thank U Imran for your honest, But How can I split the points between you and him??

Best Regards,

Osama
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12100612
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Copy Oracle database from 11g (11.2.0.1.0) to 12c (12.1.0.2.0) 15 172
Need way to extend view in MS Access 47 73
report returning null 21 82
Index Details in SQL Server 2012 4 39
In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now