Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL Question On Paradox Tables

Posted on 2004-09-17
10
Medium Priority
?
1,308 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 1000 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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 

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
 
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
Watch the video of Kernel Migrator for SharePoint, which demonstrate the process easily of migration from SharePoint to SharePoint, OneDrive for Business & Google Drive servers, Public Folder to SharePoint, File Server to SharePoint. The tool has va…

608 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