SQL Query Question - Merging Data

[tschallb]

Hi Folks,

I have a commercial Database Application that I am querying to build a Invoice Report. As part of this report I am building a query to determine how much money is left on a given PO. The PO table (simplified version) is structured as follows...

Line_No PO Desc Acct # Amt Owing
1 100 Desc 1 0 10 50
2 100 Continued 0 0 0
3 100 Continued 0 0 0
4 100 Continued 14 0 0

In the above dataset all of the information that I need is always on Line 1 except for the Acct # that can be on any line within a given PO depending upon the size of the Description field. Is there a way that I can "Merge???" the Acct # when it is non-zero to the rest of the data on line 1?

Thanks,
Tim

 

[ESquared]

SELECT
      P1.Desc,
      P1.Amt,
      P1.Owing,
      ...,
      P2.Acct
   FROM POTable P1
   INNER JOIN POTable P2 ON P1.PO = P2.PO AND P1.Line_No=1 AND P2.Acct <> 0

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[hmckillop]

you could try and select max of both owing and account if all other data is 0
i.e.

SELECT Max(Acct), Max(Owing) From po_Table

Based on what you have it should return what you require.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[tschallb]

Thanks folks.
I am exloring both suggestions right now.
I forgot to mention one thing. I also need to link Acct # field to Account_Lookup Table so that I can get a real Account Number returned as part of my query.
Thanks for the quick response!
Tim

 

[tschallb]

Thanks ESquared your suggestion worked!
Now I am working on getting the raw SQL to work in Crystal Reports.
Thanks a bunch!
Tim

 

[ESquared]

Do you understand what the query does?

There is no problem joining another table by account ID, just inner join it to P2 on that.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[tschallb]

Hi Esquired,
No, I don't understand it yet. This is the first time I have seen a Self-Join in my limited SQL real life. I have my SQL book out and I am in the process of dissecting it.

hanks,
Tim

 

[ESquared]

Do a select * with the same join clause and you might see more clearly what it's doing...

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)