Calculated field in a join

Question

Calculated field in a join

dave platt-208496

SSC Eights!

Points: 862
More actions
April 28, 2008 at 5:38 am

#181738

I've been asked to run a report on a database where the tables look something like this:
Customer:
Ref varchar(10)
Invoice:
InvoiceNumber int, CustomerRef varchar(10)
InvoiceDetails
id int, InvoiceNumber int, CustomerRef varchar(10)
The InvoiceNumber looks to be incremented by 1 for each new invoice (purchase) by the customer. Therefore I'm thinking that instead of each Invoice row having a unique id, its a combination of CustomerRef and InvoiceNumber that identifies the row. I'm not in a position to change the database. These 2 columns are used in other tables to identify the specific invoice eg InvoiceDetails.
Initially i queried the database using calculated fields - something like this:
SELECT inv.*, det.*
FROM Invoice AS inv
INNER JOIN
InvoiceDetails AS det
ON
Convert(char(3), inv.InvoiceNumber)+inv.CustomerRef = Convert(char(3), det.InvoiceNumber)+det.CustomerRef
I wonder if there is a more efficient approach to querying this data. Thanks for any suggestions.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

TraderSam SSCrazy Points: 2931 More actions · Answer 1

dave platt (4/28/2008)
Customer:
Ref varchar(10)
Invoice:
InvoiceNumber int, CustomerRef varchar(10)
InvoiceDetails
id int, InvoiceNumber int, CustomerRef varchar(10)
SELECT inv.*, det.*
FROM Invoice AS inv
INNER JOIN
InvoiceDetails AS det
ON
Convert(char(3), inv.InvoiceNumber)+inv.CustomerRef = Convert(char(3), det.InvoiceNumber)+det.CustomerRef

Without additional info, it is hard to tell what is unique and what isn't. Without knowing that, this query should work.

SELECT inv.*, det.*

FROM Invoice AS inv

INNER JOIN

InvoiceDetails AS det

ON

inv.InvoiceNumber = det.InvoiceNumber

and inv.CustomerRef = det.CustomerRef

Also, if invoice number is a unique identifier in the Invoice table (never re-used for a different customer), then you could join Invoice and InvoiceDetails on that field alone. You could probably figure that out by determining where Invoice Number comes from (identity, incremented number, etc) and looking at the data so see if it is unique.

Hope this helps!

If it was easy, everybody would be doing it!;)

dave platt-208496 SSC Eights! Points: 862 More actions · Answer 2

Thanks for the reply. CustomerRef is unique (a foreign key taken from Customer.Ref) but unfortunately Invoice.InvoiceNumber is not - it is incremented by one for every new purchase made by Customer. For example:

CustA has 2 invoices, InvoiceNumber 1 and 2

CustB has 3 invoice, InvoiceNumber 1, 2 and 3

Invoice table would look like:

CustomerRef, InvoiceNumber

CustA, 1

CustA, 2

CustB, 1

CustB, 2

CustB, 3

I looked at this database about a year ago (I don't write much tsql) and I guess i must have had an issue with:

ON inv.InvoiceNumber = det.InvoiceNumber

and inv.CustomerRef = det.CustomerRef

but I can't remember what it was. I thought it might be with outer joins although testing just now with your code, to find all invoices without any invoicedetails and it works as expected.

thanks for the help.