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'.
LVL 1
rgb192Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
try like this.
SELECT * 
    FROM (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.statusid IN (10,90,110,260, 
                                7,130)) t1 
   WHERE paymentdate BETWEEN '03/21/2011' AND '03/29/2011' 
ORDER BY orderid

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nativCommented:
The following is not different then what Sharath wrote - it is just written differently which I think looks better. It uses CTE.

with t1 as (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.statusid IN (10,90,110,260, 
                                7,130)) 
select * from t1 
   WHERE paymentdate BETWEEN '03/21/2011' AND '03/29/2011' 
ORDER BY orderid

Open in new window

0
rgb192Author Commented:
where is cte
what does that mean
0
nativCommented:
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 :) )
0
rgb192Author Commented:
both worked, thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.