October 2, 2008 at 4:15 am
Hi. I am attempting to construct a query intended for use in a report. The report is to display payments made against Sales Orders.
The Query returned multiple instances of CustomerName / SalesOrdeId for each payment. I have attempted to amend the query so it outputs the CustomerName and SalesOrderID only once then all payments made.
My query as follows returns 2 errors:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'ORDER'.
My query:
SELECT
Customers.CustomerName = case when rn = 1 then CustomerName else '' end,
SalesOrders.SalesOrderId = case when rn = 1 then SalesOrderId else '' end,
ProgressPayments.PaymentDate,
ProgressPayments.InvoiceValue AS PaymentMade,
SUM(SalesOrderItems.CurItemValue) AS BalanceOutstanding
FROM(
SELECT Customers.CustomerName, SalesOrders.SalesOrderId, ProgressPayments.PaymentDate, ProgressPayments.InvoiceValue AS PaymentMade,
SUM(SalesOrderItems.CurItemValue) AS BalanceOutstanding, rn = row_number() over(ORDER BY SalesOrders.SalesOrderId)
FROM ProgressPayments
INNER JOIN SalesOrders ON ProgressPayments.SalesOrder = SalesOrders.SalesOrder
INNER JOIN Customers ON SalesOrders.Customer = Customers.Customer
INNER JOIN SalesOrderItems ON SalesOrders.SalesOrder = SalesOrderItems.SalesOrder
GROUP BY Customers.CustomerName, SalesOrders.SalesOrderId, ProgressPayments.PaymentDate, ProgressPayments.InvoiceValue
ORDER BY SalesOrders.SalesOrderId
) AS r
order by rn
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 2, 2008 at 4:25 am
Try changing
Customers.CustomerName = case when rn = 1 then CustomerName else '' end
TO
case when rn = 1 then CustomerName else '' end AS CustomerName
Secondly, Remove the ORDER BY clause from the inner query.
.
October 2, 2008 at 5:03 am
Hi Jacob, many thanks for taking time to reply.
I have changed code but now receive different error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Payments.PaymentDate" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Payments.InvoiceValue" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "SalesOrderItems.CurItemValue" could not be bound.
SELECT
case when rn = 1 then CustomerName else '' end AS CustomerName,
case when rn = 1 then SalesOrderId else '' end AS SalesOrderId,
Payments.PaymentDate,
Payments.InvoiceValue AS PaymentMade,
SUM(SalesOrderItems.CurItemValue) AS BalanceOutstanding
FROM(
SELECT Customers.CustomerName, SalesOrders.SalesOrderId, Payments.PaymentDate, Payments.InvoiceValue AS PaymentMade,
SUM(SalesOrderItems.CurItemValue) AS BalanceOutstanding, rn = row_number() over(ORDER BY SalesOrders.SalesOrderId)
FROM Payments
INNER JOIN SalesOrders ON Payments.SalesOrder = SalesOrders.SalesOrder
INNER JOIN Customers ON SalesOrders.Customer = Customers.Customer
INNER JOIN SalesOrderItems ON SalesOrders.SalesOrder = SalesOrderItems.SalesOrder
GROUP BY Customers.CustomerName, SalesOrders.SalesOrderId, Payments.PaymentDate, Payments.InvoiceValue
) AS r
order by rn
I am quite new to SQL, can you advise what the following is doing:
rn = row_number() over(ORDER BY SalesOrders.SalesOrderId) +
AS r
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 2, 2008 at 5:23 am
Ok, I see a few more issues. I cannot run and test the code because I dont have your tables. So I am trying to suggest from what I see in the query.
Change:
Payments.PaymentDate,
Payments.InvoiceValue AS PaymentMade,
SUM(SalesOrderItems.CurItemValue) AS BalanceOutstanding
TO
PaymentDate,
InvoiceValue AS PaymentMade,
SUM(BalanceOutstanding) AS BalanceOutstanding
ROW_NUMBER() is used to generate a sequence number. You can find more about ROW_NUMBER() here:http://msdn.microsoft.com/en-us/library/ms186734.aspx
.
October 2, 2008 at 5:31 am
Hi Jacob, I appreciate it is difficult working blind. I made the change but now receive:
Msg 207, Level 16, State 1, Line 5
Invalid column name 'InvoiceValue'.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 2, 2008 at 5:41 am
Replace "InvoiceValue" in the outer query with "PaymentMade".
Here is how it works.
Your outer query (outside the brackets) can access only the columns selected by the inner query. The inner query renames "invoicevalue' to "PaymentMade" and hence "invoicevalue" is not visible any more.
.
October 2, 2008 at 5:42 am
check you have spelt it correctly
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 2, 2008 at 6:45 am
Hi guys, yes it was spilt correctly 🙂
New error after changing InvoiceValue to PaymentMade in the outer query
Msg 8120, Level 16, State 1, Line 1
Column 'r.rn' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I tried adding 'rn' to the GROUP BY but this returned invalid column name. r.rn returned 'The multi-part identifier "r.rn" could not be bound.'
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 2, 2008 at 7:41 am
Please post the script of your tables.
.
October 2, 2008 at 8:18 am
Here is the amended code that returns error:
Msg 8120, Level 16, State 1, Line 1
Column 'r.rn' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT
case when rn = 1 then CustomerName else '' end AS CustomerName,
case when rn = 1 then SalesOrderId else '' end AS SalesOrderId,
PaymentDate,
PaymentMade,
SUM(BalanceOutstanding) AS BalanceOutstanding
FROM(
SELECT Customers.CustomerName, SalesOrders.SalesOrderId, Payments.PaymentDate, Payments.InvoiceValue AS PaymentMade,
SUM(SalesOrderItems.CurItemValue) AS BalanceOutstanding, rn = row_number() over(ORDER BY SalesOrders.SalesOrderId)
FROM ProgressPayments
INNER JOIN SalesOrders ON Payments.SalesOrder = SalesOrders.SalesOrder
INNER JOIN Customers ON SalesOrders.Customer = Customers.Customer
INNER JOIN SalesOrderItems ON SalesOrders.SalesOrder = SalesOrderItems.SalesOrder
GROUP BY Customers.CustomerName, SalesOrders.SalesOrderId, Payments.PaymentDate, Payments.InvoiceValue
) AS r
order by rn
It must be something I am missing!
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 2, 2008 at 10:22 am
Hi,
I altered your query slightly (BLINDLY).
Please see if this produces the required results
SELECT
case when rn = 1 then CustomerName else '' end AS CustomerName,
case when rn = 1 then SalesOrderId else '' end AS SalesOrderId,
PaymentDate,
PaymentMade,
BalanceOutstanding
FROM(
SELECT
Customers.CustomerName,
SalesOrders.SalesOrderId,
Payments.PaymentDate,
Payments.InvoiceValue AS PaymentMade,
SUM(SalesOrderItems.CurItemValue) AS BalanceOutstanding,
row_number() over(ORDER BY SalesOrders.SalesOrderId) AS rn
FROM ProgressPayments
INNER JOIN SalesOrders ON Payments.SalesOrder = SalesOrders.SalesOrder
INNER JOIN Customers ON SalesOrders.Customer = Customers.Customer
INNER JOIN SalesOrderItems ON SalesOrders.SalesOrder = SalesOrderItems.SalesOrder
GROUP BY
Customers.CustomerName,
SalesOrders.SalesOrderId,
Payments.PaymentDate,
Payments.InvoiceValue
) AS r
order by rn
.
October 2, 2008 at 12:24 pm
Jacob that is perfect, what did you change?
Finally was my approach valid in constructing a query to use in a report?
Many Thanks,
Phil.
Update:
Jacob in answer to my own question my approach is flawed! The query executes but returns only the first Customer & SalesOrderId for ALL payments.
My intention was to return each Customer / SalesOrderId for each payment against that customer i.e.
Customer..................SalesOrderId..............PaymentDate................Payment
Customer A................12345......................01/01/2008...................£100
.............................................................25/02/08.......................£500
Customer B................123456.....................30/03/08.......................£1500
..............................................................30/04/08.......................£1000
..............................................................30/05/08.......................£10000
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 2, 2008 at 1:01 pm
Philip Horan (10/2/2008)
Jacob that is perfect, what did you change?Finally was my approach valid in constructing a query to use in a report?
Many Thanks,
Phil.
Eeeeh, the responses coming up from SQL Server MVP, and they have the power (Solution Must Find) not just trying to find it! 😎
October 2, 2008 at 1:07 pm
Philip -
Jacob was very close. If you want each customer and sales order, then change the ORDER BY to a PARTITION BY.
Change is in bold in Jacob's code below.
SELECT
case when rn = 1 then CustomerName else '' end AS CustomerName,
case when rn = 1 then SalesOrderId else '' end AS SalesOrderId,
PaymentDate,
PaymentMade,
BalanceOutstanding
FROM(
SELECT
Customers.CustomerName,
SalesOrders.SalesOrderId,
Payments.PaymentDate,
Payments.InvoiceValue AS PaymentMade,
SUM(SalesOrderItems.CurItemValue) AS BalanceOutstanding,
row_number() over(
PARTITION BY CUSTOMER,SalesOrders.SalesOrderId
) AS rn
FROM ProgressPayments
INNER JOIN SalesOrders ON Payments.SalesOrder = SalesOrders.SalesOrder
INNER JOIN Customers ON SalesOrders.Customer = Customers.Customer
INNER JOIN SalesOrderItems ON SalesOrders.SalesOrder = SalesOrderItems.SalesOrder
GROUP BY
Customers.CustomerName,
SalesOrders.SalesOrderId,
Payments.PaymentDate,
Payments.InvoiceValue
) AS r
order by rn
[/quote]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 2, 2008 at 1:16 pm
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply