March 2, 2011 at 5:18 pm
This is a query I pulled from SQL Profiler:
SELECT
invoice.contactid, invoicedetail.invoiceid, RecType=2, DetailID=invoicedetail.id, invoice.trandate, Reference=ISNULL(ItemCode,''), Details=ISNULL(Description,''), ReceiptNotes='', invoicedetail.amount, Outstanding=invoicedetail.outstanding,
ReceiptsPending=invoicedetail.receiptspending, ReceiptAmount=Convert(decimal(19,4), 0), ReceiptSundry=Convert(decimal(19,4), 0), ReceiptDiscount=Convert(decimal(19,4), 0), ReceiptOptional=Convert(decimal(19,4), 0),
invoicedetail.taxamount, TranType, invoice.posted, OptionalFee='N', invoicedetail.status, CopID = 0, receiptposted = ''
FROM invoice, InvoiceDetail
WHERE ( invoice.id = invoicedetail.invoiceid ) and ( invoice.trantype not in ('COP', 'CCB') ) and ( invoicedetail.optionalfee = 'N' ) and ( invoice.trandate >= {d '2010-01-01'} ) and ( invoice.trandate < {d '2011-03-03'} )
and ( invoice.contactid in (9866) ) and ( invoice.posted in ('Y','N') )
What I find strange is the FROM clause where it selects from 2 separate tables. I'm trying to institute a join here, below is the same query with my join:
SELECT
invoice.contactid, invoicedetail.invoiceid, RecType=2, DetailID=invoicedetail.id, invoice.trandate, Reference=ISNULL(ItemCode,''), Details=ISNULL(Description,''), ReceiptNotes='', invoicedetail.amount, Outstanding=invoicedetail.outstanding,
ReceiptsPending=invoicedetail.receiptspending, ReceiptAmount=Convert(decimal(19,4), 0), ReceiptSundry=Convert(decimal(19,4), 0), ReceiptDiscount=Convert(decimal(19,4), 0), ReceiptOptional=Convert(decimal(19,4), 0),
invoicedetail.taxamount, TranType, invoice.posted, OptionalFee='N', invoicedetail.status, CopID = 0, receiptposted = ''
FROM invoice, InvoiceDetail
INNER JOIN Contact C ON invoice.ContactID = C.ID
WHERE ( invoice.id = invoicedetail.invoiceid ) and ( invoice.trantype not in ('COP', 'CCB') ) and ( invoicedetail.optionalfee = 'N' ) and ( invoice.trandate >= {d '2010-01-01'} ) and ( invoice.trandate < {d '2011-03-03'} )
and ( invoice.contactid in (9866) ) and ( invoice.posted in ('Y','N') )
Running this query returns the following error:
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "invoice.ContactID" could not be bound.
What I cannot understand is why. The invoice table has a column called ContactID, and this is the same ID column that is in the contact table. SQL should not be returning that error for this reason. Is it something to do with how the FROM clause is constructed?
Thanks in advance, I've never seen select statements like this before.
March 2, 2011 at 6:08 pm
Hi
It might be because you are mixing old and new style joins in one statement. Do either of the following two work?
SELECT
invoice.contactid, invoicedetail.invoiceid, RecType=2, DetailID=invoicedetail.id, invoice.trandate, Reference=ISNULL(ItemCode,''), Details=ISNULL(Description,''), ReceiptNotes='', invoicedetail.amount, Outstanding=invoicedetail.outstanding,
ReceiptsPending=invoicedetail.receiptspending, ReceiptAmount=Convert(decimal(19,4), 0), ReceiptSundry=Convert(decimal(19,4), 0), ReceiptDiscount=Convert(decimal(19,4), 0), ReceiptOptional=Convert(decimal(19,4), 0),
invoicedetail.taxamount, TranType, invoice.posted, OptionalFee='N', invoicedetail.status, CopID = 0, receiptposted = ''
FROM invoice, InvoiceDetail,Contact
WHERE ( invoice.id = invoicedetail.invoiceid ) and ( invoice.trantype not in ('COP', 'CCB') ) and ( invoicedetail.optionalfee = 'N' ) and ( invoice.trandate >= {d '2010-01-01'} ) and ( invoice.trandate < {d '2011-03-03'} )
and ( invoice.contactid in (9866) ) and ( invoice.posted in ('Y','N') )
and invoice.ContactID = Contact.ID
SELECT
invoice.contactid, invoicedetail.invoiceid, RecType=2, DetailID=invoicedetail.id, invoice.trandate, Reference=ISNULL(ItemCode,''), Details=ISNULL(Description,''), ReceiptNotes='', invoicedetail.amount, Outstanding=invoicedetail.outstanding,
ReceiptsPending=invoicedetail.receiptspending, ReceiptAmount=Convert(decimal(19,4), 0), ReceiptSundry=Convert(decimal(19,4), 0), ReceiptDiscount=Convert(decimal(19,4), 0), ReceiptOptional=Convert(decimal(19,4), 0),
invoicedetail.taxamount, TranType, invoice.posted, OptionalFee='N', invoicedetail.status, CopID = 0, receiptposted = ''
FROM invoice
INNER JOIN InvoiceDetail ON invoice.id = invoicedetail.invoiceid
INNER JOIN Contact C ON invoice.ContactID = C.ID
WHERE ( invoice.trantype not in ('COP', 'CCB') ) and ( invoicedetail.optionalfee = 'N' ) and ( invoice.trandate >= {d '2010-01-01'} ) and ( invoice.trandate < {d '2011-03-03'} )
and ( invoice.contactid in (9866) ) and ( invoice.posted in ('Y','N') )
March 2, 2011 at 6:21 pm
rjohal-500813 (3/2/2011)
HiIt might be because you are mixing old and new style joins in one statement. Do either of the following two work?
SELECTinvoice.contactid, invoicedetail.invoiceid, RecType=2, DetailID=invoicedetail.id, invoice.trandate, Reference=ISNULL(ItemCode,''), Details=ISNULL(Description,''), ReceiptNotes='', invoicedetail.amount, Outstanding=invoicedetail.outstanding,
ReceiptsPending=invoicedetail.receiptspending, ReceiptAmount=Convert(decimal(19,4), 0), ReceiptSundry=Convert(decimal(19,4), 0), ReceiptDiscount=Convert(decimal(19,4), 0), ReceiptOptional=Convert(decimal(19,4), 0),
invoicedetail.taxamount, TranType, invoice.posted, OptionalFee='N', invoicedetail.status, CopID = 0, receiptposted = ''
FROM invoice, InvoiceDetail,Contact
WHERE ( invoice.id = invoicedetail.invoiceid ) and ( invoice.trantype not in ('COP', 'CCB') ) and ( invoicedetail.optionalfee = 'N' ) and ( invoice.trandate >= {d '2010-01-01'} ) and ( invoice.trandate < {d '2011-03-03'} )
and ( invoice.contactid in (9866) ) and ( invoice.posted in ('Y','N') )
and invoice.ContactID = Contact.ID
Thanks for that, this method worked. The application that generates these statements with the old style of joins is one that has been wholly developed in Powerbuilder. I could just be the way the development application generates these statements. Shoddy programming somewhere though.
Thanks for your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply