Incorrect syntax near 't1'.
selecT * FROM ( select o.dateordered,o.company,o.
when I add ' (' after status id and ) or o.orderid like '%5678%' before order by orderid i get error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 't1'.
selecT * FROM ( select o.dateordered,o.company,o.
when I add ' (' after status id and ) or o.orderid like '%5678%' before order by orderid i get error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 't1'.
selecT * FROM ( select o.dateordered,o.company,o.
ASKER
t1 is the entire query
so I can put paymentdate (derived) as part of the where statement
so I can put paymentdate (derived) as part of the where statement
try 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 MIN(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 ( 0, 0, 0, 0,
0, 0, 0 )
AND ( 1 = 0
OR 1 = 0
OR 1 = 0 )) t1
WHERE ( paymentdate BETWEEN '04/26/2011' AND '05/04/2011' )
OR orderid LIKE '%5678%'
ORDER BY orderid 
try this (but query looks so ugly :)
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 Min(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 (0,0,0,0,0,0,0) and ( 1=0 or 1=0 or 1=0 ) )
) t1
where (paymentdate between '04/26/2011' and '05/04/2011') or o.orderid like '%5678%'
order by orderid
first of all you dont need outer select
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 Min(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 (0,0,0,0,0,0,0) and ( 1=0 or 1=0 or 1=0 ) )
and
(paymentdate between '04/26/2011' and '05/04/2011') or o.orderid like '%5678%'
order by orderidASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
second, this query should be re-written from scratch...
ASKER
invalid column 'paymentdate'
select o.dateordered,o.company,o.
(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')
(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 Min(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 (0,0,0,0,0,0,0) and ( 1=0 or 1=0 or 1=0 ) )
and
(paymentdate between '04/26/2011' and '05/04/2011') or o.orderid like '%5678%'
order by orderid
no results
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 MIN(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 ( 0, 0, 0, 0,
0, 0, 0 )
AND ( 1 = 0
OR 1 = 0
OR 1 = 0 ) )
OR o.orderid LIKE '%5678%') t1
WHERE ( paymentdate BETWEEN '04/26/2011' AND '05/04/2011' )
ORDER BY orderid
invalid column 'paymentdate'
--> try this : 35515703
you need a wrapper select to filter on this column :)
--> try this : 35515703
you need a wrapper select to filter on this column :)
try the query that I posted @ 35515703 ^^^
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
do you have multiple records in payments table per order id?
if yes,
email column is repeated and same in all rows?
type column is repeated and same in all rows?
if these are same for an order then they should go into order table not payments table...
if yes,
email column is repeated and same in all rows?
type column is repeated and same in all rows?
if these are same for an order then they should go into order table not payments table...
Did you try my suggestion?
ASKER
these work thanks
what is this t1