asked on

Invalid column name 'paymentdate'.

this works with when o.orderdate

select o.dateordered,o.company,o.orderid,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,o.email,o.itemid as oitemid ,p.cost1,p.cost2,p.cost3,p.productid,p.title as ptitle ,e.title as etitle ,s.status ,i.sku,i.quantity,i.itemid as iitemid ,CASE sku WHEN p.internalsku1 THEN p.cost1*i.quantity WHEN p.internalsku2 THEN p.cost2*i.quantity WHEN p.internalsku3 THEN p.cost3*i.quantity ELSE 0 END as cost ,(select sum(subtotal) from orderitems where orderid=o.orderid)as subtotal ,(select SUM(debit) from payments where orderid=o.orderid and ( (type='Paypal' and confirmation!='' and resultcode!='Denied' and resultcode!='unconfirmed') or (type!='Paypal') ) )as debit ,(select count(*) orderid from payments where orderid=o.orderid) as paymentcount ,(select SUM(case when isnumeric(paymentfee)=1 then cast(cast(paymentfee as float) as decimal(18,2)) else 0 end) from payments where orderid=o.orderid) as paymentfee , (select top 1(type) from payments where orderid=o.orderid) as realtype ,(select case when min(type) is null then 'none' when min(type) = max(type) then min(type) else 'multiple' end from payments where orderid=o.orderid) as type ,(select top 1(email) from payments where orderid=o.orderid) as paymentemail

,(SELECT ISNULL((SELECT MAX(dateentered) FROM payments WHERE orderid = o.orderid),(SELECT TOP 1 dateordered FROM orders WHERE orderid = o.orderid))) AS paymentdate

,(select min(itemid) from orderitems where orderid=o.orderid)as minitemid from orders o left JOIN orderitems i on i.orderid = o.orderid left JOIN products p on i.productid = p.productid left join ebaydata e on o.orderid = e.orderid left join sstatuses s on o.statusid=s.statusid where (o.orderdate between '03/21/2011' and '03/29/2011') and o.statusid in (10,90,110,260,7,130) order by o.orderid

but I want to use derived column paymentdate

select o.dateordered,o.company,o.orderid,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,o.email,o.itemid as oitemid ,p.cost1,p.cost2,p.cost3,p.productid,p.title as ptitle ,e.title as etitle ,s.status ,i.sku,i.quantity,i.itemid as iitemid ,CASE sku WHEN p.internalsku1 THEN p.cost1*i.quantity WHEN p.internalsku2 THEN p.cost2*i.quantity WHEN p.internalsku3 THEN p.cost3*i.quantity ELSE 0 END as cost ,(select sum(subtotal) from orderitems where orderid=o.orderid)as subtotal ,(select SUM(debit) from payments where orderid=o.orderid and ( (type='Paypal' and confirmation!='' and resultcode!='Denied' and resultcode!='unconfirmed') or (type!='Paypal') ) )as debit ,(select count(*) orderid from payments where orderid=o.orderid) as paymentcount ,(select SUM(case when isnumeric(paymentfee)=1 then cast(cast(paymentfee as float) as decimal(18,2)) else 0 end) from payments where orderid=o.orderid) as paymentfee , (select top 1(type) from payments where orderid=o.orderid) as realtype ,(select case when min(type) is null then 'none' when min(type) = max(type) then min(type) else 'multiple' end from payments where orderid=o.orderid) as type ,(select top 1(email) from payments where orderid=o.orderid) as paymentemail

,(SELECT ISNULL((SELECT MAX(dateentered) FROM payments WHERE orderid = o.orderid),(SELECT TOP 1 dateordered FROM orders WHERE orderid = o.orderid))) AS paymentdate

,(select min(itemid) from orderitems where orderid=o.orderid)as minitemid from orders o left JOIN orderitems i on i.orderid = o.orderid left JOIN products p on i.productid = p.productid left join ebaydata e on o.orderid = e.orderid left join sstatuses s on o.statusid=s.statusid where (paymentdate between '03/21/2011' and '03/29/2011') and o.statusid in (10,90,110,260,7,130) order by o.orderid

Invalid column name 'paymentdate'.

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

nativ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rgb192

ASKER

where is cte
what does that mean

nativ

CTE: Common Table Expression
In Sharath's example she put your entire query into a sub query and then put a select from the sub query.
The way CTE works is that I defined t1 to be your entire query:
with t1 as (.....)
and then I did the query on t1
select * from t1
WHERE paymentdate BETWEEN '03/21/2011' AND '03/29/2011'
ORDER BY orderid

t1 is sort of like a temporary table which can be used directly after where it was defined.
(after that it ceases to exist)

I am not sure if there will be any performance difference between that in Sharath's sub query example.
I think it is just easier to ready (once you know how to read it :) )

rgb192

ASKER

both worked, thanks