July 4, 2011 at 10:43 pm
Hi,
I am trying to get the invoice data from the invoice_head table(single row) with the invoice_lines(purchase orders) on certain conditions.
The where is fine but SQL doesn't like binding any of the invoice_lines columns.Which why I have tried putting in a nested select statement.
SELECT TOP 1 h.Invoice_id AS Invoice_Id, h.IssueDate AS [Invoice Date], h.Optional_1 AS [Division Buyer],
h.ScanDate AS ScanDate],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[EmployeeCode],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
GROUP BY
Invoice_id) l
ON h.invoice_id = l.invoice_id
WHERE (companies.company_id = 44)AND (Invoice_lines..Invoice_id ='135291') AND (invoice_lines.ApproveDate >= (CASE WHEN datepart(dw, getdate()) = 2 THEN (getdate() - 3) ELSE (getdate() - 1)END))
GROUP BY h.invoice_id
ERROR: Msg 4104, Level 16, State 1, Line 1The multi-part identifier "l.Invoice_id" could not be bound. etc...
July 5, 2011 at 12:38 am
This might be "Concatenating values when the number of items is not known" never done that before.
July 5, 2011 at 3:12 am
Hi,
You can use the Alias "l" in the main query. not in the the subquery. just replace the "l." from the inner query. It will be okay.
Hope this helps.
-Swaroop
July 5, 2011 at 4:20 am
If your not using aggregate every column in the select list needs to be part of the group by statement else you will face an error similar to
Msg 8120, Level 16, State 1, Line 1
Column 'dbo.tablename.columnname ' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
July 5, 2011 at 9:17 am
Aggregate function error at your subquery.
Remember if you are using group by, you must have all columns in the select clause :-).
July 5, 2011 at 2:55 pm
Query from original post reformatted:
SELECT TOP 1
h.Invoice_id AS Invoice_Id,
h.IssueDate AS [Invoice Date],
h.Optional_1 AS [Division Buyer],
h.ScanDate AS ScanDate],
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 [EmployeeCode],
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
GROUP BY Invoice_id) l ON h.invoice_id = l.invoice_id
WHERE (companies.company_id = 44)
AND (Invoice_lines..Invoice_id ='135291')
AND (invoice_lines.ApproveDate >= (CASE WHEN datepart(dw, getdate()) = 2 THEN (getdate() - 3) ELSE (getdate() - 1)END))
GROUP BY h.invoice_id
The bolded part of the FROM clause below equates to a CROSS JOIN. Are you sure that's what you want?
h.Optional_7 AS [Supplier Code]
FROM Invoice_head h, Companies
INNER JOIN (SELECT l.Invoice_id,
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 5, 2011 at 6:02 pm
I was trying to avoid putting everything in the group by, which why i put the invoice_lines columns in a nested select. I orginally had it with the invoice_head columns select.
Can you suggest a better way?
July 5, 2011 at 6:04 pm
No I don't want a cross join I was only trying to reference the companies table as it is used in the lower join.
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
GROUP BY
Invoice_id) l
ON
h.invoice_id = l.invoice_id
INNER JOIN
companies ON h.company_id = companies.company_id AND h.Optional_1 = companies.companyname
WHERE (companies.company_id = 44)AND (l.Invoice_id ='135291') AND (l.ApproveDate >= (CASE WHEN datepart(dw, getdate()) = 2 THEN (getdate() - 3) ELSE (getdate() - 1)
END))
Order By invoice_head.invoice_id
July 5, 2011 at 7:05 pm
It may make a difference...
Where will the result set of this query be used? In a reporting tool? In a GUI? or ???
--Jeff Moden
July 5, 2011 at 7:26 pm
Jeff Moden (7/5/2011)
It may make a difference...Where will the result set of this query be used? In a reporting tool? In a GUI? or ???
I am trying to write a SQL statement for a package I have designed, which will then be added to a schedulded job. This is the final bit everything else works. I some how need to get the unique invoice_head(invoice_id) with it's invoice_lines(purchase orders), though not every invoice has purchase orders 🙂
Thanks for trying to help everyone.
A rough example of what I am expecting;
Invoice_head, invoice_id col2 col 3 col4 etc... Invoice_line, col1 col2 col3 col4.....
...................1234, 01072011, acme pty, jones, PO1, 01112011, acme pty, smith
................................................................. PO2, 01112011, harrys pty, williams
..................................................................PO3, 01112011, zenith pty, kate
....................11221,01072011, starcom pty, ian,PO1, 01072011, SS pty, jones
So invoice 1234 has one row for invoice_head columns and 1 or more rows for invoice_lines\purchase orders columns. Though invoice 11221 doesn't have any extra PO's.
Hopefully someone can help becuase i don't know.
July 6, 2011 at 3:55 am
July 6, 2011 at 6:39 am
ringovski (7/5/2011)
Jeff Moden (7/5/2011)
It may make a difference...Where will the result set of this query be used? In a reporting tool? In a GUI? or ???
I am trying to write a SQL statement for a package I have designed, which will then be added to a schedulded job. This is the final bit everything else works. I some how need to get the unique invoice_head(invoice_id) with it's invoice_lines(purchase orders), though not every invoice has purchase orders 🙂
Thanks for trying to help everyone.
A rough example of what I am expecting;
Invoice_head, invoice_id col2 col 3 col4 etc... Invoice_line, col1 col2 col3 col4.....
...................1234, 01072011, acme pty, jones, PO1, 01112011, acme pty, smith
................................................................. PO2, 01112011, harrys pty, williams
..................................................................PO3, 01112011, zenith pty, kate
....................11221,01072011, starcom pty, ian,PO1, 01072011, SS pty, jones
So invoice 1234 has one row for invoice_head columns and 1 or more rows for invoice_lines\purchase orders columns. Though invoice 11221 doesn't have any extra PO's.
Hopefully someone can help becuase i don't know.
This sounds like a classic use of a LEFT JOIN. I am not keen on writing code against thin air, it tends to come back to haunt me, so if you would be so kind as to provide some DDL, DML to create sample data and your expected results I am sure I can assist you in developing something that will work for you.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 6, 2011 at 6:52 am
At a guess...
WITH cte (Invoice_id, IssueDate, Account, ApproveDate, RowId)
AS (SELECT h.Invoice_id, h.IssueDate, l.Account, l.ApproveDate, ROW_NUMBER() OVER(PARTITION BY h.Invoice_id ORDER BY h.Invoice_id ASC)
FROM Companies c
JOIN Invoice_head h ON h.company_id = c.company_id
JOIN Invoice_lines l ON l.invoice_id = h.invoice_id
WHERE (c.company_id = 44)
AND (l.Invoice_id ='135291')
AND (l.ApproveDate >= (CASE WHEN DATEPART(dw, GETDATE()) = 2 THEN (GETDATE() - 3) ELSE (GETDATE() - 1) END)))
SELECT CASE WHEN RowId=1 THEN CAST(Invoice_id as varchar(20)) ELSE '' END) AS [Invoice_id],
CASE WHEN RowId=1 THEN CAST(IssueDate as varchar(20)) ELSE '' END) AS [IssueDate],
Account, ApproveDate
FROM cte
ORDER BY Invoice_id ASC
Far away is close at hand in the images of elsewhere.
Anon.
July 6, 2011 at 6:56 am
Here is David's code with a couple syntax errors removed and then reformatted:
WITH cte ( Invoice_id, IssueDate, Account, ApproveDate, RowId )
AS (
SELECT h.Invoice_id,
h.IssueDate,
l.Account,
l.ApproveDate,
ROW_NUMBER() OVER ( PARTITION BY h.Invoice_id ORDER BY h.Invoice_id ASC )
FROM Companies c
JOIN Invoice_head h ON h.company_id = c.company_id
JOIN Invoice_lines l ON l.invoice_id = h.invoice_id
WHERE ( c.company_id = 44 )
AND ( l.Invoice_id = '135291' )
AND ( l.ApproveDate >= ( CASE WHEN DATEPART(dw, GETDATE()) = 2 THEN ( GETDATE() - 3 )
ELSE ( GETDATE() - 1 )
END ) )
)
SELECT CASE WHEN RowId = 1 THEN CAST(Invoice_id AS VARCHAR(20))
ELSE ''
END AS [Invoice_id],
CASE WHEN RowId = 1 THEN CAST(IssueDate AS VARCHAR(20))
ELSE ''
END AS [IssueDate],
Account,
ApproveDate
FROM cte
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 6, 2011 at 6:14 pm
opc.three (7/6/2011)
ringovski (7/5/2011)
Jeff Moden (7/5/2011)
It may make a difference...Where will the result set of this query be used? In a reporting tool? In a GUI? or ???
I am trying to write a SQL statement for a package I have designed, which will then be added to a schedulded job. This is the final bit everything else works. I some how need to get the unique invoice_head(invoice_id) with it's invoice_lines(purchase orders), though not every invoice has purchase orders 🙂
Thanks for trying to help everyone.
A rough example of what I am expecting;
Invoice_head, invoice_id col2 col 3 col4 etc... Invoice_line, col1 col2 col3 col4.....
...................1234, 01072011, acme pty, jones, PO1, 01112011, acme pty, smith
................................................................. PO2, 01112011, harrys pty, williams
..................................................................PO3, 01112011, zenith pty, kate
....................11221,01072011, starcom pty, ian,PO1, 01072011, SS pty, jones
So invoice 1234 has one row for invoice_head columns and 1 or more rows for invoice_lines\purchase orders columns. Though invoice 11221 doesn't have any extra PO's.
Hopefully someone can help becuase i don't know.
This sounds like a classic use of a LEFT JOIN. I am not keen on writing code against thin air, it tends to come back to haunt me, so if you would be so kind as to provide some DDL, DML to create sample data and your expected results I am sure I can assist you in developing something that will work for you.
Not sure you mean by DDL & DML, but here are the table specs.
Table Invoice_Head
Invoice_idPK, int, not null
IssueDatedatetime, null
PaymentDatedatetime, null
ScanDatedatetime, null
Supplier nvarchar(50), null
InvoiceNumbernvarchar(100), null
InvoiceTotalFloat, null
Imagelocationnvarchar(500), null
Responsibleint, null
Rejected datetime, null
AssignmentDatedatetime, null
Commentsnvarchar(500), null
Completeddatetime, null
Notified datetime, null
Transferreddatetime, null
Optional_1nvarchar(50), null
Optional_2nvarchar(50), null
Optional_3nvarchar(50), null
Optional_4nvarchar(50), null
Optional_5nvarchar(50), null
Optional_6nvarchar(50), null
Optional_7nvarchar(50), null
Optional_8nvarchar(50), null
Optional_9nvarchar(50), null
Optional_10nvarchar(50), null
Amount_1Float, null
Amount_2Float, null
Amount_3Float, null
Amount_4Float, null
Currency nchar3, not null
IsCredit bit, not null
Company_idint, null
Table Invoice Lines
Invoice_idPK, INT, Not Null
Line_id PK, INT, Not Null
Sub_id INT, Not Null
Account nvarchar(50), null
Departmentnvarchar(50), null
Type nvarchar(50), null
Responsiblenvarchar(50), null
Quantity Float, null
Remarks nvarchar(400), null
Line Text nvarchar(200), null
Line TotalFloat, null
Optional_1nvarchar(50), null
Optional_2nvarchar(50), null
Optional_3nvarchar(50), null
Optional_4nvarchar(50), null
Optional_5nvarchar(50), null
Optional_6nvarchar(50), null
Optional_7nvarchar(50), null
Optional_8nvarchar(50), null
Optional_9nvarchar(50), null
Optional_10nvarchar(50), null
Amount_1Float, null
Amount_2Float, null
Amount_3Float, null
Amount_4Float, null
Approver nvarchar(200), null
ApproveDatedatetime, null
Notified datetime, null
AssignmentDatedatetime, null
Table Companies
Company_idPK, int, not null
companynamenvarchar(200), null
contactnamenvarchar(200), null
contactitlenvarchar(200), null
address nvarchar(200), null
city nvarchar(200), null
region nvarchar(200), null
postalcodenvarchar(200), null
country nvarchar(200), null
phone nvarchar(200), null
fax nvarchar(200), null
homepagenvarchar(200), null
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply