August 11, 2011 at 12:54 am
Hi All,
Trying to join 3 tables, invoice_head, Invoice_lines and companies. Condition is date which is fine.
[Code="SQL"]
SELECT h.Invoice_id AS Invoice_Id,
h.IssueDate AS [Invoice Date],
h.Optional_1 AS [Division Buyer],
h.ScanDate AS [Scan Date],
h.Supplier AS [Supplier/Vendor],
h.Optional_2 AS [PO Number],
h.InvoiceNumber AS [Invoice Number],
h.Rejected,
h.Completed AS [Transaction Date],
h.Notified,
h.Comments,
h.Transferred,
h.Optional_3 AS [Invoice Type],
h.InvoiceTotal AS [Total Invoice Amount],
h.Optional_4 AS TFN,
h.Optional_5 AS Orginator,
h.Amount_1 AS [GST Total Amount],
h.Responsible,
h.IsCredit AS [Credit Note],
h.Currency, h.PaymentDate AS [BCC Upload Date1],
h.Optional_7 AS [Supplier Code]
FROM
Invoice_head h, companies
INNER JOIN
(SELECT
l.Invoice_id,
l.Account AS [Account Code],
l.Optional_6 AS [Job Number],
l.Optional_8 AS [Cost Type Code],
l.Department AS [Department Code],
l.Optional_7 AS [Employee Code],
l.Optional_1 AS [Product Code],
l.Optional_9 AS [Campaign Code],
l.Optional_10 AS Optional,
l.LineText AS Description,
l.Approver AS [Completed By],
l.Optional_3 AS [Division Code],
l.ApproveDate AS [BCC Upload Date],
l.Optional_5 AS [Client Code],
l.Responsible AS [Previous Approvers],
l.Remarks,
l.Amount_2 AS [Total Excl GST],
l.Amount_3 AS GST,
l.LineTotal AS [Total Inc GST]
FROM
Invoice_lines) l
ON l.invoice_id = h.invoice_id
INNER JOIN
companies
ON h.company_id = companies.company_id AND h.Optional_1 = companies.companyname
WHERE (companies.company_id = 32) AND (l.ApproveDate >= (CASE WHEN datepart(dw, getdate()) = 2 THEN (getdate() - 3) ELSE (getdate() - 1)
END))
Order By invoice_head.invoice_id
[/code]
But getting errors;
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "l.Invoice_id" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "l.Account" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "l.Optional_6" could not be bound.
etc.....
For every column in invoice_lines or the inbedded select.
Thanks
August 11, 2011 at 2:19 am
The table Invoice_lines is not aliased in the sub-query - the results from the sub-query can be accessed outisde of the sub-query with the alias (as in the condition "ON l.invoice_id = h.invoice_id") but within the sub-query the alias cannot be bound.
Just remove the l. from the l.<ColumnName> for all the columns inside the sub-query and this will work.
Additionally - if this is not just sample code provided for illustrative purposes:
a) Why don't you just do a join with Invoice_lines - you are essentially selecting some columns from this table in the sub-query and you can as well replace this with a join to Invoice_lines
b) It looks like you are joining the companies table twice (once at "FROM Invoice_head h, companies" and again at "INNER JOIN companies ")
August 11, 2011 at 10:24 am
With winash's suggestions and also aliasing the companies table, your query becomes this
SELECT h.Invoice_id AS Invoice_Id,
h.IssueDate AS [Invoice Date],
h.Optional_1 AS [Division Buyer],
h.ScanDate AS [Scan Date],
h.Supplier AS [Supplier/Vendor],
h.Optional_2 AS [PO Number],
h.InvoiceNumber AS [Invoice Number],
h.Rejected,
h.Completed AS [Transaction Date],
h.Notified,
h.Comments,
h.Transferred,
h.Optional_3 AS [Invoice Type],
h.InvoiceTotal AS [Total Invoice Amount],
h.Optional_4 AS TFN,
h.Optional_5 AS Orginator,
h.Amount_1 AS [GST Total Amount],
h.Responsible,
h.IsCredit AS [Credit Note],
h.Currency, h.PaymentDate AS [BCC Upload Date1],
h.Optional_7 AS [Supplier Code],
l.Invoice_id,
l.Account AS [Account Code],
l.Optional_6 AS [Job Number],
l.Optional_8 AS [Cost Type Code],
l.Department AS [Department Code],
l.Optional_7 AS [Employee Code],
l.Optional_1 AS [Product Code],
l.Optional_9 AS [Campaign Code],
l.Optional_10 AS Optional,
l.LineText AS Description,
l.Approver AS [Completed By],
l.Optional_3 AS [Division Code],
l.ApproveDate AS [BCC Upload Date],
l.Optional_5 AS [Client Code],
l.Responsible AS [Previous Approvers],
l.Remarks,
l.Amount_2 AS [Total Excl GST],
l.Amount_3 AS GST,
l.LineTotal AS [Total Inc GST]
FROM
Invoice_head h
INNER JOIN
Invoice_lines lON l.invoice_id = h.invoice_id
INNER JOIN
companies c ON h.company_id = c.company_id AND h.Optional_1 = c.companyname
WHERE (c.company_id = 32) AND (l.ApproveDate >= (CASE WHEN datepart(dw, getdate()) = 2 THEN (getdate() - 3) ELSE (getdate() - 1)
END))
Order By invoice_head.invoice_id
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
August 11, 2011 at 7:30 pm
Thanks guys for the replies, your script works.
Just as a excercise I am trying to use a nested query and am unable to get it working. Both these return the same error, Invalid column name 'Invoice_id'.
[Code="SQL"]
SELECT h.invoice_id,
h.IssueDate
FROM invoice_head h
INNER JOIN
(SELECT invoice_lines.Account,invoice_lines.Sub_id
FROM Invoice_lines)AS l
ON l.Invoice_id = h.Invoice_id
SELECT h.invoice_id,
h.IssueDate
FROM
(SELECT invoice_lines.Account,invoice_lines.Sub_id
FROM Invoice_lines)AS l
Join
invoice_head h
on l.Invoice_id = h.Invoice_id
[/Code]
August 11, 2011 at 7:46 pm
You need to also select the invoice id in your derived table. The server doesn't see the base table, it sees what's left after that little statement has run.
August 12, 2011 at 6:56 am
nested query derived table
qualify the derived table too, look how it makes it easier to see where the column is coming from
SELECT h.invoice_id,
h.IssueDate
FROM invoice_head h
INNER JOIN
(
SELECT IL_inside.invoice_id, IL_inside.Account,IL_inside.Sub_id
FROM Invoice_lines IL_inside
)AS IL_outside
ON IL_outside.Invoice_id = h.Invoice_id
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
August 12, 2011 at 10:53 am
Since your query is not actually using any of the values, you might want to use this approach instead:
SELECT h.invoice_id,
h.IssueDate
FROM invoice_head h
WHERE EXISTS (SELECT 1 FROM Invoice_lines WHERE Invoice_lines.Invoice_id = h.Invoice_id)
Or, if you want the duplicates to appear instead,
SELECT h.invoice_id,
h.IssueDate
FROM invoice_head h
JOIN Invoice_lines ON Invoice_lines.Invoice_id = h.Invoice_id
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply