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?
 
SharathConnect With a Mentor Data 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
 
nativConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.